Solved

how can I run command line or shell script in Oracle

Posted on 2007-12-04
5
3,938 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

726 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