1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

overriding exeception handler

Discussion in 'SQL PL/SQL' started by manik, Nov 23, 2009.

  1. manik

    manik Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Kochi
    Dear Friends,

    while working on some data migration i came across a particular problem. it vl be very helpful if anyone could find a solution. i had created a procedure to import data from one table into another. the tables had got the same structure, but the data in the table which i am tryin to import data frm has duplications that is denying me frm pushing the data into th other table coz i hav created sme contraints that the other does't have. i am using a cursor to bring the data frm the table and by using a for loop i am inserting it into the other table. but if an duplicate entry comes the for loop is existed and exeception handler is fired. my requirement is to omit those data and still continue inserting without existing the for loop. is it possible?? is there any way i could override the exception??
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    @ Admin, Please move this post to "Queries and Discussions".

    Hi Manik,

    You have to use separate BEGIN .. EXCEPTION .. END block inside the procedure as follows.

    Code (SQL):

    CREATE OR REPLACE PROCEDURE IMPORT_DATA AS
    BEGIN
       FOR x IN (SELECT * FROM T1)
       LOOP

          BEGIN

          INSERT INTO T2  ...... ;

          EXCEPTION
          WHEN DUP_VAL_ON_INDEX THEN
             NULL ;
          WHEN OTHERS THEN
             ---- Log Error
          END;

       END LOOP;



    EXECEPTION
    WHEN OTHERS THEN
      ----- Log Error
    END;
     
    Anyway, It is always better to use Strainght SQL instead of procedure to import the data (with NOT EXISTS ) as below

    Code (SQL):

    INSERT INTO T2
    SELECT *
    FROM T1
    WHERE NOT EXISTS (SELECT NULL
                                   FROM  T1
                                  WHERE T1.uniq_field = T2.uniq_field );
     
    Staright SQL is better option.
     
  3. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Moved... Thanks Raj, Nice to see you back.
     
  4. manik

    manik Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Kochi
    Thanks Rajavu!! tats was very helpful...!