Re: Dynamic SQL Cursors

Actually, this is EXACTLY how I implemented it. ;-) I just thought there was a better way.

Thanks for all the help.

Chad

Thomas Kyte wrote in message ...
>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 Mon Nov 08 1999 - 10:41:40 CST