Hi All,
We are using SQL loader in our .Net application to insert data into oracle Data base. We are inserting through bulk insert process.
We have another Stored procedure in Oracle which deletes data from the same table where insertion happens. But the data sets where deletion and insertion happen are completely different (Deletion happens on data which is Older than 2 days). Usually insertion happens in the morning and deletion happens in the evening. Till here we don't have any issues.
However when we run both Insertion and Deletion at the same time, deletion is happening successfully but insertion is failing. When we look at the SQL loader logs we get the error like
"SQL*Loader-951: Error calling once/load initialization
ORA-00604: error occurred at recursive SQL level 1
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired".
We are fine if the insertion process waits until the Deletion is completed, but it should not fail.
Can you please let us know how can we prevent the failure of the insertion.