Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, marwa.

Asked: September 14, 2017 - 10:27 am UTC

Last updated: October 04, 2021 - 4:54 am UTC

Version: oracle 10g

Viewed 1000+ times

You Asked

HI,
I created a bat file to run sqlldr to load the data from a csv file into Oracle,and I want to execute it automatically ,so I tried it by DBMS_SCHEDULER

exec DBMS_SCHEDULER.run_job('DAILY_LOAD');
Rapport d'erreur -
ORA-27369: échec du travail de type EXECUTABLE avec le code sortie : No such file or directory
ORA-06512: à "SYS.DBMS_ISCHED", ligne 196
ORA-06512: à "SYS.DBMS_SCHEDULER", ligne 486
ORA-06512: à ligne 1


dbms_scheduler.create_job (
job_name=>'DAILY_LOAD',
job_type => 'EXECUTABLE',
job_action => 'c:\Windows\System32\cmd.exe',
number_of_arguments => 2,
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0',
end_date => NULL,

enabled => false,
comments => 'TEST'
);


dbms_scheduler.set_job_argument_value (
job_name =>'DAILY_LOAD',
argument_position=>1,
argument_value=> '/c'

);
dbms_scheduler.set_job_argument_value (
job_name =>'DAILY_LOAD',
argument_position=>2,
argument_value=> 'C:\orant10\BIN\loadDB.bat'


);

DBMS_SCHEDULER.enable(
name => 'DAILY_LOAD');

and Connor said...

I just did this on my machine with no problems -



C:\>type c:\temp\demo.cmd
@echo off
echo Hello > c:\temp\demo.out
dir c:\temp >> c:\temp\demo.out
exit

SQL> declare
  2    vname varchar(20) := 'DEMOJOB';
  3  BEGIN
  4    begin dbms_scheduler.drop_job (vname); exception when others then null; end ;
  5    dbms_scheduler.create_job(
  6      job_name => vname,
  7      job_type => 'EXECUTABLE',
  8      job_action => 'C:\Windows\System32\cmd.exe',
  9      number_of_arguments => 2,
 10      enabled => FALSE);
 11    dbms_scheduler.set_job_argument_value( job_name => vname, argument_position => 1, argument_value => '/c');
 12    dbms_scheduler.set_job_argument_value( job_name => vname, argument_position => 2, argument_value => 'c:\temp\demo.cmd');
 13    dbms_scheduler.enable( vname);
 14
 15  END;
 16  /

PL/SQL procedure successfully completed.


So perhaps use a CMD file rather than a BAT file.

Also, on Windows, check the the "Oracle JobScheduler" service is running. If that isnt running you'll get errors in your alert log, eg, I disabled my service and I see this:

2017-09-15T08:46:30.042872+08:00
Errors in file C:\ORACLE\diag\rdbms\db122\db122\trace\db122_j000_10140.trc:
ORA-12012: error on auto execute of job "MCDONAC"."DEMOJOB"
ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:accessing job scheduler service failed with status: 2
ORA-27301: OS failure message: The system cannot find the file specified.
ORA-27302: failure occurred at: sjsec 5
ORA-27303: additional information: The system cannot find the file specified.



Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

marwa zerzeri, September 15, 2017 - 8:19 am UTC


munaf rafique, April 20, 2021 - 9:39 am UTC

enabling the OracleJobSceduler Service did the trick for me.
Thank you. Keep up the great work
Connor McDonald
April 21, 2021 - 3:16 am UTC

Glad we could help

Krishanu Nayek, June 17, 2021 - 10:25 am UTC

We are using oracle 12C. We have a Oracle directory created which points to the physical location (A NFS file share location). We can create files using utl_file package in this share location.

Now our requirement is -- Once the file is created we need to move that file into another location in the same drive.

So I was following the steps mentioned and created the scripts as below,

created a file named as movefile.cmd contains the windows command
----------------------------------------------------------------------------
move OPEN_INV_WS_04_2021_202105261104.csv R:\Environmental_Reporting

Now the share location is network share and I gave the drive name as R while doing "map network drive". This network share is accessible via oracle directory and utl_file.

declare
vname varchar(20) := 'DEMOJOB';
BEGIN
begin
dbms_scheduler.drop_job (vname);
exception
when others
then
null;
end ;
dbms_scheduler.create_job(
job_name => vname,
job_type => 'EXECUTABLE',
job_action => 'C:\Windows\System32\cmd.exe',
number_of_arguments => 2,
enabled => FALSE);
dbms_scheduler.set_job_argument_value( job_name => vname, argument_position => 1, argument_value => '/c');
dbms_scheduler.set_job_argument_value( job_name => vname, argument_position => 2, argument_value => 'R:\Prc\Git\movefile.cmd');
dbms_scheduler.enable( vname);

END;

Initially it was throwing error as below ,
ORA-27399: job type EXECUTABLE requires the CREATE EXTERNAL JOB privilege
ORA-06512: at "SYS.DBMS_ISCHED", line 4553
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2989

Then we got the required access and after that we are executing the package . The package is not throwing any error but it is also not performing the actions. I ran the code with network share as well as C: drive of my machin. In both the cases it is not working.

DBA has given me the logs from DB as below,


ORA-12012: error on auto execute of job "MART"."DEMOJOB"

ORA-27369: job of type EXECUTABLE failed with exit code: No such file or directory

Please assist me.
Connor McDonald
June 22, 2021 - 1:44 am UTC

Couple of things to try

a) Put the "movefile" batchfile on a local drive, just in case the Oracle service cannot see R:
b) Add the following to the top of your CMD file

set SystemRoot=C:\WINDOWS

(change as appropriate)

and see how you go

dbms_scheduler.create_job

Anton, October 01, 2021 - 11:25 am UTC

I used your code, and just changed the path and the executable file, but the application I want to run (exe file) started in the background process. I mean, the application I want to run didn't open, but just appeared in the background. Is that how it should be? I need my application to open in a window, not in a background process. I use Oracle Version 10 xe. How can I achieve this? I would be very happy to receive your answer, thank you!
Connor McDonald
October 04, 2021 - 4:54 am UTC

Yes that is how it is meant to be because the database could sit anywhere (another server, another city, another continent).

If you want to run a program on your own machine that does something when something happens on the database, you'd need to write your own tool to do that, using something like AQ or CQN

We'd need to see a lot more context on what you want to achieve


More to Explore

Scheduler

All of the database utilities including the Scheduler are explained in the Utilities guide.