Not able to get Oracle stored procedure return value passed to Powerbuilder

[Former Member]

I have an Oracle Stored Procedure that receives a string value and returns a string value..  When I call it from Powerbuilder, it executes but does not send the return value of a string back.  I am trying to encrypt a string in Powerbuilder, pass it to a .net web page and then decrypt so I can check the security tables for permissions to view the web page.  I have tried creating a simple stored procedure that takes a string and returns a string to Powerbuilder; but even that is not working.  Any suggesstions?

Oracle Procedure:

CREATE OR REPLACE
PROCEDURE               TESTINOUT_VARCHAR
( P_STRING IN VARCHAR2, P_OUT OUT VARCHAR2)
IS

   BEGIN  
      P_OUT := P_STRING || 'TESTING';

END TESTINOUT_VARCHAR;

Powerbuilder Call:

string p_string ='                                 '

Declare TestingString procedure for TESTINOUT_VARCHAR(:ls_group, :p_out) using SQLCA

Execute TestingString;

IF SQLCA.CODE = 0 THEN

FETCH TestingString into:p_out;

End If

p_string = p_out;

[CobyKako · Accepted solution]

Hello John,

Could that help:

Oracle DECLARE and EXECUTE

P.S: usually, you need to pre-allocate memoy spaces for output string arguments (p_out) :

p_out =SPACE (x)

P.S: it is a best practice to use RPC calls rather than DECLARE PROCEDURE statement

HTH

Jacob

[Former Member · Accepted solution]

Hello, finally got it to work using -SUBROUTINE TESTINOUT_VARCHAR(  string  Ls_In, ref string ls_out) RPCFUNC alias for "schema.testinout_varchar"- in local external functions for a newly created transaction object and changed the parameters to have an in and out instead of just inout.

Thanks everyone for your help on this.

[CobyKako · Accepted solution]

Glad to read it works for you John !!!

P.S.: Correct answer was in fact the below one I've posted at Aug 28, 2014 12:41 AM

[Former Member]

Hi,

Without using RPC and declaring the procedure in SQLCA you can do it by fetching the output parameters:

Oracle Procedure declaration with 2 IN and 2 OUT parameters:

        PROCEDURE   p_test(pn_test IN number,

                                           pv_test IN varchar2,

                                           pn_return OUT NUMBER,

                                           pv_message OUT varchar2);

Powerbuilder code to execute the procedure and fetch the output parameters:

  DECLARE p_test PROCEDURE FOR   p_test (:ll_loadno,:ls_loadtype); /*only input parameters*/

  Execute p_test ;

  FETCH p_test INTO :ll_returnvalue, :ls_message; /*output parameters*/

  Close p_test ;

Nuno Madeira

[former_member190719]

What Jacob said.  you want to do an RPCFUNC declaration on a user object of type transaction rather than embedded SQL.

[CobyKako]

Hi again,

Just tested successfully your Oracle procedure with a DECLARE PROCEDURE statement.

Here is the PowerBuilder 12.5.2 code:

string ls, ls_outparam

ls = 'This is a string '

DECLARE myProc PROCEDURE FOR SYSTEM.TESTINOUT_VARCHAR 

( 

         P_STRING => :ls 

)  ;

EXECUTE myProc;

FETCH myProc INTO :ls_outparam;

CLOSE myProc;

messagebox("OUT parameter", ls_outparam)

[Former Member]

Thanks for your help but is not returning anything from my end.  I am connected using ODBC.  Are you connected the same way?  The message I get now is "procedure has not been executed or has no results".  With a -1 return.

[CobyKako]

I'm using the native ORA database interface, not ODBC

[CobyKako]

Hello John,

I'm not sure if you already found a solution using an ODBC connection.

Below the solution with an RPC call:

• Create a user object (uo_trans) of type transaction with the following local external function:

subroutine TESTINOUT_VARCHAR(string P_STRING,ref string P_OUT) RPCFUNC

• Execute this script

uo_trans l_transaction

string ls_outparam

l_transaction = CREATE uo_trans

// Profile ODBC_ORA

l_transaction.DBMS = "ODBC"

l_transaction.AutoCommit = False

l_transaction.DBParm = "ConnectString='DSN=ODBC_ORA;UID=system;PWD=<xxxxxx>'"

connect using l_transaction;

ls_outparam = space (30)

l_transaction.testinout_varchar( sle_1.text, ls_outparam)

messagebox("OUT parameter", ls_outparam)

disconnect using l_transaction;

DESTROY l_transaction