1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
|
sqlplus scott/tiger@rac1
CREATE OR REPLACE PACKAGE message_api AS
PROCEDURE send (p_number IN NUMBER,
p_text IN VARCHAR2,
p_date IN DATE DEFAULT SYSDATE);
PROCEDURE receive;
END message_api;
/
CREATE OR REPLACE PACKAGE BODY message_api AS
PROCEDURE send (p_number IN NUMBER,
p_text IN VARCHAR2,
p_date IN DATE DEFAULT SYSDATE) AS
l_status NUMBER;
BEGIN
DBMS_PIPE.pack_message(p_number);
DBMS_PIPE.pack_message(p_text);
DBMS_PIPE.pack_message(p_date);
l_status := DBMS_PIPE.send_message('message_pipe');
IF l_status != 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'message_pipe error');
END IF;
END;
PROCEDURE receive AS
l_result INTEGER;
l_number NUMBER;
l_text VARCHAR2(32767);
l_date DATE;
BEGIN
l_result := DBMS_PIPE.receive_message (
pipename => 'message_pipe',
timeout => DBMS_PIPE.maxwait);
IF l_result = 0 THEN
-- Message received successfully.
DBMS_PIPE.unpack_message(l_number);
DBMS_PIPE.unpack_message(l_text);
DBMS_PIPE.unpack_message(l_date);
DBMS_OUTPUT.put_line('l_number: ' || l_number);
DBMS_OUTPUT.put_line('l_text : ' || l_text);
DBMS_OUTPUT.put_line('l_date : ' || l_date);
ELSE
RAISE_APPLICATION_ERROR(-20002, 'message_api.receive was unsuccessful. Return result: ' || l_result);
END IF;
END receive;
END message_api;
/
----------------------------------------------------------------------------------------------------------------------------------------
-- MONO-INSTANCE
----------------------------------------------------------------------------------------------------------------------------------------
-- To test the package, run the following code in one session. The session will appear to hang, waiting for a message to be read off the pipe.
conn scott/tiger@rac1
SET SERVEROUTPUT ON
EXEC message_api.receive;
-- In another session, run the following code to send a message.
CONN scott/tiger@rac1
BEGIN
message_api.send(p_number => 12345,
p_text => 'This is a test.',
p_date => SYSDATE);
END;
/
-- The procedure call in the first session immediately returns, printing out the contents of the message, as seen below.
l_number: 12345
l_text : This is a test.
l_date : 20-NOV-2005 13:35:57
----------------------------------------------------------------------------------------------------------------------------------------
-- RAC
----------------------------------------------------------------------------------------------------------------------------------------
-- To test the package, run the following code in one session. The session will appear to hang, waiting for a message to be read off the pipe.
conn scott/tiger@rac1
SET SERVEROUTPUT ON
EXEC message_api.receive;
-- In another session connected on an other instance, run the following code to send a message.
CONN scott/tiger@rac2
BEGIN
message_api.send(p_number => 12345,
p_text => 'This is a test.',
p_date => SYSDATE);
END;
/
-- => The procedure call in the first session is still wainting for a message |
