[How To] Call Oracle Stored Procedure with outputs using Advance Query/SQL

Just encapsulate your stored procedure inside a package, and store outputs on package variable.

Create 2 SQL node, 1 to execute it, 2 to select package variable.


Here's my sample, I have test it and it works...

create or replace package test_os_pkg as
  result1 varchar2(100);
  result2 varchar2(100);
  
  procedure convertcase(input varchar2);
  function getresult1 return varchar2;
  function getresult2 return varchar2;
end;
/
create or replace package body test_os_pkg as
 
  procedure convertcase(input varchar2) as
  begin
    result1 := lower(input);
    result2 := upper(input);
  end;  
  
  function getresult1 return varchar2 as
  begin return result1; end;
  
    function getresult2 return varchar2 as
  begin return result2; end;
end;
/




I use External Database, so the need for setting %LogicalDatabase%

Hope it helps...

A friend from other post gave a tip on how to access package variable without the need of getter function... 

Thanks Peter... 


Peter Areewatanakul wrote:

Hi Harlin,

Thank you so much for helping. It works!

I also found a link which explains about how to create a general "getter" function to get a package variable

from any package. Here is the link if anyone is interested:

https://www.talkapex.com/2010/03/how-to-reference-package-variables/

Is this still working?

I'm trying to call an Oracle function but either way I get 'Invalid identifier', or a 'Command not properly ended' errors.

When I call within an Oracle client it works.

I've tried 2 ways:

/* %LogicalDatabase%=GetLogicalDatabase({BD_Riesgo}) */
SELECT *
  FROM GET_CODE(1) from DUAL


SyntaxEditor Code Snippet

/* %LogicalDatabase%=GetLogicalDatabase({BD_Riesgo}) */

SELECT *
  FROM table(GET_CODE(1))


Outsystems 9.0 with Java.

Your syntax is incorrect...

It should be:

/* %LogicalDatabase%=GetLogicalDatabase({BD_Riesgo}) */
SELECT GET_CODE(1) from DUAL

Thanks for the sample code, it works as intended. Just wanted to mention that the advanced query test will have an error if you try and run it. 

Hi Harlin, 

Thank you so much for your example. 

Here works fine! 

It's missing a documentation about this integration with Oracle + Store Procedure.


Best Regards,

Leandro.

Adding some details:

The structure Test:


And in the Feedback message:

SyntaxEditor Code Snippet

SQL2.List.Current.Test.result1 + " " + SQL2.List.Current.Test.result2