Failed using DBMS_PIPE (calls to shell scripts)

[upadhi]

Hi there!

I have failed to execute UNIX commands from ORACLE using DBMS_PIPE.
When I am attempting to run the following code (nothing too cleaver):

p_command := ‘cp /rims/live/bespoke/a.lis /rims/live/bespoke/a.lis.copied’;
--
dbms_pipe.pack_message (dbms_pipe.unique_session_name); -- return pipe
--
dbms_pipe.pack_message (p_command);
--
l_status := dbms_pipe.send_message ('host_command');
--
IF l_status <> 0 THEN
erm := 'Error : ' || TO_CHAR (l_status) || ' sending on pipe';
RAISE err;
END IF;
--

nothing happened, i.e. UNIX copy command does not generated the new file ‘a.lis.copied’.
I have been granted execute access to DBMS_PIPE.
I am on Oracle8i 8.1.7.4.0.

What am I doing wrong?

To step further, I am bit puzzled how oracle knows am I requesting the execution of a system command, PL/SQL procedure or a SQL query (syntax wise)?

How can we see in which session would show up the result of our piped PL/SQL or SQL commands?

Could I send DBMS_ALERT’s directly to UNIX or have to call implicitly DBMS_PIPE?

Regards

[hrishy]

Hi

Have a look at

http://www.fortunecity.com/skyscrape...iew/pipes.html


regards
Hrishy

[upadhi]

Hi Hrishy,

Followed it to the letter, copied and passed it, without success. Being silly may be, but this could more serious DBA issue?

Regards,
Viktor

[davey23uk]

where is your program that is reading the stuff from the pipe?

[TimHall]

As soon as someone says have somethig outside the DB server you loose me

This is what I do: http://www.oracle-base.com/articles/...sFromPLSQL.php

Simple Java stored procedure. Job done!

If you are using 10g you can just create a one-off job using DBMS_SCHEDULER to run an OS executable, script or command.

Job done another way!

Cheers

Tim...

[upadhi]

This time copied and passed everything from this web site
http://www.uaex.edu/srea/daemon.sql the daemon package.
Run it on the server again without any success.

Error message:

DECLARE
*
ERROR at line 1:
ORA-20011: Execute_system: Error while receiving.
Status = 1
ORA-06512: at "RIMS.DAEMON", line 26
ORA-06512: at line 30

I tested with this script the daemon.execute_system function :

SET SERVEROUTPUT ON SIZE 100000
SET PAGES 5000
SET LINESIZE 160
DECLARE
l_line VARCHAR2(2048);
l_status INTEGER;
BEGIN
deb.enable_debug(1000000);
deb.trace(0,'unix_command', 'BEFORE');
l_line := 'cp /rims/live/bespoke/viktor/bloomberg/a.lis /rims/live/bespoke/viktor/bloomberg/a.lis.de';

l_status := daemon.execute_system (l_line);

deb.trace(0,'unix_command', 'AFTER');

END;
/