Originally posted by: SystemAdmin
Hi can someone help me with the dealing with the cursor in the stored prog
I am able to retrive the records when using the following statement
=DBQUERY( "Select * from emp","-MDQ Dubai_World.mdq -DBNAME Stored_Prog -T" )
but when i am using the follwing stored prog with return type ref cursor in the map rule i am unable to get the data, below are the stored prog and trace fiel..
CREATE OR REPLACE PROCEDURE get_list (dept number ,p_cur OUT SYS_REFCURSOR)
AS
BEGIN
OPEN p_cur FOR SELECT * FROM EMP WHERE DEPTNO = dept ;
END;
/
TRACE command specified, file: C:\TECHNICAL\Mercator\Genral\Stored_Prog\Stored_Prog.dbl
<4296-3828>: Connection to Oracle has been established.
<4296-3828>: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
<4296-3828>: Returned status: (0) Success
<4296-3828>: Interface library version 8.1(114)
<4296-3828>: Map: C:\TECHNICAL\Mercator\Genral\Stored_Prog\Stored_Prog.mmc, Timestamp: 09/26/08 17:04:29.
<4296-3828>: Data being retrieved for DBQUERY function.
<4296-3828>: Database adapter: Oracle9i Version 8.1(114)
<4296-3828>: Starting a database unload...
<4296-3828>: Query : Call get_list(10,?)
<4296-3828>: DBQUERY Function, Transaction scope: Map
<4296-3828>: TRACE command specified, file: C:\TECHNICAL\Mercator\Genral\Stored_Prog\Stored_Prog.dbl
<4296-3828>: The columns are of the following types:
<4296-3828>: Column 1 (DEPT) type is NUMBER(40,0).
<4296-3828>: Unsupported datatype returned is being treated as text.
<4296-3828>: Column 2 (P_CUR) type is <Unknown>.
<4296-3828>: Number of buffers in fetch array = 16
<4296-3828>: The procedure statement to be executed is:
<4296-3828>: begin get_list(:a00,:a01); end;
<4296-3828>: Error in: OCIStmtExecute
Message : ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GET_LIST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
<4296-3828>: Returned status: (-1009) Failed_to_execute_the_SQL_statement
<4296-3828>: Cleaning up and closing the transaction...
<4296-3828>: The transaction was successfully committed.
<4296-3828>: Returned status: (0) Success
#DataExchange#IBMSterlingTransformationExtender#IBM-Websphere-Transformation-Extender