Re: get data from Ref Cursor from Oracle package function

The correct definition of the example is:
------------------------------------------------------------
create or replace
PACKAGE scotts_package
AS
TYPE CurType IS REF CURSOR;
FUNCTION show_emp RETURN CurType;
END scotts_package;
/
create or replace
PACKAGE BODY scotts_package
AS
FUNCTION show_emp RETURN CurType IS
out_cur CurType;
-- String f?r den REF CURSOR
strSelect VARCHAR2(32000);
BEGIN
strSelect := 'select ename,job
from emp
order by 1
';
OPEN out_cur FOR strSelect;
-- und zur?ckliefern
RETURN out_cur;
EXCEPTION
WHEN OTHERS THEN raise_application_error(-20100,'scotts_package.show_emp:'||strSelect,TRUE);
END show_emp;
END scotts_package;
/

Regards,
Olaf

"Olaf " <olaf.buettner@xxxxxx> wrote in message <h8llce$s4r$1@xxxxxxxxxxxxxxxxxx>...
Hi,

I want to use the output from a database package function with the Matlab database toolbox. The output is defined as Ref Cursor.
There is a simple example below. The output of the function can be received in a SQL evironment with
"select scotts_package.show_emp from dual"

If I send this statement in Matlab to the database:

sqlquery = 'select scotts_package.show_emp from dual';
curs = exec(conn, sqlquery);
setdbprefs('DataReturnFormat','structure');
results=fetch(conn, sqlquery)

I get an empty result:

curs =

Attributes: []
Data: 0
DatabaseObject: [1x1 database]
RowLimit: 0
SQLQuery: 'selectscotts_package.show_emp from dual'
Message: []
Type: 'Database Cursor Object'
ResultSet: [1x1 oracle.jdbc.driver.OracleResultSetImpl]
Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
Statement: [1x1 oracle.jdbc.driver.OracleStatementWrapper]
Fetch: 0


results =

SHOW_EMP: {[]}

Are there any ideas how I could retreive the data?
Thanks,
Olaf


The background is: We have packages with hundreds of functions in our database (all in the principle of the example) and want to use them via Matlab. The advantage is that we can maintain the functions separately from user interfaces and that we can use it in different environments. We only need a connection to the database. It would be nice to use it with Matlab too. (I could take the SQL Command itself from the functions but it is the last way and not really efficient because if there are changes inside the functions I have to change it ones more within the Matlab program. Additional to the work this may be a potential source of error.)

------------------------
-- EXAMPLE: Structure of the packagefunctions
------------------------
create or replace
PACKAGE scotts_package
AS
TYPE CurType IS REF CURSOR;
FUNCTION show_emp RETURN CurType;
END scott_test;
/
create or replace
PACKAGE BODY scotts_package
AS
FUNCTION show_emp RETURN CurType IS
out_cur CurType;
-- String f?r den REF CURSOR
strSelect VARCHAR2(32000);
BEGIN
strSelect := 'select ename,job
from emp
order by 1
';
OPEN out_cur FOR strSelect;
-- und zur?ckliefern
RETURN out_cur;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20100,'scotts_package.show_emp:'||strSelect,TRUE);
END show_emp;
END scotts_package;
/

------------------------------
--End Example
------------------------------
.

Relevant Pages
Loading