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

[bsuroop]

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

[LKBrwn_DBA]

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.

[bsuroop]

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

[slimdave]

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.

[bsuroop]

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

[bsuroop]

Thank you very much Slimdave. Now I am able to solve it and I got the solution.You are great !!

Kishan