DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Error in SQL*Loader !!! (Very Urgent)

  1. #1
    Join Date
    Mar 2002
    Posts
    38

    Error in SQL*Loader !!! (Very Urgent)

    Hi Friends,
    The following are the details

    Emp Table
    IDNUMBER Varchar2(13),
    NAME Varchar2(30)


    Data in the Emp Table (Already Exists in the database)
    1 Wilson Samuel
    2 Anthony Rogers
    3 Robert James

    Data to be loaded from Flat File
    1 Nikil Jeck
    5 Kingston Roy

    Database Trigger
    CREATE OR REPLACE TRIGGER EMPCHECK
    BEFORE INSERT ON EMP
    FOR EACH ROW
    DECLARE
    V_ID VARCHAR2(15);
    BEGIN
    SELECT IDNUMBER INTO V_ID FROM EMP
    WHERE IDNUMBER = :NEW.IDNUMBER;
    IF V_ID = :NEW.IDNUMBER THEN
    UPDATE EMP
    SET NAME = NAME
    WHERE IDNUMBER = :NEW.IDNUMBER;
    END IF;
    END;

    SQL*Loader Error:
    ORA-04088: error during execution of trigger 'SYSDEV.EMPCHECK'

    Now I receive the above flat file and I have to load it into the Emp table by using SQL*Loader.In the Flat file if I have the IDNUMBER which already exists then I need to update the table else I need to insert the new record.For this I am creating a database trigger.But I am getting error while loading.Pls let me know how I can proceed further and let me know if I am doing wrong in my scripts.If you can give me the script it will be more helpful.


    My end result in the table should be as below (After Load)
    1 Nikil Jeck
    2 Anthony Rogers
    3 Robert James
    5 Kingston Roy



    Thanks in Advance

    Kishan
    SUROOP B

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Exclamation

    1) With sql*loader you cannot replace (or update) rows on a table.
    2) Your trigger is malformed -- the underlying error is: mutating table.

    Workaround: Remove trigger, load data into staging table and update/insert into target table.

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Mar 2002
    Posts
    38
    Hi Brown,
    Thank you very much for your suggestions. Now I am loading the data into the staging table and removed the trigger. Can you pls let me know how can update/insert into the actual table by checking the ID.As I am very new please give me the script to do which will be very useful.

    Thanks
    Kishan
    SUROOP B

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    What version of Oracle are you on? In 9i you could re-define the SQL*Loader routine as an external table anduse a MERGE statement to do all this in one step.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Mar 2002
    Posts
    38
    Hi Slim,
    I am using Oracle 9i. Can you pls elaborate or show any example of how to do with these new features.

    Thank You
    Kishan
    SUROOP B

  6. #6
    Join Date
    Mar 2002
    Posts
    38
    Thank you very much Slimdave. Now I am able to solve it and I got the solution.You are great !!

    Kishan
    SUROOP B

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width