Re: why this code does not work in sqlplus?

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Mon, 22 May 2000 16:27:13 -0400
Message-Id: <10505.106278@fatcity.com>

As I'm sure you've already discovered, bind variables cannot be used in DDL statements. I saw that you've already written a PL/SQL package to do what you need to do. That may ultimately be the best choice, but there is a SQL*Plus solution, and that would look something like this:

column maxpromoid new_value maxpromotionid select max(promotionid)+1 maxpromoid
from hpxpromotion;

--At this point, the SQL*Plus user variable should --have the correct value.

create sequence hpxseq_promotion
incremenet by 1
start with &maxpromotionid;

In essence, use a user variable, not a bind variable.=20

regards,

Jonathan

---

jonathan_at_gennick.com =20
http://gennick.com
Brighten the Corner Where You Are

On Mon, 22 May 2000 07:58:35 -0800, you wrote:

>Hi:
>
>I am trying to create some oracle sequence based on the current max ID=20
>value. Does any one know why it does not work in sqlplus? I got the=20
>following error:
>
>
>MAX(PROMOTIONID)
>----------------
> 69819
> START WITH :maxpromotionid +1
> *
>ERROR at line 3:
>ORA-01722: invalid number
>
>
>
>Here is the script:
>-------------------
>variable maxpromotionid NUMBER;
>
>select max(promotionID)
>into :maxpromotionid
>from hpxpromotion;
>
>
>CREATE SEQUENCE HPXSEQ_Promotion
> INCREMENT BY 1
> START WITH :maxpromotionid +1
> MAXVALUE 999999999
> MINVALUE 1
> NOCYCLE
> NOCACHE;
>----------------------
>
>If you reply, please send me a copy to zlmei_at_hotmail.com
>
>Thanks.
>
>Guang
>________________________________________________________________________
>Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
Received on Mon May 22 2000 - 15:27:13 CDT