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

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/

mvp_badge
MVP

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. 

mvp_badge
MVP

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.

mvp_badge
MVP

Adding some details:

The structure Test:


And in the Feedback message:

SyntaxEditor Code Snippet

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

Hello Harlin,

Are the two SQL nodes executed in the same database session ? How would this work in a high traffic environment ?

I would like to call a stored procedure that inserts a record in a non-outsystems related table for downstream processing and return the id of the row inserted. 

Your example is great and works in my test environment, but I am concerned how it would work if we have multiple sessions performing this activity.

-Berton



Yes, it's executed in the same database session, otherwise the second SQL node can't read the first SQL package variables.

About performance, I cannot answer that.

Maybe you can ask Outsystems support. 

Harlin,

Thank you for your response, I am not concerned about performance, I should have used the word "executing" instead of "performing" to keep things simple.


Hi, Harlin Setiadarma!

Do you think is there any chance to perform something like this?

It's not working btw, get error executing query on SQL3...

Thanks!


Already found the solution. 

Removed the semicolon on the query to the Dual and set the proper data type on the structure field.

Thanks anyway :)

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