OutSystems and PL/SQL

OutSystems and PL/SQL

  
Hello, I am new to OutSystems...
I was wondering:
1) If my back end database is Oracle and I want to execute PL/SQL, how would I setup the calling the procedure from an Action in Service Studio?
2) It seems the deployment DB in the OutSystems cloud is .Net and SQL Server? If so, would I have issues if I later want to move my OutSystems application from a Cloud Instance to an On-Premise environment which is running WebLogic and Oracle DB ?
Thank you!!!
1) Use an Advanced Query.

2) Just deploy to the new environment. The big place you will have an issue is in any extensions that may have a .NET implementation but not a Java implementation, so examine your extensions carefully. Advanced queries will be an issue if they are using an SQL Server specific code.

J.Ja
THANK YOU.
My question #1 was: when in the Cloud offering, how do I have access to say a sqlplus prompt where I can CREATE a PL/SQL which I can later invoke from the Advanced Query? Do I have direct access to the Oracle DB so I can create the necessary PL/SQL code? 
Thanks again for replying on a Sunday!
Claudio -

Not a problem!

The Cloud offering, the last I heard, does NOT offer direct DB acess. It is SQL Server, not Oracle.

A couple of alternatives that have come up:

1. Use the "Test Query" in Service Studio to send commands to the DB.

2. Write a page that lets you write queries and send them to the DB.

3. Do things in an Extension.

J.Ja
Hi Claudio

about question 1: you can call pl/sql function using a advanced query like 'select <pl/sql-function> from dual'
If you want to use in/out parameters or if you want to call pl/sql procedures, I think you should take a look at the oracle-connecter extenstion. But, be aware, for now, it's only available for the .Net Server to use pl/sql  in an oracle database.

about question 2: as Justin says: just deploy the environment and if you use extensions, check if they are supported by both stacks. 

Hans


Hi everyone,

Let me clarify what is available in the OutSystems Enterprise Cloud offer:

  • You can choose your preferred SDK: .NET or JEE. If you choose .NET, your database options are Oracle and SQL Server. If you choose JEE (JBoss only), the database has to be Oracle.
    Claudio, if you were to choose the JBoss + Oracle configuration, you shouldn’t have any problems moving your code to your on-premises WebLogic + Oracle environment.
     
  • Regards, direct access to the database, in the cloud: we give you this access. You can use the tool of your choice (SQL*Plus, TOAD, Oracle SQL Developer, etc.).
    The DB user we give you has the following permissions:

    Read and Write for your applications’ tables. This allow you to delete data in a development table or create and run Extract, Transform and Load (ETL) processes to load data from files or external data sources - e.g. to extract and load Production data to the Test environment.
    Read (not Write) for Platform metamodel tables. This can be helpful in advanced scenarios.

Because having DB objects, not controlled by OutSystems, would impact our troubleshooting processes and the way we can guarantee our infrastructure's 99,9% availability, we don’t allow users to create database logic directly in the database, such as stored procedures.

As Justin mentioned, to implement advanced database logic, the best way is through "Advanced Queries" (or Extensions). If this is not feasible, and you really need to reuse stored procedures, you can create them in an external database and have the Platform integrate with this external DB, or alternatively, use the on-premises version of the Platform, where your control over the Platform's database is total.
Thank you for the responses!
Sometimes pl/sql is the only way you can affect records based on other records and one has to travel thru the table sequentially ... 
So I understand correctly that pl/sql procedures are not allowed in the cloud environment ?
Thanks! 
Hi Claudio,

You can use PL/SQL commands using the “Advanced Query” element:



In an "Advanced Query" you can write the PL/SQL commands you want and you can even use special placeholders for the tables/columns names, so even if you change your database or domain model (e.g. change a table column name), the query is automatically refactored.

What you can't do, in the cloud environment, is store the PL/SQL procedure beforehand (stored procedure), in the database where the Platform is running.
Arlindo -

Ah, yes, I thought he was asking about the "personal environment" (which tons of people are calling "the cloud" too). I should have asked!

J.Ja
That's a good point Justin...
I assumed we were talking about the paid Enterprise Cloud offer, but that might not be the case. :/

@Claudio, are you referring to the free personal environment (in the cloud) or are you referring to the Enterprise/ paid offer of the OutSystems Platform?
Hmmm ... I am new so I wanted to generally understand how someone would CREATE a PL/SQL and injected into the Oracle DB running in the background of the personal environment I am testing/learning from ...
I *think* I am getting conflicting answers: I thought you (Arlindo) said for performance reasons PL/SQL procedures are not allowed but now you are saying that I can RUN PL/SQL from the Advanced Query but how would I CREATE it into the schema in the first place?
THANK YOU all for all this attention ... I am impressed with how responsive everyone is!!!


Arlindo Lima wrote:

Let me clarify what is available in the OutSystems Enterprise Cloud offer:

  • You can choose your preferred SDK: .NET or JEE. If you choose .NET, your database options are Oracle and SQL Server. If you choose JEE (JBoss only), the database has to be Oracle.
     
 
Interesting Outsystems Platform .NET stack, now supports both SQL Server and Oracle? (Only in cloud?) or also on promises?
Claudio Lichtenthal wrote:
I *think* I am getting conflicting answers: I thought you (Arlindo) said for performance reasons PL/SQL procedures are not allowed but now you are saying that I can RUN PL/SQL from the Advanced Query but how would I CREATE it into the schema in the first place?
Claudio,
In our Enterprise cloud offer and personal environments, we don't allow the creation of PL/SQL procedures because a DB user with privileges to create stored procedures would always have the “power” to mess with internal tables the Platform depends on. It is also harder/slower to troubleshoot problems caused by misbehaved stored procedures than it is to troubleshoot problems that arise from logic developed in the Platform. And we need to be quick troubleshooting problems, if we’re to offer a 99,9% of guaranteed service availability.
That is the reason. It has nothing to do with performance. Custom SQL queries or procedures don't affect the performance of the Platform.

So, to clarify, you can’t create PL/SQL procedures/functions in the database where your personal environment is running (or Enterprise cloud if you were a customer), but you can execute PL/SQL commands using an anonymous block, as anonymous blocks are not stored in the database.

Let me additionally clarify that the free personal environment is only available in .NET + SQL Server. If, later, you were to move your application to a WebLogic + Oracle environment, as Justin and Hans mentioned you would have to verify if you’re using SQL Server specific code and if you’re using .NET-only Extensions.


Robert Chanphakeo wrote:
Interesting Outsystems Platform .NET stack, now supports both SQL Server and Oracle? (Only in cloud?) or also on promises?
Hi Robert,
We support SQL Server + Oracle, in the .NET stack, as long as I remember. :)
At least since 2003 (SQL Server 2000 and Oracle 9i, back then).
And yes, both on premises and in the cloud.