Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL CREATE SEQUENCE

Re: PL/SQL CREATE SEQUENCE

From: <newopt_at_my-deja.com>
Date: Mon, 14 Feb 2000 17:30:19 GMT
Message-ID: <889e3b$qlj$1@nnrp1.deja.com>


Thanks Mark, I am new to PL/SQL and wasn't aware of the parser issue even with comments.

In article <881uj3$s1d$1_at_nnrp1.deja.com>,   markp7832_at_my-deja.com wrote:
> In article <881n24$m4l$1_at_nnrp1.deja.com>,
> newopt_at_my-deja.com wrote:
> > Why does this pl/sql script prompt for the cursor name when I have
> > supplied in the script itself?
> >
> > SQL> get test3.sql
> > 1 Declare
> > 2 cid INTEGER;
> > 3 -- This procedure is used to create a new sequence used to
> > 4 -- populate the seq_number column in each NCR table
> > 5 -- with a unique ascending sequence number starting
> > 6 -- with 1.
> > 7 BEGIN
> > 8 -- Open new cursor and return cursor ID.
> > 9 cid := DBMS_SQL.OPEN_CURSOR;
> > 10 -- Parse and immediately execute dynamic SQL statement built by
> > 11 -- concatenating the sequence name to DROP & CREATE SEQUENCE
> > commands.
> > 12 DBMS_SQL.PARSE(cid, 'DROP SEQUENCE ' || 'seq_number_next',
> > dbms_sql.v7);
> > 13 DBMS_SQL.PARSE(cid, 'CREATE SEQUENCE ' || 'seq_number_next
> START
> > WITH 1 MINVALUE 1', dbms_sql.v7);
> > 14 -- Close cursor.
> > 15 DBMS_SQL.CLOSE_CURSOR(cid);
> > 16 EXCEPTION
> > 17 -- If an exception is raised, close cursor before exiting.
> > 18 WHEN OTHERS THEN
> > 19 DBMS_SQL.CLOSE_CURSOR(cid);
> > 20 RAISE; -- reraise the exception
> > 21* END;
> > 22 /
> > Enter value for create:
> > old 11: -- concatenating the sequence name to DROP & CREATE
SEQUENCE
> > commands.
> > new 11: -- concatenating the sequence name to DROP SEQUENCE
> commands.
> >
> > PL/SQL procedure successfully completed.
> >
> You are executing your pl/sql via SQL*Plus and the '&' is the prompt
> for variable identifier for sqlplus and even though you have it in a
> pl/sql comment sqlplus sees it hence you are prompted. Try using the
> letter N or and for 'and' in your comments.
>
> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
> 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 Feb 14 2000 - 11:30:19 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US