[SOLVED] IBM ACE - Calling Oracle Procedure returning a rowtype (via ESQL compute node)

IBM ACE - Calling Oracle Procedure returning a rowtype (via ESQL compute node)

I'm trying to call procedure stored in plsql. Here is what I've tried so far.

In Oracle:

create or replace PROCEDURE dbSwapParms  
( in_param IN VARCHAR2, 
  out_param OUT varchar2, 
  inout_param IN OUT customer%ROWTYPE) 
AS 
BEGIN
  select * 
  into inout_param 
  from SYS_ENDPOINTS  where customer_name=in_param; -- assuming this query returns single row
END;

In Compute node:

-- Definition of procedure  
CREATE PROCEDURE swapParms ( 
      IN parm1 CHARACTER, 
      OUT parm2 CHARACTER 
    )
    LANGUAGE DATABASE  
    DYNAMIC RESULT SETS 1 
    EXTERNAL NAME dbSwapParms;

-- Invoking the procedures

CALL swapParms( inputParm, outputParm, OutputRoot.JSON.Data.test[]); -- found this in ibm documentation returning result set

Here is the error:

BIP2230E: Error detected whilst processing a message in node 'gen.CB_testBar.postHelloWorld (Implementation).Compute4'. 
BIP2488E: ('.postHelloWorld_Compute4.Main', '19.4') Error detected whilst executing the SQL statement ''CALL swapParms(inputParm, outputParm, OutputRoot.JSON.Data.test[]);''. 
BIP2934E: Error detected whilst executing the function or procedure ''swapParms''. 
BIP2321E: Database error: ODBC return code '-1' using ODBC driver manager ''odbc32.dll''. 
BIP2322E: Database error: SQL State ''HY000''; Native Error Code '0'; Error Text ''[IBM][ODBC Oracle Wire Protocol driver]SQL type not supported: 0''.

I'm not sure if I've represented the oracle procedure correctly.

Solution

I made it work by using plsql function instead of procedure. The function returns ref cursor as a result set. Then, applied the ibm documentation of invoking procedure returning resultset.