Discussion :

[farenheiit]

Bonjour,

Est-ce que quelqu'un pourrait me donner un exemple avec ADVANCE_QUEUING pour qu'une session puisse envoyer un message dans une queue qui serait lu par une autre session.
je me perds dans la doc oracle j'ai du mal à trouver un exemple simple.

Le testcase que je souhaiterais réaliser en fait est le suivant:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
102
103
 
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@rac2
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@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 is still wainting for a message

merci de votre aide

[pifor]

Peut-être la documentation 11.2 qui a un certain nombre de nouveautés dont Oracle Streams Extended Examples est plus facile à utiliser ?