в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:05:50Z

1 версия

Новая страница

== 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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/System Packages/DBMS PIPE - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия