Home » SQL & PL/SQL » SQL & PL/SQL » Run external job with DBMS_SCHEDULER (Oracle 10.2.0.3.0; MS Windows Server 2003)
| Run external job with DBMS_SCHEDULER [message #552207] |
Tue, 24 April 2012 06:07  |
 |
Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
A few more details about the environment:- A database server OS is MS Windows Server 2003 Standard Edition Service Pack 2
- It runs an Oracle database ver. 10.2.0.3.0
- Client PC (the one I'm working on) is MS Windows 7 Enterprise Edition
Until recently, I only knew that DBMS_SCHEDULER enables you to run operating system programs. However, that was just pure theory for me as I never had to implement that.
Obviously, I can't make it work (otherwise you wouldn't read this story).
Here's what I'm trying to do. It is an Apex application that (among other stuff) is supposed to upload images to an application server.
- user browses images on his own computer and selects one by one into file browse items
- once he's done, he pushes the UPLOAD button
- using a stored procedure, images are copied from the Apex WWV_FLOW_FILES table to a directory located on a database server
So far, so good. These images require additional processing (consecutive resizing, copying to different directories on both application and database server).
The above is done by a (Win)do(w)s batch script (.BAT), and it does its job correctly. It is not a problem to schedule that BAT script using Windows' Task Scheduler, but the idea is to make everything "now" (i.e. when the user pushes that UPLOAD Apex button).
Therefore, I thought that DBMS_SCHEDULER might do the job. Here's what I've done.
- a database user (which is the Apex application's underlying schema) has the following privileges: CREATE JOB, CREATE EXTERNAL JOB
- on the database server, OracleJobSchedulerORA10 service is started
- in order to make it simpler, I created a small batch script that copies one image to anoter:
REM a.bat
copy ts3032-1.jpg slika.jpg
The source image exists in that directory:
D:\GIS\Slike_4005_upload>dir /b *.jpg
TS3032-1.jpg
The script actually works; no problem with that:D:\GIS\Slike_4005_upload>a
D:\GIS\Slike_4005_upload>copy ts3032-1.jpg slika.jpg
1 file(s) copied.
D:\GIS\Slike_4005_upload>dir /b *.jpg
slika.jpg
TS3032-1.jpg
D:\GIS\Slike_4005_upload>del slika.jpg
Now, let's create a job.
SQL> select sysdate from dual;
SYSDATE
-------------------
24.04.2012 12:34:05
SQL> begin
2 dbms_scheduler.create_job
3 (job_name => 'myjob',
4 job_type => 'EXECUTABLE',
5 job_action => 'c:\windows\system32\cmd.exe',
6 number_of_arguments => 3,
7 enabled => false,
8 auto_drop => true
9 );
10
11 dbms_scheduler.set_job_argument_value('myjob', 1, '/q');
12 dbms_scheduler.set_job_argument_value('myjob', 2, '/c');
13 dbms_scheduler.set_job_argument_value('myjob', 3, 'd:\gis\slike_4005_upload\a.bat >nul');
14
15 dbms_scheduler.enable('myjob');
16 end;
17 /
PL/SQL procedure successfully completed.
SQL> select to_char(log_date, 'hh24:mi:ss') dat,
2 status,
3 additional_info
4 from user_scheduler_job_run_details o
5 where job_name = 'MYJOB'
6 and log_id = (select max(log_id) from user_scheduler_job_run_details o1
7 where o1.job_name = o.job_name);
DAT STATUS ADDITIONAL_INFO
-------- ---------- --------------------------------------------------
12:34:13 FAILED ORA-27369: job of type EXECUTABLE failed with exit
code: Incorrect function.
SQL>
I read the DBMS_SCHEDULER documentation, as well as numerous discussions, suggestions etc. found on the Internet (one of frequently mentioned is this OTN forums topic) and I *think* I followed instructions correctly. However, I'm stuck with the above error (of course, A.BAT didn't do anything).
What did I do? - JOB_STATUS has to be 'executable'
- JOB_ACTION: it is suggested that one should rather supply arguments later than having it all in the same JOB_ACTION line. Therefore, I'm just calling CMD.EXE.
(I tried to put everything into the same line - no difference.)
CMD.EXE path indicates the database server directory.
- ENABLED should be set to FALSE, as I'm not over yet
- SET_JOB_ARGUMENT_VALUE (1 and 2): Internet sources suggest that /q /c should be used.
/C Carries out the command specified by string and then terminates
/Q Turns echo off
- SET_JOB_ARGUMENT_VALUE (3): >nul
Many people said that it helped - for example, here:Quote:
The redirection of the batch's output to nul (> nul) seems to be important in order for the function to run. When I omitted it, I received an ORA-27369: job of type EXECUTABLE failed with exit code: Incorrect function ORA-27369: job of type EXECUTABLE failed with exit code: Incorrect function.
It didn't help me.
Similarly to CMD.EXE, the path indicates a directory located on a database server.
If it makes any difference, Oracle directory object is created on the same operating system directory and it works fine (tested while uploading images from my own PC to a server using Apex):SQL> select * from all_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
-------- ------------------------------ ------------------------------
SYS SLIKE_4005_UPLOAD d:\gis\slike_4005_upload
- Finally, ENABLE the job
Among other sources, this one: Start OS Jobs From Within Oracle says - for Windows OS:Quote:
- The service OracleJobScheduler%ORACLE_SID% must be running
- User (and thus indirectly the group) must be configured via that service (Properties => Log On)
The first one is OK, I already said that. I logged onto a database server as an Administrator when starting the service. However, I don't really understand the second one. WHICH user? Administrator? I'm not even sure that this is an issue.
I'm kind of lost now. Could someone assist, please?
|
|
|
|
| Re: Run external job with DBMS_SCHEDULER [message #552215 is a reply to message #552207] |
Tue, 24 April 2012 06:30  |
John Watson
Messages: 8988
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hi, man. this works for me:exec dbms_scheduler.drop_job('tryit')
exec dbms_scheduler.drop_credential('my_cred')
exec dbms_scheduler.create_credential('my_cred','jwdell\john','pass')
exec dbms_scheduler.create_job(-
job_name=>'tryit',-
job_type=>'executable',-
job_action=>'c:\windows\system32\cmd.exe',-
number_of_arguments => 2,-
enabled=>false,-
auto_drop=>false,-
credential_name=>'my_cred')
exec dbms_scheduler.set_job_argument_value('tryit',1,'/c');
exec dbms_scheduler.set_job_argument_value('tryit',2,'dir');
exec dbms_scheduler.enable('tryit')
select job_name, status, error#, actual_start_date, additional_info
from user_scheduler_job_run_details where job_name='TRYIT';
the credential stuff is because I'm using 11.2, you don;t have that available in 10g. Here's the output from the query at the end:JOB_NAME
---------------------------------------------------------------------------------------------------
STATUS ERROR# ACTUAL_START_DATE
------------------------------ ---------- ---------------------------------------------------------
ADDITIONAL_INFO
---------------------------------------------------------------------------------------------------
TRYIT
SUCCEEDED 0 24-APR-12 12.21.09.576000 EUROPE/LISBON
EXTERNAL_LOG_ID="job_89757_8115",
USERNAME="jwdell\john"
TRYIT
FAILED 27370 24-APR-12 12.16.27.870000 EUROPE/LISBON
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 6a
ORA-27303: additional information: The system cannot find the file specified.
orcl>
orcl>
And by the way, my Windows OracleJobSchedulerORCL service is disabled.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Run external job with DBMS_SCHEDULER [message #552230 is a reply to message #552222] |
Tue, 24 April 2012 07:12 |
John Watson
Messages: 8988
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
So you have it working? As a work around to that silly location being required, could you create the Windows equivalent of a soft link (I think it is called a "junction"?) in \windows\system32
In the back of my mind (which is very dark and gloomy place) I have a memory of getting this to work with release 10.1 on Windows. Rather than specifying the job_action as cmd.exe and passing the batch file as a parameter, I gave the batch file (with a full path, which could be anywhere) as the job_action. It worked, if I set the COMSPEC environment variable to C:\wherever\COMMAND.COM as the first line of the batch file. A bit like having #!/bin/sh in a shell script.
|
|
|
|
| Re: Run external job with DBMS_SCHEDULER [message #552231 is a reply to message #552230] |
Tue, 24 April 2012 07:21 |
John Watson
Messages: 8988
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
If I specify a batch file c:\tmp\file.bat as the job_action, I get this in user_scheduler_job_run_details:TRYIT
FAILED -1.074E+09 24-APR-12 13.14.09.413000 EUROPE/LISBON
EXTERNAL_LOG_ID="job_89763_8119",
ORA-27369: job of type EXECUTABLE failed with exit code: -10737418
STANDARD_ERROR="Exception 0xc0000005 encountered at address 0x778B653B
Generating core file .\core_jssu_201242413149.dmp"
and furthermore if I place the file in c:\windows\system32 and remove the path from the job action, I get exactly the same error. So you are doing better than me at the moment.
-------------------------
Correction: not "specify a batch file c:\tmp\file.bat as the job_action" above, should be "specify a batch file c:\tmp\file.bat as the second parameter"
[Updated on: Tue, 24 April 2012 07:34] Report message to a moderator |
|
|
|
| Re: Run external job with DBMS_SCHEDULER [message #552235 is a reply to message #552231] |
Tue, 24 April 2012 07:48 |
|
Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Great ideas, thank you!
COMSPEC is already set (as an environment variable) and points to c:\windows\system32\cmd.exe.
JUNCTION is something I never heard of before; I Googled a little bit and downloaded junction.exe from Windows SysInternals; I suppose I'll have to research it a little bit further.
Here's something that seems to be working (unfortunately, just a workaround): I created another batch script (call.bat) and put it into c:\windows\system32. I call that script from DBMS_SCHEDULER, and newly created batch script calls the "original" one located on the D disk:DBMS_SCHEDULER -> c:\windows\system32\call.bat -> d:\gis\slike_4005_upload\a.bat
REM call.bat
call d:\gis\slike_4005_upload\a.bat
Although it is not perfect, it actually works, which is MUCH better than anything I managed to do for the past 2 days. I always thought that it must be some missing privileges or stuff. Batch file location never crossed my mind (maybe it would if D disk was mapped, but no - it is database server's disk).
Once again, thank you very much for suggestions you provided. If anyone knows how to make the original script work, please, say so. Otherwise, I'm quite happy with the current workaround.
P.S. I hope that my "real" batch script won't fail. I'll test that tomorrow.
P.P.S. Why another, CALL.BAT script? I'd rather not keep my stuff in \system32 directory. Slippery fingers on a slippery keyboard aren't very healthy.
[Updated on: Tue, 24 April 2012 07:58] Report message to a moderator |
|
|
|
| Re: Run external job with DBMS_SCHEDULER [message #552285 is a reply to message #552235] |
Tue, 24 April 2012 21:12 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Littlefoot,
Your original script, changing the directories and jpg file to match those on my system, produces the same error that you originally posted. If I just use complete directory paths within the a.bat file, then it runs without error and copies the file. Please see the demonstration below. The OracleJobSchedulerORCL service was running. I used dbms_lock.sleep to wait long enough for the job to finish before checking the results, otherwise you may be viewing the results of the previous run.
SCOTT@orcl_11gR2> host type a.bat
copy c:\image_dir\junk.jpg c:\image_dir\junka.jpg
SCOTT@orcl_11gR2> host dir c:\image_dir
Volume in drive C has no label.
Volume Serial Number is F0BB-93C9
Directory of c:\image_dir
04/24/2012 07:04 PM <DIR> .
04/24/2012 07:04 PM <DIR> ..
10/15/2010 05:10 PM 30,549 JUNK.JPG
1 File(s) 30,549 bytes
2 Dir(s) 397,977,796,608 bytes free
SCOTT@orcl_11gR2> begin
2 dbms_scheduler.create_job
3 (job_name => 'myjob',
4 job_type => 'EXECUTABLE',
5 job_action => 'c:\windows\system32\cmd.exe',
6 number_of_arguments => 3,
7 enabled => false,
8 auto_drop => true
9 );
10
11 dbms_scheduler.set_job_argument_value('myjob', 1, '/q');
12 dbms_scheduler.set_job_argument_value('myjob', 2, '/c');
13 dbms_scheduler.set_job_argument_value('myjob', 3, 'c:\my_oracle_files\a.bat >nul');
14
15 dbms_scheduler.enable('myjob');
16 end;
17 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> exec dbms_lock.sleep (5)
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> select to_char(log_date, 'hh24:mi:ss') dat,
2 status,
3 additional_info
4 from user_scheduler_job_run_details o
5 where job_name = 'MYJOB'
6 and log_id = (select max(log_id) from user_scheduler_job_run_details o1
7 where o1.job_name = o.job_name)
8 /
DAT STATUS
-------- ------------------------------
ADDITIONAL_INFO
--------------------------------------------------------------------------------
19:04:51 SUCCEEDED
1 row selected.
SCOTT@orcl_11gR2> host dir c:\image_dir
Volume in drive C has no label.
Volume Serial Number is F0BB-93C9
Directory of c:\image_dir
04/24/2012 07:04 PM <DIR> .
04/24/2012 07:04 PM <DIR> ..
10/15/2010 05:10 PM 30,549 JUNK.JPG
10/15/2010 05:10 PM 30,549 junka.jpg
2 File(s) 61,098 bytes
2 Dir(s) 397,976,641,536 bytes free
SCOTT@orcl_11gR2>
|
|
|
|
| Re: Run external job with DBMS_SCHEDULER [message #552305 is a reply to message #552285] |
Wed, 25 April 2012 00:54 |
|
Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Barbara
If I just use complete directory paths within the a.bat file, then it runs without error and copies the file
Thank you, Barbara!
That seems to be THE ULTIMATE solution! Based on your suggestion, I slightly modified A.BAT and - instead of specifying the whole path, I set the "current" directory at the beginning of the batch file:
REM a.bat
d:
cd d:\gis\slike_4005_upload\
copy ts3032-1.jpg slika.jpg
SQL> begin
2 dbms_scheduler.create_job
3 (job_name => 'myjob',
4 job_type => 'EXECUTABLE',
5 job_action => 'c:\windows\system32\cmd.exe',
6 number_of_arguments => 3,
7 enabled => false,
8 auto_drop => true
9 );
10
11 dbms_scheduler.set_job_argument_value('myjob', 1, '/q');
12 dbms_scheduler.set_job_argument_value('myjob', 2, '/c');
13 dbms_scheduler.set_job_argument_value('myjob', 3, 'd:\gis\slike_4005_upload\a.bat >nul');
14
15 dbms_scheduler.enable('myjob');
16 end;
17 /
PL/SQL procedure successfully completed.
SQL>
SQL> select to_char(log_date, 'hh24:mi:ss') dat,
2 status,
3 additional_info
4 from user_scheduler_job_run_details o
5 where job_name = 'MYJOB'
6 and log_id = (select max(log_id) from user_scheduler_job_run_details o1
7 where o1.job_name = o.job_name);
DAT STATUS ADDITIONAL_INFO
-------- ---------- ----------------------------------------
07:51:44 SUCCEEDED
SQL>
Thanks again, everyone. Much obliged.
|
|
|
|
Goto Forum:
Current Time: Thu Aug 28 11:28:56 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
