Solved

how can I run command line or shell script in Oracle

Posted on 2007-12-04
5
3,935 Views
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 )
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 run the procedure in PL/SQL as
begin
  host('dir/b c:\dataloader\upload\*.dat > c:\dataloader\upload\datNames.txt');
end;
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?
0
Comment
Question by:jujin
  • 3
5 Comments
 
LVL 27

Expert Comment

by:sujith80
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.
0
 

Author Comment

by:jujin
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?
0
 
LVL 27

Expert Comment

by:sujith80
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:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_pipe.htm#CHDDFCFC
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 125 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.
0
 
LVL 27

Expert Comment

by:sujith80
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

680 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