Re: "create sequence" statement and 'starting with' clause issue

On Nov 20, 10:30 am, "termoPilucco" <termoPilu..._at_gmail.com> wrote:
> Hello, i'm using Oracle 8.1.7 (but i need to make things working on 9
> and 10g too).
> I want to create a sequence using a SQL text file and launcihng with
> sqlplus.
> My problem is that i want to initialize it at a value that depends from
> the database itself.
> As far as i know, the "start with" clause in the "create sequence"
> statement needs a number... but i need a dinamic number (the results of
> a query).
>
> I even tried to make a pl/sql script, but it seem the "create sequence"
> statement is not supported in pl/sql
>
> Any idea?
> thanks,
> termoPilucco

Sure
CREATE SEQUENCE is DDL (Data Definition Language). DDL is not possible in PL/SQL except via EXECUTE IMMEDIATE <command string>.
You need to see why this is:
First of all EXECUTE IMMEDATE is *dynamic* sql (as opposed to the normal *static* sql) and needs to be parsed at runtime. This limits scalability.
Secondly, it is EXTREEMLY BAD practice to create a database on the fly. Further details on EXECUTE IMMEDIATE in the PL/SQL manual.

-- 
Sybrand Bakker
Senior Oracle DBA