Calling a Function on an external Oracle DB / Getting Return Value of the Function
Question

Hi!

Can anybody tell me how to get the Return-Value of an Oracle Functiion on an external DB?

I don't want to use the workaround with the Package Variables as described in 

https://www.outsystems.com/forums/discussion/21999/how-to-call-oracle-stored-procedure-with-outputs-using-advance-query-sql/

The reason is, that Package Variables are session variables and you run into problems when there are parallel calls of the function because there's just one session.

Here is my example:

-- =======================================================================

-- Package with a Function

-- =======================================================================

create or replace

PACKAGE PACKAGE_TEST_FUNCTION

IS

function function_concat_strings(string1 in VARCHAR2, string2 in VARCHAR2) return varchar2;

END PACKAGE_TEST_FUNCTION;

/

create or replace

PACKAGE BODY PACKAGE_TEST_FUNCTION AS

function function_concat_strings(string1 in VARCHAR2, string2 in VARCHAR2) return varchar2

is begin return string1 || string2; end function_concat_strings;

END PACKAGE_TEST_FUNCTION;

/

-- =======================================================================

-- Grant Execute on the Function

-- =======================================================================

GRANT EXECUTE ON PACKAGE_TEST_FUNCTION TO [....];

-- =======================================================================

-- Test the Function in Oracle

-- =======================================================================

set serveroutput on;

declare

result varchar2(100);

begin

result := INFRA.PACKAGE_TEST_FUNCTION.function_concat_strings('Out','Systems');

SYS.Dbms_Output.Put_Line('Function returns: ' || result);

-- Function returns: OutSystems

end;

-- =======================================================================

Test the Function in Outsystem with the Advanced Query:

The function is executed correctly but I'm not able to catch the Return-Value.

Any help is appreciated

Regards Alex

mvp_badge
MVP

Hi Alex,

I'm by no means an Oracle expert, but wouldn't you add a "SELECT result" after the "end;"?

Hi Kilian .. thanks for your post!


I already tried this and many many more variants ... before and after the "end;"

e.g. "select result from dual; "


If it's after the "end;" it's not even recognized an sql statement:

"Error in advanced query: SQL1: ORA-06550: line 7, column 1: PLS-00103: Encountered the symbo "SELECT""


If it's before the "end;" the error is:

"Error in advanced query: SQL1: ORA-06550: line 5, column 3: PLS-00428: in INTO clause is expected in this SELECT statement"


If I change it to "select result into v from dual;" there's no error but also no return value. (v is just a dummy variable).


Regards Alex


Hi Alex,

Any luck with your problem?
I'm with some similar problem... :)

Hi Luis, 

Unfortunately no.

Regards Alex

Hi Luis,

Outsystems can't handle output-parameters.
Therefor the construction with a function is used.
The simplest way is to create an entry for dual in integrationstudio. If Dual can't be selected, the entry can be made by hand.
By havind dual available, the construction with %LogicalDatabse .... isn't needed.


So the syntax in the advanced-query will be:
I'm not sure if it's allowed to also have the schema-name in front of the package, whereby the constrution has 3-levels (so use 'PACKAGE_TEST_FUNCTION.function_concat_strings' instead of 'INFRA.PACKAGE_TEST_FUNCTION.function_concat_strings')

Hi Eric,

This an Oracle DB that I don't have direct access.
I have an extension connected to the DB, and get this instruction to execute this code, the order number is passed by reference.

In fact the SP is executed and a new order is created, but the output structure is not filled.

If I add "SELECT ORDER_NUM" before or after END it returns "Error executing query."

I builded another query, right after the SP call, to select DUAL but only get an 'X'.. :)

Do you think is there any change to get the order number?

Build something on C# is not discarted, but I'm curious if there's a solution to use advance query with Oracle.

Thanks a lot!

Hi Luis,

I don't completely understand what is needed and what the Oracle-components are that need to be used.

Because the 'select * from dual' gives the right result, I expect the database-connection is working.
If you run this statement in Oracle-tooling you will get the same result: 'X'.

So I expect it will also be possible to execute Oracle-code within the advanced Sql.
But I don't understand what you intend to do with the code you constructed.

As mentioned in other posts, it's (as far as I know) not possible to get output directly from Oracle-procedures in advanced SQL.
Only input-parameters on procedures/functions can be used.
That's why the construction with package-variables and a retrieving function is needed.

And dbms_output is of no use, if the output is not retrieved and is therefor of no use within advanced SQL.

What is the definition of the procedure spcreate_order. The owner of this procedure needs to give you more detailed information; not just technically, but also functional on how this code must be used.
To me it looks like the procedure has 1 input parameter that's filled with the text-string 'param1' and 1 out parameter that will return an Ordernumber.
As mentioned before it's not possible to use output parameters, so another solution is needed.
If it's not possible to create a wrapper as mentioned in the first entry of this item, the Oracle-connector from the forge can be used to run a procedure in the Oracle-database.

A year ago I inserted an idea to be able to use Oracle-code directly in Outsystems: https://www.outsystems.com/ideas/6578/use-integration-studio-to-generate-actions-on-oracle-procedures-functions-and-pa
Given the recent activities on this item in the forum I still think this is a good idea.


Hi, Alex!

After Eric tips and since I didn't had any experience with Oracle, I investigated more, readed this post  and with some work in C# (to get the Oracle DB errors) I found a solution, at least for me :)

Like Eric said, the idea is to create a package with a function and then call it with dual for the win.

First specify the package:

Then the body

And for the end, query the function


This was a simple function to test and check that work.

To get my job done, it was needed input parameters and a call to a Stored Procedure.

Package structure with a function with 2 input parameters and a var that will be the output result.

The package body that call the stored procedure.

And then query the function

Hope it helps :)

Thank a lot Eric for the tips :)


Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.