:ROW_COUNT: Output number of rows fetched from :RC1.Where we’re supplying the following bind variables: Optionally call PL/SQL block after copy is opened. Optionally call PL/SQL block before copy is opened.įrom table(cast(fetched_rows as ref_cur_copy_c))
, null) įetched_rows ref_cur_copy_c := ref_cur_copy_c() įetch rc1 into fetched_row.
The code generated would have the following form:įetched_row ref_cur_copy_t := ref_cur_copy_t(null. Call dynamic PL/SQL code, returning a new REF CURSOR opened for selecting from the copy.Given PL/SQL block after the new copy is opened.Given PL/SQL block before the new copy is opened.Given PL/SQL function for each row that was fetched.Generate dynamic PL/SQL code that fetches all rows from given REF CURSOR into either memory or table, returning a new REF CURSOR selecting from this copy, optionally calling:.Create/reuse object types and table compatible with given REF CURSOR.This is the basic algorithm of the solution, implemented in a PL/SQL package REF_CURSOR_COPY: This blog post describes the code, which has taken a significant amount of time to develop. This code leverages the REF CURSOR description technique described in my blog post Describing a REF CURSOR in Oracle 10 g+ Using PL/SQL, Java and C. Solution: I’ve developed PL/SQL code that implements the use case (except for LONG and LONG RAW columns). Performance is not crucial.Ĭhallenges: REF CURSORs are very limited and in some Oracle releases quite fragile to work with. You need to fetch the REF CURSOR into transient (memory) or persistent storage (normal or global temporary table). 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 10 g Release 1 and newer.