1 package dbms_pipe is
2
3 -- DE-HEAD <- tell SED where to cut when generating fixed package
4
5 ------------
6 -- OVERVIEW
7 --
8 -- This package provides a DBMS "pipe" service which allows messages
9 -- to be sent between sessions.
10 --
11 -- The metaphor is similar to UNIX pipes: you can do
12 -- dbms_pipe.send_message(<pipename>)
13 -- dbms_pipe.receive_message(<pipename>)
14 -- which will cause a message to be sent or received. You do
15 -- dbms_pipe.pack_message(<varchar2>|<number>|<date>)
16 -- to pack an item into a static buffer (which will then be sent with
17 -- the "send_message" call), and
18 -- dbms_pipe.unpack_message(<varchar2>|<number>|<date>)
19 -- to get an item out of the static buffer (which is filled by the
20 -- "receive_message" call).
21 -- Pipes can be private to a user-id - which only allows session connected
22 -- under the same user-id or stored procedure owned by the user-id to read
23 -- write to the pipe. Pipes could be public - and all database users with
24 -- execute privilege on dbms_pipe and knowledge of the pipe can read or
25 -- write to the pipe.
26 --
27 -- Pipes operate independently of transactions. They also operate
28 -- asynchronously. There can be multiple readers and writers of the
29 -- same pipe.
30 --
31 -- Pipes only operate between sessions in the same instance.
32 --
33 -- Pipes can be explicitly created using
34 -- dbms_pipe.create_pipe(<pipename>)
35 -- and removed using
36 -- dbms_pipe.remove_pipe(<pipename>)
37 -- A pipe created using the explicit create command should be removed
38 -- using the remove function. A pipe can also be created implicitly.
39 -- Pipes automatically come into existence the first time they are
40 -- referenced. They effectively disappear when they contain no more
41 -- data (some overhead remains in the SGA until it gets aged out).
42 -- Pipes take up space in the SGA (see "maxpipesize" parameter to
43 -- "send_message").
44
45
46 --------
47 -- USES
48 --
49 -- The pipe functionality has several potential applications:
50 --
51 -- o External service interface. You can provide the ability to
52 -- communicate with (user-written) services that are external to the
53 -- RDBMS. This can be done in a (effectively) multi-threaded manner
54 -- so that several instances of the service can be executing
55 -- simultaneously. Additionally, the services are available
56 -- asynchronously - the requestor of the service need not block
57 -- awaiting a reply. The requestor can check (with or without
58 -- timeout) at a later time. The service can be written in any
59 -- of the 3GL languages that ORACLE supports, not just C. See
60 -- example below.
61 -- o Independent transactions. The pipe can be used to communicate
62 -- to a separate session which can perform an operation in an
63 -- independent transaction (such as logging an attempted security
64 -- violation detected by a trigger).
65 -- o Alerters (non-transactional). You can post another process
66 -- without requiring the waiting process to poll. If an "after-row"
67 -- or "after-statement" trigger were to alert an application, then
68 -- the application would treat this alert as an indication that
69 -- the data probably changed. The application would then go read
70 -- the data to get the current value. Since this is an "after"
71 -- trigger, the application would want to do a "select for update"
72 -- to make sure it read the correct data.
73 -- o Debugging. Triggers and/or stored procedures can send debugging
74 -- information to a pipe. Another session can keep reading out
75 -- of the pipe and displaying it on the screen or writing it
76 -- out to a file.
77 -- o Concentrator. Useful for multiplexing large numbers of users
78 -- over a fewer number of network connections, or improving
79 -- performance by concentrating several user-transactions into
80 -- one dbms-transaction.
81
82
83 ------------
84 -- SECURITY
85 --
86 -- Security can be achieved by use of 'grant execute' on the dbms_pipe
87 -- package, by creating a pipe using the 'private' parameter in the create
88 -- function and by writing cover packages that only expose particular
89 -- features or pipenames to particular users or roles.
90
91
92 ------------
93 -- EXAMPLES
94 --
95 -- External service interface
96 ------------------------------
97 --
98 -- Put the user-written 3GL code into an OCI or Precompiler program.
99 -- The program connects to the database and executes PL/SQL code to read
100 -- its request from the pipe, computes the result, and then executes
101 -- PL/SQL code to send the result on a pipe back to the requestor.
102 -- Below is an example of a stock service request.
103 --
104 -- The recommended sequence for the arguments to pass on the pipe
105 -- for all service requests is
106 --
107 -- protocol_version varchar2 - '1', 10 bytes or less
108 -- returnpipe varchar2 - 30 bytes or less
109 -- service varchar2 - 30 bytes or less
110 -- arg1 varchar2/number/date
111 -- ...
112 -- argn varchar2/number/date
113 --
114 -- The recommended format for returning the result is
115 --
116 -- success varchar2 - 'SUCCESS' if OK,
117 -- otherwise error message
118 -- arg1 varchar2/number/date
119 -- ...
120 -- argn varchar2/number/date
121 --
122 --
123 -- The "stock price request server" would do, using OCI or PRO* (in
124 -- pseudo-code):
125 --
126 -- <loop forever>
127 -- begin dbms_stock_server.get_request(:stocksymbol); end;
128 -- <figure out price based on stocksymbol (probably from some radio
129 -- signal), set error if can't find such a stock>
130 -- begin dbms_stock_server.return_price(:error, :price); end;
131 --
132 -- A client would do:
133 --
134 -- begin :price := stock_request('YOURCOMPANY'); end;
135 --
136 -- The stored procedure, dbms_stock_server, which is called by the
137 -- "stock price request server" above is:
138 --
139 -- create or replace package dbms_stock_server is
140 -- procedure get_request(symbol out varchar2);
141 -- procedure return_price(errormsg in varchar2, price in varchar2);
142 -- end;
143 --
144 -- create or replace package body dbms_stock_server is
145 -- returnpipe varchar2(30);
146 --
147 -- procedure returnerror(reason varchar2) is
148 -- s integer;
149 -- begin
150 -- dbms_pipe.pack_message(reason);
151 -- s := dbms_pipe.send_message(returnpipe);
152 -- if s <> 0 then
153 -- raise_application_error(-20000, 'Error:' || to_char(s) ||
154 -- ' sending on pipe');
155 -- end if;
156 -- end;
157 --
158 -- procedure get_request(symbol out varchar2) is
159 -- protocol_version varchar2(10);
160 -- s integer;
161 -- service varchar2(30);
162 -- begin
163 -- s := dbms_pipe.receive_message('stock_service');
164 -- if s <> 0 then
165 -- raise_application_error(-20000, 'Error:' || to_char(s) ||
166 -- 'reading pipe');
167 -- end if;
168 -- dbms_pipe.unpack_message(protocol_version);
169 -- if protocol_version <> '1' then
170 -- raise_application_error(-20000, 'Bad protocol: ' ||
171 -- protocol_version);
172 -- end if;
173 -- dbms_pipe.unpack_message(returnpipe);
174 -- dbms_pipe.unpack_message(service);
175 -- if service != 'getprice' then
176 -- returnerror('Service ' || service || ' not supported');
177 -- end if;
178 -- dbms_pipe.unpack_message(symbol);
179 -- end;
180 --
181 -- procedure return_price(errormsg in varchar2, price in varchar2) is
182 -- s integer;
183 -- begin
184 -- if errormsg is null then
185 -- dbms_pipe.pack_message('SUCCESS');
186 -- dbms_pipe.pack_message(price);
187 -- else
188 -- dbms_pipe.pack_message(errormsg);
189 -- end if;
190 -- s := dbms_pipe.send_message(returnpipe);
191 -- if s <> 0 then
192 -- raise_application_error(-20000, 'Error:'||to_char(s)||
193 -- ' sending on pipe');
194 -- end if;
195 -- end;
196 -- end;
197 --
198 --
199 -- The procedure called by the client is:
200 --
201 -- create or replace function stock_request (symbol varchar2)
202 -- return varchar2 is
203 -- s integer;
204 -- price varchar2(20);
205 -- errormsg varchar2(512);
206 -- begin
207 -- dbms_pipe.pack_message('1'); -- protocol version
208 -- dbms_pipe.pack_message(dbms_pipe.unique_session_name); -- return pipe
209 -- dbms_pipe.pack_message('getprice');
210 -- dbms_pipe.pack_message(symbol);
211 -- s := dbms_pipe.send_message('stock_service');
212 -- if s <> 0 then
213 -- raise_application_error(-20000, 'Error:'||to_char(s)||
214 -- ' sending on pipe');
215 -- end if;
216 -- s := dbms_pipe.receive_message(dbms_pipe.unique_session_name);
217 -- if s <> 0 then
218 -- raise_application_error(-20000, 'Error:'||to_char(s)||
219 -- ' receiving on pipe');
220 -- end if;
221 -- dbms_pipe.unpack_message(errormsg);
222 -- if errormsg <> 'SUCCESS' then
223 -- raise_application_error(-20000, errormsg);
224 -- end if;
225 -- dbms_pipe.unpack_message(price);
226 -- return price;
227 -- end;
228 --
229 -- You would typically only grant execute on 'dbms_stock_service' to
230 -- the stock service application server, and would only grant execute
231 -- on 'stock_request' to those users allowed to use the service.
232
233
234 ---------------------
235 -- SPECIAL CONSTANTS
236 --
237 maxwait constant integer := 86400000; /* 1000 days */
238 -- The maximum time to wait attempting to send or receive a message
239
240
241 ----------------------------
242 -- PROCEDURES AND FUNCTIONS
243 --
244 procedure pack_message(item in varchar2 character set any_cs);
245 pragma restrict_references(pack_message,WNDS,RNDS);
246 procedure pack_message(item in number);
247 pragma restrict_references(pack_message,WNDS,RNDS);
248 procedure pack_message(item in date);
249 pragma restrict_references(pack_message,WNDS,RNDS);
250 procedure pack_message_raw(item in raw);
251 pragma restrict_references(pack_message_raw,WNDS,RNDS);
252 procedure pack_message_rowid(item in rowid);
253 pragma restrict_references(pack_message_rowid,WNDS,RNDS);
254 -- Pack an item into the message buffer
255 -- Input parameters:
256 -- item
257 -- Item to pack into the local message buffer.
258 -- Exceptions:
259 -- ORA-06558 generated if message buffer overflows (currently 4096
260 -- bytes). Each item in the buffer takes one byte for the type,
261 -- two bytes for the length, plus the actual data. There is also one
262 -- byte needed to terminate the message.
263 --
264 procedure unpack_message(item out varchar2 character set any_cs);
265 pragma restrict_references(unpack_message,WNDS,RNDS);
266 procedure unpack_message(item out number);
267 pragma restrict_references(unpack_message,WNDS,RNDS);
268 procedure unpack_message(item out date);
269 pragma restrict_references(unpack_message,WNDS,RNDS);
270 procedure unpack_message_raw(item out raw);
271 pragma restrict_references(unpack_message_raw,WNDS,RNDS);
272 procedure unpack_message_rowid(item out rowid);
273 pragma restrict_references(unpack_message_rowid,WNDS,RNDS);
274 -- Unpack an item from the local message buffer
275 -- Output parameters:
276 -- item
277 -- The argument to receive the next unpacked item from the local
278 -- message buffer.
279 -- Exceptions:
280 -- ORA-06556 or 06559 are generated if the buffer contains
281 -- no more items, or if the item is not of the same type as that
282 -- requested (see 'next_item_type' below).
283 --
284 function next_item_type return integer;
285 pragma restrict_references(next_item_type,WNDS,RNDS);
286 -- Get the type of the next item in the local message buffer
287 -- Return value:
288 -- Type of next item in buffer:
289 -- 0 no more items
290 -- 9 varchar2
291 -- 6 number
292 -- 11 rowid
293 -- 12 date
294 -- 23 raw
295 --
296 function create_pipe(pipename in varchar2,
297 maxpipesize in integer default 8192,
298 private in boolean default TRUE)
299 return integer;
300 pragma restrict_references(create_pipe,WNDS,RNDS);
301 -- Create an empty pipe with the given name.
302 -- Input parameters:
303 -- pipename
304 -- Name of pipe to be created. WARNING: Do not use pipe names
305 -- beginning with 'ORA$'. These are reserved for use by procedures
306 -- provided by Oracle Corporation. Pipename should not be longer than
307 -- 128 bytes, and is case_insensitive. At this time, the name cannot
308 -- contain NLS characters.
309 -- maxpipesize
310 -- Maximum allowed size for the pipe. The total size of all the
311 -- messages on the pipe cannot exceed this amount. The maxpipesize
312 -- for a pipe becomes part of the pipe and persists for the lifetime
313 -- of the pipe. Callers of send_message with larger values will
314 -- cause the maxpipesize to be increased. Callers with a smaller
315 -- value will just use the larger value. The specification of
316 -- maxpipesize here allows us to avoid its use in future send_message
317 -- calls.
318 -- private
319 -- Boolean indicating whether the pipe will be private - and for the
320 -- use of the creating user-id, or public. A private pipe can be used
321 -- directly through calls to this package by sessions connected to the
322 -- database as the same user as the one that created the pipe. It can
323 -- also be used via stored procedures owned by the user that created
324 -- the pipe. The procedure may be executed by anyone with execute
325 -- privilege on it. A public pipe can be accessed by anyone who has
326 -- knowledge of it and execute privilege on dbms_pipe.
327 -- Return values:
328 -- 0 - Success. This is returned even if the pipe had been created in
329 -- mode that permits its use by the user executing the create call.
330 -- If a pipe already existed, it is not emptied.
331 -- Exceptions:
332 -- Null pipe name.
333 -- Permission error. Pipe with the same name already exists and
334 -- you are not allowed to use it.
338 pragma restrict_references(remove_pipe,WNDS,RNDS);
335 --
336 function remove_pipe(pipename in varchar2)
337 return integer;
339 -- Remove the named pipe.
340 -- Input Parameters:
341 -- pipename
342 -- Name of pipe to remove.
343 -- Return value:
344 -- 0 - Success. Calling remove on a pipe that does not exist returns 0.
345 -- Exceptions:
346 -- Null pipe name.
347 -- Permission error. Insufficient privilege to remove pipe. The
348 -- pipe was created and is owned by someone else.
349 --
350 function send_message(pipename in varchar2,
351 timeout in integer default maxwait,
352 maxpipesize in integer default 8192)
353 return integer;
354 pragma restrict_references(send_message,WNDS,RNDS);
355 -- Send a message on the named pipe. The message is contained in the
356 -- local message buffer which was filled with calls to 'pack_message'.
357 -- A pipe could have been created explicitly using 'create_pipe', or
358 -- it will be created implicitly.
359 -- Input parameters:
360 -- pipename
361 -- Name of pipe to place the message on. The message is copied
362 -- from the local buffer which can be filled by the "pack_message"
363 -- routine. WARNING: Do not use pipe names beginning with 'ORA$'.
364 -- These names are reserved for use by procedures provided by
365 -- Oracle Corporation. Pipename should not be longer than 128 bytes,
366 -- and is case_insensitive. At this time, the name cannot
367 -- contain NLS characters.
368 -- timeout
369 -- Time to wait while attempting to place a message on a pipe, in
370 -- seconds (see return codes below).
371 -- maxpipesize
372 -- Maximum allowed size for the pipe. The total size of all the
373 -- messages on the pipe cannot exceed this amount. If this message
374 -- would exceed this amount the call will block. The maxpipesize
375 -- for a pipe becomes part of the pipe and persists for the lifetime
376 -- of the pipe. Callers of send_message with larger values will
377 -- cause the maxpipesize to be increased. Callers with a smaller
378 -- value will just use the larger value. The specification of
379 -- maxpipesize here allows us to avoid the use of a "open_pipe" call.
380 -- Return value:
381 -- 0 - Success
382 -- 1 - Timed out (either because can't get lock on pipe or pipe stays
383 -- too full)
384 -- 3 - Interrupted
385 -- Exceptions:
386 -- Null pipe name.
387 -- Permission error. Insufficient privilege to write to the pipe.
388 -- The pipe is private and owned by someone else.
389 function receive_message(pipename in varchar2,
390 timeout in integer default maxwait)
391 return integer;
392 pragma restrict_references(receive_message,WNDS,RNDS);
393 -- Receive a message from the named pipe. Copy the message into the
394 -- local message buffer. Use 'unpack_message' to access the
395 -- individual items in the message. The pipe can be created explicitly
396 -- using the 'create_pipe' function or it will be created implicitly.
397 -- Input parameters:
398 -- pipename
399 -- Name of pipe from which to retrieve a message. The message is
400 -- copied into a local buffer which can be accessed by the
401 -- "unpack_message" routine. WARNING: Do not use pipe names
402 -- beginning with 'ORA$'. These names are reserved for use by
403 -- procedures provided by Oracle Corporation. Pipename should not be
404 -- longer than 128 bytes, and is case-insensitive. At this time,
405 -- the name cannot contain NLS characters.
406 -- timeout
407 -- Time to wait for a message. A timeout of 0 allows you to read
408 -- without blocking.
409 -- Return value:
410 -- 0 - Success
411 -- 1 - Timed out
412 -- 2 - Record in pipe too big for buffer (should not happen).
413 -- 3 - Interrupted
414 -- Exceptions:
415 -- Null pipe name.
416 -- Permission error. Insufficient privilege to remove the record
417 -- from the pipe. The pipe is owned by someone else.
418 procedure reset_buffer;
419 pragma restrict_references(reset_buffer,WNDS,RNDS);
420 -- Reset pack and unpack positioning indicators to 0. Generally this
421 -- routine is not needed.
422 --
423 procedure purge(pipename in varchar2);
424 pragma restrict_references(purge,WNDS,RNDS);
425 -- Empty out the named pipe. An empty pipe is a candidate for LRU
426 -- removal from the SGA, therefore 'purge' can be used to free all
427 -- memory associated with a pipe.
428 -- Input Parameters:
429 -- pipename
430 -- Name of pipe from which to remove all messages. The local
431 -- buffer may be overwritten with messages as they are discarded.
432 -- Pipename should not be longer than 128 bytes, and is
433 -- case-insensitive.
434 -- Exceptions:
435 -- Permission error if pipe belongs to another user.
436 --
437 function unique_session_name return varchar2;
438 pragma restrict_references(unique_session_name,WNDS,RNDS,WNPS);
439 -- Get a name that is unique among all sessions currently connected
440 -- to this database. Multiple calls to this routine from the same
441 -- session will always return the same value.
442 -- Return value:
443 -- A unique name. The returned name can be up to 30 bytes.
444 --
445
446 pragma TIMESTAMP('2000-06-09:14:30:00');
447
448 end;
449
450 -- CUT_HERE <- tell sed where to chop off the rest
[Home] [Help]