How can I use a REF CURSOR returned from an Oracle Stored Procedure using the Database Toolbox (R2011a)?

I have an an Oracle9i database, to which I connected with a Oracle's ojdbc5.jar driver. I have also tried ojdbc6 already. The database has a stored procedure that returns a REF CURSOR. I want to call the procedure and access the contents of the REF CURSOR from MATLAB.

When I run the following commands

>> res = runstoredprocedure(conn, 'my_procedure', {} ,{oracle.jdbc.OracleTypes.CURSOR});

I get the following output in MATLAB

 >> res

 res = 

     [1x1 oracle.jdbc.driver.OracleResultSetImpl]

This shows that the result was not parsed by MATLAB.

Alternately, I try to invoke my stored procedure using EXEC and FETCH as per solution 1-49NM4L as follows

stmt = exec(dbHandle, test_ref);

where 'test_ref' is my stored procedure. This gives me the following error

 'ORA-01008: not all variables bound'

The text of my stored procedure is as follows:

 create or replace

 PROCEDURE GETCURSOR (s_curs out types.cursorType) AS

 BEGIN

   open s_curs for select * from testupdate;

 END GETCURSOR;

Answer 1

[MathWorks Support Team]

The reason you are not able to obtain data from a CURSOR returned by an ORACLE Stored Procedure is because RUNSTOREDPROCEDURE contains a line that closes the cursor obtained from the Oracle Stored Procedure. Even if the function RUNSTOREDPROCEDURE returns a CURSOR, it is not possible to obtain the data associated with the CURSOR.

To work around this, you can try the attached file runCursorSP, this function takes the same input arguments as RUNSTOREDPROCEDURE, but does not close the CURSOR before obtaining the data. This function retuns a cell array with the desired data associated with the Oracle Cursor returned by the Stored Procedure, but does not have all the options of RUNSTOREDPROCEDURE. It is an example of a starting point of how you can retrieve data from an Oracle database via a Stored Procedure that returns a CURSOR.