IBMACE-调用Oracle过程返回行类型(通过ESQL计算节点)

Question

我正在尝试调用存储在plsql中的过程。以下是我迄今为止所做的尝试。

在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;

在计算节点中：

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

--调用程序

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

错误如下：

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''.

我不确定我是否正确地表示了oracle过程。

Answer 1

我通过使用plsql函数而不是过程来实现它。函数将ref游标作为结果集返回。然后，应用ibm文档调用返回结果集的过程。