Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Help with PL/SQL

danield_2May 15 2007 — edited May 16 2007
I need a PL/SQL code that would to this:

1. go through all rows in a table T1 (columns c1, c2)
2. insert them into table T2 (columns c1, c2 both set as a composite primary key)
3. if a row from T1 already exists in T2 and would violate PK constraint on T2 either skip this row or ignore error and continue with a next row from T1

I have this so far:

SET SERVEROUTPUT ON
DECLARE
sr_rec v$sql%ROWTYPE;
BEGIN
FOR sr_rec IN (
SELECT *
FROM v$sql)
LOOP
INSERT INTO sql_repository(sql_id, hash_value)
VALUES (sr_rec.sql_id, sr_rec.hash_value);
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

But this PL/SQL encounters an exception and exits. I need something that either check whether the same sql_id, hash_vale already exist in sql_repository and then skips that row, or quietly ignores exceptions and continues further down the v$sql view.

Any advice is greatly appreciated. Thank you for your time.

DanielD
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 13 2007
Added on May 15 2007
17 comments
692 views