Help with exception

From: ora_forum <ora_forum_at_yahoo.com>
Date: Mon, 8 Jan 2007 12:20:01 -0800 (PST)
Message-ID: <20070108202001.74155.qmail@web39705.mail.mud.yahoo.com>

Hi All!

I need some assistance with PL/SQL. I have database with 80 training and one admin schemas. Each trn schemas has 200 tables. Training schemas get refreshed every night with data from admin schema. I have written 2 stored procedures (which I call from shell script) to truncate and insert data. Some time I hit ORA-00001: unique constraint error and my insert procedure gets terminated. Could you please an example of exception block, so every time when Oracle hit this error my process will continue to run? I want procedure skip insert for one table and start insert into next table.

CREATE OR REPLACE PROCEDURE insert_data is query_str1 VARCHAR2(500);
query_str2 VARCHAR2(500);
owner varchar2 (20);

BEGIN
FOR n IN (SELECT USERNAME FROM ALL_USERS WHERE USERNAME like '%TRAIN%') LOOP

                                FOR r IN (SELECT table_name FROM dba_tables WHERE owner=n.username  AND 
                                                TABLE_NAME IN(SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER='ADMIN' AND NUM_ROWS <>0))
  LOOP
                IF r.table_name ='AUDIT_LOG' THEN
                                                query_str1 :=  ' insert into  '||n.username||'.'||r.table_name||' select A.* from admin.'||r.table_name||' A where not exists (select COUNTER from ' || n.username||'.'||r.table_name||' B where B.COUNTER = A.COUNTER)';
                                                EXECUTE IMMEDIATE query_str1 ;
                                                commit;
                ELSE
                    query_str2 :=  ' insert into  /*+ APPEND */ '||n.username||'.'||r.table_name||' select * from admin.'||r.table_name;
                    EXECUTE IMMEDIATE query_str2 ;
                commit;
                END IF;

END LOOP;
END LOOP; dbms_output.put_line ('Last Insert in '||owner||' completed: '|| TO_CHAR(SYSDATE, 'DD/MM/YYYY HH12:MI:SS PM')) ;

exception

END;
/

Thank you for your help.
George.

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jan 08 2007 - 14:20:01 CST