Copying/Transforming a REF CURSOR in Oracle 10g+
29 March 2011 2 Comments
Introduction
Use case: You need to be able to copy and optionally transform any PL/SQL REF CURSOR in a uniform way across all editions of Oracle Database from 10g Release 1 and newer. You need to fetch the REF CURSOR into transient (memory) or persistent storage (normal or global temporary table). Performance is not crucial.
Challenges: REF CURSORs are very limited and in some Oracle releases quite fragile to work with.
Solution: I’ve developed PL/SQL code that implements the use case (except for LONG and LONG RAW columns) . This code leverages the REF CURSOR description technique described in my blog post Describing a REF CURSOR in Oracle 10g+ Using PL/SQL, Java and C. This blog post describes the code, which has taken a significant amount of time to develop.
Algorithm
This is the basic algorithm of the solution, implemented in a PL/SQL package REF_CURSOR_COPY:
- Describe given
REF CURSOR. - Create/reuse object types and table compatible with given
REF CURSOR. - Generate dynamic PL/SQL code that fetches all rows from given
REF CURSORinto either memory or table, returning a newREF CURSORselecting from this copy, optionally calling:- Given PL/SQL function for each row that was fetched.
- Given PL/SQL block before the new copy is opened.
- Given PL/SQL block after the new copy is opened.
- Call dynamic PL/SQL code, returning a new
REF CURSORopened for selecting from the copy.
The code generated would have the following form:
declare
rc1 sys_refcursor;
rc2 sys_refcursor;
fetched_row ref_cur_copy_<seq>_t := ref_cur_copy_<seq>_t(null, ..., null);
fetched_rows ref_cur_copy_<seq>_c := ref_cur_copy_<seq>_c();
n pls_integer := 0;
...
begin
rc1 := :rc1;
loop
exit when not rc1%isopen;
fetch rc1 into fetched_row.<col1>, ..., fetched_row.<coln>;
exit when rc1%notfound;
n := n + 1;
-- Optionally manipulate each row.
<plsql_block_for_each>
fetched_rows.extend(1);
fetched_rows(n) := fetched_row;
end loop;
begin
if rc1%isopen then
close rc1;
end if;
exception
when others then
null;
end;
:row_count := n;
-- Optionally call PL/SQL block before copy is opened.
<plsql_block_before>
open rc2 for
select *
from table(cast(fetched_rows as ref_cur_copy_<seq>_c));
-- Optionally call PL/SQL block after copy is opened.
<plsql_block_after>
:rc2 := rc2;
end;
Where we’re supplying the following bind variables:
:RC1: InputREF CURSOR.:ROW_COUNT: Output number of rows fetched from:RC1.:RC2: OutputREF CURSOR.
It would have been nice if you could write code like the following, but unfortunately Oracle doesn’t allow that:
fetch rc into fetched_row;
where FETCHED_ROW is an instance of an object type compatible with the row type of RC. Instead, we need to nominate a variable per column.
If copying to a table instead the code generated is slightly different:
declare
rc1 sys_refcursor;
rc2 sys_refcursor;
fetched_row ref_cur_copy_<seq>_t := ref_cur_copy_<seq>_t(null, ..., null);
fetched_rows ref_cur_copy_<seq>_c := ref_cur_copy_<seq>_c();
n pls_integer := 0;
...
begin
rc1 := :rc1;
loop
exit when not rc1%isopen;
fetch rc1 into fetched_row.<col1>, ..., fetched_row.<coln>;
exit when rc1%notfound;
n := n + 1;
-- Optionally manipulate each row.
<plsql_block_for_each>
insert into ref_cur_copy_<seq> (
-- Session ID if not a global temporary table.
"sessionid",
"rownum",
<col1>,
...
<coln>
)
values (
userenv('sessionid'),
n,
fetched_row.<col1>,
...
fetched_row.<coln>
);
end loop;
begin
if rc1%isopen then
close rc1;
end if;
exception
when others then
null;
end;
:row_count := n;
commit;
-- Optionally call PL/SQL block before copy is opened.
<plsql_block_before>
open rc2 for
select t.<col1>, ..., t.<coln>
from ref_cur_copy_<seq> t
where "sessionid" = userenv('sessionid')
order by "rownum";
-- Optionally call PL/SQL block after copy is opened.
<plsql_block_after>
:rc2 := rc2;
end;
If any of the columns in the REF CURSOR is a REF CURSOR itself (weak, strong or CURSOR expression, called a sub or an embedded REF CURSOR in the following), it gets a little more complicated. Unfortunately, we cannot use the SYS_REFCURSOR datatype for an object type attribute and we cannot use it in an associative array either, which is the reason why I didn't bother using BULK COLLECT when fetching to memory. Instead, we must declare a local variable of datatype SYS_REFCURSOR for each sub REF CURSOR, "sub fetch" this into an appropriate "sub" object type and convert this to an ANYDATA instance, such that we can hold the value in the "top" object type. Since we in Oracle cannot describe a REF CURSOR before it has been executed, this is the only solution possible as we cannot describe the sub REF CURSOR until it has been fetched for each row in the top REF CURSOR.
Here is the code generated when we have embedded REF CURSORs: (here we assume that the sub REF CURSOR columns are the last columns of the top REF CURSOR. This is just in order to simplify the code shown below, REF_CURSOR_COPY doesn’t assume that):
declare
rc1 sys_refcursor;
rc2 sys_refcursor;
fetched_row ref_cur_copy_<seq>_t := ref_cur_copy_<seq>_t(null, ..., null);
fetched_rows ref_cur_copy_<seq>_c := ref_cur_copy_<seq>_c();
n pls_integer := 0;
rce1 sys_refcursor;
type_name_e1 user_types.type_name%type;
...
rce<m> sys_refcursor;
type_name_e<m> user_types.type_name%type;
...
begin
rc1 := :rc1;
loop
exit when not rc1%isopen;
fetch rc1 into fetched_row.<col1>, ..., fetched_row.<coln-m>, rce1, ..., rce<m>;
exit when rc1%notfound;
fetched_row.<coln-m+1> := ref_cursor_copy.fetch_data(rce1, type_name_e1);
type_name_e1 := ref_cursor_copy.get_collection_type_name(type_name_e1);
...
fetched_row.<colm> := ref_cursor_copy.fetch_data(rce<m>, type_name_e<m>);
type_name_e<m> := ref_cursor_copy.get_collection_type_name(type_name_e<m>);
n := n + 1;
-- Optionally manipulate each row.
<plsql_block_for_each>
fetched_rows.extend(1);
fetched_rows(n) := fetched_row;
end loop;
begin
if rc1%isopen then
close rc1;
end if;
exception
when others then
null;
end;
:row_count := n;
-- Optionally call PL/SQL block before copy is opened.
<plsql_block_before>
open rc2 for q'[
select <col1>,
...,
<coln-m>,
cursor(select * from table(<coln-m+1>)) <coln-m+1>,
...,
cursor(select * from table(<coln>)) <coln>
from (
select <col1>,
...,
<coln-m>,
cast(<coln-m+1> as ]' || type_name_e1 || q'[) <coln-m+1>,
...,
cast(<coln> as ]' || type_name_e<m> || q'[) <coln>
from table(cast(:fetched_rows as ref_cur_copy_<seq>_c))
)]'
using in fetched_rows;
-- Optionally call PL/SQL block after copy is opened.
<plsql_block_after>
:rc2 := rc2;
end;
Please note that now we need to dynamically select from the collection holding the fetched data. This is in order to cast each sub REF CURSOR to the appropriate object type and then select all the columns from this. Also note that the SELECT statement seems overly complex with the subquery, but unfortunately this is necessary as a workaround to an Oracle bug through which an ORA-00600 [kocgpn129] error is raised if we cast the ANYDATA to the appropriate collection and select from it in the same SELECT statement (seems to be Bug 9836806: ORA-600 AND ORA-7445 ERRORS WHEN UNNESTING SYS.ANYDATA). This is illustrated by the following example:
drop type ref_cur_copy_0_c;
drop type ref_cur_copy_0_t;
drop type ref_cur_copy_0e_c;
drop type ref_cur_copy_0e_t;
create type ref_cur_copy_0_t as object (
deptno number(2),
dname varchar2(14),
emp_cursor sys.anydata
);
/
show err
create type ref_cur_copy_0_c as
table of ref_cur_copy_0_t;
/
show err
create type ref_cur_copy_0e_t as object (
empno number(4),
ename varchar2(10)
);
/
show err
create type ref_cur_copy_0e_c as
table of ref_cur_copy_0e_t;
/
show err
variable rc2 refcursor
prompt Expected to succeed
declare
rc1 sys_refcursor;
fetched_row ref_cur_copy_0_t := ref_cur_copy_0_t(null, null, null);
fetched_rows ref_cur_copy_0_c := ref_cur_copy_0_c();
fetched_row_e ref_cur_copy_0e_t := ref_cur_copy_0e_t(null, null);
fetched_rows_e ref_cur_copy_0e_c := ref_cur_copy_0e_c();
rce1 sys_refcursor;
type_name_e1 user_types.type_name%type := 'REF_CUR_COPY_0E_C';
begin
open rc1 for
select deptno,
dname,
cursor(
select empno,
ename
from emp e
where e.deptno = d.deptno and
rownum <= 2
) emp_cursor
from dept d
where deptno <= 20;
loop
-- Fetch one top REF CURSOR row.
fetch rc1 into fetched_row.deptno, fetched_row.dname, rce1;
exit when rc1%notfound;
-- Fetch all sub REF CURSOR rows for the top row.
fetched_rows_e := ref_cur_copy_0e_c();
loop
fetch rce1 into fetched_row_e.empno, fetched_row_e.ename;
exit when rce1%notfound;
fetched_rows_e.extend(1);
fetched_rows_e(fetched_rows_e.count) := fetched_row_e;
end loop;
--if portable.get_major_minor_version != 10.1 then
close rce1;
--end if;
fetched_row.emp_cursor := anydata.convertcollection(fetched_rows_e);
fetched_rows.extend(1);
fetched_rows(fetched_rows.count) := fetched_row;
end loop;
--if portable.get_major_minor_version != 10.1 then
close rc1;
--end if;
open :rc2 for
select deptno,
dname,
cursor(
select *
from table(emp_cursor)
) emp_cursor
from (
select deptno,
dname,
cast(emp_cursor as ref_cur_copy_0e_c) emp_cursor
from table(cast(fetched_rows as ref_cur_copy_0_c))
);
end;
/
print :rc2
prompt Expected to fail
declare
rc1 sys_refcursor;
fetched_row ref_cur_copy_0_t := ref_cur_copy_0_t(null, null, null);
fetched_rows ref_cur_copy_0_c := ref_cur_copy_0_c();
fetched_row_e ref_cur_copy_0e_t := ref_cur_copy_0e_t(null, null);
fetched_rows_e ref_cur_copy_0e_c := ref_cur_copy_0e_c();
rce1 sys_refcursor;
type_name_e1 user_types.type_name%type := 'REF_CUR_COPY_0E_C';
begin
open rc1 for
select deptno,
dname,
cursor(
select empno,
ename
from emp e
where e.deptno = d.deptno and
rownum <= 2
) emp_cursor
from dept d
where deptno <= 20;
loop
-- Fetch one top REF CURSOR row.
fetch rc1 into fetched_row.deptno, fetched_row.dname, rce1;
exit when rc1%notfound;
-- Fetch all sub REF CURSOR rows for the top row.
fetched_rows_e := ref_cur_copy_0e_c();
loop
fetch rce1 into fetched_row_e.empno, fetched_row_e.ename;
exit when rce1%notfound;
fetched_rows_e.extend(1);
fetched_rows_e(fetched_rows_e.count) := fetched_row_e;
end loop;
--if portable.get_major_minor_version != 10.1 then
close rce1;
--end if;
fetched_row.emp_cursor := anydata.convertcollection(fetched_rows_e);
fetched_rows.extend(1);
fetched_rows(fetched_rows.count) := fetched_row;
end loop;
--if portable.get_major_minor_version != 10.1 then
close rc1;
--end if;
open :rc2 for
select deptno,
dname,
cursor(
select empno,
ename
from table(cast(emp_cursor as ref_cur_copy_0e_c))
) emp_cursor
from table(cast(fetched_rows as ref_cur_copy_0_c));
end;
/
print :rc2
drop type ref_cur_copy_0_c;
drop type ref_cur_copy_0_t;
drop type ref_cur_copy_0e_c;
drop type ref_cur_copy_0e_t;
which produces the following output (abbreviated):
Expected to succeed
PL/SQL procedure successfully completed.
DEPTNO DNAME EMP_CURSOR
---------- -------------- --------------------
10 ACCOUNTING CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
EMPNO ENAME
---------- ----------
7782 CLARK
7839 KING
20 RESEARCH CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
EMPNO ENAME
---------- ----------
7369 SMITH
7566 JONES
Expected to fail
PL/SQL procedure successfully completed.
ERROR:
ORA-00600: internal error code, arguments: [kocgpn129], [2], [], [], [], [], [], []
We could have simply selected all columns from the ANYDATA columns, letting Oracle unnest appropriately. However, this would mean that the structure of the copy wouldn’t be the same as the original REF CURSOR (we would suddenly have a collection instead of a REF CURSOR for the embedded REF CURSOR), so this is not a viable solution.
I'm assuming that the REF CURSOR columns are compatible across the rows – ie, my implementation doesn't cater for a REF CURSOR returned in given column for row 1 that has 3 columns COLA, COLB, COLC and for row 2 it has two columns COLB and COLD.
Object Type
Because object types cannot have attributes of datatypes LONG and LONG RAW, because it’s not possible to fetch more than 32KB from these in PL/SQL unless resorting to dynamic PL/SQL and because in general their use has been deprecated for many years, I won’t be supporting them with my code. Please refer to my blog post Converting a LONG Column to a CLOB on the fly on how to convert a LONG column to a CLOB value on the fly if you need this.
For obvious performance reasons it’s important that we reuse the object type instead of creating a new for each use. This is controlled through a table REF_CURSOR_COPY_TYPES that has the following definition:
create table ref_cursor_copy_types ( description_md5_hash varchar2(32), description_length number(5), ref_cursor_description xmltype, type_name varchar2(30), type_name_collection varchar2(30), table_name varchar2(30), table_impl varchar2(2) );
Table
The table generated for each REF CURSORhas the following structure:
create table ref_cur_copy_<seq> ( "sessionid" integer, "rownum" integer, <col1> <col1_datatype>, ... <coln> <coln_datatype> );
where
"sessionid"is used to ensure session-specific data."rownum"is used to ensure ordering of the data.
The code can be configured to create a global temporary table instead (with either deletion or preservation of data upon commit) as well, which means that the "sessionid" column is not used, as Oracle guarantees that the table data is session-specific. If you would like to use autonomous transactions in the copy code you need to be aware that this is not compatible with "delete on commit" as this will remove the data from the temporary table such that the copy REF CURSOR will return no rows.
VARRAY vs Nested Table
I would prefer to keep data in the copy in the same order as the original so the default implementation of the collection object types uses VARRAY. However, this creates problems on non-Windows platforms such as Linux, SPARC Solaris and Intel Solaris, as Oracle throws the error ORA-22909: exceeded maximum VARRAY limit even though this obviously isn't true. For this reason, I've made it configurable whether to use VARRAY or a nested table. It must be noted that the ordering is undefined when using the nested table.
REF_CURSOR_COPY
Here’s the implementation of the REF_CURSOR_COPY package specification:
create or replace package ref_cursor_copy
authid current_user as
/**
* Utility to copy a REF CURSOR with the possibility to inject code before, for
* each row and after the copy is fetched.
* Depends on REF_CURSOR_DESCRIBE.
* Feel free to use at your own risk.
* @version $Revision: 3 $
* @author Finn Ellebaek Nielsen, Ellebaek Consulting ApS.
*/
/**
* Controls implementation details:
* 'O': Use ODCI.SAVE-/RESTORECURSOR for top-level.
* 'o': Use ODCI.SAVE-/RESTORECURSOR for non top-level.
* 'C': Close original REF CURSOR again, after fetched and close.
* 'c': Close sub REF CURSORs outside sub fetch code, not inside.
* 'R': Use DBMS_ODCI.RESTOREREFCURSOR where it shouldn't be necessary.
* 'B': Don't fetch using BULK COLLECT, fetch one row at a time.
*/
copy_impl varchar2(5) := '';
/**
* Controls close implementation details:
* '': Close both top and sub REF CURSORs once in the appropriate place.
* 'A': Close REF CURSOR again, after fetched and close.
* 'N': Set REF CURSOR to NULL, after fetched and close.
* 'S': Close sub REF CURSORs outside sub fetch code, not inside.
* 'd': Don't close sub REF CURSORs at all.
* 'D': Don't close top REF CURSORs at all.
*/
close_impl varchar2(5) := '';
/**
* Controls collection type creation details:
* 'V': Use VARRAY for collections (default).
* 'T': Use TABLE for collections.
*/
collection_impl varchar2(2) := 'V';
/**
* Controls table creation details:
* 'T': Use normal table with "sessionid" column.
* 'td': Use global temporary table, delete rows upon commit.
* 'tp': Use global temporary table, preserve rows upon commit (default).
*/
table_impl varchar2(2) := 'tp';
function to_ref_cursor(
rc in out sys_refcursor
)
return sys_refcursor;
function to_ref_cursor(
rc in out sys_refcursor,
type_name in out user_types.type_name%type,
destination in char := 'M',
plsql_block_after_each_fetch in varchar2 := '',
plsql_block_before_copy_open in varchar2 := '',
plsql_block_after_copy_open in varchar2 := ''
)
return sys_refcursor;
function to_anydata(
rc in out sys_refcursor,
plsql_block_after_each_fetch in varchar2 := ''
)
return anydata;
function fetch_data(
rc in out sys_refcursor,
type_name in out user_types.type_name%type,
destination in char := 'M',
plsql_block_after_each_fetch in varchar2 := ''
)
return anydata;
function get_fetch_data_plsql(
rc in out sys_refcursor,
type_name in out user_types.type_name%type,
name_list in out sys.odcivarchar2list,
type_code_list in out sys.odcinumberlist,
ref_cur_count in out pls_integer,
top in boolean,
destination in char := 'M',
plsql_block_after_each_fetch in varchar2 := '',
expose_rows in boolean := true,
xml out xmltype
)
return varchar2;
function get_row_count
return pls_integer;
function get_sub_row_count
return pls_integer;
function get_type_name(
ref_cursor_desc in xmltype
)
return user_types.type_name%type;
function get_collection_type_name(
type_name in user_types.type_name%type
)
return user_types.type_name%type;
function get_table_name(
type_name in user_types.type_name%type
)
return user_tables.table_name%type;
function get_table_impl(
table_name in user_types.type_name%type,
destination in varchar2
)
return ref_cursor_copy_types.table_impl%type;
function rc_from_anydata(
ad in anydata,
type_name_collection in varchar2
)
return sys_refcursor;
function purge_tables
return integer;
function drop_types_and_tables
return integer;
function use_odci(top in boolean)
return boolean;
end ref_cursor_copy;
/
Please note the package parameters COPY_IMPL, CLOSE_IMPL and COLLECTION_IMPL that can be used to manipulate the generated code in various ways.
Here’s the implementation of the REF_CURSOR_COPY package specification:
create or replace package body ref_cursor_copy as
--- @version $Revision: 3 $
--- Number of rows fetched in latest COPY call.
row_count pls_integer;
--- Number of rows fetched in latest FETCH_DATA call.
sub_row_count pls_integer;
--- Error info.
error_info varchar2(32767);
procedure safely_close(
rc in out sys_refcursor,
force in boolean := false
);
/**
* Copies a REF CURSOR to memory.
* @param rc REF CURSOR.
* @return A new REF CURSOR opened for the copy in memory.
* @throws ORA-20000 if the REF CURSOR contains LONG or LONG RAW columns.
*/
function to_ref_cursor(
rc in out sys_refcursor
)
return sys_refcursor as
type_name user_types.type_name%type;
begin
return to_ref_cursor(rc, type_name);
end to_ref_cursor;
/**
* Copies a REF CURSOR to either memory or a table. Provides the ability to
* execute PL/SQL blocks before the copy is fetched, after each row is fetched,
* and after the entire copy has been fetched, before opening the new REF
* CURSOR.
* @param rc REF CURSOR.
* @param type_name
* Object type name of object type representing a row on the
* given REF CURSOR. Has the name of the form
* REF_CURSOR_COPY_<sequence>_T.
* @param destination
* Whether the copy should be made to memory ('M') or to a
* table ('T').
* @param plsql_block_after_each_fetch
* PL/SQL block to run for each row after it has been
* fetched, working on the FETCHED_ROW object type instance
* that holds the column values fetched. This can be used to
* transform the data fetched.
* @param plsql_block_before_copy_open
* PL/SQL block to run before the REF CURSOR copy is opened.
* @param plsql_block_after_copy_open
* PL/SQL block to run after the REF CURSOR copy is opened.
* @return A new REF CURSOR opened for the copy in memory or table.
* @throws ORA-20000 if the REF CURSOR contains LONG or LONG RAW columns.
*/
function to_ref_cursor(
rc in out sys_refcursor,
type_name in out user_types.type_name%type,
destination in char := 'M',
plsql_block_after_each_fetch in varchar2 := '',
plsql_block_before_copy_open in varchar2 := '',
plsql_block_after_copy_open in varchar2 := ''
)
return sys_refcursor as
/*pragma autonomous_transaction;*/
plsql varchar2(32767);
plsql2 varchar2(32767);
plsql3 varchar2(32767);
plsql4 varchar2(32767);
rcn number;
rc2n number;
rc2 sys_refcursor;
type_name_c user_types.type_name%type;
table_name user_tables.table_name%type;
name_list sys.odcivarchar2list := sys.odcivarchar2list();
type_code_list sys.odcinumberlist := sys.odcinumberlist();
ref_cur_count pls_integer;
j pls_integer := 0;
xml xmltype;
table_impl ref_cursor_copy_types.table_impl%type;
begin
-- This is necessary to make it work on various Oracle versions. If
-- omitted, Oracle throws various "invalid cursor" errors.
if use_odci(true) then
dbms_odci.saverefcursor(rc, rcn);
end if;
plsql := get_fetch_data_plsql(
rc,
type_name,
name_list,
type_code_list,
ref_cur_count,
true,
upper(destination),
plsql_block_after_each_fetch,
false,
xml
) || chr(10);
type_name_c := get_collection_type_name(type_name);
table_name := get_table_name(type_name);
table_impl := get_table_impl(table_name, upper(destination));
if plsql_block_before_copy_open is not null then
plsql := plsql ||
'' || chr(10) ||
' ' || plsql_block_before_copy_open || chr(10) ||
'' || chr(10);
end if;
plsql := plsql ||
'' || chr(10);
if ref_cur_count > 0 then
-- Embedded REF CURSORs. More complex SELECT statement with CAST and
-- CURSOR.
if upper(destination) = 'M' then
plsql4 :=
' from table(cast(:fetched_rows as ' ||
type_name_c || '))' || chr(10);
elsif upper(destination) = 'T' then
plsql4 :=
' from ' || table_name || ' t' || chr(10) ||
case when table_impl = 'T' then
' where "sessionid" = userenv(''sessionid'')' || chr(10)
end ||
' order by "rownum"' || chr(10);
end if;
plsql := plsql ||
' open rc2 for q''[' || chr(10);
for i in nvl(name_list.first, 0) .. nvl(name_list.last, -1) loop
if i > name_list.first then
plsql2 := plsql2 || ' ';
plsql3 := plsql3 || ' ';
end if;
if type_code_list(i) = type_codes.tc_ref_cursor then
j := j + 1;
plsql2 := plsql2 ||
'cursor(select * from table(' || name_list(i) || ')) ' ||
name_list(i);
plsql3 := plsql3 ||
'cast(' || name_list(i) || ' as ]'' || type_name_e' || j ||
' || q''[) ' || name_list(i);
else
plsql2 := plsql2 ||
name_list(i);
plsql3 := plsql3 ||
name_list(i);
end if;
if i < name_list.last then
plsql2 := plsql2 || ',';
plsql3 := plsql3 || ',';
end if;
plsql2 := plsql2 || chr(10);
plsql3 := plsql3 || chr(10);
end loop;
plsql := plsql ||
' select ' ||
plsql2 ||
' from (' || chr(10) ||
' select ' ||
plsql3 ||
plsql4 ||
' )]''';
if upper(destination) = 'M' then
plsql := plsql || chr(10) ||
' using in fetched_rows;' || chr(10);
else
plsql := plsql || ';' || chr(10);
end if;
else
-- No embedded REF CURSORs. Simple SELECT statement.
if upper(destination) = 'M' then
plsql := plsql ||
' open rc2 for' || chr(10) ||
' select *' || chr(10) ||
' from table(cast(fetched_rows as ' || type_name_c || '));' ||
chr(10);
elsif upper(destination) = 'T' then
plsql := plsql ||
' open rc2 for' || chr(10) ||
' select ';
for c in 1 .. name_list.count loop
plsql := plsql || case when c > 1 then ', ' end || 't.' || name_list(c);
end loop;
plsql := plsql || chr(10) ||
' from ' || table_name || ' t' || chr(10) ||
case when table_impl = 'T' then
' where "sessionid" = userenv(''sessionid'')' || chr(10)
end ||
' order by "rownum";' || chr(10);
end if;
end if;
if plsql_block_after_copy_open is not null then
plsql := plsql ||
'' || chr(10) ||
' ' || plsql_block_after_copy_open || chr(10) ||
'' || chr(10);
end if;
if use_odci(true) then
plsql := plsql ||
'' || chr(10) ||
' dbms_odci.saverefcursor(rc2, :rc2n);' || chr(10);
else
plsql := plsql ||
'' || chr(10) ||
' :rc2 := rc2;' || chr(10);
end if;
plsql := plsql ||
'end;';
if use_odci(true) then
execute immediate plsql
using in rcn, out row_count, out rc2n;
if rcn is not null and copy_impl like '%R%' then
dbms_odci.restorerefcursor(rc, rcn);
end if;
if rc2n is not null then
dbms_odci.restorerefcursor(rc2, rc2n);
end if;
else
execute immediate plsql
using in out rc, out row_count, in out rc2;
end if;
safely_close(rc);
return rc2;
exception
when others then
error_info :=
'ref_cursor_copy.to_ref_cursor: ' || chr(10) ||
'plsql = ''' || plsql || '''' || chr(10) ||
rtrim(sqlerrm, chr(10)) || chr(10) ||
rtrim(dbms_utility.format_error_backtrace, chr(10));
dbms_output_put_line(error_info);
raise;
end to_ref_cursor;
/**
* Copies a REF CURSOR to memory. Provides the ability to execute a PL/SQL block
* after each row is fetched.
* @param rc REF CURSOR.
* @param plsql_block_after_each_fetch
* PL/SQL block to run for each row after it has been
* fetched, working on the FETCHED_ROW object type instance
* that holds the column values fetched. This can be used to
* transform the data fetched.
* @return An ANYDATA instance for the copy in memory.
*/
function to_anydata(
rc in out sys_refcursor,
plsql_block_after_each_fetch in varchar2 := ''
)
return anydata as
type_name user_types.type_name%type;
begin
return fetch_data(rc, type_name, 'M', plsql_block_after_each_fetch);
end to_anydata;
/**
* Fetches a REF CURSOR to either memory or a table. Provides the ability to
* execute PL/SQL function for each row fetched.
* @param rc REF CURSOR.
* @param type_name
* Object type name of object type representing a row on the
* given REF CURSOR. Has the name of the form
* REF_CURSOR_COPY_<sequence>_T.
* @param destination
* Whether the copy should be made to memory ('M') or to a
* table ('T').
* @param plsql_block_after_each_fetch
* PL/SQL block to run for each row after it has been
* fetched, working on the FETCHED_ROW object type instance
* that holds the column values fetched. This can be used to
* transform the data fetched.
* @return A collection of the data fetched if DESTINATION = 'M'. NULL
* otherwise.
* @throws ORA-20000 if the REF CURSOR contains LONG or LONG RAW columns.
*/
function fetch_data(
rc in out sys_refcursor,
type_name in out user_types.type_name%type,
destination in char := 'M',
plsql_block_after_each_fetch in varchar2 := ''
)
return anydata as
/*pragma autonomous_transaction;*/
plsql varchar2(32767);
plsql2 varchar2(32767);
rc1 sys_refcursor;
rcn number;
rc2n number;
rc2 sys_refcursor;
rcd xmltype;
type_name_c user_types.type_name%type;
table_name user_tables.table_name%type;
n pls_integer;
name_list sys.odcivarchar2list := sys.odcivarchar2list();
type_code_list sys.odcinumberlist := sys.odcinumberlist();
fetched_rows anydata;
j pls_integer;
ref_cur_count pls_integer;
xml xmltype;
begin
-- This is necessary to make it work on Oracle 10.1. If omitted, Oracle
-- throws various "invalid cursor" errors.
if use_odci(false) then
dbms_odci.saverefcursor(rc, rcn);
end if;
plsql := get_fetch_data_plsql(
rc,
type_name,
name_list,
type_code_list,
ref_cur_count,
false,
destination,
plsql_block_after_each_fetch,
true,
xml
);
if plsql is not null then
plsql := plsql || chr(10) ||
'end;';
if ref_cursor_descriptor.describe_impl = 'xC' then
execute immediate plsql
using in xml;
else
if use_odci(false) then
execute immediate plsql
using in rcn, out sub_row_count, out fetched_rows;
if copy_impl like '%R%' then
dbms_odci.restorerefcursor(rc, rcn);
end if;
else
execute immediate plsql
using in out rc, out sub_row_count, out fetched_rows;
end if;
end if;
end if;
safely_close(rc);
return fetched_rows;
exception
when others then
error_info :=
'ref_cursor_copy.fetch_data: ' || chr(10) ||
'plsql = ''' || plsql || '''' || chr(10) ||
rtrim(sqlerrm, chr(10)) || chr(10) ||
rtrim(dbms_utility.format_error_backtrace, chr(10));
dbms_output_put_line(error_info);
safely_close(rc, true);
raise;
end fetch_data;
/**
* Builds the PL/SQL that can be used to fetch a REF CURSOR to either memory or
* a table. Provides the ability to execute PL/SQL function for each row
* fetched.
* @param rc REF CURSOR.
* @param type_name
* Object type name of object type representing a row on the
* given REF CURSOR. Has the name of the form
* REF_CURSOR_COPY_<sequence>_T.
* @param name_list
* Output: A collection of the column names of RC.
* @param type_code_list
* Output: A collection of the column type codes of RC.
* @param ref_cur_count
* Output: Number of REF CURSOR columns in RC.
* @param top Is this a top-level REF CURSOR (TRUE) or an embedded REF
* CURSOR (FALSE)?
* @param destination
* Whether the copy should be made to memory ('M') or to a
* table ('T').
* @param plsql_block_after_each_fetch
* PL/SQL block to run for each row after it has been
* fetched, working on the FETCHED_ROW object type instance
* that holds the column values fetched. This can be used to
* transform the data fetched.
* @param expose_rows
* Should
* @return A collection of the data fetched if DESTINATION = 'M'. NULL
* otherwise.
* @throws ORA-20000 if the REF CURSOR contains LONG or LONG RAW columns.
*/
function get_fetch_data_plsql(
rc in out sys_refcursor,
type_name in out user_types.type_name%type,
name_list in out sys.odcivarchar2list,
type_code_list in out sys.odcinumberlist,
ref_cur_count in out pls_integer,
top in boolean,
destination in char := 'M',
plsql_block_after_each_fetch in varchar2 := '',
expose_rows in boolean := true,
xml out xmltype
)
return varchar2 as
plsql varchar2(32767);
plsql2 varchar2(32767);
rcn integer;
rc2n integer;
rc1 sys_refcursor;
rc2 sys_refcursor;
rcd xmltype;
type_name_c user_types.type_name%type;
table_name user_tables.table_name%type;
n pls_integer;
fetched_rows anydata;
j pls_integer;
context dbms_xmlgen.ctxtype;
table_impl ref_cursor_copy_types.table_impl%type;
begin
if not top and ref_cursor_descriptor.describe_impl = 'xC' then
if rc is not null and rc%isopen then
context := dbms_xmlgen.newcontext(rc);
xml := dbms_xmlgen.getxmltype(context);
rcd := ref_cursor_descriptor.describe(rc);
dbms_xmlgen.closecontext(context);
safely_close(rc, true);
end if;
else
rcd := ref_cursor_descriptor.describe(rc);
end if;
type_name := get_type_name(rcd);
type_name_c := get_collection_type_name(type_name);
table_name := get_table_name(type_name);
table_impl := get_table_impl(table_name, destination);
-- This is necessary to make it work on Oracle 10.1. If omitted, Oracle
-- throws various "invalid cursor" errors.
if use_odci(top) then
dbms_odci.saverefcursor(rc, rcn);
end if;
select count(*)
into ref_cur_count
from table(
xmlsequence(
extract(rcd, '/ROWSET/ROW')
)
) c
where extractvalue(value(c), '//TYPE_CODE') = type_codes.tc_ref_cursor;
if not top and ref_cursor_descriptor.describe_impl = 'xC' then
plsql :=
'declare' || chr(10) ||
' fetched_rows ' || type_name_c || ' := ' || type_name_c || '();' || chr(10) ||
' fetched_row ' || type_name || ';' || chr(10) ||
' n pls_integer := 0;' || chr(10) ||
' ad_fetched_rows anydata;' || chr(10) ||
'begin' || chr(10);
else
plsql :=
'declare' || chr(10) ||
' rc1 sys_refcursor;' || chr(10) ||
' rc2 sys_refcursor;' || chr(10) ||
' fetched_rows ' || type_name_c || ' := ' || type_name_c || '();' || chr(10) ||
' n pls_integer := 0;' || chr(10) ||
' ' || chr(10) ||
' type_name user_types.type_name%type;' || chr(10) ||
' ad_fetched_rows anydata;' || chr(10);
end if;
if not (not top and nvl(ref_cursor_descriptor.describe_impl, '{}') = 'xC') then
for i in 1 .. ref_cur_count loop
plsql := plsql ||
' rce' || i || ' sys_refcursor;' || chr(10) ||
' type_name_e' || i || ' user_types.type_name%type;' || chr(10);
end loop;
plsql := plsql ||
'begin' || chr(10) ||
case
when use_odci(top) then
' dbms_odci.restorerefcursor(rc1, :rcn);' || chr(10)
else
' rc1 := :rc;' || chr(10)
end;
end if;
if upper(destination) = 'T' then
-- Clear table.
plsql := plsql ||
' delete' || chr(10) ||
' from ' || table_name ||
case when table_impl = 'T' then
' t ' || chr(10) ||
' where t."sessionid" = userenv(''sessionid'')'
end ||
';' || chr(10);
end if;
if not top and ref_cursor_descriptor.describe_impl = 'xC' then
plsql := plsql ||
' for r in (' || chr(10) ||
' select value(x) xml' || chr(10) ||
' from table(xmlsequence(extract(:xml, ''/ROWSET/ROW''))) x' || chr(10) ||
' ) loop' || chr(10) ||
' r.xml.toobject(fetched_row);' || chr(10) ||
' fetched_rows.extend(1);' || chr(10) ||
' fetched_rows(fetched_rows.count) := fetched_row;' || chr(10) ||
' n := n + 1;' || chr(10) ||
' end loop;' || chr(10) ||
' :row_count := n;' || chr(10);
else
plsql := plsql ||
' loop' || chr(10) ||
' exit when not rc1%isopen;' || chr(10) ||
' fetch rc1 into ';
n := 0;
j := 1;
for c in (
select extractvalue(value(c), '//ID') id,
extractvalue(value(c), '//NAME') name,
extractvalue(value(c), '//TYPE_CODE') type_code
from table(
xmlsequence(
extract(rcd, '/ROWSET/ROW')
)
) c
) loop
plsql := plsql ||
case when c.id > 1 then ', ' end;
if c.type_code = type_codes.tc_ref_cursor then
-- Embedded REF CURSOR.
plsql := plsql || 'rce' || j;
plsql2 := plsql2 ||
' fetched_row.' || ref_cursor_descriptor.quote_identifier(c.name) ||
' := ref_cursor_copy.fetch_data(' ||
'rce' || j || ', ' ||
'type_name_e' || j ||
');' || chr(10) ||
case when close_impl like '%S%' and close_impl not like '%d%' then
' begin' || chr(10) ||
' if rce' || j || '%isopen then' || chr(10) ||
' close rce' || j || ';' || chr(10) ||
' end if;' || chr(10) ||
' exception' || chr(10) ||
' when others then' || chr(10) ||
' null;' || chr(10) ||
' end;' || chr(10)
end ||
' type_name_e' || j || ' := ' ||
'ref_cursor_copy.get_collection_type_name(type_name_e' || j ||
');' || chr(10);
j := j + 1;
else
plsql := plsql || 'fetched_row.' || ref_cursor_descriptor.quote_identifier(c.name);
end if;
n := n + 1;
name_list.extend(1);
name_list(n) := ref_cursor_descriptor.quote_identifier(c.name);
type_code_list.extend(1);
type_code_list(n) := c.type_code;
end loop;
plsql :=
plsql || ';' || chr(10) ||
' exit when rc1%notfound;' || chr(10) ||
plsql2 ||
' n := n + 1;' || chr(10);
if plsql_block_after_each_fetch is not null then
plsql := plsql ||
'' || chr(10) ||
plsql_block_after_each_fetch || chr(10) ||
'' || chr(10);
end if;
if upper(destination) = 'M' then
plsql := plsql ||
' fetched_rows.extend(1);' || chr(10) ||
' fetched_rows(n) := fetched_row;' || chr(10);
elsif upper(destination) = 'T' then
plsql := plsql ||
' insert into ' || table_name || ' (' || chr(10) ||
case when table_impl = 'T' then
' "sessionid",' || chr(10)
end ||
' "rownum",' || chr(10);
for c in 1 .. name_list.count loop
plsql := plsql ||
' ' || name_list(c) ||
case when c < name_list.count then ', ' end || chr(10);
end loop;
plsql := plsql ||
' )' || chr(10) ||
' values (' || chr(10) ||
case when table_impl = 'T' then
' userenv(''sessionid''),' || chr(10)
end ||
' n,' || chr(10);
for c in 1 .. name_list.count loop
plsql := plsql ||
' fetched_row.' || name_list(c) ||
case when c < name_list.count then ', ' end || chr(10);
end loop;
plsql := plsql ||
' );' || chr(10);
end if;
plsql := plsql ||
' end loop;' || chr(10) ||
case when close_impl is null or
top and
close_impl not like '%D%'
or not top and
close_impl not like '%S%' and
close_impl not like '%d%' then
' begin' || chr(10) ||
' if rc1%isopen then' || chr(10) ||
' close rc1;' || chr(10) ||
' end if;' || chr(10) ||
' exception' || chr(10) ||
' when others then' || chr(10) ||
' null;' || chr(10) ||
' end;' || chr(10)
end ||
' :row_count := n;' || chr(10);
-- Build constructor call to FETCHED_ROW variable.
plsql := replace(
plsql,
' fetched_rows ' || type_name_c || ' := ' || type_name_c || '();',
' fetched_rows ' || type_name_c || ' := ' || type_name_c || '();' || chr(10) ||
' fetched_row ' || type_name || ' := ' || type_name || '(' ||
rtrim(rpad('null, ', 6 * n, 'null, '), ', ') || ');'
);
end if;
if expose_rows then
if destination = 'M' then
plsql := plsql ||
' ad_fetched_rows := anydata.convertcollection(fetched_rows);' || chr(10);
elsif destination = 'T' then
if not table_impl like '%td' then
plsql := plsql ||
' commit;' || chr(10);
end if;
plsql := plsql ||
' ad_fetched_rows := null;' || chr(10);
end if;
plsql := plsql ||
' :rows2 := ad_fetched_rows;';
else
if destination = 'T' and not table_impl like '%td' then
plsql := plsql ||
' commit;';
end if;
end if;
return plsql;
end get_fetch_data_plsql;
/**
* Gets the number of rows fetched in the latest COPY call.
*/
function get_row_count
return pls_integer as
begin
return row_count;
end get_row_count;
/**
* Gets the number of rows fetched in the latest FETCH_DATA call.
*/
function get_sub_row_count
return pls_integer as
begin
return sub_row_count;
end get_sub_row_count;
/**
* Creates two object types and a table matching a description of a REF CURSOR
* obtained from REF_CURSOR_DESCRIBE.DESCRIBE. The object type has the the name
* of the form REF_CURSOR_COPY_<sequence>_T. The VARRAY/TABLE collection has a
* name of the form REF_CURSOR_COPY_<sequence>_C. The table has a name of the
* form REF_CURSOR_COPY_<sequence>. If such object types and table already exist
* they are reused.
* @param ref_cursor_desc
* REF CURSOR description.
* @return Object type name.
*/
function get_type_name(
ref_cursor_desc in xmltype
)
return user_types.type_name%type as
pragma autonomous_transaction;
plsql varchar2(32767);
plsql2 varchar2(32767);
type_name user_types.type_name%type;
type_name_c user_types.type_name%type;
table_name user_tables.table_name%type;
xml varchar2(32767);
description_md5_hash ref_cursor_copy_types.description_md5_hash%type;
description_length pls_integer;
n pls_integer;
nt pls_integer := 0;
m pls_integer;
name_list sys.odcivarchar2list := sys.odcivarchar2list();
decl_list sys.odcivarchar2list := sys.odcivarchar2list();
begin
xml := ref_cursor_desc.getstringval;
description_md5_hash := rawtohex(utl_raw.cast_to_raw(
dbms_obfuscation_toolkit.md5(input_string => xml))
);
description_length := length(xml);
begin
-- Do we have a matching object type already?
select type_name
into type_name
from ref_cursor_copy_types rcct
where rcct.description_md5_hash = get_type_name.description_md5_hash and
rcct.description_length = get_type_name.description_length;
type_name_c := get_collection_type_name(type_name);
table_name := get_table_name(type_name);
-- Do both still exist?
select count(ut1.type_name) + count(ut2.table_name)
into n
from user_types ut1,
user_tables ut2
where ut1.type_name in (
get_type_name.type_name,
get_type_name.type_name_c
) and
ut2.table_name = get_type_name.table_name;
if n < 3 then
-- We had them, but not anymore. Drop what may have remained and remove
-- from mapping.
delete
from ref_cursor_copy_types rcct
where rcct.type_name = get_type_name.type_name;
commit;
begin
execute immediate 'truncate table ' || table_name;
exception
when others then
null;
end;
begin
execute immediate 'drop table ' || table_name;
exception
when others then
null;
end;
begin
execute immediate 'drop type ' || type_name_c;
exception
when others then
null;
end;
begin
execute immediate 'drop type ' || type_name;
exception
when others then
null;
end;
-- Create new ones.
raise no_data_found;
end if;
exception
when no_data_found then
-- We don't have a matching object type -- create one.
select 'REF_CUR_COPY_' || ref_cursor_copy_seq.nextval || '_T'
into type_name
from dual;
type_name_c := get_collection_type_name(type_name);
table_name := get_table_name(type_name);
plsql :=
'create type ' || type_name || ' as object (' || chr(10);
for c in (
select extractvalue(value(c), '//ID') id,
extractvalue(value(c), '//NAME') name,
extractvalue(value(c), '//TYPE_CODE') type_code,
extractvalue(value(c), '//DECLARATION') declaration,
extractvalue(value(c), '//OWNER') owner,
extractvalue(value(c), '//TYPE_NAME') type_name
from table(
xmlsequence(
extract(ref_cursor_desc, '/ROWSET/ROW')
)
) c
) loop
if c.type_code in (type_codes.tc_long, type_codes.tc_long_raw) then
raise_application_error(
-20000,
'ref_cursor_copy: LONG [RAW] is not supported: Column ' ||
c.id || ', name "' || c.name || '"'
);
end if;
plsql := plsql || c.declaration || ',' || chr(10);
name_list.extend(1);
name_list(c.id) := ref_cursor_descriptor.quote_identifier(c.name);
decl_list.extend(1);
decl_list(c.id) := c.declaration;
if c.type_code = type_codes.tc_object then
-- Check whether the type is a nested table.
select count(text)
into m
from all_type_versions atv
where atv.owner = c.owner and
atv.type_name = c.type_name and
atv.typecode = 'COLLECTION' and
lower(text) like '%table%';
if m > 0 then
-- Nested table.
nt := nt + 1;
plsql2 := plsql2 || chr(10) ||
'nested table ' || c.name || ' store as ' || table_name || nt;
end if;
end if;
end loop;
plsql := rtrim(plsql, ',' || chr(10)) || chr(10) ||
');';
begin
-- Create object type.
execute immediate plsql;
-- Create object type collection.
plsql :=
'create type ' || type_name_c || ' as' || chr(10);
if collection_impl = 'V' then
plsql := plsql ||
'varray(2147483647) of ' || type_name || ';';
elsif collection_impl = 'T' then
plsql := plsql ||
'table of ' || type_name || ';';
end if;
execute immediate plsql;
-- Create table.
plsql :=
'create ' ||
case when table_impl in ('td', 'tp') then
'global temporary '
end ||
'table ' || table_name || ' (' || chr(10) ||
case when table_impl = 'T' then
' "sessionid" integer,' || chr(10)
end ||
' "rownum" integer,' || chr(10);
for c in 1 .. decl_list.count loop
plsql := plsql ||
' ' || decl_list(c) ||
case when c < decl_list.count then ',' else '' end || chr(10);
end loop;
plsql := plsql ||
')';
if table_impl = 'td' then
plsql := plsql || chr(10) ||
'on commit delete rows';
elsif table_impl = 'tp' then
plsql := plsql || chr(10) ||
'on commit preserve rows';
end if;
if plsql2 is not null then
-- We have nested table attributes. Specify table for these.
plsql := plsql || chr(10) ||
plsql2;
end if;
execute immediate plsql;
insert into ref_cursor_copy_types (
description_md5_hash,
description_length,
type_name,
type_name_collection,
table_name,
table_impl
)
values (
description_md5_hash,
description_length,
type_name,
type_name_c,
table_name,
table_impl
);
commit;
exception
when others then
dbms_output.put_line('ref_cursor_copy.get_type_name: plsql =');
dbms_output_put_line(plsql);
dbms_output_put_line(sqlerrm);
dbms_output_put_line(dbms_utility.format_error_backtrace);
raise;
end;
end;
return type_name;
end get_type_name;
/**
* Returns collection type name for given object type name.
* @param type_name
* Object type name.
* @return Collection type name.
*/
function get_collection_type_name(
type_name in user_types.type_name%type
)
return user_types.type_name%type as
begin
return substr(type_name, 1, length(type_name) - 1) || 'C';
end;
/**
* Returns table name for given object type name.
* @param type_name
* Object type name.
* @return Table name.
*/
function get_table_name(
type_name in user_types.type_name%type
)
return user_tables.table_name%type as
begin
return substr(type_name, 1, length(type_name) - 2);
end;
/**
* Returns table implementation details for given table name.
* @param table_name
* Table name.
* @return Table implementation used (refer to TABLE_IMPL).
*/
function get_table_impl(
table_name in user_types.type_name%type,
destination in varchar2
)
return ref_cursor_copy_types.table_impl%type as
table_impl ref_cursor_copy_types.table_impl%type;
begin
select table_impl
into table_impl
from ref_cursor_copy_types rcct
where rcct.table_name = get_table_impl.table_name;
return table_impl;
exception
when no_data_found then
if destination = 'M' then
-- Ignore -- we don't need the table as we're copying to memory.
null;
else
raise;
end if;
end get_table_impl;
/**
* Opens a REF CURSOR for a collection.
* @param ad Collection converted to ANYDATA.
* @return REF CURSOR on the collection.
*/
function rc_from_anydata(
ad in anydata,
type_name_collection in varchar2
)
return sys_refcursor as
rc sys_refcursor;
begin
open rc for '
select *
from table(cast(:ad as ' || type_name_collection || '))'
using in ad;
return rc;
end rc_from_anydata;
/**
* Purges data in tables for this session and commits. In general, this is not
* necessary if global temporary tables are used as data will be purged either
* when the transaction is committed or at the very latest when the session
* ends.
* @return Number of rows deleted.
*/
function purge_tables
return integer as
dml varchar2(1000);
n integer := 0;
begin
for t in (
select table_name,
table_impl
from ref_cursor_copy_types
) loop
dml :=
'delete ' || chr(10) ||
'from ' || t.table_name || ' t' || chr(10);
if t.table_impl = 'T' then
-- Not a global temporary table.
dml := dml ||
'where t."sessionid" = userenv(''sessionid'')';
end if;
begin
execute immediate dml;
commit;
exception
when others then
dbms_output.put_line(
'ref_cursor_copy.purge_tables: Purging table "' ||
t.table_name || '": ' || sqlerrm
);
end;
n := n + sql%rowcount;
end loop;
return n;
end purge_tables;
/**
* Drops object types and tables.
* @return Number of objects dropped.
*/
function drop_types_and_tables
return integer as
dxl varchar2(1000);
n integer := 0;
begin
for t in (
select type_name,
type_name_collection,
table_name
from ref_cursor_copy_types
) loop
begin
dxl := 'drop type ' || t.type_name_collection;
execute immediate dxl;
n := n + 1;
exception
when others then
dbms_output.put_line(
'ref_cursor_copy.drop_types_and_tables: Dropping type "' ||
t.type_name_collection || '": ' || sqlerrm
);
end;
begin
dxl := 'drop type ' || t.type_name;
execute immediate dxl;
n := n + 1;
exception
when others then
dbms_output.put_line(
'ref_cursor_copy.drop_types_and_tables: Dropping type "' ||
t.type_name || '": ' || sqlerrm
);
end;
-- Empty table first. Necessary if temporary table in order to avoid
-- ORA-14452: attempt to create, alter or drop an index on temporary table
-- already in use
begin
dxl := 'delete from ' || t.table_name;
execute immediate dxl;
n := n + 1;
exception
when others then
dbms_output.put_line(
'ref_cursor_copy.drop_types_and_tables: Deleting from table "' ||
t.table_name || '": ' || sqlerrm
);
end;
commit;
-- Truncate table first. Necessary if temporary table in order to avoid
-- ORA-14452: attempt to create, alter or drop an index on temporary table
-- already in use
begin
dxl := 'truncate table ' || t.table_name;
execute immediate dxl;
n := n + 1;
exception
when others then
dbms_output.put_line(
'ref_cursor_copy.drop_types_and_tables: Truncating table "' ||
t.table_name || '": ' || sqlerrm
);
end;
begin
dxl := 'drop table ' || t.table_name;
execute immediate dxl;
n := n + 1;
exception
when others then
dbms_output.put_line(
'ref_cursor_copy.drop_types_and_tables: Dropping table "' ||
t.table_name || '": ' || sqlerrm
);
end;
end loop;
delete
from ref_cursor_copy_types;
commit;
return n;
end drop_types_and_tables;
/**
* Determines whether to use DBMS_ODCI.SAVE-/RESTOREREFCURSOR for given context.
* This can be controlled through the configuration of COPY_IMPL.
* @param top TRUE if top-level, FALSE if embedded REF CURSOR.
* @return TRUE if DBMS_ODCI must be used.
*/
function use_odci(top in boolean)
return boolean as
begin
return top and copy_impl like '%O%' or
not top and copy_impl like '%o%';
end use_odci;
/**
* Safely closes a REF CURSOR. This can be controlled by the value of COPY_IMPL
* or forced. If FORCE is FALSE this really shouldn't be necessary, but it could
* help prevent ORA-00600 [17281] [1001].
* @param rc REF CURSOR.
* @param force If TRUE configuration of COPY_IMPL is overridden and RC
* is attempted closed and set to NULL. If FALSE, the value of
* COPY_IMPL controls what is done.
*/
procedure safely_close(
rc in out sys_refcursor,
force in boolean := false
) as
begin
if rc%isopen then
if force or close_impl like '%A%' then
close rc;
end if;
if force or close_impl like '%N%' then
rc := null;
end if;
end if;
exception
when others then
null;
end safely_close;
begin
copy_impl := '';
if portable.get_major_minor_version = 10.1 then
close_impl := 'Dd';
copy_impl := 'OoR';
end if;
if portable.get_major_version = 10 or
portable.get_full_version like '11.1.0.6%' then
if portable.is_option_enabled('Java') and
portable.get_major_minor_version != 10.1 then
/*
* Force Java implementation of REF CURSOR description as the PL/SQL
* implementation doesn't correctly restore the REF CURSOR state, leading
* to one of the following errors:
* ORA-00600 [psdmsc: psdinvdef#1]
* ORA-01001: invalid cursor
*/
ref_cursor_descriptor.describe_impl := 'J2';
else
ref_cursor_descriptor.describe_impl := 'C2';
end if;
end if;
if portable.get_platform != 'Windows' then
-- Use TABLE for collections instead of VARRAY in order to avoid
-- ORA-22909: exceeded maximum VARRAY limit.
collection_impl := 'T' ;
end if;
end ref_cursor_copy;
/
The PL/SQL code shown above depends on the PL/SQL, Java and C code developed in my blog post Describing a REF CURSOR in Oracle 10g+ Using PL/SQL, Java and C.
Examples
Increase EMP.SAL by 10%
In this example we use the PLSQL_BLOCK_AFTER_EACH_FETCH parameter to call a PL/SQL block for each row fetched in order to increase the SAL column by 10%:
set feedback 1
prompt From EMP
select *
from (
select empno,
sal
from emp
order by empno
)
where rownum <= 3;
variable rc2 refcursor
prompt Transformed REF CURSOR copy of EMP
declare
rc1 sys_refcursor;
rc2 sys_refcursor;
type_name user_types.type_name%type;
begin
open rc1 for
select *
from (
select empno,
sal
from emp
order by empno
)
where rownum <= 3;
:rc2 := ref_cursor_copy.to_ref_cursor(
rc1,
type_name,
destination => 'M',
plsql_block_after_each_fetch => 'fetched_row.sal := fetched_row.sal * 1.10;'
);
end;
/
print rc2
which shows
From EMP
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
3 rows selected.
Transformed REF CURSOR copy of EMP
PL/SQL procedure successfully completed.
EMPNO SAL
---------- ----------
7369 880
7499 1760
7521 1375
3 rows selected.
Forcing DBMS_XMLGEN to Generate XML Schema-compliant Date/Time Values Without Side Effects
In this example we use the PLSQL_BLOCK_BEFORE_COPY_OPEN parameter to change various NLS date/time formats in order to make DBMS_XMLGEN generate XML Schema-compliant date/time values. Since we’re doing this after the original REF CURSOR has been fetched, this does not have any side effects on other calls to TO_CHAR in the original query:
set serveroutput on format truncated
alter session set nls_date_format = 'dd-mon-yy';
alter session set nls_timestamp_format = 'dd-mon-rr hh.mi.ssxff am';
alter session set nls_timestamp_tz_format = 'dd-mon-rr hh.mi.ssxff am tzr';
declare
rc1 sys_refcursor;
rc2 sys_refcursor;
type_name user_types.type_name%type;
context dbms_xmlgen.ctxtype;
xml xmltype;
begin
open rc1 for
select 1 id,
123.456 n,
'abcDEF' vc,
cast(timestamp'2011-02-01 14:25:30' as date) d,
timestamp'2011-02-01 14:25:30.123456789' t1,
timestamp'2011-02-01 14:25:30.123456789 +02:00' t2,
to_char(cast(timestamp'2011-02-01 14:25:30' as date)) d_tc,
to_char(timestamp'2011-02-01 14:25:30.123456789') t1_tc,
to_char(timestamp'2011-02-01 14:25:30.123456789 +02:00') t2_tc
from dual;
rc2 := ref_cursor_copy.to_ref_cursor(
rc1,
type_name,
destination => 'M',
plsql_block_before_copy_open =>
q'[
dbms_session.set_nls(
'nls_date_format', '''yyyy-mm-dd"T"hh24:mi:ss'''
);
dbms_session.set_nls(
'nls_timestamp_format', '''yyyy-mm-dd"T"hh24:mi:ss.ff9'''
);
dbms_session.set_nls(
'nls_timestamp_tz_format', '''yyyy-mm-dd"T"hh24:mi:ss.ff9tzh:tzm'''
);
]'
);
context := dbms_xmlgen.newcontext(rc2);
xml := dbms_xmlgen.getxmltype(context);
dbms_xmlgen.closecontext(context);
dbms_output_put_line(xml.getclobval);
dbms_session.set_nls(
'nls_date_format', '''dd-mon-yy'''
);
dbms_session.set_nls(
'nls_timestamp_format', '''dd-mon-rr hh.mi.ssxff am'''
);
dbms_session.set_nls(
'nls_timestamp_tz_format', '''dd-mon-rr hh.mi.ssxff am tzr'''
);
end;
/
which produces the following output:
Session altered. Session altered. Session altered. <ROWSET> <ROW> <ID>1</ID> <N>123.456</N> <VC>abcDEF</VC> <D>2011-02-01T14:25:30</D> <T1>2011-02-01T14:25:30.123456789</T1> <T2>2011-02-01T14:25:30.123456789+02:00</T2> <D_TC>01-feb-11</D_TC> <T1_TC>01-feb-11 02.25.30.123456789 pm</T1_TC> <T2_TC>01-feb-11 02.25.30.123456789 pm +02:00</T2_TC> </ROW> </ROWSET> PL/SQL procedure successfully completed.
Please note how the values for columns D, T1 and T2 are generated with XML Schema-compliant date/time values, whereas the values for columns D_TC, T1_TC and T2_TC are generated according to the NLS settings active when the original REF CURSOR was opened.
Known Issues
Fragile Cursor Management in Oracle Database 10g Release 1
Cursor management in Oracle Database 10g Release 1 seems extremely fragile. Often when the REF CURSORs are closed Oracle throws ORA-00600 [17281] [1001] when or before you terminate your session. Also when they are closed, Oracle might reuse them in an incorrect manner, leading to errors like ORA-01001: invalid cursor, ORA-01007: variable not in select list etc. The only solution I’ve found on 10.1 is to configure the implementation to not close any of the REF CURSORs, which obviously you need to be careful about. Oracle will close them correctly when your session ends but if you’ve got code that runs for a long time, calling REF_CURSOR_COPY, you might need to increase the init.ora parameter open_cursors or migrate to Oracle 10.2 or newer.
Another issue is that transferring REF CURSORs into and out from dynamic PL/SQL often leads to ORA-01001: invalid cursor. The solution here is to use DBMNS_ODCI.SAVE-/RESTOREREFCURSOR and transfer the obtained cursor number instead.
These issues have been verified on 10.1.0.2.0, 10.1.0.3.0, 10.1.0.4.0 and 10.1.0.5.0.
ORA-01001: invalid cursor in DBMS_SQL
If Oracle throws ORA-01001: invalid cursor in DBMS_SQL try to use either the direct Java or the direct C description implementation as this is a bug in DBMS_SQL. This seems to be related to DBMS_SQL that cannot handle CURSOR expressions in the call to DBMS_SQL.TO_CURSOR_NUMBER.
Example:
set serveroutput on format truncated
declare
rc1 sys_refcursor;
rc2 sys_refcursor;
type_name user_types.type_name%type;
describe_impls dbms_utility.name_array;
xml xmltype;
begin
describe_impls(1) := 'P';
describe_impls(2) := 'J2';
describe_impls(3) := 'C2';
for i in nvl(describe_impls.first, 0) .. nvl(describe_impls.last, -1) loop
ref_cursor_descriptor.describe_impl := describe_impls(i);
dbms_output.put_line('ref_cursor_descriptor.describe_impl = ''' ||
ref_cursor_descriptor.describe_impl || '''');
open rc1 for
select d.deptno,
d.dname,
cursor (
select empno,
hiredate,
ename
from emp e
where e.deptno = d.deptno and
rownum <= 2
) emp_cursor
from dept d
where deptno < 40;
begin
rc2 := ref_cursor_copy.to_ref_cursor(
rc1,
type_name,
destination => 'M',
plsql_block_before_copy_open => 'xml_gen.set_xml_nls_formats;'
);
xml := xml_gen.from_ref_cursor(rc2);
dbms_output_put_line(xml.getclobval);
xml_gen.set_session_nls_formats;
exception
when others then
dbms_output.put_line(sqlerrm);
dbms_output.put_line(dbms_utility.format_error_backtrace);
end;
end loop;
end;
/
which shows the following on Oracle 11.2.0.1.0 (abbreviated):
ref_cursor_descriptor.describe_impl = 'P'
ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_SQL", line 2610
ORA-06512: at "SCOTT.REF_CURSOR_DESCRIPTOR_PLSQL", line 123
ORA-06512: at "SCOTT.REF_CURSOR_DESCRIPTOR", line 132
ORA-06512: at "SCOTT.REF_CURSOR_DESCRIPTOR", line 83
ORA-06512: at "SCOTT.REF_CURSOR_COPY", line 425
ORA-06512: at "SCOTT.REF_CURSOR_COPY", line 305
ref_cursor_descriptor.describe_impl = 'J2'
<ROWSET>
<ROW>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<EMP_CURSOR>
<EMP_CURSOR_ROW>
<EMPNO>7782</EMPNO>
<HIREDATE>1981-06-09T00:00:00</HIREDATE>
<ENAME>CLARK</ENAME>
</EMP_CURSOR_ROW>
<EMP_CURSOR_ROW>
<EMPNO>7839</EMPNO>
<HIREDATE>1981-11-17T00:00:00</HIREDATE>
<ENAME>KING</ENAME>
</EMP_CURSOR_ROW>
</EMP_CURSOR>
</ROW>
<ROW>
<DEPTNO>20</DEPTNO>
<DNAME>RESEARCH</DNAME>
<EMP_CURSOR>
<EMP_CURSOR_ROW>
<EMPNO>7369</EMPNO>
<HIREDATE>1980-12-17T00:00:00</HIREDATE>
<ENAME>SMITH</ENAME>
</EMP_CURSOR_ROW>
<EMP_CURSOR_ROW>
<EMPNO>7566</EMPNO>
<HIREDATE>1981-04-02T00:00:00</HIREDATE>
<ENAME>JONES</ENAME>
</EMP_CURSOR_ROW>
</EMP_CURSOR>
</ROW>
<ROW>
<DEPTNO>30</DEPTNO>
<DNAME>SALES</DNAME>
<EMP_CURSOR>
<EMP_CURSOR_ROW>
<EMPNO>7499</EMPNO>
<HIREDATE>1981-02-20T00:00:00</HIREDATE>
<ENAME>ALLEN</ENAME>
</EMP_CURSOR_ROW>
<EMP_CURSOR_ROW>
<EMPNO>7521</EMPNO>
<HIREDATE>1981-02-22T00:00:00</HIREDATE>
<ENAME>WARD</ENAME>
</EMP_CURSOR_ROW>
</EMP_CURSOR>
</ROW>
</ROWSET>
ref_cursor_descriptor.describe_impl = 'C2'
<ROWSET>
<ROW>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<EMP_CURSOR>
<EMP_CURSOR_ROW>
<EMPNO>7782</EMPNO>
<HIREDATE>1981-06-09T00:00:00</HIREDATE>
<ENAME>CLARK</ENAME>
</EMP_CURSOR_ROW>
<EMP_CURSOR_ROW>
<EMPNO>7839</EMPNO>
<HIREDATE>1981-11-17T00:00:00</HIREDATE>
<ENAME>KING</ENAME>
</EMP_CURSOR_ROW>
</EMP_CURSOR>
</ROW>
<ROW>
<DEPTNO>20</DEPTNO>
<DNAME>RESEARCH</DNAME>
<EMP_CURSOR>
<EMP_CURSOR_ROW>
<EMPNO>7369</EMPNO>
<HIREDATE>1980-12-17T00:00:00</HIREDATE>
<ENAME>SMITH</ENAME>
</EMP_CURSOR_ROW>
<EMP_CURSOR_ROW>
<EMPNO>7566</EMPNO>
<HIREDATE>1981-04-02T00:00:00</HIREDATE>
<ENAME>JONES</ENAME>
</EMP_CURSOR_ROW>
</EMP_CURSOR>
</ROW>
<ROW>
<DEPTNO>30</DEPTNO>
<DNAME>SALES</DNAME>
<EMP_CURSOR>
<EMP_CURSOR_ROW>
<EMPNO>7499</EMPNO>
<HIREDATE>1981-02-20T00:00:00</HIREDATE>
<ENAME>ALLEN</ENAME>
</EMP_CURSOR_ROW>
<EMP_CURSOR_ROW>
<EMPNO>7521</EMPNO>
<HIREDATE>1981-02-22T00:00:00</HIREDATE>
<ENAME>WARD</ENAME>
</EMP_CURSOR_ROW>
</EMP_CURSOR>
</ROW>
</ROWSET>
Supporting Oracle Database 9i Release 2
The techniques in REF_CURSOR_COPY and the related blog can be made to work for Oracle 9.2 as well but you would need to remove calls to DBMS_ODCI.SAVE-/RESTOREREFCURSOR and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE as these procedures are not available in Oracle 9.2.
I haven’t used Conditional Compilation techniques for this as it would exclude Oracle versions 10.1.0.2.0 and 10.1.0.3.0.
Improvements
The code can be improved in the following ways:
- Usage of
BULK COLLECTin the fetch process. I didn't bother implementing this because it's not possible to useBULK COLLECTwith embeddedREF CURSORs because we cannot declare an associative array type that holdsREF CURSORs (you getPLS-00990: Index Tables of Cursor Variables are disallowed). So implementingBULK COLLECTwould only be used if the incomingREF CURSORdoesn't have embeddedREF CURSORs. - Commit interval in the code generated when
DESTINATIONis'T'(table), preventing errors in case the rollback segment is not large enouogh to hold all rows. - The
GET_TYPE_NAMEfunction that creates object types and table for handling a givenREF CURSORcould be improved such that it’s guarded by a critical section/region, preventing that multiple sessions try to create identical object types (with different names through the sequence) with the same MD5 hash, all but the first failing with aDUP_VALUE_ON_INDEXexception.
Source Code
You can download the source code here.
The PL/SQL code depends on the PL/SQL, Java and C code developed in my blog post Describing a REF CURSOR in Oracle 10g+ Using PL/SQL, Java and C.
Installation
Install the code for the other blog post first. Then run grant.sql with SQL*Plus logged on as a DBA, specifying the schema you would like to install in as a parameter and then run install.sql with SQL*Plus, logged on to the schema you want the code to be installed in.
Conclusion
The code shown here has been fairly well tested with the following Oracle versions and editions:
- Oracle Database 10g Release 1 10.1.0.2.0 Personal Edition on Windows XP SP3.
- Oracle Database 10g Release 1 10.1.0.3.0 Enterprise Edition on Intel Solaris 10.
- Oracle Database 10g Release 2 10.2.0.1.0 Express Edition on Windows XP SP3.
- Oracle Database 10g Release 2 10.2.0.1.0 Express Edition on Red Hat Enterprise Linux 5.3.
- Oracle Database 10g Release 2 10.2.0.2.0 Enterprise Edition on Intel Solaris 10.
- Oracle Database 11g Release 1 11.1.0.6.0 Personal Edition on Windows XP SP3.
- Oracle Database 11g Release 1 11.1.0.6.0 Enterprise Edition on SPARC Solaris 10.
- Oracle Database 11g Release 2 11.2.0.1.0 Personal Edition on Windows 7.
Feel free to use the code at your own risk. I welcome your feedback and suggestions for improvements but the code as such is not supported.
