Oracle PL/SQL Tutorial/System Packages/DBMS PIPE
Содержание
- 1 DBMS_PIPE.CREATE_PIPE
- 2 DBMS_PIPE.PACK_MESSAGE
- 3 DBMS_PIPE.RECEIVE_MESSAGE
- 4 DBMS_PIPE.RECEIVE_MESSAGE with timeout
- 5 DBMS_PIPE.REMOVE_PIPE
- 6 DBMS_PIPE.RESET_BUFFER
- 7 DBMS_PIPE.SEND_MESSAGE
- 8 DBMS_PIPE.SEND_MESSAGE with timeout
- 9 DBMS_PIPE.UNPACK_MESSAGE and record type
- 10 Use dbms_pipe package
- 11 Use DBMS_PIPE to pack message and send message
DBMS_PIPE.CREATE_PIPE
<source lang="sql">
SQL> SQL> SQL> DECLARE
2 lv_status_num PLS_INTEGER; 3 BEGIN 4 lv_status_num := DBMS_PIPE.CREATE_PIPE("TEST 1"); 5 DBMS_OUTPUT.PUT_LINE("Status: " || lv_status_num); 6 END; 7 /
PL/SQL procedure successfully completed. SQL> SQL> SQL></source>
DBMS_PIPE.PACK_MESSAGE
<source lang="sql">
SQL> SQL> SQL> DECLARE
2 v_status NUMBER; 3 BEGIN 4 DBMS_PIPE.PACK_MESSAGE("Insert"); 5 END; 6 /
PL/SQL procedure successfully completed. SQL> SQL></source>
DBMS_PIPE.RECEIVE_MESSAGE
<source lang="sql">
SQL> SQL> SQL> DECLARE
2 lv_status_num PLS_INTEGER; 3 BEGIN 4 lv_status_num := DBMS_PIPE.RECEIVE_MESSAGE("TEST 1"); 5 DBMS_OUTPUT.PUT_LINE("Status: " || lv_status_num); 6 END; 7 /
PL/SQL procedure successfully completed.</source>
DBMS_PIPE.RECEIVE_MESSAGE with timeout
<source lang="sql">
SQL> SQL> SQL> CREATE OR REPLACE FUNCTION receive_message (pipe IN VARCHAR2)
2 RETURN VARCHAR2 3 IS 4 stat PLS_INTEGER; 5 retval VARCHAR2(4096); 6 BEGIN 7 stat := DBMS_PIPE.RECEIVE_MESSAGE (pipe, timeout => 10); 8 IF stat = 0 9 THEN 10 DBMS_PIPE.UNPACK_MESSAGE (retval); 11 END IF; 12 RETURN retval; 13 END; 14 /
Warning: Function created with compilation errors. SQL> SQL> show error Errors for FUNCTION RECEIVE_MESSAGE: LINE/COL ERROR
-----------------------------------------------------------------
7/4 PL/SQL: Statement ignored 7/12 PLS-00201: identifier "DBMS_PIPE" must be declared 10/7 PL/SQL: Statement ignored 10/7 PLS-00201: identifier "DBMS_PIPE" must be declared SQL> SQL></source>
DBMS_PIPE.REMOVE_PIPE
<source lang="sql">
SQL> SQL> SQL> DECLARE
2 lv_status_num PLS_INTEGER; 3 BEGIN 4 lv_status_num := DBMS_PIPE.REMOVE_PIPE("TEST 1"); 5 DBMS_OUTPUT.PUT_LINE("Status: " || lv_status_num); 6 END; 7 /
PL/SQL procedure successfully completed. SQL></source>
DBMS_PIPE.RESET_BUFFER
<source lang="sql">
SQL> SQL> CREATE OR REPLACE PROCEDURE forward(from_in IN VARCHAR2,to_in IN VARCHAR2,timeout_secs_IN IN INTEGER := 10) IS
2 stat INTEGER; 3 BEGIN 4 DBMS_PIPE.RESET_BUFFER; 5 DBMS_PIPE.PACK_MESSAGE("message"); 6 7 stat := DBMS_PIPE.RECEIVE_MESSAGE (from_in, timeout=>timeout_secs_IN); 8 IF stat = 0 9 THEN 10 stat := DBMS_PIPE.SEND_MESSAGE (to_in, timeout=>timeout_secs_IN); 11 END IF; 12 END forward; 13 /</source>
DBMS_PIPE.SEND_MESSAGE
<source lang="sql">
SQL> SQL> DECLARE
2 lv_status_num PLS_INTEGER; 3 BEGIN 4 lv_status_num := DBMS_PIPE.SEND_MESSAGE("TEST 1"); 5 DBMS_OUTPUT.PUT_LINE("Status: " || lv_status_num); 6 END; 7 /
PL/SQL procedure successfully completed. SQL> SQL></source>
DBMS_PIPE.SEND_MESSAGE with timeout
<source lang="sql">
SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE send_message (pipe IN VARCHAR2, dtval IN DATE)
2 IS 3 stat PLS_INTEGER; 4 BEGIN 5 DBMS_PIPE.PACK_MESSAGE (dtval); 6 stat := DBMS_PIPE.SEND_MESSAGE (pipe, timeout => 60); 7 IF stat != 0 8 THEN 9 DBMS_OUTPUT.PUT_LINE ("Unable to send message to " || pipe); 10 END IF; 11 END; 12 /
Warning: Procedure created with compilation errors. SQL> SQL></source>
DBMS_PIPE.UNPACK_MESSAGE and record type
<source lang="sql">
SQL> SQL> CREATE OR REPLACE PACKAGE myPackage
2 IS 3 TYPE myRec IS RECORD ( 4 start_date DATE, 5 trial_cost NUMBER, 6 section_id VARCHAR2(100) 7 ); 8 FUNCTION critical_info RETURN myRec; 9 END; 10 /
Package created. SQL> CREATE OR REPLACE PACKAGE BODY myPackage
2 IS 3 thirty_days CONSTANT INTEGER := 30 * 24 * 60 * 60; 4 FUNCTION critical_info RETURN myRec 5 IS 6 stat PLS_INTEGER; 7 retval myRec; 8 BEGIN 9 stat := DBMS_PIPE.RECEIVE_MESSAGE ("impeachment", timeout => thirty_days); 10 IF stat = 0 11 THEN 12 DBMS_PIPE.UNPACK_MESSAGE (retval.start_date); 13 DBMS_PIPE.UNPACK_MESSAGE (retval.trial_cost); 14 DBMS_PIPE.UNPACK_MESSAGE (retval.section_id); 15 END IF; 16 RETURN retval; 17 END; 18 END; 19 /</source>
Use dbms_pipe package
<source lang="sql">
SQL> SQL> create or replace procedure proc( cmd in varchar2 )
2 as 3 status number; 4 resp long; 5 answer_pipe varchar2(255) default replace(dbms_pipe.unique_session_name,"$","_"); 6 begin 7 dbms_pipe.pack_message( answer_pipe ); 8 dbms_pipe.pack_message( cmd ); 9 10 status := dbms_pipe.send_message( "HOST_PIPE" ); 11 if ( status <> 0 ) then 12 raise_application_error( -20001, "Pipe error" ); 13 end if; 14 15 status := dbms_pipe.receive_message( answer_pipe ); 16 if ( status <> 0 ) then 17 raise_application_error( -20001, "Pipe error" ); 18 end if; 19 dbms_pipe.unpack_message( resp ); 20 dbms_output.put_line( substr( resp, 1, 80 ) ); 21 end; 22 /</source>
Use DBMS_PIPE to pack message and send message
<source lang="sql">
SQL> SQL> DECLARE
2 pipe_status INTEGER; 3 mypipe VARCHAR2(128) := DBMS_PIPE.UNIQUE_SESSION_NAME; 4 BEGIN 5 DBMS_OUTPUT.PUT_LINE ("Write to " || mypipe); 6 DBMS_PIPE.PACK_MESSAGE (SYSDATE); 7 pipe_status := DBMS_PIPE.SEND_MESSAGE (mypipe); 8 END; 9 /
Write to ORA$PIPE$002004930001 PL/SQL procedure successfully completed. SQL></source>