If someone has a few minutes to read through this, I'd be most appreciative. I could use a second set of eyes looking this over.
This procedure is designed to maintain a table/sequence map, executed after an import and increment any sequences which have a lower nextval that the max value in the column it's supposed to be matching. Often the sequences are out-of-sync after an import (even full=y and direct=y) and we have to manually adjust them. This is an effort to automate the process.
These are the displays and error from the procedure, and the code follows. The problem is in the execute immediate which is doing DDL. It's Oracle 8.1.7 on Solaris so DDL in execute immediate alter is supposed to work. The execute immediate insert does work.
Any suggestions or comments are welcome. Thanks, Linda
top of loop, counter is:1
top of loop, counter is:2
top of loop, counter is:3
top of loop, counter is:4
top of loop, counter is:5
top of loop, counter is:6
top of loop, counter is:7
top of loop, counter is:8
top of loop, counter is:9
top of loop, counter is:10
top of loop, counter is:11
top of loop, counter is:12
Show l_sql_string 4 DECLARE L_SEQUENCE_OWNER VARCHAR2(30):= :1; L_SEQUENCE_NAME VARCHAR2(30):= :2; BEGIN insert into
dbauser.table_sequence_map values (:1,:2, null,null,null,sysdate,sysdate); END;
top of loop, counter is:13
top of loop, counter is:14
top of loop, counter is:15
top of loop, counter is:16
top of loop, counter is:17
top of loop, counter is:18
top of loop, counter is:19
Show l_sql_string 1 DECLARE L_SEQUENCE_OWNER VARCHAR2(30):= :1; L_SEQUENCE_NAME VARCHAR2(30):= :2; L_INCREMENT_VALUE NUMBER
:= :3; BEGIN alter sequence :1.:2 increment by :3; END;
BEGIN table_sequence_mender ('01-DEC-2001','dev01') ; END;
*
ERROR at line 1:
ORA-06550: line 1, column 129:
PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:
begin declare exit for goto if loop mod null pragma raise
return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall
<a single-quoted SQL string>
ORA-06512: at "DBAUSER.TABLE_SEQUENCE_MENDER", line 148
ORA-06512: at line 1
create or replace procedure table_sequence_mender (date_in in date, ORACLE_SID in char ) is
fileid0 UTL_FILE.FILE_TYPE;
c_sequences_added number ;
c_sequences_unmapped number ;
c_sequences_ok number ;
c_sequences_fixed number ;
c_sequences_total number ;
v_column_name varchar(30);
v_create_dt date ;
v_dt date ;
v_increment number ;
v_max_column number ;
v_nextval number ;
v_sequence_owner varchar(30);
v_sequence_name varchar(30);
v_sqlcode number ;
v_table_owner varchar(30);
v_table_name varchar(30);
v_sql varchar(200) ;
v_cursor_id integer;
v_insert varchar(200) ;
l_sql_string varchar(2000) ;
--
-- The purpose of this procedure is to adjust the existing sequences after an import to match the dependent columns.
-- Requirements: A table matching the sequences to the table/column names. This is built manually because there is
-- no physical relationship between a sequence and a column. The table is populated by install_sequence_mender.ksh.
-- The execution is running under userid dbauser. Table access in PL/SQL will not use roles, but individual grants;
-- Therefore a grant of DBA to dbauser is insufficient to run this procedure. The install ksh grants the necessary
-- authority to dbauser.
--
cursor c is
select sequence_owner, sequence_name
from dba_sequences
where sequence_owner not in ('SYS','SYSTEM') ;
Begin
c_sequences_added := 0 ;
c_sequences_ok := 0 ;
c_sequences_fixed := 0 ;
c_sequences_unmapped := 0 ;
c_sequences_total := 0 ;
if date_in is null
then v_dt := sysdate;
else v_dt := date_in;
end if;
Begin
-- Scan table_sequence_map and delete any rows for sequences that no longer exist
Delete from table_sequence_map where sequence_owner||sequence_name not in
(select sequence_owner||sequence_name from dba_sequences) ;
End ;
-- start processing the rows in dba_sequences
begin
for i_row in c loop
c_sequences_total := c_sequences_total + 1 ;
dbms_output.put_line ('top of loop, counter is:'||c_sequences_total ) ;
begin
-- For every row in dba_sequences, find a matching row in the table_sequence_map
select table_owner, table_name, column_name, create_dt
into v_table_owner, v_table_name, v_column_name, v_create_dt
from dbauser.table_sequence_map a
where i_row.sequence_owner = a.sequence_owner
and i_row.sequence_name = a.sequence_name ;
v_sqlcode := SQLCODE ;
-- If a row exists for the sequence, and if the table information is populated,
-- then check the max value in the data column and the nextval from the sequence.
if v_sqlcode = 0 then
if v_table_owner is not null and v_table_name is not null and v_column_name is not null
then begin
v_sql := 'select max('||v_column_name||') from '||v_table_owner||'.'||v_table_name ;
execute immediate v_sql into v_max_column ;
v_sql := 'select '||i_row.sequence_owner||'.'||i_row.sequence_name||'.nextval from dual ' ;
execute immediate v_sql into v_nextval ;
end ;
-- If the max column number is > the nextval, it means the sequence is broken or out of sync and
-- has to be incremented to higher than the max column. Do the math to find the difference,
-- alter the sequence increment, call it to move the number up, and alter the sequence increment
-- back to 1.
if v_max_column > v_nextval then begin v_increment := v_max_column - v_nextval + 1 ;
l_sql_string:= 'DECLARE '||
'L_SEQUENCE_OWNER VARCHAR2(30):= :1; '||
'L_SEQUENCE_NAME VARCHAR2(30):= :2; '||
'L_INCREMENT_VALUE NUMBER := :3; '||
'BEGIN ';
l_sql_string:= l_sql_string||'alter sequence :1.:2 increment by :3; END;';
dbms_output.put_line ('Show l_sql_string 1 '||l_sql_string) ;
execute immediate l_sql_string using i_row.sequence_owner, i_row.sequence_name, v_increment ;
commit;
l_sql_string:= 'DECLARE '||
'L_SEQUENCE_OWNER VARCHAR2(30):= :1; '||
'L_SEQUENCE_NAME VARCHAR2(30):= :2; '||
'L_INCREMENT_VALUE NUMBER := :3; '||
'BEGIN ';
l_sql_string:= l_sql_string||'select :1.:2 into v_nextval from dual; END;';
dbms_output.put_line ('Show l_sql_string 2 '||l_sql_string) ;
execute immediate l_sql_string using i_row.sequence_owner, i_row.sequence_name ;
commit;
l_sql_string:= 'DECLARE '||
'L_SEQUENCE_OWNER VARCHAR2(30):= :1; '||
'L_SEQUENCE_NAME VARCHAR2(30):= :2; '||
'L_INCREMENT_VALUE NUMBER := :3; '||
'BEGIN ';
l_sql_string:= l_sql_string||'alter sequence :1.:2 increment by 1; END; ';
dbms_output.put_line ('Show l_sql_string 3 '||l_sql_string) ;
execute immediate l_sql_string using i_row.sequence_owner, i_row.sequence_name ;
commit;
c_sequences_fixed := c_sequences_fixed + 1 ;
end ;
end if ;
-- If the table_sequence_map contains an entry for the sequence, but the table information is blank,
-- it means that the sequence was added to the database, and to the table_sequence_map by a previous
-- execution of this job, and the table information was never added. Updating the table information
-- has to be manual because there are no naming conventions to guarantee that a
-- sequence and column are associated.
elsif ( (v_table_owner is null) or (v_table_name is null) or (v_column_name is null) ) then
dbms_output.put_line (i_row.sequence_owner||'.'||i_row.sequence_name||' is not mapped in table_sequence_map since '||v_create_dt ) ;
c_sequences_unmapped := c_sequences_unmapped + 1 ;
end if ;
end if ;
exception
-- If there's no entry in the table_sequence_map for the sequence, it means a new sequence has been
-- added to the database. This section will add a row for the sequence to the table_sequence_map.
-- The table and column information has to be manually entered with an update statement as there's
-- no naming convention to guarantee that a sequence and column are associated.
when no_data_found
then
begin
l_sql_string:= 'DECLARE '||
'L_SEQUENCE_OWNER VARCHAR2(30):= :1; '||
'L_SEQUENCE_NAME VARCHAR2(30):= :2; '||
'BEGIN ';
l_sql_string:= l_sql_string||'insert into dbauser.table_sequence_map values (:1,:2, null,null,null,sysdate,sysdate); END;';
dbms_output.put_line ('Show l_sql_string 4 '||l_sql_string) ;
execute immediate l_sql_string using i_row.sequence_owner, i_row.sequence_name ;
commit;
exception
when no_data_found then null ;
c_sequences_added := c_sequences_added + 1 ;
dbms_output.put_line ('New row added to the map table '||i_row.sequence_owner||'.'||i_row.sequence_name ) ;
COMMIT ;
end ;
when others then raise ;
end ;
end loop ;
end ;
end ;
/
