Skip to Main Content
  • Questions
  • execute shell script from stored procedure

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Vinay .

Asked: May 02, 2000 - 1:20 pm UTC

Last updated: July 03, 2019 - 1:29 am UTC

Version:

Viewed 100K+ times! This question is

You Asked

Is it possible to execute shell script from store
procedure in async. mode? If yes then how and if not is there any
work around to doit?


and Tom said...


I can think of a couple of different ways....

o In Oracle8i, release 8.1, we could use java to run a system command with an "&" after it (assuming unix) or perhaps "start xxx.cmd" if using NT.

o In Oracle8.0 and up, we can write an external procedure in C that runs host commands with system() and the "&". (see attached for an external procedure example)

o In Oracle7.0 and up, we can use dbms_pipes to talk to a daemon running outside the database. Here is a simple example that uses sqlplus to be the daemon:

A quick and dirty way to do this is with a csh script and sqlplus as such (cut
and pasted from another email)

Ok, so can you do this without C? Yes. Here is a PL/SQL subroutine you can
install in your schema:

create or replace procedure host( cmd in varchar2 )
as
status number;
begin
dbms_pipe.pack_message( cmd );
status := dbms_pipe.send_message( 'HOST_PIPE' );
if ( status <> 0 ) then raise_application_error( -20001, 'Pipe error' );
end if;
end;
/

Here is a C-Shell script you can run in the background, it should be named host.csh. host.csh will be run by you after the db is up. it will create temporary scripts "tmp.csh" that it will run. the last thing these tmp.csh scripts do is re-run host.csh to get the next request....

-------------------- bof ----------------------------
#!/bin/csh -f

sqlplus tkyte/tkyte <<"EOF" | grep '^#' | sed 's/^.//' > tmp.csh

set serveroutput on

declare
status number;
command varchar2(255);
begin
status := dbms_pipe.receive_message( 'HOST_PIPE' );
if ( status <> 0 ) then
dbms_output.put_line( '#exit' );
else
dbms_pipe.unpack_message( command );
dbms_output.put_line( '##!/bin/csh -f' );
dbms_output.put_line( '#' || command );
dbms_output.put_line( '#exec host.csh' );
end if;
end;
/
spool off
"EOF"

chmod +x tmp.csh
exec tmp.csh
----------------------- EOF ---------------------------------


If you run this in the background (The script), you'll be able to have it
execute any host command you want. Run this in one window for example and in
anther window go into sql*plus and try:

SQL> exec host( 'ls -l' );
SQL> exec host( 'uptime' );
SQL> exec host( 'echo Hello World' );
SQL> exec host( 'exit' );

You'll see the output of ls -l, uptime, and echo happen on the other window
where the shell script is running (shows you a way to debug pl/sql routines, use
"host( echo some string )" and you'll get real time feedback from your pl/sql
procedure).....


Make sure you understand the ramifications of the above. It does absolutely no checking anywhere that only valid commands are executed. If you run this as the oracle account and someone sends "rm -rf *" -- watch out. This is an example -- it needs to be more robust.


Rating

  (103 ratings)

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

Comments

Host Command in PL/SQL

Peter Hewitt, July 18, 2001 - 12:38 am UTC

Sheer magic!

DBMS_PIPE

SV, August 07, 2001 - 4:27 pm UTC

It's a Good example for DBMS_PIPE and helps when ever we run into problems of file pemissions on Unix where the file owner is Oracle (esp. When we use UTL_FILE).


vani, October 08, 2001 - 2:12 pm UTC

create or replace procedure host( cmd in varchar2 )
as
status number;
begin
dbms_pipe.pack_message( cmd );
status := dbms_pipe.send_message( 'HOST_PIPE' );
if ( status <> 0 ) then raise_application_error( -20001, 'Pipe error' );
end if;
end;
/
I tried executing this procedure in my schema. Later I ran it and it does not run the unix command passed.
Please let me know what I am missing.

Tom Kyte
October 08, 2001 - 2:14 pm UTC

what does it do if it does not run the commnd. Have you run the script in the background?

script in Dos batch file

A reader, October 29, 2001 - 8:37 am UTC

Hope that your answer for this question would be helpful for me as i need to run dos batch files from Pl/sql. Can you provide me dos batch file as like shell script. I tried my best, but i was not succeeded.



Running command from sqlplus

Ron Chennells, April 16, 2002 - 9:15 am UTC

Excellent example

Does not work for me

SB, April 19, 2002 - 3:18 pm UTC

I have the proc working fine in Oracle 8i. The unix shell script runs but does not print anything out to the tmp.sh file. Please let me know. Thanks

Tom Kyte
April 19, 2002 - 3:24 pm UTC

don't know -- play with it.

It is very "simple" and straight forward stuff. Very very simple shell scripts. I cannot imagine any reason why tmp.sh wouldn't have something in it after a message was read off of the pipe.

In 8i you could just use:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:952229840241 <code>

much easier, much more secure.

DBMS_PIPE

Yang Jiang, May 23, 2002 - 3:41 pm UTC

Good tool for Oracle7!
One question: when I try manually exec host procedure, it run one time only. But when it's executed by a trigger, it's kept running until I killed the background process. What's happened and how to fix it?

Thanks

Tom Kyte
May 23, 2002 - 3:44 pm UTC

you did something wrong -- it cannot TELL if it was fired from a trigger, the moon, whatever -- put debug in and find out whats going on



run unix script from pl/sql

mo, August 24, 2002 - 3:27 pm UTC

TOm:

1. I want to run a unix shell script that deletes some files and set up environment from pl/SQL procedure.

SInce I have not used C or java much I am trying to implement your solution of using dbms_pipe. However I do not quite understand what you did:


1. First you created a pl/sql procedure "host" that takes a "cmd" parameter and then packs it but then you send "host_pipe". what is that for?

2. Then you create a script host.csh that runs sqlplus and then another pl/sql procedure that receive message.

Is this a standard file that you should always have. or basically you are showing that you ran host.csh from pl/sql.

3. Since you can always run unix commands in sql*plus using host cmd can you run a script that way too? or is this what yo uare bascially trying to do.

Thank you,



Tom Kyte
August 24, 2002 - 7:42 pm UTC

Mo,

suggest you get my book "Expert one on one".


Many (like most all) of the questions you ask in the followups are in there with lots of examples. Including the above question!

unix from stored procedure

Mo, August 24, 2002 - 9:36 pm UTC

TOm:

I will. which chapter you cover this?

Thank you

Tom Kyte
August 25, 2002 - 9:07 am UTC

dbms_alert/pipe section in the appendix.

execute sheel script ....

John McAllister, November 12, 2002 - 8:48 am UTC

Excellent simple to use.

How to do this in windows

Jeff, August 14, 2003 - 6:58 pm UTC

Tom,

This is useful for those who are working on unix. Do you have any trick for windows user?

I am running some pl/sql script to verify data and I want to know the progress of the script. Unfortunately, I cannot create any temp table and start logging into it to check the logs. Neither am I updating any table so as to check how many rows have been updated. So I want to know somehow what is going on in the script?

Is it possible to run the host command from pl/sql?

ps : I am working on Oracle 817 EE.

Tom Kyte
August 14, 2003 - 8:04 pm UTC

just use dbms_application and set_session_longops

very easy. this (the above) would be a really wrong way to do it.

search this site for those two terms for examples.

A little precision

Jean Boulé, January 15, 2004 - 11:03 am UTC

Hi,

I would like to know what would be the intelligent way of doing it.

If I have Oracle 9i and I want to execute a cShell. Do I use JAva or do I use a C program with dbms_pipe.


Thanks

Tom Kyte
January 15, 2004 - 12:10 pm UTC

i would probably use a java stored procedure

unless

i had to call this from a short lived session and i had to call this often from short lived sessions, then I would use an external procedure (not a pipe server).

Nice

Raju, March 14, 2005 - 10:27 am UTC

Hello Tom,
I would like to have a shell script which deletes
trace files whose sizes are greater than 1MB.
My P-code is

#!/usr/bin/csh
cd .../user_dump_dest
chksum 'ls' > Trc1.lst
...

I don't know how to proceed further.
Could you please help??


Tom Kyte
March 14, 2005 - 10:44 am UTC

get a book on csh programming?


man "find"

find has a size option to it.

Change user when executing shell from oracle

Kris, April 19, 2005 - 4:38 am UTC

Hi,

Found some great examples on how to execute a unix shell from oracle. I personnaly prefer the solution with the java stored procedure. However, is there a way to change the user who will run the shell. By default this is oracle but this involves some security threads off course. How and where would be the best way to change this? (knowing the dba has no control over the shell scripts that will be started witht the host procedure)

Thanks for your help!

Tom Kyte
April 19, 2005 - 7:40 am UTC

No, you would have to use a utility like msu to accomplish that (the Oacle user would have to be privileged enough to "become" some other user, tools like msu in linux/unix let you do that)

10G updates

Jeremy, April 19, 2005 - 10:31 am UTC

in 10G, you can do this with the scheduler package, can't you?  this worked for me, although i don't think it's async and i don't know if there's an easy way to run with different privelages than "nobody" (which it appears to be running as)...


$ cat >/tmp/test111.sh <<EOF
#!/bin/bash
echo Success > /tmp/test111.out
EOF
$ chmod +x /tmp/test111.sh
$ sqlplus system

SQL> begin
  dbms_scheduler.create_job(
    job_name=>'TEST_OS_EXEC_111',
    job_type=>'executable',
    job_action=>'/tmp/test111.sh',
    enabled=>TRUE
  );
end;
/
  2    3    4    5    6    7    8
PL/SQL procedure successfully completed.

SQL> select status from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name='TEST_OS_EXEC_111';

STATUS
------------------------------
SUCCEEDED

SQL> exit

$ cat /tmp/test111.out
Success
$ ls -l /tmp/test111.out
-rw-r--r--    1 nobody   nobody          8 Apr 19 10:15 /tmp/test111.out
 

execute OS command from stored procedure on Windows OS

sanaaa, July 12, 2005 - 2:39 am UTC

how to execute OS command or script from stored procedure on Windows OS ?

Need to Run Sql script from a procedure.

Amit Singh, August 04, 2005 - 4:12 am UTC

Hi Tom,
       I have written a procedure which creates a .sql file on the server using utl_file.Now i want to execute this from the same program as we do in sqlplus 
:sql>@PATH\<SCRIPTNAME.SQL> 
How can we do this. 

Tom Kyte
August 04, 2005 - 9:29 am UTC

it would be a really bad idea to do this as you wouldn't be able to really figure out if it worked or not reliably

you would have to run it as a host command, search for

host command java

on this site for a java stored procedure that can do this, but RETHINK this approach please. It will not be very reliable.

A reader, September 28, 2005 - 8:20 pm UTC


Excellent idea!!

ram_0218, October 14, 2005 - 4:59 pm UTC

I loved these examples, and am very much inspired by TOM., going to buy his book today!!

A reader, October 16, 2005 - 10:09 am UTC

Hi tom,

Please help me.My problem is i want to check for the existence of any folder in particular drive SAY C:\ through procedure and if the folder does not exist it creates that folder while showing the message "folder is creating".

Plz help me it is like
1)To check for x folder in c:\
2)If not catch exception and create a new folder
3)If there is some memory problem while creating again display the message to the user.

Thanks in advance

Tom Kyte
October 16, 2005 - 4:15 pm UTC

you'll be writing a java stored procedure for that - sorry, I don't have one...

or a shell script...

but then again, creating directories like that on the server scares me a bit, think that through.

Also, the database doesn't really "show messages", the application that called the procedure would do that.

A reader, October 16, 2005 - 10:11 am UTC

you can't do this through procedure i think.You are checking drives folder that can't be done .

good stuff

naveen, November 21, 2005 - 6:21 am UTC

