Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DBMS_SCHEDULER

455919Jun 12 2006 — edited Jun 15 2006
Hi
I am trying to do the following
1) Create a bat file which does nothing but a simple dir/b > a.txt i.e creates a file a.txt with the list of objects in the current directory
2) Execute the .bat file by using dbms_scheduler using pl/sql

From various examples on the net i did the following
Step 1 is straightforward i created the file a.bat in my d drive
Step 2 I created a stored procedure
create or replace procedure sushant123
as
begin
dbms_scheduler.create_job(job_name => 'myjob',
job_type => 'EXECUTABLE',
job_action => 'd:\a.bat',
enabled => TRUE,
auto_drop => FALSE);

end;
/
exec sushant123
*
ERROR at line 1:
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line
99
ORA-06512: at "SYS.DBMS_SCHEDULER",
line 262
ORA-06512: at "SYSTEM.SUSHANT123", line
4
ORA-06512: at line 1

The thing is that this job doesnt even exist in the dba_objects;

I am in the System user with create external job privilege granted to me
When i try to execute this in sql ie outside the PL/SQL block
SQL> begin
2
3 dbms_scheduler.create_job(job_name => 'myjob',
4 job_type => 'executable',
5 job_action => 'dir/b > abc.txt',
6 enabled => TRUE,
7 auto_drop => FALSE
8 );
9
10 end;
11 /

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.run_job('myjob');
BEGIN dbms_scheduler.run_job('myjob'); END;

*
ERROR at line 1:
ORA-27370: job slave failed to launch a
job of type EXECUTABLE
ORA-27300: OS system dependent
operation:accessing execution agent
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.
ORA-06512: at "SYS.DBMS_ISCHED", line
150
ORA-06512: at "SYS.DBMS_SCHEDULER",
line 441
ORA-06512: at line 1

Though the file is very much there .. but in this approach i can see 'myjob' in dba_objects

I am running a 10g R2 database on windows 2000 professional.
Could it be possible for anyone to helo me acheive step2 above ?

Thanks in Advance
Sushant
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 13 2006
Added on Jun 12 2006
1 comment
7,739 views