Stored Procedures in the OutSystems Cloud

Stored Procedures in the OutSystems Cloud


The OutSystems Cloud runs on Java or .NET. The Java version supports Oracle as a DBMS, and in the .NET version you can choose between Oracle and SQL Server.

In the OutSystems Cloud you have direct access to the database, so you can use your favorite tools, like Oracle SQL Developer, Microsoft SQL Management Studio, TOAD, or others.

To ensure high availability for your environments, the following permissions are granted:

  • Read and Write for application's tables;

  • Read for the OutSystems Platform metamodel tables.

This means that it’s possible to:

  • Add indexes to the tables directly in the OutSystems Platform;

  • Perform CRUD operations on the data;

  • Run ETL processes.

The only thing that is not supported, is creating custom database object, such as stored procedures or functions.

This ensures that all operations necessary to deploy an application can be done using the OutSystems Platform. If you could create stored procedures, you would have to manually create deployment scripts to ensure those procedures were on the destination environment once you deployed an application.
If you forgot to do so, your applications would be left with runtime errors.

Since it’s not possible to create stored procedures, you’ll need another way to execute that logic using the OutSystems Platform.
There are three options you can use:

  • Server-side logic;

  • Advanced Queries;

  • Extension modules;

Use Server-Side Logic

Most times, it's possible to not use stored procedures at all. If you are using stored procedures to perform computation that doesn’t use any database data, then you can easily implement the same logic in Service Studio.

Some examples are generating random numbers, performing validations, or some complex arithmetic.

In these scenarios, it's not only easy to implement the same logic using server-side logic, but also:

  • It's easier to debug and change;

  • It centralizes your business logic in a single place;

  • It scales horizontally. Since the logic runs on the front-end server, if you have multiple front-ends, then only one will be busy doing the computation. If you implement the same logic as a stored procedure, all front-ends need to wait for the database to perform the computation.

Use Advanced Queries


If your logic needs to process data from the database in a business agnostic way, then consider using Advanced Queries (standard SQL) instead of stored procedures.

Examples of this, is when you simply need to aggregate data in a single table and use plain SELECT, INSERT, UPDATE statements.

To ensure you write once and reuse often, implement several Advanced Queries in the same eSpace module, and turn that module into an API that encapsulates the logic you need.

This makes the logic:

  • Easier to reuse;

  • Easier to change;

Use Extension modules

If you really need to use some advanced functionality that is not supported by Advanced Queries, like using PL-SQL/T-SQL statements such as BEGIN, END, CATCH, THROW, you can always implement an Extension module that:

  • Connects to the database;

  • Fetches the data;

  • Returns a Record List;

You can turn this Extension Module into an API that encapsulates all the logic, and abstracts the complexity of interacting with the database.

After developing the Extension module, you can reuse it in Service Studio in a transparent way.

Hello João,

we're trying to write an extension that we can use to call an Oracle Stored procedure that has 1 output parameter that is defined as SYS_REFCURSOR. The procedure returns a resultset with 0,1 or more rows. Each row has a couple of attributes like an id, a code, a description, a creation date etc. We can't use the Oracle Connector since that is not available for the Java stack. The functionality that we want to use is not supported by Advanced Queries.

The question is: do you of an example that we can use to fetch the records via a stored procedure call?
The first paragraph in this post says that we can use common database tools to access our cloud database.  I'm assuming this also applies to the personal environment.  I've searched high and low for the actual instuctions on how to do this but haven't found anything.  What do we use for the server name, authentication type, etc.?  Any help is appreciated.

Hi Curt,

I'm afraid that accessing the underlying database is not applicable to the OutSystems Platform Personal Environment. Please see the Personal Environment - FAQ for further details, specifically points 3.7 and 3.8

Saves me writing this question! Been searching since the start of training how to use Toad / Squirrel to interrogate the data as this is always my first step...but I am using a personal envrionment...