Tags: call, convert, cursor, cursorthe, database, interface, mysql, oracle, procedures, records, ref, sql, stored, table
Convert PL/SQL Table of PL/SQL Records to a REF CURSOR
Is it possible in Oracle PL/SQL to convert a PL/SQL Table of PL/SQL Records to a REF CURSOR?
The interface I use to call stored procedures and retrieve data using Oracle does not support Oracle PL/SQL tables nor records; however, it does support the output of REF CURSOR as an OUT parameter. The interface in question is ODP.NET.
Leave a comment...
- 2 Comments
- 1) You could insert the values from the PL/SQL table into a global temporary table (GTT) and then use the GTT in the select statement of the ref cursor.
2) Use a collection/array built on a SQL type, instead of a PL/SQL type, and then return the contents using the TABLE function:#1; Fri, 22 Feb 2008 23:13:00 GMT
- I will do that as follows:-
1. CREATE OR REPLACE TYPE MyType AS OBJECT
( EmpNo NUMBER,
2. CREATE OR REPLACE TYPE MyTableType AS TABLE OF MyType ;
3. CREATE OR REPLACE PROCEDURE MyProcedure
( rcEmp IN OUT SYS_REFCURSOR)
outdata MyTableType := MyTableType() ;
<Populate data row-wise into outdata>
END LOOP ;
OPEN rcEmp FOR
FROM TABLE(CAST(outdata AS MyTableType));
/#2; Fri, 22 Feb 2008 23:14:00 GMT