i have tried with the given pl/sql procedure..

the only problem i m facing is..

when i exececute host procedure first time, the command works fine.

but on second time putty gets close.i mean program exits .

i dont know why this is happening.

any idea please..

regards
naveen

help

Raju, November 21, 2005 - 9:55 am UTC

Hi Tom,
please see this unix shell script snippet


exists=`find . -name test_summary*`
echo $exists -- returns the file name
for sumfile in $exists
do
cp $sumfile ${LOGS_DIR}/test_summary_${timestamp}.txt
cp $sumfile ${ARCHIVE_DIR}/test_summary_${timestamp}.txt
done


shell variables are correct.
cp command is not working properly.It fails abruptly.
What can be the reason??


Tom Kyte
November 21, 2005 - 10:04 am UTC

my car won't start either, why?


Not a ton of information here. "stops abruptly" is not very descriptive.

copying files

mo, February 22, 2006 - 9:53 pm UTC

Tom:

I am not sure if this is this what you would use for this problem.

1. Can you use MOD_PLSQL to create an HTML page that allows a user to select files on one disk and then copy it to another unix disk? What package would you use?

thank you

Tom Kyte
February 23, 2006 - 7:40 am UTC

you don't even mention where these hypothetical files reside.

But in general, the answer would be "no"


PLSQL cannot see the client file system.
PLSQL cannot really do a "dir" on the server file system either.

And I doubt you mean to have them copy files from A to B on the server, you likely mean on the client - meaning you need a bit of code running on the CLIENT to do this work.

moving files

mo, March 15, 2006 - 6:22 pm UTC

Tom:

Basically, I have 3 unix servers (3 data stores) to store Digial Talking Books: Operational data store, Archive and Distribution Data Store. Initially the book will be copied to the Opertional store. After Quality Control checks it will be moved to the Archive and Distribution stores.

Each Digital book is in MP3 format and composed of many WAV files and other file types. The books will be in oracle but stored physically on the unix drive. Each book is about 250 MBytes.

What I am trying to do is be able to list all books on one machine and then move a selected book from one data store to another. Can you do that using pl/sql and mod_plsql? I thought UTL_FILE was a package to interact with the unix system.

or is this more requires a digital asset management software like Documentum?

Thank you,

Tom Kyte
March 16, 2006 - 7:43 am UTC

...
The books will be in oracle but stored physically on the unix drive.
.....

then the books are NOT in Oracle at all.
you have a bunch of files in the file system.


PLSQL cannot "read a directory".

Moving hundreds of megabytes of files with mod_plsql is not anything I would ever consider doing.

(these things belong in the database, but only if you actually CARE about them)

scheduler - executable in UNIX job_type

Andrew, March 17, 2006 - 8:47 am UTC

Hi Tom,

I am having problems with this - although Jeremy claimed (see below...) that he could do it.

I tested this in Oracle_HOME as well as /tmp (as he did) - but I am getting a FAILED status for the job defined exactly like he did.

What are the pre-requsites to do this successfully...?

Your help will be appreciately greatly as usual.

Thanks
Regards AS


10G updates  April 19, 2005 
Reviewer:  Jeremy  from Lansing, MI 

in 10G, you can do this with the scheduler package, can't you?  this worked for 
me, although i don't think it's async and i don't know if there's an easy way to 
run with different privelages than "nobody" (which it appears to be running 
as)...


$ cat >/tmp/test111.sh <<EOF
#!/bin/bash
echo Success > /tmp/test111.out
EOF
$ chmod +x /tmp/test111.sh
$ sqlplus system

SQL> begin
  dbms_scheduler.create_job(
    job_name=>'TEST_OS_EXEC_111',
    job_type=>'executable',
    job_action=>'/tmp/test111.sh',
    enabled=>TRUE
  );
end;
/
  2    3    4    5    6    7    8
PL/SQL procedure successfully completed.

SQL> select status from DBA_SCHEDULER_JOB_RUN_DETAILS where 
job_name='TEST_OS_EXEC_111';

STATUS
------------------------------
SUCCEEDED

 

Tom Kyte
March 17, 2006 - 5:41 pm UTC

documentation says....

...
'EXECUTABLE'

This specifies that the job is a job external to the database. External jobs are anything that can be executed from the operating system's command line. Anydata arguments are not supported with a job or program type of EXECUTABLE. The job owner must have the CREATE EXTERNAL JOB system privilege before the job can be enabled or run.

......

moving files using oracle

mo, March 17, 2006 - 3:41 pm UTC

Tom:

What I meant is to use extrnal LOB type (BFILE) to reference the title of the book to hundreds of files that make it up and are stored in the unix system, more like storing images of a web site.

1. What I am thinking is that the oracle table would reference the unix directory and sub directories where the files for that Digital book reside. Is not this considered that the files are in oracle but stored externally?

2. Would not it be too much overhead to load hundred of files into oracle if I want to do internal LOB storage? How would you get those files from the CD into oracle?

3. You even say that MOD_PLSQL or pl/SQL is not efficient or the right tool to move hundreds of mega bytes from one server to another? What would you use?

4. Does this sound doable to you? A web page that displays all titles from an oracle table. Then user selects a title and hits move. The oracle procedure reads the the fields for the unix directories where files are stored and then runs a UNIX shell script that does a CP (COPY) from source directory to destination directory.

Thank you,

Tom Kyte
March 17, 2006 - 6:02 pm UTC

I know exactly what you meant. My point is - if you care about this data, you won't let it sit in a filesystem, you would put it somewhere safe, say a database for example.

1) No, Oracle has a filename. The files reside in a file system. We have their name (hopefully we have their correct name, nothing stopping anyone from doing something to their name and messing it all up, happens all of the time)

2) no, not at all. We have a single fileserver for 50,000 people here at Oracle. It is 10's of terabytes in size. It is called "Oracle".

There are dozens of ways to get data into the database from the simple dbms_lob.loadfromfile to sqlldr to custom written applications.

3) I wouldn't, because I wouldn't move megabytes of files from server to server, I would keep them in a nice database for all to access from anywhere.

ftp, cp, whatever you want to use - this is a question for the "os guys"

4) sounds like a recipe for disaster - scripts (hard to figure out if they actually worked by the caller). Nope, I would never do it that way - it would call for a program that reads and writes files so you could have things like error handling and such.

moving files in oracle

mo, March 17, 2006 - 9:34 pm UTC

Tom:

Thanks for the great explanation you provided.

1. You say on #4 it would be a disaster doing it using a call to a shell script. Does oracle have tools/program that reads/writes files to do that?would UTL_FILE be useful here if the files are on the file system?

2. Your sound like if you had this problem you would propose to store all the files in the database instead of the file system. Correct?

3. If we did use a database, are you suggesting to elimnate three data stores and have one common database with a book flag to indicate the status? or you would have three databases and simply move it from one opearional database to archival database?

Tom Kyte
March 18, 2006 - 4:33 pm UTC

1) you could use utl_file, it would not be "the fastest thing you ever saw". I would (as stated) not be moving files, what a waste.

2) umm, yes.

3) yes, indeed - one database is about all you need for this.

executable from dbms_scheduler.

Andrew, March 20, 2006 - 1:52 pm UTC

Tom, 

Thank you for your response.

I tried the exact same PL/SQL code on my laptop and it worked. I am running Oracle 10g r2 the same as at Logica. 

Yes - I have made sure to grant CREATE EXTERNAL JOB priv.
I have NOT done anything else - like ensuring that arguments are set up. I assume that if no arguments are specified - then all is OK. and it was on my laptop but not on Logica server with the same (presumably) set-up.

+

So let me re-qualify my questions:
(1) Is there any way to verify whether specific arguments have been set up rather than ANYDATA..?
(2) Are there any database defaults that kick in and make ANYDATA as default..?
(3) What about the OWNERSHIP of the generated output file - I got the same nobody:nobody ownership as Jeremy had..?
What are the possible consequences for Oracle..? if the result of UNIX command executed via scheduler is to end up as the INPUT to the database..? - I think there aren't any, as permissions are rw-rw-rw but I am not 100% sure.

Kind regards
Andrew

For reference - I am quoting the former question/follow-up.


Hi Tom,

I am having problems with this - although Jeremy claimed (see below...) that he 
could do it.

I tested this in Oracle_HOME as well as /tmp (as he did) - but I am getting a 
FAILED status for the job defined exactly like he did.

What are the pre-requsites to do this successfully...?

Your help will be appreciately greatly as usual.

Thanks
Regards AS


10G updates  April 19, 2005 
Reviewer:  Jeremy  from Lansing, MI 

in 10G, you can do this with the scheduler package, can't you?  this worked for 
me, although i don't think it's async and i don't know if there's an easy way to 

run with different privelages than "nobody" (which it appears to be running 
as)...


$ cat >/tmp/test111.sh <<EOF
#!/bin/bash
echo Success > /tmp/test111.out
EOF
$ chmod +x /tmp/test111.sh
$ sqlplus system

SQL> begin
  dbms_scheduler.create_job(
    job_name=>'TEST_OS_EXEC_111',
    job_type=>'executable',
    job_action=>'/tmp/test111.sh',
    enabled=>TRUE
  );
end;
/
  2    3    4    5    6    7    8
PL/SQL procedure successfully completed.

SQL> select status from DBA_SCHEDULER_JOB_RUN_DETAILS where 
job_name='TEST_OS_EXEC_111';

STATUS
------------------------------
SUCCEEDED

 


Followup:  
documentation says....

...
'EXECUTABLE'

This specifies that the job is a job external to the database. External jobs are 
anything that can be executed from the operating system's command line. Anydata 
arguments are not supported with a job or program type of EXECUTABLE. The job 
owner must have the CREATE EXTERNAL JOB system privilege before the job can be 
enabled or run.

...... 
 


Now that this has actually worked - the file 

nobody owner:group

AS, March 21, 2006 - 12:58 pm UTC

Hello Tom,

I found the answer on metalink.
extjob had this ownership. when this is changed to oracle:dba or another existing O/S user:group output generates accordingly

Hopefully, more people will read about it.

Also an observation - as some people were asking about how to capture std output or error...?

The view DBA_SCHEDULER_JOB_RUN_DETAILS stores all such output in ADDITIONAL_INFO column VARCAHR2(4000). Hopefully the length of this column is OK and whatever the output size may be it would be truncated to the first 4000 chars...

BR - AS


Tom Kyte
March 22, 2006 - 2:31 pm UTC

Indeed - I worked through that with someone else a couple of weeks ago, slipped my mind (the group stuff)

storing files

mo, March 21, 2006 - 10:51 pm UTC

Tom:

I am not sure why the study proposes 3 different servers with three databases. Maybe it has to do with securoty. I like your proposed solution though.

1. What would you say the advantages/disadavantages of storing each book (250 Mbytes) in the database over the file system? I am talking about storing 2000 digital books per year.

2. Would you bascially have the book title in one table and them link that to another table to store the unique id of the book and all the files that make it up. It will be one record per file in the second table?

Tom Kyte
March 22, 2006 - 3:48 pm UTC

1) You are talking about a small amount of data really. I've said over and over "database", you have my input. security - I have tons of it in the database, not much in the OS. backup and recovery (point in time, media recovery, whatever) - database check, file system - no check. And so on.

If you have data that means something to you - you will protect it. Databases do that.

2) I don't know your application. You need to design that based on your requirements.

files

mo, March 22, 2006 - 11:16 pm UTC

Tom:

OK the book files will be either FTP to a unix disk or provided on a CD

1. Would you create a web app that uses dbms_lob.loadfromfile to load these files from disk or CD to oracle?
I thought oracle can't read files off a CD on the client machine.

2 How long you think it will take to load one book (250 M bytes) into oracle 9i.

thanks,

