Challenge: Execute Immediate equivalent in 8.0

[mortonsa]

Hi Everybody,

I have a little problem. I have written a procedure in 8i that runs beautifully using an Execute Immediate command. Problem is, the client I have to deliver this to is using Oracle 8.0. Can somebody please help me re-write my procedure so it works in 8.0? I have got my books out but these dbms_sql packages have me cross eyed! Thanks is advance!

Notes: This procedure dynamically resets the sequences associated with tables in the database that have a name starting with 'T' (tAttorney for example) and a sequence with a naming convention of seq_tablename (seq_tattorney). The sequence will be dropped and recreated with a starting
number of one higher than the maximum primary key value
of the table.

CREATE OR REPLACE PROCEDURE ECC.RESETSEQUENCES
AS
v_table_name varchar2(50);
v_primarykeyname varchar2(50);
v_maxidy number(28);
v_sequence_name varchar2(50);
v_getidysql varchar2(1000);
v_dropsql varchar2(1000);
v_createsql varchar2(1000);

cursor c_sequence is
select t.table_name, s.sequence_name
from all_tables t, user_sequences s
where t.table_name like 'T%' and
substr(sequence_name,5,length(rtrim(sequence_name))) = t.table_name
and owner = 'ECC';

begin
for i in c_sequence loop

--Get the name of the table
v_table_name := i.table_name;

--Get the name of the table's primary key
Select column_name into v_primarykeyname
from all_tab_columns
where table_name = i.table_name and
column_id = 1;

--Get the highest idy of the table
v_getidysql := 'Select max(' ||v_primarykeyname|| ')from '||v_table_name;
Begin
Execute immediate v_getidysql into v_maxidy;
End;

If v_maxidy is null then
v_maxidy := 0;
End If;

--Get the name of the sequence associated with the table
v_sequence_name := i.sequence_name;

--Drop the sequence
Begin
v_dropsql := 'DROP SEQUENCE '||v_sequence_name;
Execute Immediate v_dropsql;
End;

--Recreate the sequence starting at one higher than the max idy in the table
Begin
v_createsql := 'CREATE SEQUENCE '||v_sequence_name||' START WITH '||TO_CHAR(v_maxidy + 1);
Execute Immediate v_createsql;
End;
End Loop;
Commit;
End;

 

[karluk]

You simply have to replace your execute immediate commands with the appropriate call to the DBMS_SQL package. For an example of code see thread186-76531.