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:
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.
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;
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.