This script deletes a pipe if it exists in the context of the current session, then recreates it. : DBMS_PIPE « System Packages « Oracle PL / SQL






  1. Oracle PL / SQL
  2. System Packages
  3. DBMS_PIPE

This script deletes a pipe if it exists in the context of the current session, then recreates it.

    
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL> 
SQL> DECLARE
  2
  3  
  4    returnValue INTEGER := DBMS_PIPE.REMOVE_PIPE('PLSQL$MESSAGE_INBOX');
  5
  6  BEGIN
  7
  8   NULL;
  9
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL> 
SQL> DECLARE
  2
  3  
  4    message_pipe VARCHAR2(30) := 'PLSQL$MESSAGE_INBOX';
  5    message_size INTEGER      := 20000;
  6    message_flag BOOLEAN      := FALSE;
  7
  8  
  9    returnValue INTEGER;
 10
 11  BEGIN
 12
 13  
 14    returnValue := DBMS_PIPE.CREATE_PIPE(message_pipe,message_size,message_flag);
 15
 16  
 17    IF (returnValue = 0) THEN
 18      DBMS_OUTPUT.PUT_LINE('MESSAGE_INBOX pipe is created.');
 19    END IF;
 20
 21  EXCEPTION
 22
 23  
 24    WHEN others THEN
 25      DBMS_OUTPUT.PUT_LINE(SQLERRM);
 26      RETURN;
 27
 28  END;
 29  /
ORA-23322: Privilege error accessing pipe

PL/SQL procedure successfully completed.

SQL>
SQL>








Related examples in the same category

1.DBMS_PIPE.PACK_MESSAGE
2.DBMS_PIPE.UNPACK_MESSAGE
3.dbms_pipe.remove_pipe
4.Use DBMS_PIPE package to receive a message.
5.Use DBMS_PIPE package to send a message.
6.Use DBMS_PIPE.PACK_MESSAGE in a trigger
7.Define an anonymous block to populate the local private pipe.
8.An anonymous block program to create a pipe.
9.An anonymous block program to delete a pipe
10.An Oracle9i Pipelined Table Function
11.Run a DBMS_PIPE.RECEIVE_MESSAGE call to empty the local buffer
12.This script unpacks the local buffer.