1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Hi, i need details on dbms_pipe

Discussion in 'SQL PL/SQL' started by Shanmugapriya, May 20, 2016.

  1. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    Hi, I have a requirement to log errors in my package which has 25 procedures in it. I need to log errors using DBMS_PIPE. Can you please help with an example. Also is it possible to send the log as a file using DBMS_PIPE.
    Kindly provide examples of
    1. using DBMS_PIPE to log errors and insert into a table
    2. using DBMS_PIPE to log errors and send as a file

    Your answer would be great help
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    In documentation there are detailed examples.
    Look at the references given here.
    Try to create the examples.
    If there are mistakes, then write here and receive the help

    And most important: use Advanced Queuing better

    p.s. For saving errors into file it is necessary to use UTL_FILE

    Additional link :

    https://oracle-base.com/articles/misc/dbms_pipe
     
    Shanmugapriya likes this.
  3. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    Thank you very much. Would check and reply back
     
  4. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    I see that oracle doc has below code:

    Should I replace msg which is in the IN parameter of the procedure with the "Procedure name"?
    Why are they assigning status which is a number data type with a string message "PLSQL DEBUG"?
    There is no DBMS_PIPE.RECEIVE_MESSAGE and DBMS_PIPE.UNPACK_MESSAGE?

    Please explain

    Example 1: Debugging - PL/SQL
    This example shows the procedure that a PL/SQL program can call to place debugging information in a pipe.

    CREATE OR REPLACE PROCEDURE debug (msg VARCHAR2) AS
    status NUMBER;
    BEGIN
    DBMS_PIPE.PACK_MESSAGE(LENGTH(msg));
    DBMS_PIPE.PACK_MESSAGE(msg);
    status := DBMS_PIPE.SEND_MESSAGE('plsql_debug');
    IF status != 0 THEN
    raise_application_error(-20099, 'Debug error');
    END IF;
    END debug;
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Apparently you don't understand programming PL/SQL. No, you don't replace msg with 'Procedure name', msg is a variable used to hold text. This is an example of using DBMS_PIPE to SEND messages down a pipe; another procedure would be called to unpack the message at the other end of the pipe. The plsql_debug will return a numeric code on error which is why the next piece of the code checks for the status. It's best to read ALL of the documentation before trying to dissect sample code.
     
  6. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    Thanks for your reply. Its not that I don't know PL/SQL but my question was related to the requirement that I had which I have posted initially. I understand "msg" is the input variable name used to input VARCHAR2 datatype.
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    once again I will notice that option only test.
    in the working decision it is better to use AQ(Advanced Queuing) that there was no loss of data on errors.

    For exaple :

    1. Create a pipe and listen for messages on it
    Code (SQL):

    DECLARE
      STATUS  NUMBER;
      message  varchar2(255);
      pipe_name varchar2(30) := 'TEST_PIPE';
    BEGIN
      -- create a pipe
      STATUS := dbms_pipe.create_pipe(pipe_name);

      -- listen for incoming messages on the pipe
      STATUS := dbms_pipe.receive_message(pipename => pipe_name,
      timeout  => dbms_pipe.maxwait);

      -- message received successfully.
      IF STATUS = 0 THEN
      dbms_pipe.unpack_message(message);
      dbms_output.put_line('message received: ' || message);
      END IF;
    END;
    /
     
    2.Send a message into the pipe(other session)

    Code (SQL):

    DECLARE
      STATUS  NUMBER;
      i NUMBER;
      message  varchar2(255);
      pipe_name varchar2(30) := 'TEST_PIPE';
    BEGIN
      message := lpad('@',256,'*');
    exception
      WHEN value_error
      dbms_pipe.pack_message(errmsg);
      STATUS := dbms_pipe.send_message(pipe_name);
    END;
    /
     
     
    Last edited: May 23, 2016
  8. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    Hi Sergey,
    I used the above code and it did work as an anonymous block. However, I tried to implement the same in my package as below and get error
    "ORA-23322: Privilege error accessing pipe".
    I used the pack_message in my first procedure and then calling the "pipe_receive_proc" procedure. The "pipe_receive_proc" procedure gets called but it goes to exception part which gives the error "ORA-23322: Privilege error accessing pipe".

    I have grant permission on dbms_pipe and also using the same pipe name.

    Kindly help me on this please.

    Firstly in my first procedure I used

    pipe_name := 'TEST';

    dbms_pipe.pack_message('nmp15be_batch_process_pkg');
    dbms_pipe.pack_message('exch_node_subnode_term_proc');
    dbms_pipe.pack_message('log');
    dbms_pipe.pack_message(substr(sqlcode,1,15));
    dbms_pipe.pack_message(substr(sqlerrm,1,200));
    dbms_pipe.pack_message('');


    STATUS := dbms_pipe.send_message(pipe_name);

    pipe_receive_proc(pipe_name); -- calling receive procedure


    Secondly the pipe_receive_proc is as below:

    PROCEDURE pipe_receive_proc (name varchar2)
    AS

    STATUS NUMBER;
    message1 varchar2(50);
    message2 varchar2(50);
    message3 varchar2(10);
    message4 varchar2(15);
    message5 varchar2(300);
    message6 varchar2(300);


    BEGIN

    dbms_output.put_line('name inside pipe_receive_proc ' || name);

    dbms_output.put_line('status above pipe_receive_proc ' || STATUS );

    STATUS := dbms_pipe.create_pipe(name);

    dbms_output.put_line('status inside pipe_receive_proc ');

    -- listen for incoming messages on the pipe
    STATUS := dbms_pipe.receive_message(pipename => name, timeout => dbms_pipe.maxwait);

    -- message received successfully.
    IF STATUS = 0 THEN
    dbms_pipe.unpack_message(message1);
    dbms_output.put_line('message1 received: ' || message1);

    dbms_pipe.unpack_message(message2);
    dbms_output.put_line('message2 received: ' || message2);

    dbms_pipe.unpack_message(message3);
    dbms_output.put_line('message3 received: ' || message3);

    dbms_pipe.unpack_message(message4);
    dbms_output.put_line('message4 received: ' || message4);

    dbms_pipe.unpack_message(message5);
    dbms_output.put_line('message5 received: ' || message5);

    dbms_pipe.unpack_message(message6);
    dbms_output.put_line('message6 received: ' || message6);

    insert into nmp15be_log_test
    values (sysdate, message1, message2, message3, message4, message5, message6);

    END IF;

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,200));


    END pipe_receive_proc;
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your privileges are probably through a role so you'll need to modify your procedure to include the following directive:

    authid current_user

    This compiles the procedeure with INVOKER RIGHTS, and traverses the privilege tree through all roles the user running the procedure has been granted. As an example:

    Code (SQL):
    CREATE OR REPLACE package mytest_pkg
    authid CURRENT_USER AS
    PROCEDURE IS_METHOD_DEFINED(pMethodType IN VARCHAR2, /* FUNCTION, PACKAGE,PROCEDURE*/
        pMethodName IN VARCHAR2, /* Name of function,package or procedure*/
        pSubMethodName IN VARCHAR2, /* if package, name of the procedure*/
        pMethodDefined OUT VARCHAR2); /* Y/N */
    END;
    /

    SHOW errors
     
    This s a package example but the declaration would be in the same place for a procedure.
     
  10. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    Thank you very much for your help :)