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

Hi,

This maybe related to an incompatibility we detect between CocoBase 5 procedure maps and some of the runtime apis. Engineering created an patch to CocoBase 5 SR2 that should resolve this issue. The file is at:

http://www.thoughtinc.com/cbupdate/cbsr2updt_070718.zip

Download the file above and save it under the CocoBase 5 installation directory, then from a command prompt in that directory execute the following commands:

prompt> copy cbruntime.jar cbruntime.original
prompt> jar xvf cbsr2updt_070718.zip
prompt> jar uvf cbruntime.jar -C update .
prompt> rmdir /s /q update

Give this update a try and let us know how it goes. We will also ask engineering if there are any specifics regarding the use of REF return fields.

THOUGHT Support