DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Failed using DBMS_PIPE (calls to shell scripts)

  1. #1
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    3

    Unhappy Failed using DBMS_PIPE (calls to shell scripts)

    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

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828

  3. #3
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    3
    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

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    where is your program that is reading the stuff from the pipe?

  5. #5
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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...
    Last edited by TimHall; 06-30-2005 at 02:48 AM.
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  6. #6
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    3
    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;
    /

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width