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

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

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!

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

Not sure why I got error as below:

I found a way where no extra coding in the Oracle database is needed.
This can be helpful if there is no authorization to create database objects.

• Create an external entity for DUAL. 
  This will guide the SQL to the right database/connection.
• Create advanced SQL, where the wrapper on the procedure call is in a WITH part:
  
  WITH FUNCTION
    RunProcedure
     ( p_type   VARCHAR2  
     , p_value  VARCHAR2  
     )
  RETURN VARCHAR2
  IS 
    l_result1 varchar2(50);
    l_result2 varchar2(50);
    l_return  varchar2(250);
  BEGIN  
    DO_PROCEDURE  -- The procedure to be called
      ( p1 => p_type
      , p2 => p_value
      , p3 => l_result1
      , p4 => l_result2
      );  
    l_return := ‘{“result1”: “’ || l_result1 
                || ‘“, ”result2” : “’ || l_result2
                || ‘“}’;
   RETURN (l_return);
  END;
  --
  SELECT RunProcedure (@InType, @InValue)
  FROM {DUAL}
  
  
• Use JSON-Deserialize to extract the OUT-parameters 

Thank you Harlin Setiadarma. Finally this solution solved my problem after getting stuck on it for a very very long time. It looks like the Oracle Connector P11 has a bug does not read stored packages output all the time.