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

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

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