Tom Kyte
March 23, 2006 - 10:17 am UTC

1) sounds more like a bulk operation to me, not a web application. Web pages should take seconds, this will take more than a second or two. This is called "a load"

And I don't care where the files "are", where they "are" limits my options maybe. If I want to use loadfromfile, they HAVE to be on the database server (readable by it).

But, that doesn't mean I cannot use sqlldr, a custom application - WHATEVER YOU WANT - from the client to load this data.

2) 10's of seconds to a few minutes. Depends on your processing, size of server, etc.

execute shell script from stored procedure

Sreeni, March 29, 2006 - 9:31 am UTC

excellent example.. very much useful..

dbms_scheduler - job_type 'executable' obtaining execution status or error

Andrew from London, April 05, 2006 - 1:44 pm UTC

Hello Tom,

Back to the SCHEDULER...

Ownership of the job results (if/when output is generated on UNIX) is OK – I found some info on metalink and rest was my tests.

However there are two issues –
- problems that I have not found addressed anywhere – docs, metalink not on your site.

(1) SCHEDULER goes on executing and job never finishes until it is STOPÂ’ped or database is dhutdown
(2) Error status is not always to be found in the ADDITIONAL_INFO attribute

FIRST: happens when you issue a command with wild-cards that “pull” many files into processing – say as input for a cat or cpio or scp/cp commends etc., What works fine in UNIX as a command shell program hangs FOR EVER under SCHEDULER. However, as the number of candidate files as input to these commands is smaller, at some point SCHEDULER works OK.

My guess is: it has something to do with some Oracle related to SCHEDULER buffer..? Perhaps when all files found in the relevant directories after applying wild-cards ae added together, perhaps..? total number of bytes exceeds some number (4000..?)


SECOND: Contrary to what one would think the ADDITIONAL_INFO (although defined as VARCHAR2(4000)) gets filled up only with 217 bytes.

This also creates a problem with capturing any ERROR condition – normally displayed as STANDARD OUTPUT.

One of Oracle support guys gave his advise on a metalink to deploy a shell executable program with SCRIPT UNIX command that would capture all output. I tried this, but SCHEDULER does not appear to work with a combo “script + followed up by + the actual UNIX shell program”, say cpio or any other.

And if the output exceeds 217 bytes – one could not see what execution status or error message was, as this field (view: USER_SCHEDULER_JOB_RUN_DETAILS) end on the byte 217 – i.e. before more important info like error status.

Is there any practical solution to this, please ?

Best regards,
Andrew





Tom Kyte
April 06, 2006 - 9:13 am UTC

explain in more detail - the job runs as long as the job runs - perhaps your script is "stuck" waiting for interactive input or something?



No error but .......

Soham, April 06, 2006 - 8:55 am UTC

Hi Tom,
I am running following script.

dbms_pipe.pack_message( 'cp v_filename IRCOFSA1.txt');
status := dbms_pipe.send_message( 'HOST_PIPE' );
if ( status <> 0 ) then
Fnd_File.put_line (Fnd_File.output, 'Error in coping the file to IRCOFSA1.txt');
end if;

It is not giving any error ( status = 0 ) but not copying the file either.

Please let me know how can I monitor the process or what might be the problem?

Thanks,
Soham

Tom Kyte
April 07, 2006 - 3:30 pm UTC

likely means the shell script isn't running (message packed, but no one at home to unpack it)

today in 2006, I would not suggest this method anymore, use this:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:952229840241 <code>

or in 10g, use dbms_scheduler - builtin package that can run external programs.

exeuctable UNIX shell script via dbms_scheduler - run away "stuck" job

Andrew, April 06, 2006 - 12:17 pm UTC

Hi Tom,

To be specific here is an example:

shell script = tj05.sh
#!/sbin/sh
ls -1 /opt/oracle/oxg2_base/hopsitl/xfr/abba* | cpio -ocv \
| compress -c > /opt/oracle/oxg2_base/hopsitl/zabba.dmp.Z

the abba* family of files are one byte each - for testing.

the same shell script can be executed and completes the job in a couple of seconds - regardless whether there are a few or a few hundred abba* files.

DBMS_SCHEDULER, however, is fine with a few dozen files but when tested with 200+ just keeps churning CPU cycles and does not finish. The result is an empty file that stays empty.

I am getting tempted to check where the boundary is..?
Perhaps (my guess) some buffer fills up, say 4000 bytes long and if all files along with their paths as provided by ls -1 command go over this limit, perhaps? then scheduler calls "UNCLE"...

As you see there is no interaction with a user = i.e. no input expected.

I believe it is important to know such limitations, and if Oracle does not like wild-cards then it should be documented, or if Oracle uses some buffers that hold an interim "expanded" command generated with the wild-cards then again this should be noted in documentation. Surely, designers must know of this limitation if in fact commands get buffered first (again it is my guess - have not read it anywhere)... wouldn't you think..?

+

The second item - as per my former posting was about error handling - HOW can I get an error back into Oracle when DBMS_SCHEDULER executes a shell script..?

Do I need to program in perl instead (need to get to know it).

Kind regards
Andrew




Tom Kyte
April 07, 2006 - 4:06 pm UTC

there should be no differences here - WE are not running the shell script, the SHELL is running the shell script.

Not sure what to say else wise - it is not like we have changed the shell or anything. The environment is different (might want to echo out some things to see "what is different" environment and otherwise - to see if there is anything obvious)

run-away executable - follow-up

Andrew, April 10, 2006 - 8:47 am UTC

Tom,

Thanks, but -

(1) Have you tried to reproduce this on your system?
(2) Yes, I agree in principle - however the fact of the matter is that - WITH ONLY ONE THING CHANGED = i.e. number of files (abba* family) the same scheduler process executing the same very shell - either does the job (and quickly) or turns into a run-away..?

I have been very careful and executed this with precisely ONE thing being different = i.e. number of the candidate files that would be pulled by this shell script.

Let me reiterate - if the shell runs on its own it can process any number of files (I mean I have not tested millions - but a dozen, a hundred and as many as 200 hundred). The same shell script being invoked by DBMS_SCHEDULER behaves fine when the number of files is under some threshold.

Now, that you are equally baffled - I will follow through and test the actual "Breaking point" - would not be surprised if it was indeed around 4000 byte mark..?

I will appreciate any further input from you.

ALSO:

Coulr you please let me know how you would capture = echo what exactly is being done when shell script is executed under DBMS_SCHEDULER..?

As I said before - I cannot see how I can do this - the UNIX script command would not work under this scenario.

PLEASE HELP

Thank you
Andrew

Tom Kyte
April 11, 2006 - 10:02 am UTC

1) nope, I don't have your system - in fact - not sure what your system is?

2) that is not the only thing changed here - the entire environment is different, you are running the script in an enviroment that is somewhat "rarified". Could be a stack limitation (ulimit related) or environmental.




stack - ulimit ..? re -scheduler for executable shell scripts

Andrew, April 12, 2006 - 10:33 am UTC

Thanks Tom,

I tested this on my laptop (Solaris + Oracle 10gr2) as well as on client's DEV server (also Solaris + 10gr2).

SAME PROBLEM.

I checked UNIX man pages for ulimit, and ran ulimit -a
to get the following:

time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 8192
coredump(blocks) unlimited
nofiles(descriptors) 15000
vmemory(kbytes) unlimited

+

It is an uncharted territory to me...

Say that you are called on the client site and there is a job to do cat of split files (all 676 of them xaa .. xzz) and you check the view dba_scheduler_running_jobs to read:

OWNER JOB_NAME
------------------------------ ------------------------------
JOB_SUBNAME SESSION_ID SLAVE_PROCESS_ID SLAVE_OS_PRO
------------------------------ ---------- ---------------- ------------
RUNNING_INSTANCE RESOURCE_CONSUMER_GROUP
---------------- --------------------------------
ELAPSED_TIME
---------------------------------------------------------------------------
CPU_USED
---------------------------------------------------------------------------
XYZ TJ02_ABBA000
316 42 942
1
+000 01:51:55.56
+000 00:00:00.01


that shows that the job has been in progress for nearly 2 hours and is not doing anything. The same script does the job in a few seconds if executed on the command line instead of being invoked from dbms_scheduler.

WHAT would you do + check..?
- to find out WHY and what parameters and/or environment variables should get adjusted...?

I will be very grateful if you could help ...

Thanks
Andrew



Tom Kyte
April 12, 2006 - 11:20 am UTC

have you done the ulimits and environment checks from a script run by the scheduler to see what the ulimits/environment is for the running jobs?

ulimit/stck etc that causes scheduler to misbehave..???

Andrew, April 13, 2006 - 11:03 am UTC

Tom,

Thanks.

As per your advice I executed the ulimit -a as a shell script invoked from scheduler

this is what I got (exec as user=nobody):
$ cat x_ulimit.log
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 32768
coredump(blocks) unlimited
nofiles(descriptors) 65536
vmemory(kbytes) unlimited

and when the same script is executed from Oracle account - command line the output is as follows:
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 8192
coredump(blocks) unlimited
nofiles(descriptors) 15000
vmemory(kbytes) unlimited

I have no idea how to interpret this.

As far as env - there is virtually nothing defined, as it executes as nobody. The client's Sys Admin would need to create a UNIX user and then change the ownership for external jobs into this user.

As I cannot get them to do this, I cannot execute .profile from within the shell program.

On my laptop I created a special unix user and it is able to source the .profile from within the shell program and when I output env into a log file it is identical to what I have defined for Oracle.

Yet, I am lost as to what to do next.

Would you please help - thanks

Tom Kyte
April 14, 2006 - 11:50 am UTC

stack size is 32k for you.
stack size is 8k for the job running.


It is likely exactly what I was guessing, the length of the command line is exceeding the limits - most probably the stack size is.


Next step:

working with the DBA and SA of your system, request an increase in the ulimits for the Oracle user - change the ulimits for the Oracle user - restart the database so the processes inherit this new ulimit.

scheduler hanging? = stack limitations - thank you

Andrew f, April 17, 2006 - 11:24 am UTC

Tom,

Thank you and Happy Easter.

Andrew

Andrew, April 18, 2006 - 1:44 pm UTC

Tom,

you wrote...
>>>
Followup:

stack size is 32k for you.
stack size is 8k for the job running.


It is likely exactly what I was guessing, the length of the command line is
exceeding the limits - most probably the stack size is.


Next step:

working with the DBA and SA of your system, request an increase in the ulimits
for the Oracle user - change the ulimits for the Oracle user - restart the
database so the processes inherit this new ulimit.
<<<

and I tested this as follows:

(1) I introduced ulimit -Ss 32768 and ulimit -Sn 65536 into a .profile of a UNIX user that actuallu runs scheduler (ownership changed from nobody into this new user)

(2) I also executed the same two statements as Oracle and restarted the database (10gr2).

(3) the ulimit -Ha shows unlimited at every parameter including the stack

(4) Verified that these new values would now show and these were as Oracle would have when ulimit -a was executed as the UNIX command under dbms_scheduler

(5) THE PROBLEM STILL PERSISTS, i.e. job starts and HANGS...

Either I have NOT followed your advice correctly, or there is still something else there.

Would appreciate your further HELP

Thanks
A


Tom Kyte
April 18, 2006 - 3:39 pm UTC

you'll need to work with support on this one - I quite simply don't have your platform/setup available.

If you ran a job that simply prints out the ulimits - AND your modified ulimits are in place, I would agree that something could be afoot here.

INFORMATION REQUIRED

Animesh Singh, April 19, 2006 - 5:43 am UTC

Hi Tom,
i got the following error while executing a report(oracle reports 6i) from oracle applications after FTP and giving permissions to the file...

Routine FDPREP cannot read the file /oracle/shared/oraapp1/pcrp3appl/r2i/1.0.0/reports/US/prne_control_log_report.rep.

