Oracle PL/SQL/System Packages/DBMS PIPE
Содержание
- 1 An anonymous block program to create a pipe.
- 2 An anonymous block program to delete a pipe
- 3 An Oracle9i Pipelined Table Function
- 4 DBMS_PIPE.PACK_MESSAGE
- 5 dbms_pipe.remove_pipe
- 6 DBMS_PIPE.UNPACK_MESSAGE
- 7 Define an anonymous block to populate the local private pipe.
- 8 Run a DBMS_PIPE.RECEIVE_MESSAGE call to empty the local buffer
- 9 This script deletes a pipe if it exists in the context of the current session, then recreates it.
- 10 This script unpacks the local buffer.
- 11 Use DBMS_PIPE package to receive a message.
- 12 Use DBMS_PIPE package to send a message.
- 13 Use DBMS_PIPE.PACK_MESSAGE in a trigger
An anonymous block program to create a pipe.
SQL> DECLARE
2
3
4 returnValue INTEGER;
5
6 BEGIN
7
8
9 returnValue := DBMS_PIPE.CREATE_PIPE("PLSQL$THREAD_ABORT", 20000 );
10
11
12 returnValue := DBMS_PIPE.CREATE_PIPE("PLSQL$THREAD_ABORT_LOCK", 20000 );
13
14
15 returnValue := DBMS_PIPE.CREATE_PIPE("PLSQL$THREAD_COMMIT", 20000 );
16
17
18 returnValue := DBMS_PIPE.CREATE_PIPE("PLSQL$THREAD_COMMIT_LOCK", 20000 );
19
20
21 returnValue := DBMS_PIPE.CREATE_PIPE("PLSQL$THREAD_COMPLETE", 20000 );
22
23
24 returnValue := DBMS_PIPE.CREATE_PIPE("PLSQL$THREAD_COMPLETE_LOCK", 20000 );
25
26
27 returnValue := DBMS_PIPE.CREATE_PIPE("PLSQL$THREAD_REGISTER", 20000 );
28
29
30 returnValue := DBMS_PIPE.CREATE_PIPE("PLSQL$THREAD_REGISTER_LOCK", 20000 );
31
32
33 IF (returnValue = 0) THEN
34 DBMS_OUTPUT.PUT_LINE("THREAD_PIPES are created.");
35 END IF;
36
37 EXCEPTION
38
39
40 WHEN others THEN
41 DBMS_OUTPUT.PUT_LINE(SQLERRM);
42 RETURN;
43
44 END;
45 /
THREAD_PIPES are created.
PL/SQL procedure successfully completed.
SQL>
SQL>
An anonymous block program to delete a pipe
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL>
SQL> DECLARE
2
3
4 returnValue INTEGER;
5
6
7 FUNCTION get_user
8 RETURN VARCHAR2 IS
9
10 BEGIN
11
12
13 FOR i IN (SELECT user FROM dual) LOOP
14 return i.user;
15 END LOOP;
16
17 END get_user;
18
19 BEGIN
20
21
22 returnValue := DBMS_PIPE.REMOVE_PIPE(get_user||"$"||"MESSAGE_INBOX");
23 returnValue := DBMS_PIPE.REMOVE_PIPE(get_user||"$"||"MESSAGE_OUTBOX");
24
25 END;
26 /
PL/SQL procedure successfully completed.
An Oracle9i Pipelined Table Function
SQL>
SQL> create or replace type dateTableType as table of date;
2 /
Type created.
SQL>
SQL> create or replace function month_generator_piped(p_num_months in number)
2 RETURN dateTableType
3 PIPELINED
4 AS
5 month_table dateTableType := dateTableType();
6 BEGIN
7 for i in 1..p_num_months loop
8 PIPE ROW ( add_months(sysdate, -i) );
9 end loop;
10 return;
11
12 END;
13 /
Function created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> select * from TABLE( CAST (month_generator_piped(12) AS dateTableType) );
COLUMN_VA
---------
26-SEP-09
26-AUG-09
26-JUL-09
26-JUN-09
26-MAY-09
26-APR-09
26-MAR-09
26-FEB-09
26-JAN-09
26-DEC-08
26-NOV-08
26-OCT-08
12 rows selected.
SQL>
SQL> select to_Char(x.column_value, "mm/yyyy")
2 from TABLE( month_generator_piped(12) ) x
3 group by to_Char(x.column_value, "mm/yyyy") ;
TO_CHAR
-------
01/2009
02/2009
03/2009
04/2009
05/2009
06/2009
07/2009
08/2009
09/2009
10/2008
11/2008
12/2008
12 rows selected.
SQL>
SQL>
SQL>
DBMS_PIPE.PACK_MESSAGE
SQL>
SQL> DECLARE
2 v_statpipe1 integer;
3 v_statpipe2 integer;
4 v_pubchar VARCHAR2(100) := "This is a text string";
5 v_pubdate DATE := SYSDATE;
6 v_pubnum NUMBER := 109;
7 BEGIN
8 v_statpipe1 := DBMS_PIPE.CREATE_PIPE("myprivatepipe");
9 IF (v_statpipe1 = 0) THEN
10 DBMS_PIPE.PACK_MESSAGE("privateline1");
11 DBMS_PIPE.PACK_MESSAGE("privateline2");
12 v_statpipe1 := DBMS_PIPE.SEND_MESSAGE("myprivatepipe");
13 END IF;
14
15 DBMS_PIPE.PACK_MESSAGE(v_pubchar);
16 DBMS_PIPE.PACK_MESSAGE(v_pubdate);
17 DBMS_PIPE.PACK_MESSAGE(v_pubnum);
18 v_statpipe2 := DBMS_PIPE.SEND_MESSAGE("mypublicpipe");
19 DBMS_OUTPUT.PUT_LINE("The Status of your Private Pipe is: " || v_statpipe1 );
20 DBMS_OUTPUT.PUT_LINE("The Status of your Public Pipe is: " || v_statpipe2 );
21 END;
22 /
The Status of your Private Pipe is: 0
The Status of your Public Pipe is: 0
PL/SQL procedure successfully completed.
SQL>
SQL> --
dbms_pipe.remove_pipe
SQL>
SQL> DECLARE
2 v_stat NUMBER ;
3 BEGIN
4 v_stat := dbms_pipe.remove_pipe("myprivatepipe");
5 DBMS_OUTPUT.PUT_LINE("The status for removing the private pipe is: " || v_stat);
6 v_stat := dbms_pipe.remove_pipe("mypublicpipe");
7 DBMS_OUTPUT.PUT_LINE("The status for removing the public pipe is: "|| v_stat);
8 END;
9 /
The status for removing the private pipe is: 0
The status for removing the public pipe is: 0
PL/SQL procedure successfully completed.
SQL>
SQL> --
DBMS_PIPE.UNPACK_MESSAGE
SQL>
SQL> DECLARE
2 v_statpipe1 integer;
3 v_statpipe2 integer;
4 v_holdtype INTEGER;
5 v_holdchar VARCHAR2(100);
6 v_holddate DATE;
7 v_holdnum NUMBER;
8 BEGIN
9 v_statpipe1 := DBMS_PIPE.RECEIVE_MESSAGE("myprivatepipe",15);
10 DBMS_PIPE.UNPACK_MESSAGE(v_holdchar);
11 DBMS_OUTPUT.PUT_LINE(v_holdchar);
12 DBMS_PIPE.UNPACK_MESSAGE(v_holdchar);
13 DBMS_OUTPUT.PUT_LINE(v_holdchar);
14
15 v_statpipe2 := DBMS_PIPE.RECEIVE_MESSAGE("mypublicpipe",10);
16 LOOP
17 v_holdtype := DBMS_PIPE.NEXT_ITEM_TYPE;
18 IF v_holdtype = 0 THEN EXIT;
19 ELSIF v_holdtype = 6 THEN
20 DBMS_PIPE.UNPACK_MESSAGE(v_holdnum);
21 ELSIF v_holdtype = 9 THEN
22 DBMS_PIPE.UNPACK_MESSAGE(v_holdchar);
23 ELSIF v_holdtype = 12 THEN
24 DBMS_PIPE.UNPACK_MESSAGE(v_holddate);
25 END IF;
26 END LOOP;
27 DBMS_OUTPUT.PUT_LINE(v_holdchar || " " || v_holddate || " "
28 || v_holdnum);
29 END;
30 /
privateline1
privateline2
This is a text string 16-JUN-2008 18:14:00 109
PL/SQL procedure successfully completed.
SQL>
SQL> --
Define an anonymous block to populate the local private pipe.
SQL> DECLARE
2
3
4 message VARCHAR2(30 CHAR);
5 success INTEGER;
6
7 BEGIN
8
9
10 message := DBMS_PIPE.UNIQUE_SESSION_NAME;
11
12
13 DBMS_PIPE.RESET_BUFFER;
14
15
16 DBMS_PIPE.PACK_MESSAGE(message);
17
18
19 DBMS_OUTPUT.PUT_LINE("Written to pipe ["||message||"]");
20
21 END;
22 /
Written to pipe [ORA$PIPE$0023000C0001]
PL/SQL procedure successfully completed.
SQL>
Run a DBMS_PIPE.RECEIVE_MESSAGE call to empty the local buffer
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL>
SQL>
SQL> SELECT DBMS_PIPE.RECEIVE_MESSAGE("Nowhere",0)
2 FROM dual;
DBMS_PIPE.RECEIVE_MESSAGE("NOW
------------------------------
1
1 row selected.
SQL>
SQL>
SQL>
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>
This script unpacks the local buffer.
SQL>
SQL> set serveroutput on size 1000000
SQL> DECLARE
2
3 message VARCHAR2(30 CHAR) := NULL;
4 success INTEGER;
5
6 BEGIN
7
8 success := DBMS_PIPE.RECEIVE_MESSAGE("ORA$PIPE$00F3B7B50001",1);
9
10 IF (success = 0) THEN
11 DBMS_PIPE.UNPACK_MESSAGE(message);
12 ELSE
13 DBMS_OUTPUT.PUT_LINE("Error");
14 message := TO_CHAR(success);
15 END IF;
16
17 DBMS_OUTPUT.PUT_LINE("Message ["||message||"]");
18
19 END;
20 /
Error
Message [1]
PL/SQL procedure successfully completed.
SQL>
Use DBMS_PIPE package to receive a message.
set serveroutput on
DECLARE
v_PipeName VARCHAR2(30) := "MyPipe";
v_Status INTEGER;
v_DateVal DATE;
v_NumberVal NUMBER;
v_StringVal VARCHAR2(100);
BEGIN
v_Status := DBMS_PIPE.RECEIVE_MESSAGE(v_PipeName);
IF v_Status != 0 THEN
DBMS_OUTPUT.PUT_LINE("Error " || v_Status ||
" while receiving message");
END IF;
DBMS_PIPE.UNPACK_MESSAGE(v_DateVal);
DBMS_PIPE.UNPACK_MESSAGE(v_NumberVal);
DBMS_PIPE.UNPACK_MESSAGE(v_StringVal);
DBMS_OUTPUT.PUT_LINE("Unpacked " || v_DateVal);
DBMS_OUTPUT.PUT_LINE("Unpacked " || v_NumberVal);
DBMS_OUTPUT.PUT_LINE("Unpacked " || v_StringVal);
END;
/
--
Use DBMS_PIPE package to send a message.
DECLARE
v_PipeName VARCHAR2(30) := "MyPipe";
v_Status INTEGER;
BEGIN
DBMS_PIPE.PACK_MESSAGE(SYSDATE);
DBMS_PIPE.PACK_MESSAGE(123456);
DBMS_PIPE.PACK_MESSAGE("This is a message sent from the pipe!");
v_Status := DBMS_PIPE.SEND_MESSAGE(v_PipeName);
IF v_Status != 0 THEN
DBMS_OUTPUT.PUT_LINE("Error " || v_Status ||
" while sending message");
END IF;
END;
/
--
Use DBMS_PIPE.PACK_MESSAGE in a trigger
CREATE TABLE myStudent (
student_id NUMBER(5) NOT NULL,
department CHAR(3) NOT NULL,
course NUMBER(3) NOT NULL,
grade CHAR(1)
);
CREATE OR REPLACE TRIGGER LogRSInserts
BEFORE INSERT ON myStudent
FOR EACH ROW
DECLARE
v_Status INTEGER;
BEGIN
DBMS_PIPE.PACK_MESSAGE("I");
DBMS_PIPE.PACK_MESSAGE(user);
DBMS_PIPE.PACK_MESSAGE(sysdate);
DBMS_PIPE.PACK_MESSAGE(:new.student_ID);
DBMS_PIPE.PACK_MESSAGE(:new.department);
DBMS_PIPE.PACK_MESSAGE(:new.course);
DBMS_PIPE.PACK_MESSAGE(:new.grade);
v_Status := DBMS_PIPE.SEND_MESSAGE("RSInserts");
IF v_Status != 0 THEN
RAISE_APPLICATION_ERROR(-20010, "LogRSInserts trigger " || "couldn""t send the message, status = " || v_Status);
END IF;
END LogRSInserts;
/
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10000, "CS", 102, "A");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10002, "CS", 102, "B");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10003, "CS", 102, "C");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10000, "HIS", 101, "A");
INSERT INTO myStudent (student_id, department, course, grade)
VALUES (10001, "HIS", 101, "B");
drop table myStudent;
--