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

[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:

http://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