Creating a job with DBMS_SCHEDULER

[rahno]

We have Oracle 10g 10.2.0 Enterprise Edition on a Redhat Linux box. I am trying to run a bash shell script via a job that will run immediately once only. I entered the following in SQL+ after connecting as SYS/password AS SYSDBA. The PL/SQL block was executed without error. However, when I check for the job (select owner, job_name, enabled from dba_scheduler_jobs it is not in the table. Also, when I try to run the job synchronously I get the error that the job does not exist.

The PL/SQL block is:

BEGIN
dbms_scheduler.create_job
(
job_name => 'LOADNRCUPDATE_JOB',
job_type => 'EXECUTABLE',
job_action => '/opt/controlfiles/uploaded/loadNRCupdate.sh',
repeat_interval => NULL,
enabled => true,
comments => 'Load NRC data update via bash script'
);
END;
/

Any ideas/suggestions would be greatly appreciated.

 

[LKBrwnDBA]

If it's a one time only job, just execute from command line.

Otherwise just use the Enterprise Manager console to create/execute the job.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[rahno]

The long version is: our plan is to call the job from within an Oracle stored proc by jdbc from another server. Our java guy doesn't have a way to call an OS script on the Oracle box from the other box, but he can call an Oracle stored proc. The whole process is to insert data to the Oracle DB from csv files uploaded to the other, web server, box. The users uploading the csv files to the web server via jsp pages initiate the process, and we need a way to automate scrubbing the uploaded files into the proper format (done) transfering the scrubbed files from the web server to the Oracle box (done), then executing a bash shell script that runs sql loader to insert the data and does some housecleaning of files. The last part is where this problem lies.

My research indicated that using dbms_scheduler to run an OS script was the most straightforward way to go. If I can solve the problem of the job not being created, then we're done.

 

[LKBrwnDBA]

You may need to set the auto_drop flag to FALSE, if TRUE, it causes the job to be automatically dropped after it has completed or has been disabled.

Maybe instead of executing sqlloader, define the OS file as external table and have then execute a prodedure to load the data.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[rahno]

I'll give the auto_drop => FALSE a try.

Thx^2