Re: Any PL/SQL guru can help me?
Date: 1995/12/04
Message-ID: <donigian_dave-0412951345310001_at_156.80.155.107>
As part of my dba duties, I have to regenerate all the sequence numbers for our database (coversions to new releases cause some of the primary key values t0 change). I've included, at the bottom of this post, a PL/SQL routine which goes through all tables for a specific schema/user and creates/sets the sequence number generator for all tables which have a single, numeric primary key. Hope this helps others.
David
In article <496ic5$g8_at_inet-nntp-gw-1.us.oracle.com>, mlonnrot_at_us.oracle.com wrote:
> Actually, bind-variables are illegal in DDL statements. Convert 'n' to
> a character string and use it as a constant in the create sequence
> command:
>
> str := 'create sequence seq1 start with ' || to_char(n);
>
> mboduch_at_interaccess.com (Michael T. Boduch) wrote:
>
> >In article <DIJoqq.HrH_at_mecati.mecasoft.ch> unter_at_mecati.mecasoft.ch
(Stefano UNTERNAEHRER) writes:
> >>I need help with PL/SQL !
> >>Yes I know, I have to attend a PL/SQL course... anyway:
> >>Here is my problem: I'm trying to create a sequence starting with
> >>vaule = max(current_value)+1.
> >>This should be interesting for quite a lot of people...
> >>This is my last version, but still having errors:
>
> <snip>
>
> >>This command gives an error in line 10, but I think it is enough to let you
> >>understand what I'm trying to do...
> >>I've also found in manual Oracle7 Server Documentation Addendum Release 7.1,
> >>Chapter 7, a call to dbms_sql.execute, but this give me the error
> >>PLS-00221: 'EXECUTE' is not a procedure or is undefined
> >That's because dbms_sql.execute is actually a function, not a procedure.
> >I assume that you used the procedure calling convention when you attempted
> >to call dbms_sql.execute...
> >Still, since the statement you're trying to execute is DDL, you really
> >don't need to call "execute"--the statement is execute at parse time
> >(but I'm sure you knew that.) As to the overall problem, and without
> >knowing the exact error being generated, could it be possible that the
> >sequence that you're trying to create already exists? I don't see an
> >attempt to drop the sequence before creating it.
> >>Many thanks for any help or suggestions!
> >>Stefano
> >Hope that helps some. Good luck to you, Stefano.
> >Mike
>
> Magnus Lonnroth
> Senior Product Manager
> Internet Products, Server Technologies
> Oracle Corp.
create or replace procedure getseq as
/* ** Cursor to loop through all primary keys for tables where ** the table has only one, numeric primary key. The idea is that any ** primary key of NUMBER type which is the ONLY primary key ** in a table most likely needs a sequence generator ** If you have two tables sharing the same numeric primary key ** then you'll have to exclude one of them somehow, otherwise ** this code will create the sequence on the first table, then drop it ** and recreate it for the second
*/
cursor c_seq is
select a.column_name, a.table_name
from user_cons_columns a,
user_tab_columns d
where a.constraint_name in
(select c.constraint_name from user_constraints b, user_cons_columns c where b.constraint_type = 'P' and b.constraint_name = c.constraint_name having count(c.constraint_name) = 1 group by c.constraint_name) and a.table_name = d.table_name and a.column_name = d.column_name and d.data_type = 'NUMBER';
tab_name varchar2(50);
col_name varchar2(50);
create_string varchar2(50);
start_num NUMBER;
num_rows NUMBER;
actr NUMBER;
rows_processed NUMBER;
ddl_cursor integer; ddl_string varchar2(100); dml_string varchar2(100);
BEGIN ddl_cursor := sys.dbms_sql.open_cursor;
open c_seq;
LOOP
fetch c_seq into col_name, tab_name;
EXIT when c_seq%NOTFOUND;
/* check to see if there is any data in the table */ dml_string := 'select count('||col_name||') from '
||tab_name;
sys.dbms_sql.parse(ddl_cursor, dml_string, sys.dbms_sql.v7); sys.dbms_sql.define_column(ddl_cursor, 1, num_rows); rows_processed := sys.dbms_sql.execute(ddl_cursor); actr := sys.dbms_sql.fetch_rows(ddl_cursor); sys.dbms_sql.column_value(ddl_cursor, 1, num_rows);
if (num_rows > 0) then
/* Find out what the largest value is */
dml_string := 'select max('||col_name||') from '
||tab_name;
sys.dbms_sql.parse(ddl_cursor, dml_string, sys.dbms_sql.v7);
sys.dbms_sql.define_column(ddl_cursor, 1, start_num);
rows_processed := sys.dbms_sql.execute(ddl_cursor);
actr := sys.dbms_sql.fetch_rows(ddl_cursor);
sys.dbms_sql.column_value(ddl_cursor, 1, start_num);
else
start_num := 0;
end if;
/* check to see if the sequence already exists */
dml_string :=
'select count(*) from user_sequences where sequence_name = ''SEQ_'
||upper(col_name)||''' ';
sys.dbms_sql.parse(ddl_cursor, dml_string, sys.dbms_sql.v7);
sys.dbms_sql.define_column(ddl_cursor, 1, num_rows);
rows_processed := sys.dbms_sql.execute(ddl_cursor);
actr := sys.dbms_sql.fetch_rows(ddl_cursor);
sys.dbms_sql.column_value(ddl_cursor, 1, num_rows);
if (num_rows <> 0) then
/* if so drop it */
ddl_string := 'drop sequence SEQ_'||col_name;
sys.dbms_sql.parse(ddl_cursor, ddl_string, sys.dbms_sql.v7);
end if;
/*
** create the new sequence generator with the form of
** SEQ_ appended with the name of the column, e.g. SEQ_TABLE_ID
*/
ddl_string := 'create sequence SEQ_'||col_name||
' start with '||to_char(start_num + 1);
sys.dbms_sql.parse(ddl_cursor, ddl_string, sys.dbms_sql.v7);
end loop;
sys.dbms_sql.close_cursor(ddl_cursor);
END getseq;
-- David Donigian Booz, Allen & Hamilton donigian_dave_at_bah.comReceived on Mon Dec 04 1995 - 00:00:00 CET