?
Solved

Temporary Table in PL/SQL / Avoid duplicate key inserts

Posted on 2007-10-18
4
Medium Priority
?
4,635 Views
Last Modified: 2013-12-19
I am trying to use a table to store the key of records I have inserted, and check that table for occurrences of the primary key to verify it does not exist before another is inserted.  The below code works, but uses a normal oracle table and is slow, so I thought some sort of virtual table would be better.  Can I query Associative arrays like they were tables?  Is that efficient?  Any alternate suggestions?  

DECLARE
   var_A number;   var_B number;   count_A number;
   
   CURSOR cur_C IS SELECT (...) FROM tab_X;
   
BEGIN

   EXECUTE IMMEDIATE 'CREATE TABLE tab_a (...)';

   OPEN cur_C;
   LOOP
         FETCH cur_C INTO var_A, var_B;
   
         SELECT count(A) INTO count_A FROM tab_X WHERE A = var_A;
   
         IF count_A = 0 THEN
               INSERT INTO tab_B (...)
           ELSE
               /* Log Dupes */
         END IF;
   
         EXIT WHEN cur_C%NOTFOUND;
   
   END LOOP;
   
   CLOSE cur_C;
   
   COMMIT;

    /* Build tab_A indexes here */
   
END;
0
Comment
Question by:CalDude
  • 2
4 Comments
 
LVL 21

Expert Comment

by:oleggold
ID: 20101212
You can execute the same DDL command with temporary clause
0
 
LVL 12

Expert Comment

by:jwahl
ID: 20101262
i think there is no need to use tables for this purpose. simply do

INSERT INTO tab_b
SELECT ...
FROM   tab_a
WHERE NOT EXISTS (
    SELECT 1
    FROM   tab_b
    WHERE tab_b.a = tab_a.a);

if you have a primary key or unique key on tab_b, you could handle the duplicates in an exception:

BEGIN
   INSERT ..
   FROM ..
   WHERE NOT EXISTS ();
EXCEPTION
   WHEN DUP_VAL_ON_INDEX
              ... -- do something
END;
0
 
LVL 12

Accepted Solution

by:
jwahl earned 1200 total points
ID: 20101298
sorry, last post only makes sense inside a pl/sql block with cursor ... also DUP_VAL_ON_INDEX will never be raised if NOT EXISTS is used ... ;-)

BEGIN
    FOR tab_a_rt IN (SELECT * FROM tab_a)
    LOOP
        BEGIN
            INSERT INTO tab_b VALUES (tab_a_rt.a, tab_a_rt.b, ...);
        EXCEPTION
            WHEN DUP_VAL_ON_INDEX
              ... -- do something
        END;
    END LOOP;
END;
/

0
 

Author Comment

by:CalDude
ID: 20101326
Thanks!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question