sql loader is not loading the table with the error SQL*Loader-601:

1
votes

there is a daily job that runs 4 times a day. I could see that weekly twice the last 4th job is failing and by log i could see that it shows as SQL*Loader-601: For INSERT option, table must be empty. For each run the job creates a temporary table then it drops and same is happening here. Even though the table has been dropped it shows this error. Daily after the 3rd run i have checked n could see that the table is dropped since then i am getting this error. Can anyone let me know in this ?

1
oraclesql-loader
Also the task status is in stale in oracle - User071295
You need to look at what the job is doing in more detail; we can't see that or how SQL*Loader is being run or what is in the control file. Maybe the fourth load is to a different table, or there's a schema issue and it's hitting the wrong table, or it's accidentally calling sqlldr twice, or... who knows. Unfortunately there isn't enough information or detail to help you. - Alex Poole
" the job creates a temporary table then it drops". That itself is a red flag. Instead of constantly creating and dropping "temporary" tables in your job, you should just - once - create a Global Temporary Table, and use that. Of course we can't know for sure, since we don't have the code you are asking us to comment on. - EdStevens

1 Answers

0
votes

can you verify by query dba_objects or all_objects for table recreate datetime? that will tell you if table is successful drop and recreated.

scott