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_at_4ax.com>


[Quoted] 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 [Quoted] that DDL commits, cleaning up all of those views at some point in the future -- [Quoted] not pretty.

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

[Quoted] It works by relying on the fact that a query's answer is 'preordained' when the [Quoted] 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 [Quoted] see it, open a cursor to select it -- delete all of the data before we fetch the [Quoted] 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 [Quoted] was created by our transaction and will not be freed until we ourselves commit [Quoted] (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 [Quoted] other in any event). The table will always appear empty except for the brief [Quoted] 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 - 15:38:18 CET

Original text of this message