Home » SQL & PL/SQL » SQL & PL/SQL » [Problem] Stored Procedure and Scheduler
[Problem] Stored Procedure and Scheduler [message #376685] Thu, 18 December 2008 04:55 Go to next message
FabienB
Messages: 2
Registered: December 2008
Location: France
Junior Member
Hi all !

First of all, excuse my english which is rather poor Embarassed

On a 10G database, I created a job like this :
dbms_scheduler.create_job(job_name => 'JOB_SRZ_TO_DV', job_type => 'STORED_PROCEDURE', job_action => 'SRZ_TO_DV', start_date => sysdate, repeat_interval => 'freq=minutely; interval=10', enabled => TRUE);

The job is correctly created and the SP runs every 10 minutes.
The RUN_COUNT field of the dba_scheduler_jobs view is incremented and the FAILURE_COUNT field is set to 0.

Here is the definition of SRZ_TO_DV :
CREATE OR REPLACE
PROCEDURE SRZ_TO_DV 
 IS
 [...]
BEGIN
 [...]
 insert into JOURNAL values (...);
 TEMPSEJ_ENVOI;
END;
/


Here is the definition of TEMPSEJ_ENVOI:
CREATE OR REPLACE
PROCEDURE TEMPSEJ_ENVOI
 IS
 [...]
BEGIN
 INSERT INTO SRZ_TW_SEND_TO_SRZ@NEKOPEN
   SELECT * FROM TEMPSEJ;
 DELETE FROM TEMPSEJ;
 COMMIT;
END;
/


Here is my problem :
When SRZ_TO_DV is launched by the scheduler, nothing is inserted into SRZ_TW_SEND_TO_SRZ@NEKOPEN, as if TEMPSEJ_ENVOI was not executed, but I can see that SRZ_TO_DV is launched because I have an entrey in my 'JOURNAL' table
When SRZ_TO_DV is launched manually ('exec SRZ_TO_DV;' with the same user than the job owner), the content of TEMPSEJ is correctly inserted into SRZ_TW_SEND_TO_SRZ@NEKOPEN.

I cannot explain why, can you help me please ?
Re: [Problem] Stored Procedure and Scheduler [message #376754 is a reply to message #376685] Thu, 18 December 2008 08:06 Go to previous message
FabienB
Messages: 2
Registered: December 2008
Location: France
Junior Member
Well ....
It seems to be good by adding a "commit;" after the call of TEMPSEJ_ENVOI ...

CREATE OR REPLACE
PROCEDURE SRZ_TO_DV 
 IS
 [...]
BEGIN
 [...]
 insert into JOURNAL values (...);
 TEMPSEJ_ENVOI;
 COMMIT;
END;
/

Very strange, because a "commit;" was already present at the end of TEMPSEJ_ENVOI, and it works when launching SRZ_TO_DV manually... Shocked

Oracle will always suprise me
Previous Topic: Calling EXE from PL/SQL(urgent)
Next Topic: migration to Oracle 10gR2 issue
Goto Forum:
  


Current Time: Mon Dec 05 06:52:05 CST 2016

Total time taken to generate the page: 0.11888 seconds