Solved

how can I run command line or shell script in Oracle

Posted on 2007-12-04
5
3,927 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
Comment Utility
>> 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
Comment Utility
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
Comment Utility
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 34

Accepted Solution

by:
Mark Geerlings earned 125 total points
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now