how can I run command line or shell script in Oracle

Posted on 2007-12-04
Medium Priority
Last Modified: 2008-02-01
Please help me to create one text file to save all file names in directory
To put all filenames.dat into one file called datNames.txt I wrote a procedure as
create or replace
procedure host(cmd in varchar2 )
status number;
dbms_pipe.pack_message( cmd );
status := dbms_pipe.send_message( 'HOST_PIPE' );
if ( status <> 0 ) then raise_application_error( -20001, 'Pipe error' );
end if;
I run the procedure in PL/SQL as
  host('dir/b c:\dataloader\upload\*.dat > c:\dataloader\upload\datNames.txt');
There is not error but it can not create c:\dataloader\upload\datNames.txt' eventhough I have granted write permission on the directory. But when I run it in MS DOs, It run OK.
Another question, I use 'dir/b c:\dataloader\upload\*.dat > c:\dataloader\upload\datNames.txt'  to run on window but if I want run  the procedure on Linux so Which shell script will be used to replace the MS DOS command line?
Question by:jujin
  • 3
LVL 27

Expert Comment

ID: 20401593
>> There is not error but it can not create
Do you have a supporting pro*C code that reads the pipe and execute the command?

>> Which shell script will be used to replace the MS DOS command line?
You should pass a different command to the procedure in Linux.

Author Comment

ID: 20401828
hi sujith80
Not yet, I don't know how to support pro*C code to reads the pipe and execute the command. How can I do that?
LVL 27

Expert Comment

ID: 20401961
You should have a host language program(Pro*C or Java or ...) that reads the pipe and executes your command. DBMS_PIPE does "NOT" execute the commands.

See this link for a detailed example:
LVL 36

Accepted Solution

Mark Geerlings earned 250 total points
ID: 20402562
To put it simply: in SQL*Plus you *CAN* execute "host" commands directly, but in PL\SQL you *CANNOT* do that!  PL\SQL does not directly support "host" commands.  What you can do in PL\SQL though is call Java procedures, and Java can do "host" commands.  So, what you need to do is find (or write) a Java procedure to do the operating system tasks you want, then from PL\SQL you call the Java procedure.
LVL 27

Expert Comment

ID: 20417044
Using dbms pipes also perfectly works to execute operating system commands. But DBMS_PIPE does NOT EXECUTE your commands. You have to write another program to read the pipe and execute the commands. This second program can be written in ANY host language that can read the pipe using DBMS_PIPE.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question