Hi,
We are evaluating cocobase v5.0 & tried invoking a procedure which returns a ref cursor as a OUT parameter. While invoking the procedure we are getting the following exception.
[INFO][ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] 04 Jul 2007 16:06:41,437 (BaseApplicationException.java:<init>:151) Exception::thought.CocoBase.CocoBaseException: CBMessage=Call failed due to a low level exception. CoreMessage=null CocoBaseErrorCode=DBCALL_FAILURE
[INFO][ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] 04 Jul 2007 16:06:41,437 (ServiceException.java:<init>:109) Exception::thought.CocoBase.CocoBaseException: CBMessage=Call failed due to a low level exception. CoreMessage=null CocoBaseErrorCode=DBCALL_FAILURE
I've given below the CBObject map below.
<CBObject MapName="SPGetCodeList" ClassName="SPGetCodeList" DefaultBlockSize="100" Version="1" AppVersion="5.00" DateModified="2007-07-04 15:54:09.0">
<call>
<CBFieldReturns/>
<CBProcedures>
<CBTable catalogname="null" schemaname="" tablename="pkg_code.SP_CODE_GETCODELIST" tabletype="null"/>
</CBProcedures>
<CBFieldParams>
<CBField location="0" catalogname="null" schemaname="" tablename="pkg_code.SP_CODE_GETCODELIST" fieldname="AS_SELECT" fieldvalue=":AS_SELECT" dbfieldtype="1" fielddirection="0" dbtype="12" dbfieldsize="0" searchable="false" signed="false" keyposition="-1"/>
<CBField location="1" catalogname="null" schemaname="" tablename="pkg_code.SP_CODE_GETCODELIST" fieldname="AS_CODE_LIST" fieldvalue=":AS_CODE_LIST" dbfieldtype="4" fielddirection="0" dbtype="-10" dbfieldsize="0" searchable="false" signed="false" keyposition="-1"/>
</CBFieldParams>
</call>
Note: I've generated the map using the cocobase workbench. I've mapped the DBType as OTHER for 'AS_CODE_LIST' field. Since it gave me a java.sql.SQLException: Invalid column Type, i changed the dbtype as "-10".
The stored procedure is given below.
CREATE OR REPLACE
PACKAGE types
AS
TYPE ref_cursor IS REF CURSOR;
END;
CREATE OR REPLACE
package pkg_code is
TYPE REF_CURSOR IS REF CURSOR;
procedure getcodelist(as_select varchar2, as_code_list out REF_CURSOR);
end pkg_code;
CREATE OR REPLACE
PACKAGE BODY PKG_CODE IS
procedure getcodelist(as_select varchar2, as_code_list out REF_CURSOR) IS
BEGIN
OPEN as_code_list FOR
as_select;
END getcodelist;
END PKG_CODE;
Also, would appreciate if you could share sample code for calling procedures with ref cursor as OUT parameter.
Thanks in Advance.
Regards,
Mahesh
|