please help me out soon.



Tom Kyte
April 19, 2006 - 8:45 am UTC

"sorry"? not sure what else to say.

Using dbms_scheduler

A reader, April 21, 2006 - 10:25 am UTC

Tom,

I try to start a script using dbms_schedule. I keep to get the error below. What I do wrong? I have no problem to execute the script from OS (and it really saved in c:\scripts).

My another question is:
if the script are stored in the OS - how should/can I protect the script from being modified by OS users. Is there any way to save the script in the Oracle database (as for e.g. external file)

SQL> BEGIN
  2  dbms_scheduler.create_job(
  3  job_name => 'LOGSHIP',
  4  job_type => 'EXECUTABLE',
  5  job_action => 'C:\scripts\logshipvbs.vbs');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> execute DBMS_SCHEDULER.run_job ('LOGSHIP')
BEGIN DBMS_SCHEDULER.run_job ('LOGSHIP'); 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
 

Tom Kyte
April 21, 2006 - 3:29 pm UTC

c:\scripts\logshipvbs.vbs exists on the database SERVER right.

A reader, April 23, 2006 - 2:29 pm UTC

Tom,

the scritp exists on the database server in the folder and I granted full access to everyone to the file (only to test - not really I want to have) but I keep to get the error that the scheduler can't find the file

Tom Kyte
April 23, 2006 - 2:41 pm UTC

Please utilize support for this one - if you are sure the script is on the server and the permissions are correct, there is no reason for the file not found issue.

One possible answer...

djb, May 10, 2006 - 12:32 pm UTC

I too was having a problem with ORA-27370. I found that, on Windoze, the service OracleJobScheduler<sid> must be running. I didn't need to grant any special privileges to the file.

Hope that helps

Tom Kyte
May 11, 2006 - 8:57 am UTC


Script to be executed on Client

AVS, May 19, 2006 - 1:54 pm UTC

Tom,

Our production unix server hold multiple oracle databases. Hence we(developers) are restricted to logon to unix server. No output files can to be generated on the unix server hosting the databases. Now my problem is: I wanted to write a procedure to find out any record entered in the table and it should create a zero byte indicator file on the different (not hosting database) client (unix) server whose IP address is know. How can I achieve this?

Cheers,
AVS

Tom Kyte
May 20, 2006 - 4:26 pm UTC

You would sort of need a process running on that ip address to talk to.

Otherwise you would have what is known as "a virus like piece of software" and that would be bad.

If you can get that ip address to export a file system and your unix server to mount that filesystem, then you could do something.

yes

p.g.s.manian, August 02, 2006 - 9:19 am UTC

yes very help full

To run a Stored Proc using UNIX C-Shell script

A reader, August 08, 2006 - 7:24 am UTC

Hi Tom,

I am running the following script test.csh file which has the following contents.

