1 PACKAGE utl_tcp AUTHID CURRENT_USER AS
2
3 /*******************************************************************
4 * OVERVIEW
5 *
6 * This package provides TCP/IP client-side access functionality in PL/SQL.
7 * With this package, a PL/SQL program can communicate with external TCP/IP
8 * services and retrieve data. The API only allows connections to be
9 * initiated by the PL/SQL program. It does not allow the PL/SQL program
10 * to accept connections initiated from outside of the program.
11 *
12 * USES
13 *
14 * A TCP/IP connection is initiated by a call to open_connection, which
15 * returns a TCP/IP connection. Text and binary data can be sent or
16 * received on the connection. It is also possible to look ahead at the
17 * data before it is actually read. The connection is terminated by
18 * a call to close_connection.
19 *
20 * EXAMPLES
21 * Retrieve the home page from http://www.acme.com/
22 *
23 * DECLARE
24 * c utl_tcp.connection; -- TCP/IP connection to the Web server
25 * len PLS_INTEGER;
26 * BEGIN
27 * -- open connection
28 * c := utl_tcp.open_connection(remote_host => 'www.acme.com',
29 * remote_port => 80,
30 * charset => 'US7ASCII');
31 *
32 * len := utl_tcp.write_line(c, 'GET / HTTP/1.0'); -- send HTTP request
33 * len := utl_tcp.write_line(c);
34 * BEGIN
35 * LOOP
36 * dbms_output.put_line(utl_tcp.get_line(c, TRUE)); -- read result
37 * END LOOP;
38 * EXCEPTION
39 * WHEN utl_tcp.end_of_input THEN
40 * NULL; -- end of input
41 * END;
42 * utl_tcp.close_connection(c);
43 * END;
44 */
45
46 /*
47 * TCP connection type
48 */
49 TYPE connection IS RECORD (
50 remote_host VARCHAR2(255), -- Remote host name
51 remote_port PLS_INTEGER, -- Remote port number
52 local_host VARCHAR2(255), -- Local host name
53 local_port PLS_INTEGER, -- Local port number
54 charset VARCHAR2(30), -- Character set for on-the-wire comm.
55 newline VARCHAR2(2), -- Newline character sequence
56 tx_timeout PLS_INTEGER, -- Transfer time-out value (in seconds)
57 private_sd PLS_INTEGER -- For internal use only
58 );
59
60 /*
61 * Carriage-return line-feed character sequence.
62 */
63 CRLF CONSTANT VARCHAR2(2 CHAR) := unistr('\000D\000A');
64
65 /*
66 * Exceptions
67 */
68 buffer_too_small EXCEPTION; -- Buffer is too small for I/O
69 end_of_input EXCEPTION; -- End of input from the connection
70 network_error EXCEPTION; -- Network error
71 bad_argument EXCEPTION; -- Bad argument passed in API call
72 partial_multibyte_char EXCEPTION; -- A partial multi-byte character found
73 transfer_timeout EXCEPTION; -- Transfer time-out occurred
74 network_access_denied EXCEPTION; -- Network access denied
75 buffer_too_small_errcode CONSTANT PLS_INTEGER:= -29258;
76 end_of_input_errcode CONSTANT PLS_INTEGER:= -29259;
77 network_error_errcode CONSTANT PLS_INTEGER:= -29260;
78 bad_argument_errcode CONSTANT PLS_INTEGER:= -29261;
79 partial_multibyte_char_errcode CONSTANT PLS_INTEGER:= -29275;
80 transfer_timeout_errcode CONSTANT PLS_INTEGER:= -29276;
81 network_access_denied_errcode CONSTANT PLS_INTEGER:= -24247;
82 PRAGMA EXCEPTION_INIT(buffer_too_small, -29258);
83 PRAGMA EXCEPTION_INIT(end_of_input, -29259);
84 PRAGMA EXCEPTION_INIT(network_error, -29260);
85 PRAGMA EXCEPTION_INIT(bad_argument, -29261);
86 PRAGMA EXCEPTION_INIT(partial_multibyte_char,-29275);
87 PRAGMA EXCEPTION_INIT(transfer_timeout, -29276);
88 PRAGMA EXCEPTION_INIT(network_access_denied, -24247);
89
90 /**
91 * Opens a connection to a TCP/IP service. When connection is made
92 * succesfully, the remote host name and remote port number will be stored in
93 * the connection. If local_host, local_port or charset is specified,
94 * it will be stored in the connection as well.
95 *
96 * Communication with the remote service may be secured using Secure Socket
97 * Layer / Transport Layer Security (SSL/TLS). To start SSL/TLS in the
98 * connection, the user must specify an Oracle wallet when opening the
99 * connection and call the SECURE_CONNECTION procedure. The wallet must
100 * contain the trusted certificate of the certificate authority who signs
101 * the remote server's certificate for UTL_TCP to validate the remote
102 * server's certificate.
103 *
104 * UTL_TCP supports client authentication over SSL/TLS using the client
105 * certificate in the wallet. The client certificate will be sent to the
106 * remote server if it is present in the wallet and if the caller has the
107 * "use-client-certificates" ACL privilege on the wallet.
108 *
109 * For more information on Oracle wallets, see Oracle Wallet Manager
110 * documentation.
111 *
112 * PARAMETERS
113 * remote_host remote host name to connect to
114 * remote_port remote port number to connect to
115 * local_host local host name to connect from
116 * local_port local port number to connect from
117 * in_buffer_size input buffer size
118 * out_buffer_size output buffer size
119 * charset character set for on-the-wire communication
120 * newline newline character sequence
121 * tx_timeout a time in seconds this package should wait before
122 * giving up in a read or write operation in this
123 * connection. In read operations, this package will give
124 * up if no data is available for reading immediately.
125 * In write operations, this package will give if the
126 * output buffer is full and no data be sent in the
127 * network without being blocked. 0 indicates not to wait
128 * at all. NULL indicates to wait forever.
129 * wallet_path the directory path that contains the Oracle wallet for
130 * SSL/TLS. The format is "file:<directory-path>".
131 * wallet_password the password to open the wallet. When the wallet is
132 * auto-login enabled, the password may be set to NULL.
133 * RETURN
134 * a connection to the destinated TCP/IP service.
135 * EXCEPTIONS
136 * network_error - network error
137 * NOTES
138 * In the current release of this package, the parameters local_host and
139 * local_port are ignored when open_connection makes a TCP/IP connection.
140 * It does not attempt to use the specified local host and port number
141 * when the connection is made. The local_host and local_port fields
142 * will not be set in the connection record returned by this function.
143 * Time-out on write operations is not supported in the current release
144 * of this package.
145 */
146 FUNCTION open_connection(remote_host IN VARCHAR2,
147 remote_port IN PLS_INTEGER,
148 local_host IN VARCHAR2 DEFAULT NULL,
149 local_port IN PLS_INTEGER DEFAULT NULL,
150 in_buffer_size IN PLS_INTEGER DEFAULT NULL,
151 out_buffer_size IN PLS_INTEGER DEFAULT NULL,
152 charset IN VARCHAR2 DEFAULT NULL,
153 newline IN VARCHAR2 DEFAULT CRLF,
154 tx_timeout IN PLS_INTEGER DEFAULT NULL,
155 wallet_path IN VARCHAR2 DEFAULT NULL,
156 wallet_password IN VARCHAR2 DEFAULT NULL)
157 RETURN connection;
158
159 /**
160 * Secure a TCP/IP connection using Secure Socket Layer / Transport Layer
161 * Security (SSL/TLS). SSL/TLS requires an Oracle wallet which must be
162 * specified when the connection was opened by the OPEN_CONNECTION function.
163 *
164 * PARAMETERS
165 * c TCP/IP connection
166 * RETURN
167 * None
168 * EXCEPTIONS
169 * SSL/TLS errors - error occurred in SSL/TLS communication
170 * NOTES
171 * See the OPEN_CONNECTION function.
172 */
173 PROCEDURE secure_connection(c IN OUT NOCOPY connection);
174
175 /**
176 * Determines the number of bytes available for reading from a TCP/IP
177 * connection. It is the number of bytes that can be read immediately
178 * without blocking.
179 *
180 * PARAMETERS
181 * c TCP/IP connection
182 * timemout a time in seconds to wait before giving up and reporting
183 * no data available. 0 indicates not to wait at all. NULL
184 * indicates to wait forever.
185 * RETURN
186 * The number of bytes available for reading without blocking.
187 * EXCEPTIONS
188 * network_error - network error
189 * NOTES
190 * The number of bytes available for reading returned by this function
191 * may be a conservative estimate in some situations. It may be less than
192 * what is actually available for reading. On some platforms, this function
193 * may only be able to return just 1 to indicate the fact that some data is
194 * available for reading. Users who are concerned with the portability of
195 * their applications should assume that this function returns a positive
196 * value when data is available for reading, and zero when no data is
197 * available. This is an example that illustrates how to use this function
198 * in a portable manner:
199 *
200 * DECLARE
201 * c utl_tcp.connection;
202 * data VARCHAR2(256);
203 * len PLS_INTEGER;
204 * BEGIN
205 * c := utl_tcp.open_connection(...);
206 * LOOP
207 * IF (utl_tcp.available(c) > 0) THEN
208 * len := utl_tcp.read_text(c, data, 256);
209 * ELSE
210 * -- do some other things
211 * ...
212 * END IF;
213 * END LOOP;
214 * END;
215 */
216 FUNCTION available(c IN OUT NOCOPY connection,
217 timeout IN PLS_INTEGER DEFAULT 0)
218 RETURN PLS_INTEGER;
219
220 /*----------------------- Binary Input/Output API -----------------------*/
221
222 /**
223 * Reads binary data from a TCP/IP connection. This function does not
224 * return until the specified number of bytes have been read, or the end
225 * of input has been reached.
226 *
227 * PARAMETERS
228 * c TCP/IP connection
229 * data the data read (OUT)
230 * len the max number of bytes to read
231 * peek should this call be peek-only (i.e. keep the data read
232 * in the input buffer to be read again later)?
233 * RETURN
234 * The number of bytes read. The actual number of bytes read may be
235 * less than specified because the end of input has been reached.
236 * EXCEPTIONS
237 * value_error - the buffer "data" is not big enough to hold the
238 * requested amount of data.
239 * end_of_input - no data is read and the end of input is reached
240 * transfer_timeout - no data is read and a read time-out occurred
241 * network_error - network error
242 * NOTES
243 * If transfer time-out is set when the connection is opened, this
244 * function will wait for each data packet to be ready to read until
245 * time-out occurs. If it occurs, this function will stop reading and
246 * return all the data read successfully. If no data is read successfully,
247 * the transfer_timeout exception will be raised. The exception can be
248 * handled and the read operation can be retried at a later time.
249 */
250 FUNCTION read_raw(c IN OUT NOCOPY connection,
251 data IN OUT NOCOPY RAW,
252 len IN PLS_INTEGER DEFAULT 1,
253 peek IN BOOLEAN DEFAULT FALSE)
254 RETURN PLS_INTEGER;
255
256 /**
257 * Writes binary data to a TCP/IP connection. This function does not
258 * return until the specified number of bytes have been written.
259 *
260 * PARAMETERS
261 * c TCP/IP connection
262 * data the data to be written
263 * len the number of bytes to write. When len is NULL, the
264 * whole length of data is written. The actual amount of
265 * data written may be less because of network condition
266 * RETURN
267 * The actual number of bytes written to the connection.
268 * EXCEPTIONS
269 * network_error - network error
270 */
271 FUNCTION write_raw(c IN OUT NOCOPY connection,
272 data IN RAW,
273 len IN PLS_INTEGER DEFAULT NULL)
274 RETURN PLS_INTEGER;
275
276 /*------------------------- Text Input/Output API ----------------------*/
277
278 /**
279 * Reads text data from a TCP/IP connection. This function does not
280 * return until the specified number of characters have been read, or the end
281 * of input has been reached.
282 *
283 * PARAMETERS
284 * c TCP/IP connection
285 * data the data read (OUT)
286 * len the max number of characters to read
287 * peek should this call be peek-only (i.e. keep the data read
288 * in the input buffer to be read again later)?
289 * RETURN
290 * The number of characters read. The actual umber of characters read
291 * may be less than specified because the end of input has been reached.
292 * EXCEPTIONS
293 * value_error - the buffer "data" is not big enough to hold the
294 * requested amount of data.
295 * end_of_input - no data is read and the end of input is reached
296 * transfer_timeout - no data is read and a read time-out occurred
297 * partial_multibyte_char - no data is read and a partial multi-byte
298 * character is found at the end of input
299 * network_error - network error
300 * NOTES
301 * If transfer time-out is set when the connection is opened, this
302 * function will wait for each data packet to be ready to read until
303 * time-out occurs. If it occurs, this function will stop reading and
304 * return all the data read successfully. If no data is read successfully,
305 * the transfer_timeout exception will be raised. The exception can be
306 * handled and the read operation can be retried at a later time.
307 * Text messages will be converted from the on-the-wire character set,
308 * specified when the connection was opened, to the database character set
309 * before they are returned to the caller.
310 * Note that unless it is explicitly overridden as in terms of characters,
311 * the size of a VARCHAR2 buffer is normally specified in terms of bytes,
312 * while the parameter len refers to the max. number of characters to be
313 * read. When the database character set is multi-byte where a single
314 * character may consist of more than 1 byte, user should make sure that
315 * the buffer is big enough to hold the max. number of characters. In
316 * general, the size of the VARCHAR2 buffer should equal to the number
317 * of characters to read multiplied by the max. number of bytes of a
318 * character of the database character set.
319 * If a partial multi-byte character is found at the end of input,
320 * this function will stop reading and return all the complete multi-byte
321 * characters read successfully. If no complete character is read
322 * successfully, the partial_multibyte_char exception will be raised.
323 * The exception can be handled and the bytes of that partial multi-byte
324 * character can be read as binary by the read_raw function. If a partial
325 * multi-byte character is seen in the middle of the input because the
326 * remaining bytes of the character have not arrived and read time-out
327 * occurs, the transfer_timeout exception will be raised instead.
328 * The exception can be handled and the read operation can be retried
329 * at a later time.
330 */
331 FUNCTION read_text(c IN OUT NOCOPY connection,
332 data IN OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
333 len IN PLS_INTEGER DEFAULT 1,
334 peek IN BOOLEAN DEFAULT FALSE)
335 RETURN PLS_INTEGER;
336
337 /**
338 * Writes text data to a TCP/IP connection. This function does not
339 * return until the specified number of characters have been written.
343 * data the data to be written
340 *
341 * PARAMETERS
342 * c TCP/IP connection
344 * len the number of characters to write. When len is NULL,
345 * the whole length of data is written. The amount of
346 * data returned may be less because of network condition
347 * RETURN
348 * The number of characters of data written to the connection.
349 * EXCEPTIONS
350 * network_error - network error
351 * NOTES
352 * Text messages will be converted from the database character set
353 * to the on-the-wire character set, specified when the connection was
354 * opened, before they are transmitted on the wire.
355 */
356 FUNCTION write_text(c IN OUT NOCOPY connection,
357 data IN VARCHAR2 CHARACTER SET ANY_CS,
358 len IN PLS_INTEGER DEFAULT NULL)
359 RETURN PLS_INTEGER;
360
361 /*------------------- Line-oriented Input/Output API ----------------------*/
362
363 /**
364 * Reads a text line from a TCP/IP connection. A line is terminated by
365 * a line-feed, a carriage-return or a carriage-return followed by a
366 * line-feed. The function does not return until the end of line or the
367 * end of input is reached.
368 *
369 * PARAMETERS
370 * c TCP/IP connection
371 * data the data read (OUT)
372 * remove_crlf remove the trailing new-line character(s) or not
373 * peek should this call be peek-only (i.e. keep the data read
374 * in the input buffer to be read again later)?
375 * RETURN
376 * The number of characters read.
377 * EXCEPTIONS
378 * value_error - the buffer "data" is not big enough to hold the
379 * requested amount of data.
380 * end_of_input - no data is read and the end of input is reached
381 * transfer_timeout - no data is read and a read time-out occurred
382 * partial_multibyte_char - no data is read and a partial multi-byte
383 * character is found at the end of input
384 * network_error - network error
385 * NOTES
386 * If transfer time-out is set when the connection is opened, this
387 * function will wait for each data packet to be ready to read until
388 * time-out occurs. If it occurs, this function will stop reading and
389 * return all the data read successfully. If no data is read successfully,
390 * the transfer_timeout exception will be raised. The exception can be
391 * handled and the read operation can be retried at a later time.
392 * Text messages will be converted from the on-the-wire character set,
393 * specified when the connection was opened, to the database character set
394 * before they are returned to the caller.
395 * Note that unless it is explicitly overridden as in terms of characters,
396 * the size of a VARCHAR2 buffer is normally specified in terms of bytes,
397 * while the parameter len refers to the max. number of characters to be
398 * read. When the database character set is multi-byte where a single
399 * character may consist of more than 1 byte, user should make sure that
400 * the buffer is big enough to hold the max. number of characters. In
401 * general, the size of the VARCHAR2 buffer should equal to the number
402 * of characters to read multiplied by the max. number of bytes of a
403 * character of the database character set.
404 * If a partial multi-byte character is found at the end of input,
405 * this function will stop reading and return all the complete multi-byte
406 * characters read successfully. If no complete character is read
407 * successfully, the partial_multibyte_char exception will be raised.
408 * The exception can be handled and the bytes of that partial multi-byte
409 * character can be read as binary by the read_raw function. If a partial
410 * multi-byte character is seen in the middle of the input because the
411 * remaining bytes of the character have not arrived and read time-out
412 * occurs, the transfer_timeout exception will be raised instead.
413 * The exception can be handled and the read operation can be retried
414 * at a later time.
415 */
416 FUNCTION read_line(c IN OUT NOCOPY connection,
417 data IN OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
418 remove_crlf IN BOOLEAN DEFAULT FALSE,
419 peek IN BOOLEAN DEFAULT FALSE)
420 RETURN PLS_INTEGER;
421
422 /**
423 * Writes a text line to a TCP/IP connection. The line is terminated
424 * with the new-line character sequence sepecified when this connection
425 * is opened.
426 *
427 * PARAMETERS
428 * c TCP/IP connection
429 * data the data to be written
430 * RETURN
431 * Then number of characters of data written to the connection.
432 * EXCEPTIONS
433 * network_error - network error
434 * NOTES
435 * Text messages will be converted from the database character set
436 * to the on-the-wire character set, specified when the connection was
437 * opened, before they are transmitted on the wire.
438 */
439 FUNCTION write_line(c IN OUT NOCOPY connection,
440 data IN VARCHAR2 CHARACTER SET ANY_CS
441 DEFAULT NULL)
442 RETURN PLS_INTEGER;
443
444 /*----------------- Convenient functions for Input API ------------------*/
445
446 /**
447 * A convenient form of the read functions, which return the data read
448 * instead of the amount of data read.
449 *
450 * PARAMETERS
451 * c TCP/IP connection
452 * len the max number of bytes or characters to read
453 * removle_crlf remove the trailing new-line character(s) or not
454 * peek should this call be peek-only (i.e. keep the data read
455 * in the input buffer to be read again later)?
456 * RETURN
457 * The data (or line) read.
458 * EXCEPTIONS
459 * end_of_input - no data is read and the end of input is reached
460 * partial_multibyte_char - no data is read and a partial multi-byte
461 * character is found at the end of input
462 * transfer_timeout - no data is read and a read time-out occurred
463 * network_error - network error
464 * NOTES
465 * For all get_XXX API, see the corresponding read_XXX API for the
466 * read time-out issue.
467 * For get_text and get_line, see the corresponding read_XXX API for
468 * character set conversion, buffer size, and multi-byte character issues.
469 */
470 FUNCTION get_raw(c IN OUT NOCOPY connection,
471 len IN PLS_INTEGER DEFAULT 1,
472 peek IN BOOLEAN DEFAULT FALSE)
473 RETURN RAW;
474 FUNCTION get_text(c IN OUT NOCOPY connection,
475 len IN PLS_INTEGER DEFAULT 1,
476 peek IN BOOLEAN DEFAULT FALSE)
477 RETURN VARCHAR2;
478 FUNCTION get_line(c IN OUT NOCOPY connection,
479 remove_crlf IN BOOLEAN DEFAULT false,
480 peek IN BOOLEAN DEFAULT FALSE)
481 RETURN VARCHAR2;
482 FUNCTION get_text_nchar(c IN OUT NOCOPY connection,
483 len IN PLS_INTEGER DEFAULT 1,
484 peek IN BOOLEAN DEFAULT FALSE)
485 RETURN NVARCHAR2;
486 FUNCTION get_line_nchar(c IN OUT NOCOPY connection,
487 remove_crlf IN BOOLEAN DEFAULT false,
488 peek IN BOOLEAN DEFAULT FALSE)
489 RETURN NVARCHAR2;
490
491 /**
492 * Transmits all the output data in the output queue to the connection
493 * immediately.
494 *
495 * PARAMETERS
496 * c TCP/IP connection
497 * RETURN
498 * None.
499 * EXCEPTIONS
500 * network_error - network error
501 */
502 PROCEDURE flush(c IN OUT NOCOPY connection);
503
504 /**
505 * Closes a TCP/IP connection. After the connection is closed, all the
506 * in the connection will be set to NULL.
507 *
508 * PARAMETERS
509 * c TCP/IP connection
510 * RETURN
511 * None.
512 * EXCEPTIONS
513 * network_error - network error
514 */
515 PROCEDURE close_connection(c IN OUT NOCOPY connection);
516
517 /**
518 * Closes all open TCP/IP connections.
519 *
520 * PARAMETERS
521 * None
522 * RETURN
523 * None
524 * EXCEPTIONS
525 * None
526 */
527 PROCEDURE close_all_connections;
528
529 END;
[Home] [Help]