1 PACKAGE dbms_lob IS
2 $if utl_ident.is_oracle_server <> TRUE and
3 utl_ident.is_timesten <> TRUE $then
4 $error 'dbms_lob is not supported in this environment' $end
5 $end
6
7 ------------
8 -- OVERVIEW
9 --
10 -- This package provides general purpose routines for operations
11 -- on Oracle Large OBject (LOBs) datatypes - BLOB, CLOB (read-write)
12 -- and BFILEs (read-only).
13 --
14 -- Oracle 8.0 SQL supports the definition, creation, deletion, and
15 -- complete updates of LOBs. The main bulk of the LOB operations
16 -- are provided by this package.
17 --
18
19 ------------------------
20 -- RULES AND LIMITATIONS
21 --
22 -- The following rules apply in the specification of functions and
23 -- procedures in this package.
24 --
25 -- LENGTH and OFFSET parameters for routines operating on BLOBs and
26 -- BFILEs are to be specified in terms of bytes.
27 -- LENGTH and OFFSET parameters for routines operating on CLOBs
28 -- are to be specified in terms of characters.
29 --
30 -- A function/procedure will raise an INVALID_ARGVAL exception if the
31 -- the following restrictions are not followed in specifying values
32 -- for parameters (unless otherwise specified):
33 --
34 -- 1. Only positive, absolute OFFSETs from the beginning of LOB data
35 -- are allowed. Negative offsets from the tail of the LOB are not
36 -- allowed.
37 -- 2. Only positive, non-zero values are allowed for the parameters
38 -- that represent size and positional quantities such as AMOUNT,
39 -- OFFSET, NEWLEN, NTH etc.
40 -- 3. The value of OFFSET, AMOUNT, NEWLEN, NTH must not exceed the
41 -- value lobmaxsize (which is (4GB-1) in Oracle 8.0) in any DBMS_LOB
42 -- procedure or function.
43 -- 4. For CLOBs consisting of fixed-width multi-byte characters, the
44 -- maximum value for these parameters must not exceed
45 -- (lobmaxsize/character_width_in_bytes) characters
46 -- For example, if the CLOB consists of 2-byte characters such as
47 -- JA16SJISFIXED, then the maximum amount value should not exceed
48 -- 4294967295/2 = 2147483647 characters
49 --
50 -- PL/SQL language specifications stipulate an upper limit of 32767
51 -- bytes (not characters) for RAW and VARCHAR2 parameters used in
52 -- DBMS_LOB routines.
53 --
54 -- If the value of AMOUNT+OFFSET exceeds 4GB (i.e. lobmaxsize+1) for
55 -- BLOBs and BFILEs, and (lobmaxsize/character_width_in_bytes)+1 for
56 -- CLOBs in calls to update routines - i.e. APPEND, COPY, TRIM, and
57 -- WRITE routines, access exceptions will be raised. Under these input
58 -- conditions, read routines such as READ, COMPARE, INSTR, SUBSTR, will
59 -- read till End of Lob/File is reached.
60 -- For example, for a READ operation on a BLOB or BFILE, if the user
61 -- specifies offset value of 3GB, and an amount value of 2 GB, READ
62 -- will read only ((4GB-1) - 3GB) bytes.
63 --
64 -- Functions with NULL or invalid input values for parameters will
65 -- return a NULL. Procedures with NULL values for destination LOB
66 -- parameters will raise exceptions.
67 --
68 -- Operations involving patterns as parameters, such as COMPARE, INSTR,
69 -- and SUBSTR do not support regular expressions or special matching
70 -- characters (such as % in the LIKE operator in SQL) in the PATTERN
71 -- parameter or substrings.
72 --
73 -- The End Of LOB condition is indicated by the READ procedure using
74 -- a NO_DATA_FOUND exception. This exception is raised only upon an
75 -- attempt by the user to read beyond the end of the LOB/FILE. The
76 -- READ buffer for the last read will contain 0 bytes.
77 --
78 -- For consistent LOB updates, the user is responsible for locking
79 -- the row containing the destination LOB before making a call to
80 -- any of the procedures (mutators) that modify LOB data.
81 --
82 -- For BFILEs, the routines COMPARE, INSTR, READ, SUBSTR, will raise
83 -- exceptions if the file is not already opened using FILEOPEN.
84 --
85
86 -----------
87 -- SECURITY
88 --
89 -- Privileges are associated with the the caller of the procedures/
90 -- functions in this package as follows:
91 -- If the caller is an anonymous PL/SQL block, the procedures/functions
92 -- are run with the privilege of the current user.
93 -- If the caller is a stored procedure, the procedures/functions are run
94 -- using the privileges of the owner of the stored procedure.
95 --
96
97 ------------
98 -- CONSTANTS
99 --
100 file_readonly CONSTANT BINARY_INTEGER := 0;
101 lob_readonly CONSTANT BINARY_INTEGER := 0;
102 lob_readwrite CONSTANT BINARY_INTEGER := 1;
103 lobmaxsize CONSTANT INTEGER := 18446744073709551615;
104 call CONSTANT PLS_INTEGER := 12;
105 transaction CONSTANT PLS_INTEGER := 11;
106 session CONSTANT PLS_INTEGER := 10;
107 warn_inconvertible_char CONSTANT INTEGER := 1;
108 default_csid CONSTANT INTEGER := 0;
109 default_lang_ctx CONSTANT INTEGER := 0;
110 no_warning CONSTANT INTEGER := 0;
111
112 -- Option Types
113 opt_compress CONSTANT PLS_INTEGER := 1;
114 opt_encrypt CONSTANT PLS_INTEGER := 2;
115 opt_deduplicate CONSTANT PLS_INTEGER := 4;
116 -- 16 is reserved for ContentType (also referred to as MimeType)
117 --
118
119 -- Option Values
120 compress_off CONSTANT PLS_INTEGER := 0;
121 compress_on CONSTANT PLS_INTEGER := opt_compress;
122 encrypt_off CONSTANT PLS_INTEGER := 0;
123 encrypt_on CONSTANT PLS_INTEGER := opt_encrypt;
124 deduplicate_off CONSTANT PLS_INTEGER := 0;
125 deduplicate_on CONSTANT PLS_INTEGER := opt_deduplicate;
126
127 $if utl_ident.is_oracle_server $then
128 -- DBFS Link State Values
129 dbfs_link_never CONSTANT PLS_INTEGER := 0;
130 dbfs_link_yes CONSTANT PLS_INTEGER := 1;
131 dbfs_link_no CONSTANT PLS_INTEGER := 2;
132
133 -- DBFS Link flags
134 -- These need to align with flags in SecureFile header
135 dbfs_link_nocache CONSTANT PLS_INTEGER := 0;
136 dbfs_link_cache CONSTANT PLS_INTEGER := 1;
137
138 -- maximum length of DBFS Link pathname
139 dbfs_link_path_max_size CONSTANT PLS_INTEGER := 1024;
140
141 -- maximum length of contenttype string
142 -- The assumption is that the ContentType is in ASCII
143 -- (i.e. 1-byte/7-bit UTF8).
144 contenttype_max_size CONSTANT PLS_INTEGER := 128;
145 $else
146 /* DBFS features not supported in this environment */
147 $end
148
149 -------------
150
151
152 -------------
153 -- STRUCTURES
154 --
155 TYPE blob_deduplicate_region IS RECORD (
156 lob_offset INTEGER,
157 len INTEGER,
158 primary_lob BLOB,
159 primary_lob_offset NUMBER,
160 mime_type VARCHAR2(80));
161
162 TYPE blob_deduplicate_region_tab IS TABLE OF blob_deduplicate_region
163 INDEX BY PLS_INTEGER;
164
165 TYPE clob_deduplicate_region IS RECORD (
166 lob_offset INTEGER,
167 len INTEGER,
168 primary_lob CLOB,
169 primary_lob_offset NUMBER,
170 mime_type VARCHAR2(80));
171
172 TYPE clob_deduplicate_region_tab IS TABLE OF clob_deduplicate_region
173 INDEX BY PLS_INTEGER;
174
175 -------------
176 -- EXCEPTIONS
177 --
178 invalid_argval EXCEPTION;
179 PRAGMA EXCEPTION_INIT(invalid_argval, -21560);
180 invalid_argval_num NUMBER := 21560;
181 -- *Mesg: "argument %s is null, invalid, or out of range"
182 -- *Cause: The argument is expecting a non-null, valid value but the
183 -- argument value passed in is null, invalid, or out of range.
184 -- Examples include when the LOB/FILE positional or size
185 -- argument has a value outside the range 1 through (4GB - 1),
186 -- or when an invalid open mode is used to open a file, etc.
187 -- *Action: Check your program and correct the caller of the routine
188 -- to not pass a null, invalid or out-of-range argument value.
189
190 access_error EXCEPTION;
191 PRAGMA EXCEPTION_INIT(access_error, -22925);
192 eccess_error_num NUMBER := 22925;
193 -- *Mesg: "operation would exceed maximum size allowed for a lob"
194 -- *Cause: Trying to write too much data to the lob. Lob size is limited
195 -- to 4 gigabytes.
196 -- *Action: Either start writing at a smaller lob offset or write less data
197 -- to the lob.
198
199 noexist_directory EXCEPTION;
200 PRAGMA EXCEPTION_INIT(noexist_directory, -22285);
201 noexist_directory_num NUMBER := 22285;
202 -- *Mesg: "%s failed - directory does not exist"
203 -- *Cause: The directory leading to the file does not exist.
204 -- *Action: Ensure that a system object corresponding to the specified
205 -- directory exists in the database dictionary.
206
207 nopriv_directory EXCEPTION;
208 PRAGMA EXCEPTION_INIT(nopriv_directory, -22286);
209 nopriv_directory_num NUMBER := 22286;
210 -- *Mesg: "%s failed - insufficient privileges on directory"
211 -- *Cause: The user does not have the necessary access privileges on the
212 -- directory alias and/or the file for the operation.
213 -- *Action: Ask the database/system administrator to grant the required
214 -- privileges on the directory alias and/or the file.
215
216 invalid_directory EXCEPTION;
217 PRAGMA EXCEPTION_INIT(invalid_directory, -22287);
218 invalid_directory_num NUMBER := 22287;
219 -- *Mesg: "%s failed - invalid or modified directory"
220 -- *Cause: The directory alias used for the current operation is not valid
221 -- if being accessed for the first time, or has been modified by
222 -- the DBA since the last access.
223 -- *Action: If you are accessing this directory for the first time, provide
224 -- a valid directory name. If you have been already successful in
225 -- opening a file under this directory before this error occured,
226 -- then first close the file, then retry the operation with a valid
227 -- directory alias as modified by your DBA. Oracle strongly
228 -- recommends that any changes to directories and/or their
229 -- privileges should be done only during quiescent periods of
230 -- database operation.
231
232 operation_failed EXCEPTION;
233 PRAGMA EXCEPTION_INIT(operation_failed, -22288);
234 operation_failed_num NUMBER := 22288;
235 -- *Mesg: "file operation %s failed\n%s"
236 -- *Cause: The operation attempted on the file failed.
237 -- *Action: See the next error message for more detailed information. Also,
238 -- verify that the file exists and that the necessary privileges
239 -- are set for the specified operation. If the error
240 -- still persists, report the error to the DBA.
241
242 unopened_file EXCEPTION;
243 PRAGMA EXCEPTION_INIT(unopened_file, -22289);
244 unopened_file_num NUMBER := 22289;
245 -- *Mesg: "cannot perform %s operation on an unopened file"
246 -- *Cause: The file is not open for the required operation to be performed.
247 -- *Action: Check that the current operation is preceded by a successful
248 -- file open operation.
249
250 open_toomany EXCEPTION;
251 PRAGMA EXCEPTION_INIT(open_toomany, -22290);
252 open_toomany_num NUMBER := 22290;
253 -- *Mesg: "%s failed - max limit reached on number of open files"
254 -- *Cause: The number of open files has reached the maximum limit.
255 -- *Action: Close some of your open files, and retry the operation for your
256 -- current session. To increase the database wide limit on number
257 -- of open files allowed per session, contact your DBA.
258
259 securefile_badlob EXCEPTION;
260 PRAGMA EXCEPTION_INIT(securefile_badlob, -43856);
261 securefile_badlob_num NUMBER := 43856;
262 -- *Mesg: "%s failed - A non-SecureFile LOB type was used in a SecureFile only call"
263 -- *Cause: The locator passed was for a BFILE, TEMP, ABSTRACT or BasicFile LOB
264 -- when we expected a SecureFile LOB.
265 -- *Action: Be sure a SecureFile is being used before calling this
266 -- function or procedure. You can use issecurefile for this.
267
268 securefile_badparam EXCEPTION;
269 PRAGMA EXCEPTION_INIT(securefile_badparam, -43857);
270 securefile_badparam_num NUMBER := 43857;
271 -- *Mesg: "%s failed - An invalid argument was passed to a SecureFile function or procedure"
272 -- *Cause: One of the parameters passed was invalid
273 -- *Action: Check all the parameters to be sure they are valid.
274
275 securefile_markerased EXCEPTION;
276 PRAGMA EXCEPTION_INIT(securefile_markerased, -43861);
277 securefile_markerased_num NUMBER := 43861;
278 -- *Mesg: "%s failed - The mark provided to a Fragment function has been deleted."
279 -- *Cause: The given mark had been erased before the call.
280 -- *Action: Perform a mark reset and check the application for consistency.
281
282 securefile_outofbounds EXCEPTION;
283 PRAGMA EXCEPTION_INIT(securefile_outofbounds, -43883);
284 securefile_outofbounds_num NUMBER := 43883;
285 -- *Mesg: "%s failed - Attempted to perform a Fragment operation past LOB end"
286 -- *Cause: The given offset was past the end of the LOB.
287 -- *Action: Make sure the offsets given are valid at the time of the call.
288
289 contenttype_toolong EXCEPTION;
290 PRAGMA EXCEPTION_INIT(contenttype_toolong, -43859);
291 contenttype_toolong_num NUMBER := 43859;
292 -- *Mesg: "CONTENTTYPE string too long"
293 -- *Cause: Length of CONTENTTYPE string exceeds defined maximum
294 -- *Action: Modify length of CONTENTTYPE string and retry operation
295
296 contenttypebuf_wrong EXCEPTION;
297 PRAGMA EXCEPTION_INIT(contenttypebuf_wrong, -43862);
298 contenttypebuf_wrong_num NUMBER := 43862;
299 -- *Mesg: "CONTENTTYPE buffer length incorrect"
300 -- *Cause: Length of CONTENTTYPE buffer less than defined constant.
301 -- *Action: Modify length of CONTENTTYPE buffer and retry operation
302
303 ---------------------------
304 -- PROCEDURES AND FUNCTIONS
305 --
306 PROCEDURE append(dest_lob IN OUT NOCOPY BLOB,
307 src_lob IN BLOB);
308
309 PROCEDURE append(dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
310 src_lob IN CLOB CHARACTER SET dest_lob%CHARSET);
311
312 FUNCTION compare(lob_1 IN BLOB,
313 lob_2 IN BLOB,
317 RETURN INTEGER DETERMINISTIC;
314 amount IN INTEGER := 18446744073709551615,
315 offset_1 IN INTEGER := 1,
316 offset_2 IN INTEGER := 1)
318 PRAGMA RESTRICT_REFERENCES(compare, WNDS, RNDS, WNPS, RNPS);
319
320 PROCEDURE close(lob_loc IN OUT NOCOPY BLOB);
321
322 PROCEDURE close(lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);
323
324 $if utl_ident.is_oracle_server $then
325 PROCEDURE close(file_loc IN OUT NOCOPY BFILE);
326 $else
327 /* BFILE overloads are not supported */
328 $end
329
330 FUNCTION compare(lob_1 IN CLOB CHARACTER SET ANY_CS,
331 lob_2 IN CLOB CHARACTER SET lob_1%CHARSET,
332 amount IN INTEGER := 18446744073709551615,
333 offset_1 IN INTEGER := 1,
334 offset_2 IN INTEGER := 1)
335 RETURN INTEGER DETERMINISTIC;
336 PRAGMA RESTRICT_REFERENCES(compare, WNDS, RNDS, WNPS, RNPS);
337
338 $if utl_ident.is_oracle_server $then
339 FUNCTION compare(file_1 IN BFILE,
340 file_2 IN BFILE,
341 amount IN INTEGER,
342 offset_1 IN INTEGER := 1,
343 offset_2 IN INTEGER := 1)
344 RETURN INTEGER DETERMINISTIC;
345 PRAGMA RESTRICT_REFERENCES(compare, WNDS, RNDS, WNPS, RNPS);
346 $else
347 /* BFILE overloads are not supported */
348 $end
349
350 PROCEDURE copy(dest_lob IN OUT NOCOPY BLOB,
351 src_lob IN BLOB,
352 amount IN INTEGER,
353 dest_offset IN INTEGER := 1,
354 src_offset IN INTEGER := 1);
355
356 PROCEDURE copy(dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
357 src_lob IN CLOB CHARACTER SET dest_lob%CHARSET,
358 amount IN INTEGER,
359 dest_offset IN INTEGER := 1,
360 src_offset IN INTEGER := 1);
361
362 PROCEDURE createtemporary(lob_loc IN OUT NOCOPY BLOB,
363 cache IN BOOLEAN,
364 dur IN PLS_INTEGER := 10);
365
366 PROCEDURE createtemporary(lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
367 cache IN BOOLEAN,
368 dur IN PLS_INTEGER := 10);
369
370 PROCEDURE erase(lob_loc IN OUT NOCOPY BLOB,
371 amount IN OUT NOCOPY INTEGER,
372 offset IN INTEGER := 1);
373
374 PROCEDURE erase(lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
375 amount IN OUT NOCOPY INTEGER,
376 offset IN INTEGER := 1);
377
378 $if utl_ident.is_oracle_server $then
379 PROCEDURE fileclose(file_loc IN OUT NOCOPY BFILE);
380
381 PROCEDURE filecloseall;
382
383 FUNCTION fileexists(file_loc IN BFILE)
384 RETURN INTEGER;
385 PRAGMA RESTRICT_REFERENCES(fileexists, WNDS, RNDS, WNPS, RNPS);
386
387 PROCEDURE filegetname(file_loc IN BFILE,
388 dir_alias OUT VARCHAR2,
389 filename OUT VARCHAR2);
390
391 FUNCTION fileisopen(file_loc IN BFILE)
392 RETURN INTEGER;
393 PRAGMA RESTRICT_REFERENCES(fileisopen, WNDS, RNDS, WNPS, RNPS);
394
395 PROCEDURE fileopen(file_loc IN OUT NOCOPY BFILE,
396 open_mode IN BINARY_INTEGER := file_readonly);
397 $else
398 /* BFILE overloads are not supported */
399 $end
400
401 PROCEDURE freetemporary(lob_loc IN OUT NOCOPY BLOB);
402
403 PROCEDURE freetemporary(lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);
404
405 FUNCTION getchunksize(lob_loc IN BLOB)
406 RETURN INTEGER DETERMINISTIC;
407 PRAGMA RESTRICT_REFERENCES(getchunksize, WNDS, RNDS, WNPS, RNPS);
408
409 FUNCTION getchunksize(lob_loc IN CLOB CHARACTER SET ANY_CS)
410 RETURN INTEGER DETERMINISTIC;
411 PRAGMA RESTRICT_REFERENCES(getchunksize, WNDS, RNDS, WNPS, RNPS);
412
413 FUNCTION getlength(lob_loc IN BLOB)
414 RETURN INTEGER DETERMINISTIC;
415 PRAGMA RESTRICT_REFERENCES(getlength, WNDS, RNDS, WNPS, RNPS);
416
417 FUNCTION getlength(lob_loc IN CLOB CHARACTER SET ANY_CS)
418 RETURN INTEGER DETERMINISTIC;
419 PRAGMA RESTRICT_REFERENCES(getlength, WNDS, RNDS, WNPS, RNPS);
420
421 $if utl_ident.is_oracle_server $then
422 FUNCTION getlength(file_loc IN BFILE)
423 RETURN INTEGER DETERMINISTIC;
424 PRAGMA RESTRICT_REFERENCES(getlength, WNDS, RNDS, WNPS, RNPS);
425 $else
426 /* BFILE overloads are not supported */
427 $end
428
429 FUNCTION get_storage_limit(lob_loc IN CLOB CHARACTER SET ANY_CS)
430 RETURN INTEGER DETERMINISTIC;
431 PRAGMA RESTRICT_REFERENCES(get_storage_limit, WNDS, RNDS, WNPS, RNPS);
432
433 FUNCTION get_storage_limit(lob_loc IN BLOB)
434 RETURN INTEGER DETERMINISTIC;
435 PRAGMA RESTRICT_REFERENCES(get_storage_limit, WNDS, RNDS, WNPS, RNPS);
436
437 FUNCTION istemporary(lob_loc IN BLOB)
438 RETURN INTEGER DETERMINISTIC;
439 PRAGMA RESTRICT_REFERENCES(istemporary, WNDS, RNDS, WNPS, RNPS);
440
441 FUNCTION istemporary(lob_loc IN CLOB CHARACTER SET ANY_CS)
442 RETURN INTEGER DETERMINISTIC;
443 PRAGMA RESTRICT_REFERENCES(istemporary, WNDS, RNDS, WNPS, RNPS);
444
448
445 function isopen(lob_loc in blob)
446 RETURN INTEGER;
447 PRAGMA RESTRICT_REFERENCES(isopen, WNDS, RNDS, WNPS, RNPS);
449 function isopen(lob_loc in clob character set any_cs)
450 RETURN INTEGER;
451 PRAGMA RESTRICT_REFERENCES(isopen, WNDS, RNDS, WNPS, RNPS);
452
453 $if utl_ident.is_oracle_server $then
454 function isopen(file_loc in bfile)
455 RETURN INTEGER;
456 PRAGMA RESTRICT_REFERENCES(isopen, WNDS, RNDS, WNPS, RNPS);
457
458
459 PROCEDURE loadfromfile(dest_lob IN OUT NOCOPY BLOB,
460 src_lob IN BFILE,
461 amount IN INTEGER,
462 dest_offset IN INTEGER := 1,
463 src_offset IN INTEGER := 1);
464
465 PROCEDURE loadfromfile(dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
466 src_lob IN BFILE,
467 amount IN INTEGER,
468 dest_offset IN INTEGER := 1,
469 src_offset IN INTEGER := 1);
470
471 PROCEDURE loadblobfromfile(dest_lob IN OUT NOCOPY BLOB,
472 src_bfile IN BFILE,
473 amount IN INTEGER,
474 dest_offset IN OUT INTEGER,
475 src_offset IN OUT INTEGER);
476
477 PROCEDURE loadclobfromfile(dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
478 src_bfile IN BFILE,
479 amount IN INTEGER,
480 dest_offset IN OUT INTEGER,
481 src_offset IN OUT INTEGER,
482 bfile_csid IN NUMBER,
483 lang_context IN OUT INTEGER,
484 warning OUT INTEGER);
485 $else
486 /* BFILE overloads are not supported */
487 $end
488
489 $if utl_ident.is_oracle_server $then
490 PROCEDURE convertToClob(dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
491 src_blob IN BLOB,
492 amount IN INTEGER,
493 dest_offset IN OUT INTEGER,
494 src_offset IN OUT INTEGER,
495 blob_csid IN NUMBER,
496 lang_context IN OUT INTEGER,
497 warning OUT INTEGER);
498
499 PROCEDURE convertToBlob(dest_lob IN OUT NOCOPY BLOB,
500 src_clob IN CLOB CHARACTER SET ANY_CS,
501 amount IN INTEGER,
502 dest_offset IN OUT INTEGER,
503 src_offset IN OUT INTEGER,
504 blob_csid IN NUMBER,
505 lang_context IN OUT INTEGER,
506 warning OUT INTEGER);
507 $else
508 /* Conversion features not supported in this environment */
509 $end
510
511 PROCEDURE open(lob_loc IN OUT NOCOPY BLOB,
512 open_mode IN BINARY_INTEGER);
513
514 PROCEDURE open(lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
515 open_mode IN BINARY_INTEGER);
516
517 $if utl_ident.is_oracle_server $then
518 PROCEDURE open(file_loc IN OUT NOCOPY BFILE,
519 open_mode IN BINARY_INTEGER := file_readonly);
520 $else
521 /* BFILE overloads are not supported */
522 $end
523
524 FUNCTION instr(lob_loc IN BLOB,
525 pattern IN RAW,
526 offset IN INTEGER := 1,
527 nth IN INTEGER := 1)
528 RETURN INTEGER DETERMINISTIC;
529 PRAGMA RESTRICT_REFERENCES(instr, WNDS, RNDS, WNPS, RNPS);
530
531 FUNCTION instr(lob_loc IN CLOB CHARACTER SET ANY_CS,
532 pattern IN VARCHAR2 CHARACTER SET lob_loc%CHARSET,
533 offset IN INTEGER := 1,
534 nth IN INTEGER := 1)
535 RETURN INTEGER DETERMINISTIC;
536 PRAGMA RESTRICT_REFERENCES(instr, WNDS, RNDS, WNPS, RNPS);
537
538 $if utl_ident.is_oracle_server $then
539 FUNCTION instr(file_loc IN BFILE,
540 pattern IN RAW,
541 offset IN INTEGER := 1,
542 nth IN INTEGER := 1)
543 RETURN INTEGER DETERMINISTIC;
544 PRAGMA RESTRICT_REFERENCES(instr, WNDS, RNDS, WNPS, RNPS);
545 $else
546 /* BFILE overloads are not supported */
547 $end
548
549
550 PROCEDURE read(lob_loc IN BLOB,
551 amount IN OUT NOCOPY INTEGER,
552 offset IN INTEGER,
553 buffer OUT RAW);
554
555 PROCEDURE read(lob_loc IN CLOB CHARACTER SET ANY_CS,
556 amount IN OUT NOCOPY INTEGER,
557 offset IN INTEGER,
558 buffer OUT VARCHAR2 CHARACTER SET lob_loc%CHARSET);
559
560 $if utl_ident.is_oracle_server $then
561 PROCEDURE read(file_loc IN BFILE,
562 amount IN OUT NOCOPY INTEGER,
563 offset IN INTEGER,
564 buffer OUT RAW);
565 $else
569 FUNCTION substr(lob_loc IN BLOB,
566 /* BFILE overloads are not supported */
567 $end
568
570 amount IN INTEGER := 32767,
571 offset IN INTEGER := 1)
572 RETURN RAW DETERMINISTIC;
573 PRAGMA RESTRICT_REFERENCES(substr, WNDS, RNDS, WNPS, RNPS);
574
575 FUNCTION substr(lob_loc IN CLOB CHARACTER SET ANY_CS,
576 amount IN INTEGER := 32767,
577 offset IN INTEGER := 1)
578 RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET DETERMINISTIC;
579 PRAGMA RESTRICT_REFERENCES(substr, WNDS, RNDS, WNPS, RNPS);
580
581 $if utl_ident.is_oracle_server $then
582 FUNCTION substr(file_loc IN BFILE,
583 amount IN INTEGER := 32767,
584 offset IN INTEGER := 1)
585 RETURN RAW DETERMINISTIC;
586 PRAGMA RESTRICT_REFERENCES(substr, WNDS, RNDS, WNPS, RNPS);
587 $else
588 /* BFILE overloads are not supported */
589 $end
590
591 PROCEDURE trim(lob_loc IN OUT NOCOPY BLOB,
592 newlen IN INTEGER);
593
594 PROCEDURE trim(lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
595 newlen IN INTEGER);
596
597 PROCEDURE write(lob_loc IN OUT NOCOPY BLOB,
598 amount IN INTEGER,
599 offset IN INTEGER,
600 buffer IN RAW);
601
602 PROCEDURE write(lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
603 amount IN INTEGER,
604 offset IN INTEGER,
605 buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET);
606
607 PROCEDURE writeappend(lob_loc IN OUT NOCOPY BLOB,
608 amount IN INTEGER,
609 buffer IN RAW);
610
611 PROCEDURE writeappend(lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
612 amount IN INTEGER,
613 buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET);
614
615 $if utl_ident.is_oracle_server $then
616 -- fragment update API
617
618 PROCEDURE fragment_insert(
619 lob_loc IN OUT NOCOPY BLOB,
620 amount IN INTEGER,
621 offset IN INTEGER,
622 buffer IN RAW
623 );
624
625 PROCEDURE fragment_insert(
626 lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
627 amount IN INTEGER,
628 offset IN INTEGER,
629 buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET
630 );
631
632 PROCEDURE fragment_delete(
633 lob_loc IN OUT NOCOPY BLOB,
634 amount IN INTEGER,
635 offset IN INTEGER
636 );
637
638 PROCEDURE fragment_delete(
639 lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
640 amount IN INTEGER,
641 offset IN INTEGER
642 );
643
644 PROCEDURE fragment_move(
645 lob_loc IN OUT NOCOPY BLOB,
646 amount IN INTEGER,
647 src_offset IN INTEGER,
648 dest_offset IN INTEGER
649 );
650
651 PROCEDURE fragment_move(
652 lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
653 amount IN INTEGER,
654 src_offset IN INTEGER,
655 dest_offset IN INTEGER
656 );
657
658 PROCEDURE fragment_replace(
659 lob_loc IN OUT NOCOPY BLOB,
660 old_amount IN INTEGER,
661 new_amount IN INTEGER,
662 offset IN INTEGER,
663 buffer IN RAW
664 );
665
666 PROCEDURE fragment_replace(
667 lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
668 old_amount IN INTEGER,
669 new_amount IN INTEGER,
670 offset IN INTEGER,
671 buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET
672 );
673 $else
674 /* fragment operations are not supported */
675 $end
676
677 $if utl_ident.is_oracle_server $then
678 FUNCTION getoptions(
679 lob_loc IN BLOB,
680 option_types IN PLS_INTEGER
681 ) RETURN PLS_INTEGER;
682 PRAGMA RESTRICT_REFERENCES(getoptions, WNDS, RNDS, WNPS, RNPS);
683
684 FUNCTION getoptions(
685 lob_loc IN CLOB CHARACTER SET ANY_CS,
686 option_types IN PLS_INTEGER
687 ) RETURN PLS_INTEGER;
688 PRAGMA RESTRICT_REFERENCES(getoptions, WNDS, RNDS, WNPS, RNPS);
689
690 PROCEDURE setoptions(
691 lob_loc IN OUT NOCOPY BLOB,
692 option_types IN PLS_INTEGER,
693 options IN PLS_INTEGER
694 );
695
696 PROCEDURE setoptions(
697 lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
698 option_types IN PLS_INTEGER,
699 options IN PLS_INTEGER
700 );
701 $else
702 /* get and set options feature not supported in this environment */
703 $end
704
705 $if utl_ident.is_oracle_server $then
706 PROCEDURE get_deduplicate_regions(
707 lob_loc IN BLOB,
708 region_table IN OUT NOCOPY BLOB_DEDUPLICATE_REGION_TAB
709 );
710
711 PROCEDURE get_deduplicate_regions(
712 lob_loc IN CLOB CHARACTER SET ANY_CS,
713 region_table IN OUT NOCOPY CLOB_DEDUPLICATE_REGION_TAB
714 );
715
716 FUNCTION issecurefile(
717 lob_loc IN BLOB
718 ) RETURN BOOLEAN;
719 PRAGMA RESTRICT_REFERENCES(issecurefile, WNDS, RNDS, WNPS, RNPS);
720
721 FUNCTION issecurefile(
722 lob_loc IN CLOB CHARACTER SET ANY_CS
723 ) RETURN BOOLEAN;
724 PRAGMA RESTRICT_REFERENCES(issecurefile, WNDS, RNDS, WNPS, RNPS);
725
726 -- DBFS Link Functionality
727 PROCEDURE copy_from_dbfs_link(
728 lob_loc IN OUT BLOB
729 );
730
731 PROCEDURE copy_from_dbfs_link(
732 lob_loc IN OUT CLOB CHARACTER SET ANY_CS
733 );
734
735 PROCEDURE move_to_dbfs_link(
736 lob_loc IN OUT BLOB,
737 storage_path IN VARCHAR2,
738 flags IN BINARY_INTEGER := DBFS_LINK_NOCACHE
739 );
740
741 PROCEDURE move_to_dbfs_link(
742 lob_loc IN OUT CLOB CHARACTER SET ANY_CS,
743 storage_path IN VARCHAR2,
744 flags IN BINARY_INTEGER := DBFS_LINK_NOCACHE
745 );
746
747 FUNCTION get_dbfs_link(
748 lob_loc IN BLOB
749 ) RETURN VARCHAR2;
750 PRAGMA RESTRICT_REFERENCES(get_dbfs_link, WNDS, RNDS, WNPS, RNPS);
751
752 FUNCTION get_dbfs_link(
753 lob_loc IN CLOB CHARACTER SET ANY_CS
754 ) RETURN VARCHAR2;
755 PRAGMA RESTRICT_REFERENCES(get_dbfs_link, WNDS, RNDS, WNPS, RNPS);
756
757 PROCEDURE set_dbfs_link(
758 lob_loc IN OUT BLOB,
759 storage_path IN VARCHAR2
760 );
761
762 PROCEDURE set_dbfs_link(
763 lob_loc IN OUT CLOB CHARACTER SET ANY_CS,
764 storage_path IN VARCHAR2
765 );
766
767 PROCEDURE copy_dbfs_link(
768 dest_lob IN OUT BLOB,
769 src_lob IN BLOB,
770 options IN PLS_INTEGER
771 );
772
773 PROCEDURE copy_dbfs_link(
774 dest_lob IN OUT CLOB CHARACTER SET ANY_CS,
775 src_lob IN CLOB CHARACTER SET ANY_CS,
776 options IN PLS_INTEGER
777 );
778
779 PROCEDURE get_dbfs_link_state(
780 lob_loc IN BLOB,
781 storage_path OUT VARCHAR2,
782 state OUT PLS_INTEGER,
783 cached OUT BOOLEAN
784 );
785
786 PROCEDURE get_dbfs_link_state(
787 lob_loc IN CLOB CHARACTER SET ANY_CS,
788 storage_path OUT VARCHAR2,
789 state OUT PLS_INTEGER,
790 cached OUT BOOLEAN
791 );
792
793 -- PROCEDURE dbfs_link_purge_cache(
794 -- lob_loc IN OUT BLOB
795 -- );
796 -- PROCEDURE dbfs_link_purge_cache(
797 -- lob_loc IN OUT CLOB CHARACTER SET ANY_CS
798 -- );
799
800 FUNCTION dbfs_link_generate_path(
801 lob_loc IN BLOB,
802 storage_dir IN VARCHAR2
803 ) RETURN VARCHAR2;
804 PRAGMA RESTRICT_REFERENCES(dbfs_link_generate_path,
805 WNDS, RNDS, WNPS, RNPS);
806
807 FUNCTION dbfs_link_generate_path(
808 lob_loc IN CLOB CHARACTER SET ANY_CS,
809 storage_dir IN VARCHAR2
810 ) RETURN VARCHAR2;
811 PRAGMA RESTRICT_REFERENCES(dbfs_link_generate_path,
812 WNDS, RNDS, WNPS, RNPS);
813
814 FUNCTION getcontenttype(
815 lob_loc IN BLOB
816 ) RETURN VARCHAR2;
817 PRAGMA RESTRICT_REFERENCES(getcontenttype, WNDS, RNDS, WNPS, RNPS);
818
819 FUNCTION getcontenttype(
820 lob_loc IN CLOB CHARACTER SET ANY_CS
821 ) RETURN VARCHAR2;
822 PRAGMA RESTRICT_REFERENCES(getcontenttype, WNDS, RNDS, WNPS, RNPS);
823
824 PROCEDURE setcontenttype(
825 lob_loc IN OUT NOCOPY BLOB,
826 contenttype IN VARCHAR2
827 );
828
829 PROCEDURE setcontenttype(
830 lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
831 contenttype IN VARCHAR2
832 );
833 $else
834 /* DBFS features not supported in this environment */
835 $end
836
837 END dbms_lob;