How to map Oracle function output params and return value to a structure

Hello guys! I'm having some issues using an external Oracle function. The function definition says that i have 4 output params and 1 return value (as you can see in the screenshot).


I've tried this with advanced query using the LogicalDatabase command to get the correct database connection.

Ex:

SyntaxEditor Code Snippet

"/* %LogicalDatabase%=GetLogicalDatabase({ExternalEntity}) */
declare
myvar varchar2(2000);
p_FirstDay date;
p_LastDay date;
p_MessageType varchar2(2000);
p_ReturnMsg varchar2(2000);
begin
    myvar := test('','',p_FirstDay,p_LastDay,p_MessageType,p_ReturnMsg,'1'); 
end;"

This works but without mapping to the final output structure. I've tried also to insert into an outsystems local entity but without success (different database schemas).


Also tried the forge component OracleConnector without success (i think that the issue is having 4 out params and 1 return value, but not sure about this).


Any suggestion?!

Thanks,

Diogo Pacheco

Hallo Diogo,

using a function like this in advanced sql will not work for you because the variable myvar will not be used to fill a text attribuut in your output structure.

same is true for output parameters that you are using in your pl/sql code.

the pl/sql code is all a black box for os.

what you can do with functions is using them in sql statements, but than only one return value is allowed.

the oracle connector can handle functions but only with simple return values and without out parameters.

i agree with you that it would be handy if it was possible to map returns and output paramters of oracle functions to variables in os in a transparent way.

But out parameters in a function is also maybe not best practice..

Regards, Sven


ps: using oracle procedures in this way will work fine by the way.

if you want to make changes in the database by calling a procedure in advanced sql, the changes will be done.

Thanks Sven! We made it work using an extension that we coded! Thanks anyway! 

Diogo Pacheco wrote:

Thanks Sven! We made it work using an extension that we coded! Thanks anyway! 

Hallo Diogo

if you made an extension that can handle all the function output that you needed maybe you can share it in the forge. I think this will be used by developers.

Regards, Sven