-
Notifications
You must be signed in to change notification settings - Fork 3
Expand file tree
/
Copy path01_Sys_Setup.sql
More file actions
109 lines (97 loc) · 3.29 KB
/
Copy path01_Sys_Setup.sql
File metadata and controls
109 lines (97 loc) · 3.29 KB
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
104
105
106
107
108
109
/* -----------------------------------------------------------
* 01_Sys_Setup.sql : setup the environment for the test.
*
* Author: Francisco Saucedo (http://fcosfc.wordpress.com)
*
* Versioning:
*
* v1.0, 14-12-2014: Initial version.
*
* License: GNU GPL (http://www.gnu.org/licenses/gpl-3.0.html)
* ----------------------------------------------------------- */
-- Create a test user with the proper permissions
create user testaq
identified by test1234
default tablespace users
quota unlimited on users;
grant create session to testaq;
grant create table to testaq;
grant create procedure to testaq;
grant execute on dbms_aqadm to testaq;
grant execute on dbms_aq to testaq;
-- This permission is needed for the test case
grant execute on dbms_lock to testaq;
-- ########################################
-- Auxiliar objects in testaq schema
-- ########################################
-- Create the type for the messages payload.
create or replace type testaq.messages_t
as object (message varchar2(100 char));
/
-- Create a table to store the received messages.
create table testaq.received_messages (
received_message_id number primary key,
received_timestamp timestamp default systimestamp,
content varchar2(100 char));
-- Create a sequence for id generation.
create sequence testaq.received_messages_id_s;
-- Create a package with procedures to enqueue and dequeue messages.
create package testaq.test_p
as
procedure receive_message_callback (
context raw,
reginfo sys.aq$_reg_info,
descr sys.aq$_descriptor,
payload raw,
payloadl number);
procedure send_message (
queue in varchar,
message_content in clob);
end;
/
-- Create a package with procedures to enqueue and dequeue messages.
create package body testaq.test_p
as
procedure receive_message_callback (
context raw,
reginfo sys.aq$_reg_info,
descr sys.aq$_descriptor,
payload raw,
payloadl number)
is
r_dequeue_options dbms_aq.dequeue_options_t;
r_message_properties dbms_aq.message_properties_t;
v_message_handle raw(26);
o_payload messages_t;
begin
r_dequeue_options.msgid := descr.msg_id;
r_dequeue_options.consumer_name := descr.consumer_name;
dbms_aq.dequeue(queue_name => descr.queue_name,
dequeue_options => r_dequeue_options,
message_properties => r_message_properties,
payload => o_payload,
msgid => v_message_handle);
insert into received_messages
(received_message_id, content)
values (received_messages_id_s.nextval, o_payload.message);
commit;
exception
when others then
rollback;
end receive_message_callback;
procedure send_message (
queue in varchar,
message_content in clob)
is
enq_msgid raw(16);
eopt dbms_aq.enqueue_options_t;
mprop dbms_aq.message_properties_t;
begin
dbms_aq.enqueue(queue_name => queue,
enqueue_options => eopt,
message_properties => mprop,
payload => messages_t(message_content),
msgid => enq_msgid);
end send_message;
end;
/