Help with exception
- From: ora_forum <ora_forum@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 8 Jan 2007 12:20:01 -0800 (PST)
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
Other related posts:
