Re: Re-using Cursors in DBMS

From: <pbolduc_at_my-deja.com>
Date: Mon, 29 Nov 1999 20:47:30 GMT
Message-ID: <81uop0$3cv$1@nnrp1.deja.com>

I would suggest creating a procedure that does it.

PROCEDURE DDL(sql IN VARCHAR2) AS
v_cursor INTEGER;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;

  DBMS_SQL.PARSE(v_cursor, sql);
  DBMS_SQL.EXECUTE(v_cursor);
  DBMS_SQL.CLOSE_CURSOR(v_cursor);

END; DDL('CREATE TABLE emp ( empno NUMBER, ename VARCHAR2(30) );');

Phil Bolduc
North Vancouver, BC Canada

In article <81uf60$rvm$1_at_nnrp1.deja.com>, john_galt29_at_my-deja.com 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 ...
>
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Nov 29 1999 - 14:47:30 CST

Re: Re-using Cursors in DBMS_SQL