Re: creating sequence using a variable

A copy of this was sent to zbaumel_at_shadowfinancial.com (ziv) (if that email address didn't require changing) On 23 May 2001 07:04:49 -0700, you wrote:

>Let me explain what is the problem.
>
>I would like to create a sequence starting with a value I get from a
>select statment.
>
>CREATE SEQUENCE <NAME> START WITH <VARIABLE> .....
>
>ORACLE gives me invalid number error.
>The variable is define correctly, I can print the value to the screen
>with no problem, but the CREATE SEQUENCE does not like it.
>

in 8i and up:

begin

   for x in ( select number_col from some_table where ... )    loop

      execute immediate 'create sequence <name> start with ' || x.number_col;    end loop;
end;
/

in 8.0 and before, you'll have to use DBMS_SQL to dynamically execute the statement.

DDL cannot have ANY bind variables.

see
http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html if you put that in a procedure and get an ORA-01031

>Thanks
>Ziv
>
>"Phil Haigh" <phil.haigh_at_fwpharma.com> wrote in message news:<v4BO6.1717$8Z5.77677_at_monolith.news.easynet.net>...
>> ziv wrote in message <5b98834.0105221318.5388547f_at_posting.google.com>...
>> >Hi all,
>> >I am trying to create a sequence using a variable which I will read in to a
 value.
>> >I get an erroe -01722 invalid number.
>> >any one?
>> >Thanks
>>
>> Not quite sure what your issue is but here goes...
>>
>> Presumably you've already created your sequence:
>> create sequence my_seq;
>> You can then grab values using my_seq.nextval (which increments the counter)
>> and my_Seq.currval (which returns the value you last obtained from nextval):
>> SELECT my_seq.nextval FROM Dual (SQL*Plus)
>> SELECT my_seq.nextval INTO myVar FROm Dual (PL/SQL)

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp