Execution Oracle Stored Procedures and Functions

Execution Oracle Stored Procedures and Functions

  
Is it possible to execute Oracle stored procedures and/or functions in the advanced query?

If so, how can i execute them?

Thank you in advance
There should be no problem in doing that.

The syntax is exactly the same as in Oracle SQL (see http://www.ss64.com/ora/call.html).

If you have any other problems please don’t hesitate to ask.
i have the same ptoblem,any example?
Hi Filipe,

Yes, it is possible, but take in consideration that the best way to do this would be to use an extension, like the OracleConnector from the Forge. If your installation is running on Oracle, it is as simple as executing the stored procedure in an advanced query:

BEGIN
  PCKG_NAME.SP_NAME;
END;
Where PCKG_NAME is the package name, and SP_NAME the name of the stored procedure you want to run.
In this case the stored procedure does not have any parameters, but if it did, you would also call it as you would in regular PL/SQL.

If your installation is running on SQL Server, and you are executing a stored procedure from an external database, you have to do some additional steps to make sure the platform uses your Oracle connection on your advanced query, making this method not the best way to execute stored procedures:

1. Create an Outsystems extension (Integration Studio) and import a table from your Oracle database connection (that you previously set up on Service Center). 
2. Publish the extension, and import the table reference to the espace where you are planning on executing the stored procedure via advanced query.
3. Create the Advanced Query, and add the imported Oracle table to the output structure of the advanced query. (Note that if you want to use output parameters from the stored procedure into your output structure this is not enough.This is simply to retrieve the proper db connection.)
4. Paste this on the advanced query text:
/* %LogicalDatabase%=GetLogicalDatabase({ORACLE_TABLE_NAME}) */
BEGIN
  PCKG_NAME.SP_NAME;
END;
Where ORACLE_TABLE_NAME is the table that you imported, and included in the Output Structure.

As I said earlier, this is not the way to go if you want to work with Oracle stored procedures and functions, and you really should be using an extension to do this.

Best regards,
Tiago Martins
Hi,

Did you see the Oracle Connector in the Forge?
http://www.outsystems.com/forge/Component_Detail.aspx?ProjectId=392&ProjectName=oracle-connector

Regards,

Nuno
Thanks for providing the direct link Nuno. I had indeed mentioned the Oracle Connector to the previous posters as the best way to accomplish this, even though it was not what the OP was asking for. But take in consideration that the Oracle Connector from the forge is not yet complete (e.g. It does not allow for the execution of stored procedures that use Oracle object types).

Best regards


"3. Create the Advanced Query, and add the imported Oracle table to the output structure of the advanced query. (Note that if you want to use output parameters from the stored procedure into your output structure this is not enough.This is simply to retrieve the proper db connection.)" By Tiago Martins

I need to use the OutputParameters of my Stored Procedure in my logic, witch means that i need them at the Output of the Query, how i acomplish that (Im using version 6.0 os Service Studio, so i can't use Oracle Connector)?!

Best Regards,
Pedro Domingues