I am attempting to return an oracle cursor from an Oracle 9i stored procedure but am not having much success. I am running webMethods 6.0.1 on Windows 2000. I can successfully return a cursor from a select on the database using the JDBC adapter. It makes no difference in the stored procedure whether the cursor is a passed parameter or is a return value (a function). I am currently trying to write a JAVA service that uses the WmDB package to do it without much success as yet.
Here is the version of the JDBC adapter I am using:
Description JDBC Adapter
Adapter Version 6.0.1 Release Notes
JCA Spec Version 1.0
Vendor Name webMethods
Has anyone else successfully attempted to do this?
I have the same problem. I am trying to call an Oracle 8.1.7 packaged stored procedure using the JDBC adapter which returns a table type (REF CURSOR) as one of the OUT parameters. I keep getting the error “invalid ref cursor”. Can someone tell me the steps to configure this?
I’ve worked out how to do it. I found a good simple example stored procedure at the following site:
Java,stored procedure,Oracle,stored procedures and Java,result set,cursor,REF CURSOR,PL/SQL,sqlplus,Java,JDBC,SQL
Then in Developer:
Stored Procedure Call
- Create a Adapter Service for Stored Procedure
- In the parameter tab add parameters and for the REF CURSOR, select type ORACLECURSOR and give it a name.
- In the resultset tab in the bottom panel of the screen add the columns to be returned in the ref cursor using the name you assigned in step 2 as the result set name.
Stored Function Call
- Create a Adapter Service for Stored Procedure
- In the call tab add a parameter for the REF CURSOR, select type ORACLECURSOR and give it a name.
- In the resultset tab in the bottom panel of the screen add the columns to be returned in the ref cursor using the name you assigned in step 2 as the result set name.
Hope this helps. 
Cheers,
Allen.
If you are still having problems after you implemented Allen configuration, check if the store procedure is opening always the cursor.
I have the same “invalid ref cursor” error. but only when the cursor was not opened.
solution: always open the cursor with something like
– Force Data CURSOR to be initialized
OPEN Data FOR SELECT * FROM DUAL WHERE 1 = 2;
Regards,
Norberto
Norberto,
That is exactly right. The Stored Procedure never got around to opening the cursor if there were no records found. The answer was to open the cursor with a null query (WHERE 1 = 2) at the beginning of the Stored Proc then close and reopen if data is returned.
Cheers,
Allen.
we encounter the same problem when we try to open a cursor that is not initialized in the stored procedure
as we cannot modify the stored procedures on this database this is a problem for us, currently we are catching the error but now we cannot see if there is a serious problem on this database
Currently I do a workaround by doing a Select from dual on the database to see if it is alive
why is this throwing an error as in plain SQL this is no problem? can’t this issue be solved in a future release?
Hi All,
I have tried this and it is working fine in IS.
But I can not implement the same thing in ES JDBC Adapter 4.6. These What I did in ES.
- Created a JDBC Adapter Operation for Stored Procedure.
- In the Call tab added input fields as input parameters.
- In the Call tab Selected output option as Oracle Cursor.
- In the Call tab in first Output Field given the ref cursor name.
- In the Results tab specified the return values.
- In Input tab EI automatically creates the input paramates.
- In Output tab EI automatically creates the output as cusorname as long type.
But component throws the following error at runtime:
Error message: COM.activesw.adapter.devkit.AdapterException: (108) Could not invoke script “T04300_JDE_Select_F3002_ITMMET”
(316) Could not execute SQL statement “{call BOMBAY.ITEMPACKAGE.PROC_JDE_ITMMET (?, ?, ?, ?, ?) }”.
(65000/6550) ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to ‘PROC_JDE_ITMMET’
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to ‘PROC_JDE_ITMMET’
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to ‘PROC_JDE_ITMMET’
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to ‘PROC_JDE_ITMMET’
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Can some any one look into this?.
Jey.
I can’t get your example stored function to work. Here are the details.
OS= Win/XP/Pro
wm IS 6.1 (FP2)
wm Developer 6.1 (FP1)
JDBC Adapter 6.0.3
Oracle 9.2
=============================
Stored function SP_GET_STOCKS works ok when invoked from SQLPlus workbench)
==============================
JDBC Adapter Service settings
call
catalog name=
schema name=SCOTT
procedure name=SP_GET_STOCKS
Parameter tab (top)
|Param JDBC Type |Param Name |Param Type |Expression
|VARCHAR |v_price |IN |?
|ORACLECURSOR |cursor |OUT |stock_cursor
Parameter tab (middle)
|Input Name |Input Type
|v_price |java.lang.string
Result Set tab (top)
|Result Set Index |Result Set Name
|1 |stock_cursor
Result Set tab (bottom)
|Result Set Name |Column Name |JDBC Type|Output Type
|stock_cursor |price |BIGINT |java.lang.long
=====================================
Error message when service is executed
Adapter Runtime (Adapter Service):
Unable to invoke adapter service ladson_echostar:call_getstocks.
[ADA.1.316] Cannot execute the SQL statement
“{call SCOTT…SP_GET_STOCKS (?, cursor) }”. "
(HY000/931) [wm-cjdbc33-0009][Oracle JDBC Driver][Oracle]ORA-00931: missing identifier
ORA-06512: at “SYS.DBMS_UTILITY”, line 114
ORA-06512: at line 1
I found the problem.
If I get lazy and set “enable procedure name lookup=true”, then Developer sets the procedure reference as follows (note the two periods where there should be one)
SYS…DATABASE_NAME
If I set “enable procedure name lookup=false”, then I am able to type-in the correct name
SYS.DATABASE_NAME