-
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
-
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
-
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
-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|