How can I use a REF CURSOR returned from an Oracle Stored Procedure using the Database Toolbox (R2011a)?
5 views (last 30 days)
Show older comments
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;
Accepted Answer
MathWorks Support Team
on 2 Nov 2011
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.
0 Comments
More Answers (0)
See Also
See Also
Categories
Find more on Reporting and Database Access in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!