Re: How to create an object from PL/SQL: SOLVED!

unter_at_mecati.mecasoft.ch (Stefano UNTERNAEHRER) wrote:

>Hi!
 

>My aim was to create sequences having the 'start with' value as
>max(current_used_value)+1 for different tables. Thank to you,
>and in particular thank to Branislav Valny <valny_at_slovnaft.sk>
>I've solved my problem with:
 

>SQL> declare
> 2 n number;
> 3 str string(100);
> 4 cur integer;
> 5 begin
> 6 cur := dbms_sql.open_cursor;
> 7 select max(pkey)+1 into n from pieces;
> 8 str := 'create sequence pieces_seq start with '||n ;
> 9 dbms_sql.parse(cur, str, dbms_sql.v7);
> 10 dbms_sql.close_cursor(cur);
> 11 exception
> 12 when others then
> 13 dbms_sql.close_cursor(cur);
> 14 end;
> 15 /
 

>PL/SQL procedure successfully completed.
 

>As you can see, it works!
>SQL> select max(pkey) from pieces;
> MAX(PKEY)
>----------
> 5269
>SQL> select pieces_seq.nextval from dual;
> NEXTVAL
>----------
> 5270
 

>Thank you again!
>Stefano

An alternative that doesn't require PL/SQL at all would be:

SET PAGES 0
SET FEEDBACK OFF
SPOOL create.sq2
SELECT 'CREATE SEQUENCE pieces_seq START WITH '||MAX(pkey)+1||';' FROM pieces;
SPOOL OFF
_at__at_create.sq2

--
Chuck Hamilton
chuckh_at_ix.netcom.com

Never share a foxhole with anyone braver than yourself!