TS=`sqlplus userName/Password@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=SERVER_IP_ADDRESS)(PORT=1532)))(CONNECT_DATA=(SID=SIDNAME))) <<EOF
set heading off feedback off verify off
EXEC PKG_TEST.P_TEST_BUSINESS_DATE;
EXEC PKG_TEST.P_TEST_POSITION;
exit
EOF
'


Is there any way by which I can include a condition so that
I execute the second stored proc i.e PKG_EOD.P_TEST_POSITION only if the first one succeeds i.e
PKG_TEST.P_TEST_BUSINESS_DATE.
Also is there any way to determine if the Stored procs have executed successfully.


Tom Kyte
August 08, 2006 - 8:02 am UTC

begin
pkg_test.p_test_business_date;
pkg_test.p_test_position;
end;

if p_test_business_date raises an exception - then p_test_position will not be invoked and the transaction will roll back.


Just write real code there - don't use single line exec this, exec that. Write a well formed block and you have all of the power of PLSQL

How can I execute an SQL script file from stored procedure ?

Paul, August 11, 2006 - 6:16 pm UTC

Hi Tom.

Congratulations by this site.

Would you please how can I execute an SQL script file from stored procedure ?

I try with native sql, but error ORA-00900, ORA-06512 appeared.

Code:

CREATE OR REPLACE PROCEDURE JOB AS
BEGIN
execute immediate '@f:\tmp.sql';
END JOB;
/
Show errors

Please help me.

Tom Kyte
August 12, 2006 - 7:45 pm UTC

</code> http://asktom.oracle.com/pls/ask/search?p_string=How+can+I+execute+an+SQL+script+file+from+stored+procedure+ <code>

You cannot really - sqlplus is the application (the ".exe" program if you will) that runs scripts.

scripts are not something indigenous to "sql" or "oracle" - they are a thing executed by a client application called sqlplus.

How can I execute an SQL script file from stored procedure ?

Paul, August 11, 2006 - 6:19 pm UTC

Hi Tom.

Congratulations by this site.

Would you please how can I execute an SQL script file from stored procedure ?

I try with native sql, but error ORA-00900, ORA-06512 appeared.

Code:

CREATE OR REPLACE PROCEDURE JOB AS
BEGIN
execute immediate '@f:\tmp.sql';
END JOB;
/
Show errors



Paul, August 24, 2006 - 8:53 pm UTC

Thanks Tom

Paul, August 24, 2006 - 8:53 pm UTC

Thanks Tom

dbms_scheduler to the rescue

Rick, September 05, 2006 - 6:38 am UTC

Hello Tom,

We're executing unix scripts from PL/SQL using java (pretty
much as scripted on this site) and it worked fine until
we started hitting a problem on just one of our databases,
where the java just dies without a message and return code -1.

While looking into this on your site I realized that in 10g
we can call os scripts straight from dbms_scheduler, which
is a relief.

My question is: is there a way to get a call to dbms_scheduler
to not return until the job it calls (to execute immediately)
finishes, or will I have to write a wrapper to loop around
and wait for it?

many thanks

Tom Kyte
September 05, 2006 - 5:06 pm UTC

without looking into it very deeply - I would be able to suggest dbms_alert (have the job you schedule use dbms_alert.signal) with a reasonable timeout...

dbms_scheduler to the rescue

Rick, September 06, 2006 - 8:02 am UTC

I don't see how I can generically put dbms_alert.signal into
an os file. It's a bit messier but I think more generally
applicable to loop around in pl/sql and check for the job
finishing. Maybe they'll add a "execute in foreground"
option to dbms_scheduler.create_job one day.
Thanks for the response!


Tom Kyte
September 06, 2006 - 8:06 am UTC

the job itself would include "a signal" call was the point.

Awesome as always

BC, September 08, 2006 - 3:02 pm UTC

Tom,

Can a user, with the appropriate privileges schedule jobs to in different schema's using DBMS_SCHEDULER ? How ?

For example, can user 'A' who is also a schema owner schedule job that runs in schema 'B' & 'C'.


Same question ...

Greg, September 15, 2006 - 11:13 am UTC

*bump* ...

I have the same question as the above poster ... was doing some searching for the answer, and found this thread .. (question minus the answer) ...

I notice the date is a few days old, so at the risk of sounding rude, I'll *bump* this question and ask again ..

Tom? (I know you don't see all reviews ... but I'm hoping you see this one this time around .. ) ;)

Just to clarify, the question is:

Can a Database User "A" launch a DBMS_SCHEDULER job and have it run as User "B" (ie in Schema "B")

In my specific case, we don't want the user to have all the necessary privs on "B" .. but we do want them to be able to run a job (coded through a procedure ... oh wait .. did I just answer my own question?) :)

If User "A" runs Package/Procedure "B".My_Proc ... if "B".My_Proc calls DBMS_SCHEDULER .. it can/will launch the job as user "B", correct??


Tom Kyte
September 15, 2006 - 11:32 am UTC

you can, with the CREATE ANY JOB privilege, create a job in another schema - yes. See the description of the create_job call:

....
To create a job in your own schema, you need to have the CREATE JOB privilege. A user with the CREATE ANY JOB privilege can create a job in any schema. If the job being created will reside in another schema, the job name must be qualified with the schema name. For a job of type EXECUTABLE (or for a job that points to a program of type EXECUTABLE), the job owner must have the CREATE EXTERNAL JOB system privilege before the job can be enabled or run.
......

Thanks !!

BC, September 15, 2006 - 11:45 am UTC

Tom,

Thank you very much for your response, I should have read a little bit more before posting.

BC

Cannot create procedure

xtravel, October 19, 2006 - 4:37 am UTC

Hi

When I run

CREATE OR REPLACE procedure host( cmd in varchar2 )
as
status number;

begin
dbms_pipe.pack_message( cmd );
status := dbms_pipe.send_message( 'HOST_PIPE' );
if ( status <> 0 ) then raise_application_error( -20001, 'Pipe error' );
end if;
end;
/

I am getting following error;

PLS-00201: identifier 'DBMS_PIPE' must be declared

I am using Oracle 9i, Any help?

Tom Kyte
October 19, 2006 - 8:17 am UTC

grant execute on dbms_pipe to yourself;

</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

I think Andrew has a point

Prashant, October 24, 2006 - 11:23 am UTC

Tom,

I read what Andrew is saying above and I think he has got a point. I strongly feel that the DBMS_SCHEDULER has a problem handling wildcard characters in shell scripts. I don't know the limit. But I tried to remove 65 *.dat and 65 *.ctl files and it would just hang using dbms_scheduler whereas when I run it from a command prompt it would run in 2 seconds.

My script is as follows:

#!/bin/csh -x
date>/usr/users/catssvc/test.log
pwd>>/usr/users/catssvc/test.log
env>>/usr/users/catssvc/test.log

ls -la /usr/users/catssvc/*.ctl|wc -l >>/usr/users/catssvc/test.log
ls -la /usr/users/catssvc/*.dat|wc -l >>/usr/users/catssvc/test.log
rm -f /usr/users/catssvc/*.ctl /usr/users/catssvc/*.dat

I created over 130 files in this directory using your unloader script(65 *.ctl and 65 *.dat). I want to delete the files. I ran this script using dbms_scheduler and also from the command prompt. DBMS_SCHEDULER hung and command prompt ran in 2 seconds.

This is not a permission issue either. I have created all the files/directories with 777.
Also, note that when I try to remove 20 files it works fine. Which leads me to believe that there is some kind of limit on how many files can be handled by DBMS_SCHEDULER.


Have you come across this before? I have opened up SR with support. Wanted to know your thoughts.

Tom Kyte
October 24, 2006 - 2:07 pm UTC

I have not and I'm not sure how it "could be" - the shell is already running by then, we are not in control anymore.

DBMS_SCHEDULER

Prashant, October 24, 2006 - 11:29 am UTC

I forgot to post the scheduler job definition:

DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'ARCHIVE_TABLES',
job_type => 'EXECUTABLE',
job_action => '/usr/users/catssvc/delete.sh',
start_date => SYSTIMESTAMP,
job_class => 'DEFAULT_JOB_CLASS',
enabled => TRUE,
auto_drop => FALSE,
comments => 'Delete ctl and dat files.'
);


Could you please try and see

Prashant, October 24, 2006 - 3:36 pm UTC

Tom,

I understand what you are saying. Once DBMS_SCHEDULER hands the control to the shell script, Oracle should have no say in the way in which the script executes. But sometimes what is supposed to happen is not what is happening.

Could you please try and see if you get the same results as I do?

Try creating at least 100 files. And remove them using DBMS_SCHEDULER.

Another change I made was to change the $ORACLE_HOME/rdbms/admin/externaljob.ora file to make the user catssvc and group cats which is what I want the shell script to run as instead of 'nobody'. I tried it with 'nobody' and it still hung.



I am narrowing down what the problem could be.

Prashant, October 24, 2006 - 4:37 pm UTC

Make sure to run your script with '-x' option at the top. Also, make sure you have atleast 150 files each 1K.

I think when you have a lot of output generated (with '-x' option) by the shell script, the scheduler hangs; as if some internal buffer is full and it can't flush the buffer before proceeding further.


Using DBMS_Scheduler to export the database

A reader, October 26, 2006 - 7:06 am UTC

Tom,

I want to use dbms_scheduler to start my daily export and avoid saving password in the script. I don't want to use connect as sysdba in the script and use sys to start a job (in both case it produce a lot of entries in the windows application log because of auditing operation).
So how can I do this. My understanding that as far as export is external job for Oracle I will need the password in the external file in any case....

Tom Kyte
October 26, 2006 - 12:04 pm UTC

why are you doing a daily export is my first question.

you definitely would NEVER use sysdba for export anyway - sysdba cannot - CANNOT - do consistent exports.

but basically, you would (should) use the data pump and the data pump is PLSQL api based - so it'll run as the user the job is run as.

so, stop using export
start using data pump
but really - reconsider what you are doing - export and data pump ARE NOT backup tools, they are logical data copy tools, nothing more.


</code> http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_datpmp.htm#i1007277 <code>

A reader, October 27, 2006 - 3:45 am UTC

Thanks Tom.

No, I don't use export instead of backup - I simple found that having exp file is the simplest way to quick recreate the object (or in my case the whole schema) "as it was yesterday". I support developers and I have a lot of requests like this....

Is data pump in SE of Oracle. There are some people who really use the SE....

Tom Kyte
October 27, 2006 - 7:47 am UTC

datapump is part of SE, yes.

Executing script from PL/SQL

PC, November 03, 2006 - 7:13 am UTC

Thanks for providing useful and valuable information to the Oracle community.

Please can you suggest a workaround to accomplish the following in an anonymous pl/sql block:

DECLARE
Cursor c_1 IS
select script_name name
from dq_fix_scripts
where freq =1 ;
BEGIN
FOR x IN c_1
LOOP
--execute the script
@x.name ;
END LOOP ;
END ;



Tom Kyte
November 03, 2006 - 10:43 am UTC

you cannot, you would have to write a script parsing routine of your own. plsql is a programming language.

sqlplus is a command line tool that processes scripts.

sqlplus can be used to run plsql
plsql cannot issue sqlplus commands, it typically isn't even running on the same machine!

dbms_scheduler job hangs

Balvant, January 16, 2007 - 4:38 pm UTC

I did face same problem as faced by Andrew f.

In my case, I was running a sheduler job using follwoing command:

begin
dbms_scheduler.create_job(
job_name => 'EXP_GP_SCHEMAS_test_JOB'
,job_type => 'EXECUTABLE'
,job_action => '/scratch/oradata/t1invods/jobs/exp/exp_brs_gp_schema.ksh'
,enabled => TRUE
,comments => 'Demo for job schedule.');
end;
/

and my job was running forever, never ending... I need to kill or stop the job manually. The same shell script was running properly at OS level.

As suggested by Tom, I introduced "ulimt -s unlimited" in the begening of the shell script and my nightmare gone.

My scheduler job is running fine now.

Thanks Tom for your help.

-Thanks & Regards,
Balwant Chhasiya.


Stil struggling with dbms_scheduler - Executable ..

Greg, January 30, 2007 - 10:17 am UTC

I followed the examples above from others (that seemed to work) .. but I'm just getting the same error.

I've got create external job, I've ensured the proper file privs are setup ...

however, every time I launch the job:

dbms_scheduler.create_job
( job_name => 'GREG_TEST_SHELL',
job_type => 'executable',
job_action => '/home/gregs/run_me.sh',
enabled => TRUE );
end;
/

and check the status table, I see this:

2742 30-JAN-07 10.02.58.863786 AM -05:00 GREGS GREG_TEST_SHELL
FAILED 27369 30-JAN-07 10.02.58.086472 AM AMERICA/NEW_YORK
30-JAN-07 10.02.58.448575 AM -05:00 +000 00:00:00 1 1033,119
20528 +000 00:00:00.01
ORA-27369: job of type EXECUTABLE failed with exit code: 255
STANDARD_ERROR="execve: Exec format error"


What's exit code 255? what's the "Exec format error" mean?

Tom Kyte
January 31, 2007 - 12:29 pm UTC

does your script start with

#!/bin/sh

or the like?

*sigh*

Greg, February 01, 2007 - 8:23 am UTC

Thanks ... (man I hate Unix for that ... I don't think I'll ever get used to that silly little "#!ksh/whatever" that always seems to need addding .... *sigh*

Thank-you kindly!!

PLS-00201: identifier 'DBMS_SCHEDULER.CREATE_JOB' must be declared

Jeeva, August 21, 2007 - 5:16 am UTC

Hi,

I have created a job in 10g to delete the rows from a table before every 2 hours (in Windows platform) .

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'test_DELETE_job_FUNCTION',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DELETE FROM <TABLE_NAME> WHERE <COLUMN_NAME>
< SYSDATE - 2/24; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=HOURLY INTERVAL=2',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined for deletion program.');
END;
/

But, When I executed this script, It gave the eror like

"ORA-06550: line 2, column 4:
PLS-00201: identifier 'DBMS_SCHEDULER.CREATE_JOB' must be declared
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored"

Please help me out in this issue and give me a solution. It's very urgent.

Regards,
Jeeva
Tom Kyte
August 22, 2007 - 11:27 am UTC

can you describe dbms_scheduler?

perhaps you have not been authorized to use this package.

PLS-00201: identifier 'DBMS_SCHEDULER.CREATE_JOB' must be declared

Jeeva, August 21, 2007 - 5:19 am UTC

Hi,

I have created a job in 10g to delete the rows from a table before every 2 hours (in Windows platform) .

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'test_DELETE_job_FUNCTION',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DELETE FROM <TABLE_NAME> WHERE <COLUMN_NAME>
< SYSDATE - 2/24; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=HOURLY INTERVAL=2',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined for deletion program.');
END;
/

But, When I executed this script, It gave the eror like

"ORA-06550: line 2, column 4:
PLS-00201: identifier 'DBMS_SCHEDULER.CREATE_JOB' must be declared
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored"

Please help me out in this issue and give me a solution. It's very urgent.

Regards,
Jeeva

ORA-27369

Victor, August 23, 2007 - 11:09 pm UTC

Dear Tom,

I tried to use dbms_scheduler to run a shell script 'test.sh' which is created by the following script:

SQL> DECLARE
  2    vFile utl_file.file_type;
  3    vcnt number;
  4    vStatus varchar2(30);
  5  BEGIN
  6     vFile := utl_file.fopen('OHSDEV_IMAGE', 'test.sh', 'W');
  7     utl_file.put_line(vFile,'#!/bin/sh'); 
  8     utl_file.put_line(vFile, 'sqlplus ohs/ohs@dev03 @/ora_apps/ohs/dev/image/ohss1106.sql /ora_apps/ohs/dev/image/test_sp.txt'
);
  9     utl_file.fclose(vFile);
 10  END;
 11  /

PL/SQL procedure successfully completed.

The file is successfully created.  But when I run the job, I got the following error.

SQL> EXEC DBMS_SCHEDULER.RUN_JOB('TEST_JB2');
BEGIN DBMS_SCHEDULER.RUN_JOB('TEST_JB2'); END;

*
ERROR at line 1:
ORA-27369: job of type EXECUTABLE failed with exit code: Permission denied
ORA-06512: at "SYS.DBMS_ISCHED", line 150
ORA-06512: at "SYS.DBMS_SCHEDULER", line 441
ORA-06512: at line 1

Could you please help me?  Thanks in advance.

Tom Kyte
August 24, 2007 - 2:48 pm UTC

well, what are the permissions on that shell script (this by the way looks like a HUGELY BAD IDEA)

permissions to execute shell script using dbms_scheduler

Nikhil, August 27, 2007 - 12:43 pm UTC

You need to setup permissions as shown below, to execute shell scripts using the DBMS_SCHEDULER.

-rw-r----- root:dba $ORACLE_HOME/rdbms/admin/externaljob.ora
-rwsr-x--- root:dba $ORACLE_HOME/bin/extjob
-rwsr-xr-x oracle:dba $ORACLE_HOME/bin/extjobo

Also please look at the contents of $ORACLE_HOME/rdbms/admin/externaljob.ora, it should reflect correct ORACLE BINARIES unix owner and group.

Cheer!!
Nikhil

10g dbms_scheduler shell script

Sup, September 07, 2007 - 1:54 am UTC

Hi Tom,

I am trying to execute the following script as system in 10g server:
begin
dbms_scheduler.create_job(
job_name=>'TEST1',
job_type=>'executable',
job_action=>'/home/oracle/cmp/sh/XLFK300H.sh',
enabled=>TRUE
);
end;
/

with the script located in 'orcle' unix user account as follows:

-rwxrwxrwx 1 oracle oinstall 3771 Sep 7 11:16 XLFK300H.sh

but I am getting a status of failed when I see the details:

select error#,status,additional_info from DBA_SCHEDULER_JOB_RUN_DETAILS
where job_name='TEST1';

ORA-27369: job of type EXECUTABLE failed with exit code: 255
STANDARD_ERROR="execve: No such file or directory"

-- Could you please help!
Tom Kyte
September 11, 2007 - 8:47 am UTC

check the entire path to the file. could be one of the directories is not readable or executable.

Bosco, July 23, 2008 - 1:01 pm UTC

Hi Tom,
I understand the Unix shell script can be called using dbms_scheduler package. Can you explain what are advantages dbms_scheduler having over Unix Cron.

Tom Kyte
July 24, 2008 - 10:37 am UTC

the assumption is - you want to do a database thing, dbms_scheduler runs only when database is, it logs errors and history into database, it is cross platform, it just goes where ever the database does. You schedule jobs from anywhere - just by connecting to the database.

it is a database thing, it is integrated into the management tools, you have complete visibility into it as a DBA.

most dba's should not be allowed to log onto the server itself - using things like cron make that hard.

Running a batch file

Zahir M, September 04, 2008 - 3:25 pm UTC

Tom : 

I am trying to execute a windows batch file via dbms_scheduler. I am getting the following error . 
Please advise



SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 64-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production



SQL> BEGIN
  2  sys.dbms_scheduler.create_job(
  3  job_name => '"SYSTEM"."P_TESTBAT1"',
  4  job_type => 'EXECUTABLE',
  5  job_action => 'd:\ps\t1.bat',
  6  start_date => systimestamp at time zone 'US/Eastern',
  7  job_class => 'DEFAULT_JOB_CLASS',
  8  auto_drop => FALSE,
  9  enabled => FALSE);
 10  sys.dbms_scheduler.set_attribute( name => '"SYSTEM"."P_TESTBAT1"', attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_FULL);
 11  sys.dbms_scheduler.enable( '"SYSTEM"."P_TESTBAT1"' );
 12  END;
 13
 14  /

PL/SQL procedure successfully completed.

SQL>  exec dbms_scheduler.run_job('SYSTEM.P_TESTBAT1');
BEGIN dbms_scheduler.run_job('SYSTEM.P_TESTBAT1'); 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


SQL>

SQL> host dir d:\ps\t1*
 Volume in drive D is Data
 Volume Serial Number is 908E-B8E0

 Directory of d:\ps

09/04/2008  02:47 PM                21 t1.bat
               1 File(s)             21 bytes
               0 Dir(s)  29,816,266,752 bytes free
               


This is the content of t1.bat
==============================

echo date > datew.txt


Tom Kyte
September 04, 2008 - 4:32 pm UTC

... ORA-27301: OS failure message: The system cannot find the file specified. ...

that drive d: - it

a) is on the SERVER, the database SERVER right?

b) it is a REAL FILE SYSTEM - not a share right?

c) the permissions are such that the account under which the database service is executing can see it right?

Batch File in DBMS_SCHEDULER

Zahir, September 04, 2008 - 5:19 pm UTC

a) is on the SERVER, the database SERVER right?
It is on the DB server .


b) it is a REAL FILE SYSTEM - not a share right?
it is a REAL FILE SYSTEM

c) the permissions are such that the account under which the database service is executing can see it right?

the user has all the permissions . Siging on as SYSTEM.
( Local System Admin)

I even tried placing the batch files in c: drive . Still It does not work.
Tom Kyte
September 05, 2008 - 8:10 am UTC

the user system is not the account the database service is running as.

use utl_file to see if you can "fopen" this file - and see it.

system is the database schema/account/username

the OS user Oracle runs under is entirely different from a database account


and stop using SYS and SYSTEM - don't do it. Use your OWN accounts, these accounts are special and shouldn't really be used for anything, especially testing because you'll never use them in production.

ora-27369 execve

Dik Pater, September 06, 2008 - 4:45 pm UTC

I had the same problems when executing an executable on linux from EM-jobs.
This executable was a shell script which echo'd to a file.
( so write permissions were needed)
I was not awake and had better executed a simple /bin/df of /bin/ls which creates no file, later on I tested this and this worked direct.

At metalink there is a note Note:391820.1.
On linux I tested this and it worked out for my testcase, with a little remark.

Excerpt from the note :
Ensure the configuration file $ORACLE_HOME/rdbms/admin/externaljob.ora is owned by root:
# cd $ORACLE_HOME/rdbms/admin/
# chown root externaljob.ora
# chmod 640 externaljob.ora
# ls -la externaljob.ora
-rw-r----- 1 root oinstall 1537 Sep 13 09:24 externaljob.ora
Edit $ORACLE_HOME/rdbms/admin/externaljob.ora and set run_user to the OS account that owns the Oracle installation and the database and run_group to the OS group that owns the Oracle_Home.
Example:
OS account: oracle
OS group: oinstall
run_user = oracle
run_group = oinstall
Ensure the setuid bit is set on the $ORACLE_HOME/bin/extjob executable.
# cd $ORACLE_HOME/bin
# chmod 4750 extjob
# ls -la extjob
-rwsr-x--- 1 root oinstall 64988 Mar 29 18:22 extjob

I tested my script and it wrote the file, but....
the job still failed in EM, ora-29369 with execve 255
After putting in a exit 0 in the shell script it gave SUCCEEDED.
The created file belonged to root, and the contents of the externaljobs.ora seems not to be considered, so I changed the owner of $ORACLE_HOME/bin/extjob to oracle.
Now I get files created owned by Oracle.
Hope this will help some of you.

Useful

vkumar, August 06, 2009 - 4:58 am UTC

This discussion was very useful.Thanks a lot.

Clarification regarding perl script from trigger

Navyasri, April 06, 2010 - 1:50 pm UTC

Is it possible to execute a perl script from a trigger in a similar manner? It would be a great help if you could give an example. Or atleast specify what to use?
Tom Kyte
April 12, 2010 - 7:23 pm UTC

you could "shell out" and run an OS command, yes but it would be a really horribly bad idea, I'll point you to why in a minute

You can run host commands (don't use this pipe server, java stored procedure would be more appropriate in 2010 - see
http://asktom.oracle.com/pls/asktom/asktom.search?p_string=java+host+command
)

hosting out and running a command like that from a trigger is going to be non-transactional, if it does something (makes any sort of change) and you roll back - that will be really bad. You probably want to do this asynchronously - that is, AFTER YOU COMMIT have the perl script run (dbms_job would be useful).

hosting out and running a command will be slow - process creation - slow slow slow. You probably do not want this in a trigger.

Executing a unix shell script with DBMS_SCHEDULER

DT, May 17, 2010 - 10:36 am UTC

Hi Tom,

I have the following Unix shell script create_backup_file.sh:

#!/usr/bin/ksh
/usr/bin/ssh trotestbat@10.160.41.129 /usr/bin/touch /app_home/home/trotestbat/scripts/TRO_batch_complete_`date +%d-%m-%Y-%H%M`


If I execute this from the command prompt it creates the file on the remote server.

I've used dbms_scheduler to try and execute this from Oracle:

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_PROGRAM
    (
      program_name         => 'DTE.CREATE_TRO_BACKUP_FILE'
     ,program_type         => 'EXECUTABLE'
     ,program_action       => '/APP/TORPEDO/DTE/SCRIPTS/create_backup_file_tro.sh'
     ,number_of_arguments  => 0
     ,enabled              => TRUE
     ,comments             => NULL
    );
 
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'DTE.TESTAGAIN'
      ,start_date      => TO_TIMESTAMP_TZ('2010/05/17 16:09:24.710789 +01:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
      ,repeat_interval => NULL
      ,end_date        => NULL
      ,program_name    => 'DTE.CREATE_TRO_BACKUP_FILE'
      ,comments        => NULL
    );
 
END;
/ 


The problem I have is that scheduler executes the shell script and creates the file but it never completes the job. The status of the job is permanently 'RUNNING'. Why is the scheduler not returning a completed status?


Dave T, May 27, 2010 - 11:15 am UTC

Tom,

The above issue was solved using the -n option with ssh:

ssh -n

The remote jobs now execute and the job status returns as COMPLETE.

Rupam, May 31, 2010 - 11:58 pm UTC

Tom:
I have faced a problem like

CHAIN_LOG_ID="1634263", STEP_NAME="GEN_PENDING_DIR_FILE", ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:accessing extjob wrapper failed with status: 13
ORA-27301: OS failure message: Permission denied
ORA-27302: failure occurred at: sjsec 3

whenever i tried to execute a dbms_scheduler job.Could please help me out what would be the workaround for this problem.
Tom Kyte
June 08, 2010 - 8:02 am UTC

"permission denied."

What might cause that?


The job runs "as Oracle", it would seem that Oracle likely doesn't have the ability to execute that program....

How to run Windows OS to Unix shellscript via Oracle

shailesh, June 28, 2010 - 3:12 am UTC

Hi Tom,

I am using a Windows Oracle server. I need to connect to a Unix server (Oracle daatabase available) and run some Unix command and need to redirect those output into a txt file and then i need to copy that txt file into my Windows Oracle server m/c.

I whole purpose is to do all these activity to capture the output of Unix command into my Windows databse table.

Could you please help me out on this?

Note: I cant use FTP bcos of security reasons.
I am ot using any other tool or s/w like java/ProC/C.
Tom Kyte
July 06, 2010 - 11:21 am UTC

you can write a java stored procedure OR use the scheduler (read about dbms_scheduler) to run OS commands.

The output of these could be redirected to a file or easier still, returned as a string output.

And you can of course call remote stored procedures that return a string...

So, write a stored procedure (java) on the unix machine:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:952229840241#2717585194628

and call it from windows and do whatever you like with the output.

unix

A reader, September 23, 2010 - 10:18 am UTC

Tom:

If i need to use SCP protocol and command to copy a file created by oracle from local server to a remote server, would you use the same method described here.

create or replace procedure host( cmd in varchar2 )
as
status number;
begin
dbms_pipe.pack_message( cmd );
status := dbms_pipe.send_message( 'HOST_PIPE' );
if ( status <> 0 ) then raise_application_error( -20001, 'Pipe error' );
end if;
end;
/

Tom Kyte
September 27, 2010 - 9:35 am UTC

no, I cannot imagine writing a pipe server today in the 21st century.

You have dbms_scheduler which can run external jobs.

You have java stored procedures which can run external code as well.

I would use either of them - but not a pipe server.

Is APEX package installation using SQL*Plus from PL/SQL

Dieter Oberkofler, January 26, 2011 - 12:07 pm UTC

When installing a packaged APEX Installation a typical SQL*Plus script file is processed and I was wondering if APEX actually uses SQL*Plus from PL/SQL to parse the script file and hoe this works.

I'm looking for a similar solution for our own installation procedure and was hoping you might have some insights on how APEX is handling this problem.

Tom Kyte
February 01, 2011 - 2:49 pm UTC

apex does not use sqlplus from plsql.

YOU are using sqlplus directly.

All scripts run by APEX (using the APEX UI) are executed internally by APEX, it parses the script and runs it much like sqlplus would have.

Most interesting

Dieter, February 01, 2011 - 11:22 pm UTC

This is most interesting and very surprising!

1) Would you know why they decided to go for an internal parser instead of calling Sqlplus on the server?

2) Am I possible/allowed (and does it make sense) to use the APEX parser to execute scripts not directly related to an APEX app?

3) Would you know how the APEX parses has been implemented?

Thank you very much.
Tom Kyte
February 02, 2011 - 7:30 am UTC

1) because

o sqlplus doesn't have to exist on the server.

o invoking another program as if it were a "subroutine" is impossibly hard to process errors and such

o running sqlplus would require access to a username and password - something APEX doesn't necessarily have

o running sqlplus would expose the username and password easily in general

o spawning a process for each script run would be hugely expensive.

o there have to be a billion other reasons why trying to use a standalone program as a subroutine is a *bad* idea.


2) not sure, you can ask on the APEX forums on otn.oracle.com

3) see #2 - mostly in plsql though...

permission issues dbms_scheduler can't return parameter from shell script

Salaam Yitbarek, April 21, 2011 - 11:10 am UTC

I've got a shell script script1.sh owned by unix user user1. script1.sh has execute permission for user and group. I execute script1.sh from a PL/SQL procedure using the Java method. This works fine because user1 happens to be in the dba group along with oracle.

Now the dba's want to remove user1 from the dba group. So script1.sh no longer works via the Java call unless execute permission on it is granted to other. Of course, we can't have a script with execute permission!

We've tried creating another group for both oracle and user1 to be in, and then tried it, but no luck. The only other choice would be to put the scripts under the oracle unix user, but we don't want to do that either.

The next option was to try dbms_scheduler, but this doesn't seem to return return parameters from shell script.

Any suggestions?
Tom Kyte
April 25, 2011 - 8:24 am UTC

... We've tried creating another group for both oracle and user1 to be in, and then
tried it, but no luck. ...

should work, define "no luck" please. You may have to restart the database after adding to the group as they group identity wouldn't be known by the processes - like if you add yourself to a group, your existing shell sessions don't know about it right away, you have to create a new shell.

Re: permission issues dbms_scheduler can't return parameter from shell script

Salaam Yitbarek, May 03, 2011 - 12:54 pm UTC

Well, actually, just to reconfirm our tests, we put user1 back in the dba group and tested the script. Still not 'no luck'. By 'no luck' I mean that shell script never executes and the java procedure RunOSCmd returns a -1 for the error code.

The shell script is:

----------
#!/bin/ksh

echo 'Running script'
exit 0
----------

OS: AIX 6.1
db version: 11.1.0.7.0


Tom Kyte
May 04, 2011 - 1:43 pm UTC

you are confusing me now - what is up with dbms_scheduler and a java stored procedure to run an os command? given that dbms_scheduler can just run scripts itself?

shell execution with argument from dbms_scheduler failingfailing

Sattam, September 23, 2011 - 12:15 pm UTC

Hi Tom,

After much effort and lot of googling I am not being able to achieve this
Execute a shell script with argument from the dbms scheduler

My target is to run the below command from dbms_scheduler which runs perfectly from UNIX command prompt

sattam:>/sattam/rnd/tst.sh /sattam/rnd

The shell tst.sh is as below

##### beginning of shell script #####
#!/usr/bin/ksh
scriptloc=$1

/usr/bin/rm $scriptloc/tst.log
echo "do not delete this file!!!" >> $scriptloc/tst.log
/usr/bin/chmod 777 $scriptloc/tst.sh

##### end of shell script #####



The file tst.sh is owned by OS user sattam and having 777 permission (so is the entire path /sattam/rnd)

sattam:>ls -ltr tst.sh
-rwxrwxrwx 1 sattam dba 325 Sep 23 17:43 tst.sh


Below is what I am doing in sys I have a procedure

CREATE OR REPLACE PROCEDURE PR_EXEC_OS_COMMAND(P_OS_COMMAND IN VARCHAR2) IS
BEGIN

DBMS_SCHEDULER.CREATE_JOB (job_name => 'RUN_OS_COMMAND',
schedule_name => '"SYS"."DAILY_PURGE_SCHEDULE"',
job_type => 'EXECUTABLE',
job_action => P_OS_COMMAND,
number_of_arguments => 0,
job_class => 'DEFAULT_JOB_CLASS',
enabled => true,
auto_drop => TRUE,
comments => NULL);

DBMS_SCHEDULER.RUN_JOB (job_name => 'RUN_OS_COMMAND');
DBMS_SCHEDULER.DROP_JOB (job_name => 'RUN_OS_COMMAND');

END;

BEGIN
pkg_sat_util.PR_EXEC_OS_COMMAND('sattam/rnd/tst.sh /sattam/rnd');
END;

and getting an error


ORA-27369: job of type EXECUTABLE Failed with exit code: No such File or directory
ORA-06512: at "SYS.DBMS_ISCHED, line 150
ORA-06512: at "SYS.DBMS_5CHEDULER, line 441
ORA-06512: at line 2
View program sources of error stack?

Can anybody suggest Where am I going wrong?
I am using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as SYS

Tom Kyte
September 23, 2011 - 12:55 pm UTC

... ('sattam/rnd/tst.sh /sattam/rnd'); ...

that looks messed up, why are you using a relative path in there???? Are you missing a /???

A reader, September 27, 2011 - 12:42 pm UTC

No Sir,I am not! must have messed up something while copy pasting, sorry about that. Presently I am trying to do the same thing using arguments as somebody told me it is because the scheduler can only absolute path to a shell script without argumants. I will try and update.

script is not executed

Archana, February 16, 2012 - 5:27 am UTC

Hi Tom

I did exactly the same like you have mentioned(Copy paste)

I'm new to c shell script.

Created a procedure and then the host.csh file

When I run the host.csh file nothing happens, but returns to the command promt

When call the procedure, it says successfully completed,but script is not executed.

For some reason host.csh file does not run.

Do I have to do something here?

create or replace procedure host( cmd in varchar2 )
as
status number;
begin
dbms_pipe.pack_message( cmd );
status := dbms_pipe.send_message( 'HOST_PIPE' );
if ( status <> 0 ) then raise_application_error( -20001, 'Pipe error' );
end if;
end;
/

Here is a C-Shell script you can run in the background, it should be named host.csh.
host.csh will be run by you after the db is up. it will create temporary scripts
"tmp.csh" that it will run. the last thing these tmp.csh scripts do is re-run host.csh
to get the next request....

-------------------- bof ----------------------------
#!/bin/csh -f

sqlplus myusername/password <<"EOF" | grep '^#' | sed 's/^.//' > tmp.csh

set serveroutput on

declare
status number;
command varchar2(255);
begin
status := dbms_pipe.receive_message( 'HOST_PIPE' );
if ( status <> 0 ) then
dbms_output.put_line( '#exit' );
else
dbms_pipe.unpack_message( command );
dbms_output.put_line( '##!/bin/csh -f' );
dbms_output.put_line( '#' || command );
dbms_output.put_line( '#exec host.csh' );
end if;
end;
/
spool off
"EOF"

chmod +x tmp.csh
exec tmp.csh
----------------------- EOF ---------------------------------



Tom Kyte
February 16, 2012 - 7:24 am UTC

do not use this method, it is unnecessarily complex and not necessary today.

Use something like this:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:952229840241#2717585194628

Options

Developer, February 27, 2012 - 10:23 am UTC

I have a pl\sql block that generated .csv on the db server. On a daily basis a ksh scripts is scheduled to run this pl\sql block and sftp (key based) files to EFT server (different server) .

Now on a ad-hoc basis my client wants to run the entire set up fron .net web forms( i have created a page that on button_click event calls the pl\sql block ) now i need to sftp them to the EFT location some how. I see following option s with me
1. Can i have a pl\sql block that take care of SFTP *.csv files(from DB server) to EFT location . by which i can invoke this pl\sql block from .Net .

Thanks for any help on this .
Tom Kyte
February 28, 2012 - 7:13 am UTC

please do not ask the same question all over the place, that is not productive.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2556405800346433753#4460941900346806208


Abhishek, April 22, 2013 - 9:35 am UTC

Hi Tom,

I was trying to call a unix script from oracle as you have mentioned in this thread but I am encountering the following error:

java.io.IOException: executable not found (Note that lookup with PATH isn't done due to the oracle executable being setuid.)
at java.lang.OracleProcess.create(Native Method)
at java.lang.OracleProcess.construct(OracleProcess.java:111)
at java.lang.OracleProcess.<init>(OracleProcess.java:41)
at java.lang.OracleProcess.start(OracleProcess.java:381)
at java.lang.ProcessBuilder.start(ProcessBuilder.java)
at java.lang.Runtime.exec(Runtime.java:591)
at java.lang.Runtime.exec(Runtime.java:429)
at java.lang.Runtime.exec(Runtime.java:326)
at Util.RunThis(UnixCall:35)
value returned : -1

I have given all the required grants of the file to the oracle schema.

Kindly reply.

Regars,
Abhishek
Tom Kyte
April 22, 2013 - 8:15 pm UTC

how did you call it

are you sure it is there

and that the permissions are set appropriately.

Abhishek, April 23, 2013 - 1:10 am UTC

Hi,

I have given the following permissions

dbms_java.grant_permission( 'OFSAAATOMIC', 'SYS:java.io.FilePermission', '/oracle/app/product/11.2.0.2/R2BDFRC/dbs/wrapper.sh','read,write');
dbms_java.grant_permission( 'OFSAAATOMIC', 'SYS:java.io.FilePermission', '/oracle/app/product/11.2.0.2/R2BDFRC/dbs/wrapper.sh', 'execute' );

dbms_java.grant_permission( 'OFSAAATOMIC', 'SYS:java.io.FilePermission', '/oracle/app/product/11.2.0.2/R2BDFRC/dbs','read,write' );
dbms_java.grant_permission( 'OFSAAATOMIC', 'SYS:java.io.FilePermission', '/oracle/app/product/11.2.0.2/R2BDFRC/dbs', 'execute' );

and I have changed the file mode to 777.

I am calling using the oracle proc

run_cmd('/oracle/app/product/11.2.0.2/R2BDFRC/dbs/wrapper.sh')


Tom Kyte
April 23, 2013 - 12:53 pm UTC

why would you put this on a page that doesn't have anything to do with run_cmd? makes it really difficult as the stuff we need isn't even here.


full example, start to finish, all code (too many people modify the code and say "we ran YOUR code"). everything.

something like this (showing is not working and again working....)

ops$tkyte%ORA11GR2> drop user rt_test cascade;

User dropped.

ops$tkyte%ORA11GR2> create user rt_test identified by rt_test;

User created.

ops$tkyte%ORA11GR2> grant create session, create procedure to rt_test;

Grant succeeded.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> begin
  2    dbms_java.grant_permission
  3    ('RT_TEST',
  4     'java.io.FilePermission',
  5     '/home/ora11gr2/script/wrapper.sh',
  6     'execute');
  7  
  8    dbms_java.grant_permission
  9    ('RT_TEST',
 10     'java.lang.RuntimePermission',
 11     '*',
 12     'writeFileDescriptor' );
 13  end;
 14  /

PL/SQL procedure successfully completed.

<b>that is all we need for this user, now we'll create the code:</b>

ops$tkyte%ORA11GR2> pause

ops$tkyte%ORA11GR2> connect rt_test/rt_test
Connected.
rt_test%ORA11GR2> create or replace and compile
  2  java source named "Util"
  3  as
  4  import java.io.*;
  5  import java.lang.*;
  6  
  7  public class Util extends Object
  8  {
  9    public static int RunThis(String args)
 10    {
 11    Runtime rt = Runtime.getRuntime();
 12    int        rc = -1;
 13  
 14    try
 15    {
 16       Process p = rt.exec(args);
 17  
 18       int bufSize = 4096;
 19       BufferedInputStream bis =
 20        new BufferedInputStream(p.getInputStream(), bufSize);
 21       int len;
 22       byte buffer[] = new byte[bufSize];
 23  
 24       // Echo back what the program spit out
 25       while ((len = bis.read(buffer, 0, bufSize)) != -1)
 26          System.out.write(buffer, 0, len);
 27  
 28       rc = p.waitFor();
 29    }
 30    catch (Exception e)
 31    {
 32       e.printStackTrace();
 33       rc = -1;
 34    }
 35    finally
 36    {
 37       return rc;
 38    }
 39    }
 40  }
 41  /

Java created.

rt_test%ORA11GR2> create or replace
  2  function RUN_CMD(p_cmd in varchar2) return number
  3  as
  4  language java
  5  name 'Util.RunThis(java.lang.String) return integer';
  6  /

Function created.

rt_test%ORA11GR2> create or replace procedure RC(p_cmd in varchar2)
  2  as
  3    x number;
  4  begin
  5    x := run_cmd(p_cmd);
  6  end;
  7  /

Procedure created.

rt_test%ORA11GR2> pause

<b>now the script we want to run is out there - but the person (the user id) running it must be able to r and x EVERY PART of the path and have x on the script - in this case, I don't have execute (i suspect maybe part of your path is precluding you, i cannot tell)</b>

rt_test%ORA11GR2> 
rt_test%ORA11GR2> !ls -lag /home/ora11gr2/script/wrapper.sh
-rwx------ 1 ora11gr2 44 Apr 23 08:47 /home/ora11gr2/script/wrapper.sh

rt_test%ORA11GR2> 

<b>Now, if I connect locally, not using sqlnet, my dedicated server is forked by me - and will be "me", it will fail:</b>

rt_test%ORA11GR2> connect rt_test/rt_test
Connected.
rt_test%ORA11GR2> variable x number;
rt_test%ORA11GR2> set serveroutput on
rt_test%ORA11GR2> exec dbms_java.set_output(100000);

PL/SQL procedure successfully completed.

rt_test%ORA11GR2> exec :x := RUN_CMD('/home/ora11gr2/script/wrapper.sh');

PL/SQL procedure successfully completed.

rt_test%ORA11GR2> print x

         X
----------
       126

<b>but if I connect over the network, the dedicated server is forked by the listener and is running as "oracle", and it works</b>


rt_test%ORA11GR2> connect rt_test/rt_test@ora11gr2
Connected.
rt_test%ORA11GR2> variable x number;
rt_test%ORA11GR2> set serveroutput on
rt_test%ORA11GR2> exec dbms_java.set_output(100000);

PL/SQL procedure successfully completed.

rt_test%ORA11GR2> exec :x := RUN_CMD('/home/ora11gr2/script/wrapper.sh');
Hello World
HOSTNAME=localhost.localdomain
TERM=xterm
SHELL=/bin/bash
HISTSIZE=1000
USER=ora11gr2
LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;
LD_LIBRARY_PATH=/home/ora11gr2/app/ora11gr2/product/11.2.0/dbhome_3/lib
ORACLE_SID=ora11gr2
PATH=
MAIL=/var/spool/mail/ora11gr2
PWD=/home/ora11gr2/app/ora11gr2/product/11.2.0/dbhome_3/dbs
INPUTRC=/home/tkyte/.inputrc
LANG=en_US.UTF-8
ORA_NET2_DESC=14,17
SQLPATH=/mnt/hgfs/CDrive/Users/tkyte/My Documents/docs/sqlstuff
ORACLE_SPAWNED_PROCESS=1
SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
HOME=/home/ora11gr2
SHLVL=2
LOGNAME=ora11gr2
CVS_RSH=ssh
ORACLE_PROC_MAKEFILE=/home/ora11gr2/app/ora11gr2/product/11.2.0/dbhome_3/precom
CLASSPATH=/home/ora11gr2/app/ora11gr2/product/11.2.0/dbhome_3/jdbc/lib/classes1
LESSOPEN=|/usr/bin/lesspipe.sh %s
DISPLAY=:0.0
ORACLE_HOME=/home/ora11gr2/app/ora11gr2/product/11.2.0/dbhome_3
G_BROKEN_FILENAMES=1
_=/bin/env

PL/SQL procedure successfully completed.

rt_test%ORA11GR2> print x

         X
----------
         0

rt_test%ORA11GR2>



try this very simple test - exactly as is - from your database server. wrapper.sh for me was just:


[ora11gr2@localhost script]$ cat wrapper.sh
#!/bin/bash

/bin/echo Hello World
/bin/env
[ora11gr2@localhost script]$

Abhishek, April 24, 2013 - 2:59 am UTC

Hi,

Sorry for putting this question in the wrong forum.

Thanks for the detailed analysis and help.

All the points mentioned by you are good and I have followed them.

The only discrepancy I find is the user that my oracle proc uses when it tries to execute the shell script.

in place of RUN_CMD('/home/ora11gr2/script/wrapper.sh');
i have tried to fire
RUN_CMD('/bin/env ');

The output is :

************************************************
__CLSAGFW_TYPE_NAME=ora.service.type
ORA_CRS_HOME=/oracle/grid/11.2.0.3
HOSTNAME=orkxdr2bintdb01.espdev.aurdev.national.com.au
TERM=xterm
SHELL=/bin/bash
__CRSD_CONNECT_STR=(ADDRESS=(PROTOCOL=IPC)(KEY=CRSD_IPC_SOCKET_11))
HISTSIZE=1000
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
CRF_HOME=/oracle/grid/11.2.0.3
GIPCD_PASSTHROUGH=false
ORA_DAEMON_TRACING_LEVELS=AGENT=0,AGFW=0,CLSFRAME=0,CLSVER=0,CLUCLS=0,COMMCRS=0,COMMNS=0,CRSAPP=0,CRSCCL=0,CRSCEVT=0,CRSCOMM=0,CRSD=0,CRSEVT=0,CRSMAIN=0,CRSOCR=0,CRSPE=0,CRSPLACE=0,CRSRES=0,CRSRPT=0,CRSRTI=0,CRSSE=0,CRSSEC=0,CRSTIMER=0,CRSUI=0,CSSCLNT=0,SuiteTes=0,UiServer=0,OCRAPI=1,OCRCLI=1,OCRSRV=1,OCRMAS=1,OCRMSG=1,OCRCAC=1,OCRRAW=1,OCRUTL=1,OCROSD=1,OCRASM=1
__CRSD_AGENT_NAME=/oracle/grid/11.2.0.3/bin/oraagent_oracle
_ORA_AGENT_ACTION=TRUE
__CRSD_MSG_FRAME_VERSION=2
USER=root
LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:
LD_LIBRARY_PATH=/oracle/grid/11.2.0.3/lib:/etc/ORCLcluster/lib
ORACLE_SID=R2BDFRC_1
__CLSAGENT_INCARNATION=1
CRS_LIMIT_MEMLOCK=unlimited
ORASYM=/oracle/grid/11.2.0.3/bin/oraagent.bin
PATH=
MAIL=/var/spool/mail/root
PWD=/
INPUTRC=/etc/inputrc
ENV_FILE=/oracle/grid/11.2.0.3/crs/install/s_crsconfig_orkxdr2bintdb01_env.txt
CRS_LIMIT_OPENFILE=65536
LANG=en_US.ISO-8859-1
TZ=Australia/Melbourne
__IS_HASD_AGENT=
__CLSAGENT_LOG_NAME=ora.service.type_oracle
ORA_DAEMON_LOGGING_LEVELS=AGENT=1,AGFW=0,CLSFRAME=0,CLSVER=0,CLUCLS=0,COMMCRS=0,COMMNS=0,CRSAPP=0,CRSCCL=0,CRSCEVT=0,CRSCOMM=0,CRSD=0,CRSEVT=0,CRSMAIN=0,CRSOCR=0,CRSPE=0,CRSPLACE=0,CRSRES=0,CRSRPT=0,CRSRTI=0,CRSSE=0,CRSSEC=0,CRSTIMER=0,CRSUI=0,CSSCLNT=0,SuiteTes=1,UiServer=0,OCRAPI=1,OCRCLI=1,OCRSRV=1,OCRMAS=1,OCRMSG=1,OCRCAC=1,OCRRAW=1,OCRUTL=1,OCROSD=1,OCRASM=1
HOME=/root
SHLVL=1
__CLSAGENT_LOGDIR_NAME=crsd
LD_ASSUME_KERNEL=
CRS_LIMIT_CORE=unlimited
__CLSAGENT_USER_NAME=oracle
LOGNAME=root
LESSOPEN=|/usr/bin/lesspipe.sh %s
DISPLAY=localhost:10.0
ORACLE_HOME=/oracle/app/product/11.2.0.2/R2BDFRC
G_BROKEN_FILENAMES=1
HISTTIMEFORMAT=%h/%d - %H:%M:%S
CRS_LIMIT_STACK=2048
TNS_ADMIN=/oracle/grid/11.2.0.3/network/admin/
ORACLE_BASE=/oracle/app
ORA_NET2_DESC=77,80
ORACLE_SPAWNED_PROCESS=1
*************************************************

Please note that the user is root rather than oracle.

Please let me know if that is the problem.

when i execute RUN_CMD('whoami');

I get oracle as the output.

This is making me confused.

Conclusion is my problem is when I fire the shell script from oracle it has some problem with the unix user which oracle user to fire the shell script.

Thanks for bearing with my long problem description.

Regards,
Abhishek

Tom Kyte
April 24, 2013 - 12:47 pm UTC

USER is just an environment variable. it looks like someone might have su'd from root to oracle to start the database. that USER variable is not meaningful.

you do not have access to that wrapper.sh script - you do have access to env and whoami.

meaning - there is a permissions problem.


Abhishek, April 24, 2013 - 3:15 am UTC

Hi,

Kindly give some thought to the error message

"java.io.IOException: executable not found (Note that lookup with PATH isn't done due to the oracle
executable being setuid.)"

has it got to do anything with setuid concept.

Thanks,
Abhishek
Tom Kyte
April 24, 2013 - 12:49 pm UTC

permissions...

do something easier. put a script in /tmp or something without a ton of directories. it is likely that one of the directories in that path isn't navigable.

Abhishek, April 24, 2013 - 4:24 am UTC

Hi,

I have checked in the server and following are the user rights on oracle executables

ORACLE_HOME=/oracle/app/product/11.2.0.2/R2BDFRC

Path :/oracle/app/product/11.2.0.2/R2BDFRC/bin

-rwsrwx---+ 1 oracle oinstall 228908629 Jun 14 2012 oracle
-rwsrwx---+ 1 oracle oinstall 228886854 Jun 14 2012 oracleO
-rwsrwx---+ 1 root oinstall 68432 May 31 2012 oradism

Should I change the rights to -rwxrwxrwx+ .

Will it solve my problem?

Thanks,
Abhishek
Tom Kyte
April 24, 2013 - 12:50 pm UTC

do not play around with executable permissions.

you are already running the oracle process, you wouldn't be connected otherwise.

Abhishek, April 24, 2013 - 4:37 am UTC

Hi,

Sorry the rights are

-rwsrws--x+ 1 oracle oinstall 228908629 Jun 14 2012 oracle
-rwsrws--x+ 1 oracle oinstall 228886854 Jun 14 2012 oracleO
-rwsrwx---+ 1 root oinstall 68432 May 31 2012 oradism

Thanks,
Abhishek

Abhishek ray, April 26, 2013 - 10:43 am UTC

Hi,

Thanks for the help.

That was indeed a issue of oracle not finding the Shell Script.

I found that this current setup of oracle databse is on a multi node system(3 nodes) and though I connect to the same database ,it depends on the host name used to connect to oracle.si if I have connected to Oracle through node 1 then the shell script has to be present in node 1 server not in the other nodes(which was my mistake ..:().

Thanks,
Abhishek

Dmitry, August 16, 2013 - 10:12 am UTC

Hi,

I need to copy/move file from one directory on the Oracle server to the FTP server. I have a script for this, by I have a problem with it. I have used the code you gave to execute shell script (server has a UNIX OS), but this script can't find any file in the source directory. The directory and file have access rights 0777 and the Java permission granting command was executed, but still I can't even rename the file.
What other Java permissions should be granted or this is not Java problem?

Thanks in advance.
Tom Kyte
August 28, 2013 - 4:58 pm UTC

put debug in your script - remember the environment is the environment of the server process that executes the script *not* the environment you see at your command prompt.

verify directories (cd to where you want to be), users (id command), explicit paths (never rely on path, always qualify all commands) and so on

Run a file from procedure

marc weinstock, September 19, 2013 - 6:14 pm UTC

I simplified my example, but I want a procedure to run either run2.sql or run1.sql scripts. Simple question is there any issue to execute a script by "@@&runFile" where I am using a &var in the @@ line. It works, but I looked all over the internet for someone using @@& and no examples or issues.
set echo on
set define on
col runFile for a30 new_value runFile

--this will be stored function
select decode(1,1,'run2.sql','run1.sql') as runFile from dual;

def runFile

--the following line is the question in hand:
@@&runFile

Using Java Stored Proc to execute a shell script

Bob, March 31, 2015 - 11:04 am UTC

Hi Tom,

Thanks for that wonderful example of using stored Java source to execute an O/S shell script. I was able to get your example working - which is great.

But what I am trying to do is call a wrapper script that sources in environment variables from a library (e.g. common.ksh) and then execute another shell script in the same directory as the wrapper.

Here's an example of my wrapper.ksh script

#!/bin/ksh

. /home/oracle/bb/common.ksh

function main
{
typeset -u DBNAME
typeset -l SERVERNAME
typeset -u DB_TYPE

DBNAME=$1
SERVERNAME=$2
DB_TYPE=$3
TIMESTAMP=`date +'%d-%m-%y.%H-%M-%S'`

echo -e "Calling Drop Database ($DBNAME) ($SERVERNAME)($DB_TYPE) BEGIN at $TIMESTAMP\n" >> $LOG_FILE

. /home/oracle/bb/bb_remove_database.sh -d "$DBNAME" -n "$SERVERNAME" -t "$DB_TYPE"

TIMESTAMP=`date +'%d-%m-%y.%H-%M-%S'`

echo -e "Calling Drop Database ($DBNAME) ($SERVERNAME)($DB_TYPE) END at $TIMESTAMP\n" >> $LOG_FILE

}

main $1 $2 $3

cat common.ksh
export BB_LOG_DIR=/home/oracle/bb
export SYSDATE="`date +'%m%d%Y'`"
export LOG_FILE="$BB_LOG_DIR/wrapperlog.$SYSDATE"

Any ideas if this is possible? bb_remove_database resides in the same directory as wrapper.ksh

A reader, July 02, 2019 - 2:34 pm UTC

you can use host command.
example:
BEGIN
host ('backup_using_rman.ksh');
end;

Connor McDonald
July 03, 2019 - 1:29 am UTC

The scheduler can be used to run external scripts as well

https://connor-mcdonald.com/2016/10/17/running-external-programs-from-the-scheduler/

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library