Problem using dbms_job from within insert trigger

[slicendice]

Hi
I am performing a migration into a large Oracle database that is the back-end for a client appliation.
One of the tables I am inserting into, using PL/SQL, has an AFTER EACH ROW insert trigger that kicks off a job using DBMS_JOB.SUBMIT and DBMS_JOB.RUN (the trigger is part of the application).
Every time I try and insert a row into this table, the CPU usage goes through the roof and then the SQL*Plus session bombs out with:

===============================================
ERROR:
ORA-03114: not connected to ORACLE

begin
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

ORA-24323: value not allowed
Error accessing package DBMS_APPLICATION_INFO
ERROR:
ORA-03114: not connected to ORACLE
===============================================

Accompanying this in the Oracle log file is the dreaded ORA-0600.

Occasionally (*very* occasionally!), after hammering the CPU for a while, it works - so this has the added complexity of being inconsistent! However, if I disable the trigger that uses DBMS_JOB, everything works fine every time.

I really have no idea why this is happening, and would greatl appreciate any help anyone can give.

Thanks very much

 

[MPeccorini]

Have you tried changing the job to execute a dummy procedure? that way you can determine if the problem is because of dbms_job usage or the procedure itself.

 

[slicendice]

Yes, I've tried exchanging the job for a trivial one (selecting a value from dual) but the problem remains.

I've also tried stepping through it using a PL/SQL debugger (not always entriely reliable, I know!) and it's when the calls to RDBMS_JOB occur that the debugger just hangs (along with 100% CPU).

Thanks

 

[sem]

Try to create wrapper procedure calling DBMS_JOB with AUTONOMOUS TRANSACTION, this may be transaction-related.

Regards, Dima

http://seminihin.narod.ru

 

[slicendice]

Hi sem

The call to DBMS_JOB is already in an AUTONOMOUS TRANSACTION procedure:

PROCEDURE Pro_Create_Auto_Referral_Job(pPerID IN INTEGER)
IS
iJobNum PLS_INTEGER := -1;
bCommit BOOLEAN := TRUE;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
BEGIN
-- Create job
dbms_job.submit(iJobNum,'PARIS.pro_Create_Auto_Referral('||pPerID||');',SYSDATE,'sysdate + 1/86400',NULL);

EXCEPTION
WHEN OTHERS THEN
bCommit := FALSE;
END;

IF (bCommit) THEN
COMMIT;

BEGIN
-- Run job immediately
dbms_job.RUN(iJobNum);
-- Remove Job when job finished...
dbms_job.remove(iJobNum);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
ELSE
ROLLBACK;
END IF;
END;

I should also point out that when this is fired off via the front-end application, there are no problems - it only seems to be a problem when inserting a row using PL/SQL that the problem occurs.
I can also run a simple INSERT statement from a SQL*Plus window and again, there are no problems.

 

[sem]

Does PARIS.pro_Create_Auto_Referral query this table by id? In fact when your job starts at first time there's no record in table (it exists in another transaction not finished yet). It may be a locking issue. Try to deffere execution and look what happens.

But it would be even better to redesign your logic, because you start a job regardless of the success of insert statement, thus you may get potentiallly a lot of died jobs. I may suggest single job that executes PARIS.pro_Create_Auto_Referral in a loop.

Regards, Dima

http://seminihin.narod.ru

 

[MPeccorini]

I totally agree with sem.

 

[slicendice]

sem, thanks for your comments

If it was a locking issue, surely that wouldn't manifest itself in causing the ORA-03113 and ORA-24323 errors that I've outlined above? And also, it wouldn't result in excessive CPI usage would it? If it was a locking issue, wouldn't it just sit there waiting for a lock to be released rather than hogging CPI and then crashing?

Also, it doesn't explain why it seems to work fine when triggered by the front-end application - if it was a locking problem then wouldn't it manifest itself regardless of whether it was the front-end app or a PL/QSL script that was triggering it?

Thanks for your comments and suggestions - they are greatly appreciated

 

[sem]

I didn't mean ordinary locking, but rather some internal low-level processes, resource contention etc.
BTW, what is your exect oracle version? We had similar problems (not DBMS_JOB, but rather AT-related) and they've been resolved by applyin a patch (upgraded to 8.1.7.4, if I rememder).

It worked from front-end, because in this case all work was done serially, not parallelly, as with AT.


Regards, Dima

http://seminihin.narod.ru