Re: Re-using Cursors in DBMS

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 29 Nov 1999 13:58:04 -0500
Message-ID: <h4j54s4mfjo5ljno7g3e2p32ckjbdoie90@4ax.com>

A copy of this was sent to john_galt29_at_my-deja.com (if that email address didn't require changing) On Mon, 29 Nov 1999 18:03:47 GMT, you wrote:

>I am using DBMS_SQL to create a bunch of tables, triggers, sequences,
>etc. Instead of creating a bunch of interegere variables like this:
>
>Cursor1 integer default dbms_sql.open_cursor;
>
>I would like to create one variable and just keep opening, parsing and
>closing it, changing the SQL statement each time. But even though I am
>careful to close the cursor each time, I get an "Invalid Cursor" error
>everytime I try to re-open a cursor with a different SQL statement ...
>

easy -- DON'T close the cursor! Here is an example:

tkyte_at_8i> create or replace procedure demo 2 as

  3      exec_cursor     integer default dbms_sql.open_cursor;
  4      rows_processed  number  default 0;
  5  begin
  6      dbms_sql.parse(exec_cursor, 'create table t1 ( x int )',
dbms_sql.native );
  7      rows_processed := dbms_sql.execute(exec_cursor);
  8  
  9      dbms_sql.parse(exec_cursor, 'create table t2 ( x int )',
dbms_sql.native );
 10      rows_processed := dbms_sql.execute(exec_cursor);
 11  
 12      dbms_sql.close_cursor( exec_cursor );

13 end;
14 /

Procedure created.

tkyte_at_8i>
tkyte_at_8i> drop table t1;
drop table t1

*
ERROR at line 1:
ORA-00942: table or view does not exist

tkyte_at_8i> drop table t2;
drop table t2

*
ERROR at line 1:
ORA-00942: table or view does not exist

tkyte_at_8i>
tkyte_at_8i> exec demo

PL/SQL procedure successfully completed.

tkyte_at_8i> desc t1

 Name                                                  Null?    Type
 ----------------------------------------------------- --------
------------------------------------
 X                                                              NUMBER(38)

tkyte_at_8i> desc t2
 Name                                                  Null?    Type
 ----------------------------------------------------- --------
------------------------------------
 X                                                              NUMBER(38)

>This must be possible. Can anyone tell me how to do this without
>creating 30 variables, all of which do the exact same thing?
>
>Thanks.
>John
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
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 29 1999 - 12:58:04 CST

Re: Re-using Cursors in DBMS_SQL