Re: Creating sequences w/ variable starting values

The trick to creating dynamic DDL in PL/SQL is using the DBMS_SQL package. You can look how to use the package in most Oracle reference. Below an example of how to dynamically drop constraints:

Declare

   v_statement1      Varchar2 (100);
   v_getc            Integer;
   v_status          Integer;
   v_exec            Integer;

   cursor c_constraint is
       select table_name, constraint_name
       from   user_constraints
       where  r_constraint_name =
          (select constraint_name
            from   user_constraints
             where  table_name      = 'TRACKED_PERSON'
             and    constraint_type = 'P');

Begin

   for c_constraint_rec in c_constraint loop

      v_statement1 := 'Alter table '  || c_constraint_rec.table_name  ||
                      ' drop constraint ' ||  c_constraint_rec.constraint_name;

      v_getc := dbms_sql.open_cursor;
      dbms_sql.parse (v_getc, v_statement1, dbms_sql.v7);
      v_exec := dbms_sql.execute (v_getc);
      dbms_output.put_line(v_statement1);

The Open_Curson, Parse, and Execute are the key portions of this example.

Good Luck,

Meryl Birn
Pacific Northwest National Laboratory

Michael Spoonauer wrote:

> I should have been more descriptive in my first message. My question is
> whether Oracle supports setting the starting value of a sequence using a
> variable that has been populated from a select statement, e.g., (pardon the
> pseudocode)...
>
> DECLARE
>
> SEQ_START NUMBER(5) := 1;
>
> CURSOR CURSOR1 IS
> SELECT MAX(TABLE_COL)
> FROM TABLE;
>
> BEGIN
>
> OPEN CURSOR1;
> FETCH CURSOR1 INTO SEQ_START;
> CLOSE CURSOR1;
>
> WHENEVER SQLERROR CONTINUE;
> DROP SEQUENCE MY_SEQ;
>
> WHENEVER SQLERROR EXIT ROLLBACK;
> CREATE SEQUENCE MY_SEQ
> INCREMENT BY 1
> START WITH SEQ_START
> MAXVALUE 99999;
>
> If DDL is not supported within PL/SQL blocks, can I use substitution
> variables or some other trick?
>
> Thanks,
> Mike Spoonauer
>
> ------
>
> (original message follows)
>
> Does anyone know if it is possible to set the starting value for a sequence
> I am about to drop and recreate based on the returned value from a select
> statement against a table or group of tables?
>
> I'm in Oracle 7.3.x.
>
> Thanks,
> Mike Spoonauer
Received on Tue Dec 08 1998 - 21:39:53 CET