Re: Dynamic SQL Cursors

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 07 Nov 1999 09:38:18 -0500
Message-ID: <v4olONyDbkbvQZImUU032Ky+IeJf@4ax.com>

A copy of this was sent to "Simon Hedges" <shedges_at_hhhh.freeserve.co.uk> (if that email address didn't require changing) On Sat, 6 Nov 1999 19:09:31 -0000, you wrote:

>
>Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
>news:LBgjOL=A12UbX1T2m+OP4oywI9+U_at_4ax.com...
>> A copy of this was sent to "Chad Sheley" <csheley_at_usa.capgemini.com>
>> (if that email address didn't require changing)
>> On Fri, 5 Nov 1999 11:11:48 -0600, you wrote:
>>
>> >I am fairly knew to the PL/SQL / Oracle world, but have become pretty
>> >familiar with a couple of concepts: returning a cursor from a function or
>> >stored procedure AND dynamic SQL.
>> >
>> >Now... I want to combine the 2 concepts. I want to dynamically create a
>SQL
>> >statement and then return a cursor object from my function that creates
>the
>> >statement. As near as I can tell, the dynamic SQL package (DBMS_SQL)
>> >identifies the cursor with an integer variable, not a cursor object.
>> >
>> >How can I reference a cursor object created from a dynamic SQL statement.
>> >
>>
>> you cannot until Oracle8i, release 8.1
>
>It's possible that you can create a view using dynamic SQL, and then base
>the cursor on selecting from the view. This would, of course, not be
>possible if the view was completely different each time it was
>created, but if the view (on recreation) had an identical (or very similar)
>structure,
>with identical column names , then you might get away with it.
>I think it would certainly work if all you needed to do was to
>change the where clause dynamically.
>

I wouldn't want to create a view/query -- the overhead of doing DDL, the fact that DDL commits, cleaning up all of those views at some point in the future -- not pretty.

Here is something that works as well - if you have a reasonable number of rows. You would create a table that we be used as a temporary table. Our procedure will always open "select * from that_table". our procedure will also fill and delete that table. Using multi-versioning and read consistency, we'll get our result set in a non-blocking fashion that does not require us to clean up the data at some future point.

tkyte_at_8.0> create or replace package dyn_ref_cur_pre8i   2 as
  3 type refCur is ref cursor;
  4
  4 procedure doit( p_sql_stmt in varchar2, p_rc in out refCur );   5 end;
  6 /

Package created.

tkyte_at_8.0> create or replace package body dyn_ref_cur_pre8i   2 as
  3
  3 procedure doit( p_sql_stmt in varchar2, p_rc in out refCur )   4 as

  5      exec_cursor     integer default dbms_sql.open_cursor;
  6      rows_processed  number  default 0;
  7  begin
  8      dbms_sql.parse(exec_cursor, 'insert into TMP_emp ' || p_sql_stmt,
dbms_sql.native );
  9      rows_processed := dbms_sql.execute(exec_cursor);
 10      dbms_sql.close_cursor( exec_cursor );
 11  
 11      open p_rc for select * from TMP_emp;
 12      delete from TMP_emp;

 13 end;
 14
 14
 14 end;
 15 /

Package body created.

tkyte_at_8.0> 
tkyte_at_8.0> variable rc refcursor
tkyte_at_8.0> exec dyn_ref_cur_pre8i.doIt( 'select * from emp where job =

''CLERK''', :rc )

PL/SQL procedure successfully completed.

tkyte_at_8.0> print rc

     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------

---

      7369 SMITH CLERK 7902 17-DEC-80 800 20

      7876 ADAMS CLERK 7788 12-JAN-83 1100 20

      7900 JAMES CLERK 7698 03-DEC-81 950 30

      7934 MILLER CLERK 7782 23-JAN-82 1300 10

tkyte_at_8.0> exec dyn_ref_cur_pre8i.doIt( 'select * from emp where mgr is NULL', :rc )

PL/SQL procedure successfully completed.

tkyte_at_8.0> print rc

     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------

---

      7839 KING PRESIDENT 17-NOV-81 5000 10

It works by relying on the fact that a query's answer is 'preordained' when the cursor is opened (read consistency). This is achived by multi-versioning (rollback segments). We can put some data into a table so our transaction can see it, open a cursor to select it -- delete all of the data before we fetch the first row and then sucessfully fetch all of the rows we had inserted.

This will not suffer from a ORA-1555 since the rollback our transaction needs was created by our transaction and will not be freed until we ourselves commit (so don't commit or rollback before you are done fetching else you will be subject to ORA-1555).

This will not suffer from any multi-user issues. Any number of people can concurrently insert/select/delete from this table (you might want to use >1 freelist if you expect lots of concurrent queries but they will not block each other in any event). The table will always appear empty except for the brief moment of time you are in the procedure itself.

>Simon Hedges
>Gloucester
>UK
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Nov 07 1999 - 08:38:18 CST