Oracle DBMS_LOB
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Provides APIs to access and manipulate specific parts of a LOB or complete LOBs.

The two STANDARD package functions EMPTY_BLOB and EMPTY_CLOB are demonstrated on this page as this is the part of the library where they will most likely find use.
AUTHID DEFINER
Constants
Name Data Type Value
Miscellaneous
archive_path_max_size PLS_INTEGER 32767
call PLS_INTEGER 12
contenttype_max_size PLS_INTEGER 128
default_csid INTEGER 0
default_lang_ctx INTEGER 0
file_readonly BINARY_INTEGER 0
lob_readonly BINARY_INTEGER 0
lob_readwrite BINARY_INTEGER 1
lobmaxsize INTEGER 18446744073709551615
no_warning INTEGER 0
session PLS_INTEGER 10
transaction PLS_INTEGER 11
warn_inconvertible_char INTEGER 1
Archive Put Flags
archive_put_nocache PLS_INTEGER 0
archive_put_cache PLS_INTEGER 1
Archive Reference Flags
archive_copy_ref PLS_INTEGER 0
archive_copy_cache PLS_INTEGER 1
Archive State
archive_never PLS_INTEGER 0
archive_yes PLS_INTEGER 1
archive_no PLS_INTEGER 2
Option Types
opt_compress PLS_INTEGER 1
opt_encrypt PLS_INTEGER 2
opt_deduplicate PLS_INTEGER 4
Option Values
compress_off PLS_INTEGER 0
compress_on PLS_INTEGER 1
encrypt_off PLS_INTEGER 0
encrypt_on PLS_INTEGER 2
deduplicate_off PLS_INTEGER 0
deduplicate_on PLS_INTEGER 4
Data Types TYPE blob_deduplicate_region IS RECORD (
lob_offset         INTEGER,
len                INTEGER,
primary_lob        BLOB,
primary_lob_offset NUMBER,
mime_type          VARCHAR2(80));

TYPE blob_deduplicate_region_tab
IS TABLE OF blob_deduplicate_region
INDEX BY PLS_INTEGER;

TYPE clob_deduplicate_region IS RECORD (
lob_offset         INTEGER,
len                INTEGER,
primary_lob        CLOB,
primary_lob_offset NUMBER,
mime_type          VARCHAR2(80));

TYPE clob_deduplicate_region_tab
IS TABLE OF clob_deduplicate_region;
INDEX BY PLS_INTEGER;
Demo Table Some of the demos require a table with the LOB stored in a SECUREFILE in an ASSM tablespace. This tablespace and table will make those demos will reference use the table secure_file_tab.

CREATE TABLESPACE securefiletbs
DATAFILE 'c:\temp\securefile01.dbf' SIZE 25M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLE secure_file_tab (
rid     NUMBER(5),
clobcol CLOB)
LOB (clobcol) STORE AS SECUREFILE clobcol (
TABLESPACE securefiletbs
RETENTION MIN 3600
DEDUPLICATE NOCOMPRESS);

INSERT INTO secure_file_tab VALUES (1, 'AbCdEfGhIjKlMnOpQrStUvWxYz');
INSERT INTO secure_file_tab VALUES (2, 'AAAAAAAAAAAAAAAAAAAAA');
COMMIT;
Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_LOB'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_LOB';

-- returns 234 objects
Documented Yes: Packages and Types Reference
Exceptions
Error Code Reason
ORA-01403 ENDOFLOB indicator for looping read operations. This is not a hard error
ORA-06502 PL/SQL error for invalid values to subprogram's parameters
ORA-14553 Cannot perform a LOB write inside a query or PDML slave
ORA-21560 Expecting a non-null, valid value but the argument value passed in is null, invalid, or out of range
ORA-22279 Cannot perform operation with LOB buffering enabled
ORA-22285 The directory leading to the file does not exist
ORA-22286 User does not have the necessary access privileges on the directory alias and/or file
ORA-22287 Directory alias is not valid
ORA-22288 File operation failed
ORA-22289 The file is not open for the required operation
ORA-22290 Open files has reached the maximum limit
ORA-22925 Operation exceeds maximum lob size
ORA-43854 A BASICFILE LOB was used in subprogram that can be used with only SECUREFILE LOB
ORA-43856 A non-SECUREFILE LOB type was used in a SECUREFILE only call. This means the operation only works if the source is a LOB stored in a SECUREFILE.
ORA-43857 An invalid argument was passed to a SECUREFILE subprogram
ORA-43859 The length of the contenttype string exceeds the defined maximum
ORA-43861 The mark provided to a FRAGMENT_* operation has been deleted
ORA-43862 The length of the contenttype buffer is less than defined constant
ORA-43883 Attempted to perform a FRAGMENT_* operation past the LOB end
First Available 8.0
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/dbmslob.sql
Subprograms
APPEND FRAGMENT_DELETE LOADBLOBFROMFILE
CLOB2FILE FRAGMENT_INSERT LOADCLOBFROMFILE
CLOSE FRAGMENT_MOVE LOADFROMFILE
COMPARE FRAGMENT_REPLACE MOVE_TO_DBFS_LINK
CONVERTOBLOB FREETEMPORARY OPEN
CONVERTOCLOB GETCHUNKSIZE READ
COPY GETCONTENTTYPE SETCONTENTTYPE
COPY_DBFS_LINK GETLENGTH SETOPTIONS
COPY_FROM_DBFS_LINK GETOPTIONS SET_DBFS_LINK
CREATETEMPORARY GET_DBFS_LINK SUBSTR
DBFS_LINK_GENERATE_PATH GET_DBFS_LINK_STATE TRIM
ERASE GET_DEDUPLICATE_REGIONS WRITE
FILECLOSE GET_STORAGE_LIMIT WRITEAPPEND
FILECLOSEALL INSTR  
FILEEXISTS ISOPEN STANDARD.EMPTY_BLOB
FILEGETNAME ISREMOTE STANDARD.EMPTY_CLOB
FILEISOPEN ISSECUREFILE  
FILEOPEN ISTEMPORARY Demos
 
APPEND
Appends the contents of a source internal LOB to a destination LOB

Overload 1		 dbms_lob.append(
dest_lob IN OUT NOCOPY BLOB,
src_lob  IN            BLOB);
CREATE OR REPLACE PROCEDURE example_1a AUTHID DEFINER IS
 dest_lob BLOB;
 src_lob  BLOB;
BEGIN
  -- get the LOB locators
  -- note that the FOR UPDATE clause locks the row
   SELECT b_lob INTO dest_lob
  FROM lob_table
  WHERE key_value = 12
  FOR UPDATE;

  SELECT b_lob INTO src_lob
  FROM lob_table
  WHERE key_value = 21;

  dbms_lob.append(dest_lob, src_lob);
  COMMIT;
END example_1a;
/
Overload 2 dbms_lob.append(
dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
src_lob  IN            CLOB CHARACTER SET dest_lob%CHARSET);
CREATE OR REPLACE PROCEDURE example_1b AUTHID DEFINER IS
 dest_lob, src_lob BLOB;
BEGIN
  -- get the LOB locators
  SELECT b_lob INTO dest_lob
  FROM lob_table
  WHERE key_value = 12
  FOR UPDATE;

  SELECT b_lob INTO src_lob
  FROM lob_table
  WHERE key_value = 12;

  dbms_lob.append(dest_lob, src_lob);
  COMMIT;
END example_1b;
/
 
CLOB2FILE
Writes a CLOB to a file with given character encoding: Defaults to the db charset dbms_lob.clob2file(
cl        IN CLOB,
flocation IN VARCHAR2,
fname     IN VARCHAR2,
csid      IN NUMBER := 0,
openmode  IN VARCHAR2 := 'wb');
DECLARE
 cVal CLOB := 'This is a test of new DBMS_LOB functionality';
 fLoc dba_directories.directory_name%TYPE := 'ORACLE_HOME'; -- from dba_directories
BEGIN
  dbms_lob.clob2file(cVal, fLoc, 'morgan.txt' );
END;
/
 
CLOSE
Closes a previously opened internal or external LOB
Overload 1		 dbms_lob.close(lob_loc IN OUT NOCOPY BLOB);
TBD
Overload 2 dbms_lob.close(lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);
See CREATETEMPORARY Demo Below
Overload 3 dbms_lob.close(file_loc IN OUT NOCOPY BFILE);
See CREATETEMPORARY Demo Below
 
COMPARE
Compares two entire LOBs or parts of two LOBs

Overload 1		 dbms_lob.compare(
lob_1    IN BLOB,
lob_2    IN BLOB,
amount   IN INTEGER := 18446744073709551615,
offset_1 IN INTEGER := 1,
offset_2 IN INTEGER := 1)
RETURN INTEGER;
TBD
Overload 2 dbms_lob.compare(
lob_1    IN CLOB CHARACTER SET ANY_CS,
lob_2    IN CLOB CHARACTER SET lob_1%CHARSET,
amount   IN INTEGER := 18446744073709551615,
offset_1 IN INTEGER := 1,;
offset_2 IN INTEGER := 1)
RETURN INTEGER;
TBD
Overload 3 dbms_lob.compare(
file_1   IN BFILE,
file_2   IN BFILE,
amount   IN INTEGER,
offset_1 IN INTEGER := 1,
offset_2 IN INTEGER := 1)
RETURN INTEGER;
TBD
 
CONVERTOBLOB
Reads character data from a source CLOB or NCLOB instance, converts the character data to the specified character, writes the converted data to a destination BLOB instance in binary format, and returns the new offsets dbms_lob.convertToBlob(
dest_lob     IN OUT NOCOPY BLOB,
src_clob     IN            CLOB CHARACTER SET ANY_CS,
amount       IN            INTEGER,
dest_offset  IN OUT        INTEGER,
src_offset   IN OUT        INTEGER,
blob_csid    IN            NUMBER,
lang_context IN OUT        INTEGER,
warning         OUT        INTEGER);
TBD
 
CONVERTOCLOB
Takes a source BLOB instance, converts the binary data in the source instance to character data using the specified character, writes the character data to a destination CLOB or NCLOB instance, and returns the new offsets dbms_lob.convertToClob(
dest_lob     IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
src_blob     IN     BLOB,
amount       IN     INTEGER,
dest_offset  IN OUT INTEGER,
src_offset   IN OUT INTEGER,
blob_csid    IN     NUMBER,
lang_context IN OUT INTEGER,
warning         OUT INTEGER);
See LONG RAW to CLOB Demo
 
COPY
Copies all, or part, of the source LOB to the destination LOB

Overload 1		 dbms_lob.copy(
dest_lob    IN OUT NOCOPY BLOB,
src_lob     IN     BLOB,
amount      IN     INTEGER,
dest_offset IN     INTEGER := 1,
src_offset  IN     INTEGER := 1);
TBD
Overload 2 dbms_lob.copy(
dest_lob    IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
src_lob     IN            CLOB CHARACTER SET dest_lob%CHARSET,
amount      IN            INTEGER,
dest_offset IN            INTEGER := 1,
src_offset  IN            INTEGER := 1);
TBD
 
COPY_DBFS_LINK
Undocumented

Overload 1		 dbms_lob.copy_dbfs_link(
dest_lob IN OUT BLOB,
src_lob  IN     BLOB,
options  IN     PLS_INTEGER);
TBD
Overload 2 dbms_lob.copy_dbfs_link(
dest_lob IN OUT CLOB CHARACTER SET ANY_CS,
src_lob  IN     CLOB CHARACTER SET ANY_CS,
options  IN     PLS_INTEGER);
TBD
 
COPY_FROM_DBFS_LINK
Undocumented

Overload 1		 dbms_lob.copy_from_dbfs_link(
lob_loc      IN  BLOB,
storage_path OUT VARCHAR2,
state        OUT PLS_INTEGER,
cached       OUT BOOLEAN);
TBD
Overload 2 dbms_lob.copy_from_dbfs_link(
lob_loc      IN  CLOB CHARACTER SET ANY_CS,
storage_path OUT VARCHAR2,
state        OUT PLS_INTEGER,
cached       OUT BOOLEAN);
TBD
 
CREATETEMPORARY
Creates a temporary BLOB or CLOB and its corresponding index in the user's default temporary tablespace

Overload 1		 dbms_lob.createTemporary(
lob_loc IN OUT NOCOPY BLOB,
cache   IN BOOLEAN,
dur     IN PLS_INTEGER := 10);
TBD
Overload 2 dbms_lob.createTemporary(
lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
cache   IN            BOOLEAN,
dur     IN            PLS_INTEGER := 10);
DECLARE
 clobVar CLOB := EMPTY_CLOB;
 len     BINARY_INTEGER;
 retVal  PLS_INTEGER;
 x       VARCHAR2(80);
BEGIN
  dbms_lob.createTemporary(clobVar, TRUE);
  dbms_lob.open(clobvar, dbms_lob.lob_readwrite);
  x := 'before line break' || CHR(10) || 'after line break';
  len := LENGTH(x);

  retval := dbms_lob.isOpen(clobVar);
  dbms_output.put_line(retVal);

  dbms_lob.writeAppend(clobVar, len, x);
  dbms_lob.close(clobVar);
END;
/
 
DBFS_LINK_GENERATE_PATH
Undocumented

Overload 1		 dbms_lob.dbfs_link_generate_path(
lob_loc     IN BLOB,
storage_dir IN VARCHAR2)
RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(dbfs_link_generate_path, WNDS, RNDS, WNPS, RNPS);
TBD
Overload 2 dbms_lob.dbfs_link_generate_path(
lob_loc     IN CLOB CHARACTER SET ANY_CS,
storage_dir IN VARCHAR2)
RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(dbfs_link_generate_path, WNDS, RNDS, WNPS, RNPS);
TBD
 
EMPTY_BLOB
Null BLOB

Technically part of STANDARD but I have intentionally put this demo here for convenience		 standard.empty_blob();
CREATE TABLE ebdemo (
fid   NUMBER(3),
iclob BLOB);

INSERT INTO ebdemo
(fid, iblob)
VALUES
(1, EMPTY_BLOB());
 
EMPTY_CLOB
Null CLOB

Technically part of STANDARD but I have intentionally put this demo here for convenience		 standard.empty_clob();
CREATE TABLE ecdemo (
fid   NUMBER(3),
iclob CLOB);

INSERT INTO ecdemo
(fid, iclob)
VALUES
(1, EMPTY_CLOB());
 
ERASE
Erases all or part of a LOB

Overload 1		 dbms_lob.erase(
lob_loc IN OUT NOCOPY BLOB,
amount  IN OUT NOCOPY INTEGER,
offset  IN INTEGER := 1);
TBD
Overload 2 dbms_lob.erase(
lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
amount  IN OUT NOCOPY INTEGER,
offset  IN INTEGER := 1);
TBD
 
FILECLOSE
Closes a file opened with dbms_lob.file_open dbms_lob.fileClose(file_loc IN OUT NOCOPY BFILE);
See DBMS_LOB Demos Below
 
FILECLOSEALL
Closes all files opened with dbms_lob.file_open dbms_lob.fileCloseAll;
exec dbms_lob.fileCloseAll;
 
FILEEXISTS
Returns 1 if the file exists in the named directory: otherwise 0 dbms_lob.fileExists(file_loc IN BFILE) RETURN INTEGER;
DECLARE
 src_file BFILE;
 retval   INTEGER;
BEGIN
  src_file := bfilename('CTEMP', 'sphere.mpg');
  retval := dbms_lob.fileExists(src_file);
  dbms_output.put_line(TO_CHAR(retval));
END;
/
 
FILEGETNAME
Returns the source filename and directory given a BFILE dbms_lob.fileGetName(
file_loc  IN  BFILE,
dir_alias OUT VARCHAR2,
filename  OUT VARCHAR2);
DECLARE
 src_file  BFILE;
 alias_out VARCHAR2(30);
 fname_out VARCHAR2(30);
BEGIN
  src_file := bfilename('CTEMP', 'sphere.mpg');
  dbms_lob.fileGetName(src_file, alias_out, fname_out);

  dbms_output.put_line(alias_out);
  dbms_output.put_line(fname_out);
END;
/
 
FILEISOPEN
Returns 1if the file is currently open: Otherwise 0 dbms_lob.fileisopen(file_loc IN BFILE) RETURN INTEGER;
DECLARE
 src_file BFILE;
 retval   INTEGER;
BEGIN
  src_file := bfilename('CTEMP', 'sphere.mpg');
  retval := dbms_lob.fileIsOpen(src_file);
  dbms_output.put_line(TO_CHAR(retval));
END;
/
 
FILEOPEN
Open a file to read its contents dbms_lob.fileOpen(
file_loc  IN OUT NOCOPY BFILE,
open_mode IN            BINARY_INTEGER := file_readonly);
DECLARE
 src_file BFILE;
 retval   INTEGER;
BEGIN
  src_file := bfilename('CTEMP', 'sphere.mpg');
  retval := dbms_lob.fileOpen(src_file, dbms_lob.file_readonly);
END;
/
 
FRAGMENT_DELETE
Deletes the data at the given offset for the given length from the LOB

Overload 1		 dbms_lob.fragment_delete(
lob_loc IN OUT NOCOPY BLOB,
amount  IN            INTEGER,
offset  IN            INTEGER);
TBD
Overload 2

Clearly there is an issue here so I am opening an SR at MOS (5 Feb, 2014)		 dbms_lob.fragment_delete(
lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
amount  IN            INTEGER,
offset  IN            INTEGER);
DECLARE
 dest_lob CLOB;
BEGIN
  SELECT clobcol
  INTO dest_lob
  FROM secure_file_tab
  WHERE rid = 1
  FOR UPDATE;

  dbms_output.put_line(dest_lob);
  dbms_lob.fragment_delete(dest_lob, 2, 2);
  dbms_output.put_line(dest_lob);
END;
/
AbCdEfGhIjKlMnOp
DECLARE
*
ERROR at line 1:
ORA-43856: Unsupported LOB type for SECUREFILE LOB operation
ORA-06512: at "SYS.DBMS_LOB", line 1187
ORA-06512: at line 11
 
FRAGMENT_INSERT
Inserts the given data (limited to 32K) into the LOB at the given offset

Overload 1		 dbms_lob.fragment_insert(
lob_loc IN OUT NOCOPY BLOB,
amount  IN            INTEGER,
offset  IN            INTEGER,
buffer  IN            RAW);
TBD
Overload 2

Clearly there is the issue here so I am opening an SR at MOS (5 Feb, 2014)		 dbms_lob.fragment_insert(
lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
amount  IN            INTEGER,
offset  IN            INTEGER,
buffer  IN            VARCHAR2 CHARACTER SET lob_loc%CHARSET);
DECLARE
 dest_lob CLOB;
BEGIN
  SELECT clobcol
  INTO dest_lob
  FROM secure_file_tab
  WHERE rid = 1
  FOR UPDATE;

  dbms_output.put_line(dest_lob);
  dbms_lob.fragment_insert(dest_lob, 2, 3, 'Zz');
  dbms_output.put_line(dest_lob);
END;
/
AbCdEfGhIjKlMnOp
DECLARE
*
ERROR at line 1:
ORA-43856: Unsupported LOB type for SECUREFILE LOB operation
ORA-06512: at "SYS.DBMS_LOB", line 1169
ORA-06512: at line 11
 
FRAGMENT_MOVE
Moves the amount of bytes (BLOB) or characters (CLOB/NCLOB) from the given offset to the new offset specified

Overload 1		 dbms_lob.fragment_move(
lob_loc     IN OUT NOCOPY BLOB,
amount      IN            INTEGER,
src_offset  IN            INTEGER,
dest_offset IN            INTEGER);
TBD
Overload 2

Clearly there is the issue here so I am opening an SR at MOS (5 Feb, 2014)		 dbms_lob.fragment_move(
lob_loc     IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
amount      IN            INTEGER,
src_offset  IN            INTEGER,
dest_offset IN            INTEGER);
DECLARE
 dest_lob CLOB;
BEGIN
  SELECT clobcol
  INTO dest_lob
  FROM secure_file_tab
  WHERE rid = 1
  FOR UPDATE;

  dbms_output.put_line(dest_lob);
  dbms_lob.fragment_move(dest_lob, 1, 1, 1);
  dbms_output.put_line(dest_lob);
END;
/
AbCdEfGhIjKlMnOp
DECLARE
*
ERROR at line 1:
ORA-43856: Unsupported LOB type for SECUREFILE LOB operation
ORA-06512: at "SYS.DBMS_LOB", line 1207
ORA-06512: at line 11
 
FRAGMENT_REPLACE
Replaces the data at the given offset with the given data (not to exceed 32k)

Overload 1		 dbms_lob.fragment_replace(
lob_loc    IN OUT NOCOPY BLOB,
old_amount IN            INTEGER,
new_amount IN            INTEGER,
offset     IN            INTEGER,
buffer     IN            RAW);
TBD
Overload 2 dbms_lob.fragment_replace(
lob_loc    IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
old_amount IN            INTEGER,
new_amount IN            INTEGER,
offset     IN            INTEGER,
buffer     IN            VARCHAR2 CHARACTER SET lob_loc%CHARSET);
DECLARE
 dest_lob CLOB;
BEGIN
  SELECT clobcol
  INTO dest_lob
  FROM secure_file_tab
  WHERE rid = 1
  FOR UPDATE;

  dbms_output.put_line(dest_lob);
  dbms_lob.fragment_replace(dest_lob, 1, 1, 1, 'Zz');
  dbms_output.put_line(dest_lob);
END;
/
AbCdEfGhIjKlMnOp
DECLARE
*
ERROR at line 1:
ORA-43856: Unsupported LOB type for SECUREFILE LOB operation
ORA-06512: at "SYS.DBMS_LOB", line 1229
ORA-06512: at line 11
 
FREETEMPORARY
Frees the temporary BLOB or CLOB in the default temporary tablespace

Overload 1		 dbms_lob.freeTemporary(lob_loc IN OUT NOCOPY BLOB);
TBD
Overload 2 dbm_lob.freeTemporary(lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);
conn pm/pm@pdbdev

desc print_media

SELECT ad_sourcetext
FROM print_media
WHERE product_id = 2056;

set long 100000

SELECT ad_sourcetext
FROM print_media
WHERE product_id = 2056;

set serveroutput on

DECLARE
 clobvar CLOB;
BEGIN
  SELECT ad_sourcetext
  INTO clobvar
  FROM print_media
  WHERE product_id = 2056;

  dbms_output.put_line('1: ' || clobvar);
  dbms_lob.freeTemporary(clobvar);
  dbms_output.put_line('2: ' || clobvar);
END;
/
 
GETCHUNKSIZE
Returns the amount of space used in the LOB chunk to store the LOB value
Overload 1		 dbms_lob.getChunkSize(lob_loc IN BLOB)
RETURN INTEGER DETERMINISTIC;
TBD
Overload 2 dbms_lob.getChunkSize(lob_loc IN CLOB CHARACTER SET ANY_CS)
RETURN INTEGER DETERMINISTIC;
SELECT dbms_lob.getChunkSize('ABCDEFG')
FROM dual;
 
GETCONTENTTYPE
Returns the content ID string previously set by means of the SETCONTENTTYPE procedure
Overload 1		 dbms_lob.getContentType(lob_loc IN BLOB) RETURN VARCHAR2;
See SETCONTENTTYPE Demo Below
Overload 2

Clearly there is the issue here too so I am opening an SR at MOS (5 Feb, 2014)		 dbms_lob.getContentType(lob_loc IN CLOB CHARACTER SET ANY_CS)
RETURN VARCHAR2;
DECLARE
 dest_lob CLOB;
BEGIN
  SELECT clobcol
  INTO dest_lob
  FROM secure_file_tab
  WHERE rid = 1
  FOR UPDATE;

  dbms_output.put_line(dest_lob);
  dbms_output.put_line('-' || dbms_lob.getContentType(dest_lob) || '-');
END;
/
 
GETLENGTH
Gets the length of the LOB value

Overload 1		 dbms_lob.getLength(lob_loc IN BLOB) RETURN INTEGER;
conn pm/pm@pdbdev

desc print_media

SELECT dbms_lob.getLength(ad_photo)
FROM print_media;
Overload 2 dbms_lob.getLength(lob_loc IN CLOB CHARACTER SET ANY_CS)
RETURN INTEGER;
conn pm/pm@pdbdev

desc print_media

SELECT dbms_lob.getLength(ad_sourcetext)
FROM print_media;
Overload 3 dbms_lob.getLength(file_loc IN BFILE) RETURN INTEGER;
DECLARE
 src_file BFILE;
 dst_file BLOB;
 lgh_file BINARY_INTEGER;
BEGIN
  src_file := bfilename('CTEMP', 'myfile.txt');
  lgh_file := dbms_lob.getLength(src_file);
END;
/
 
GETOPTIONS
Obtains settings corresponding to the option_types field for a particular LOB

Overload 1		 dbms_lob.getOptions(
lob_loc      IN BLOB,
option_types IN PLS_INTEGER)
RETURN PLS_INTEGER;
See SECUREFILES Demo Below
Overload 2 dbms_lob.getOptions(
lob_loc      IN CLOB CHARACTER SET ANY_CS,
option_types IN PLS_INTEGER)
RETURN PLS_INTEGER;
TBD
 
GET_DBFS_LINK
Undocumented

Overload 1		 dbms_lob.get_dbfs_link( lob_loc IN BLOB)
RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(get_dbfs_link, WNDS, RNDS, WNPS, RNPS);
TBD
Overload 2 dbms_lob.get_dbfs_link( lob_loc IN CLOB CHARACTER SET ANY_CS)
RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(get_dbfs_link, WNDS, RNDS, WNPS, RNPS);
TBD
 
GET_DBFS_LINK_STATE
Undocumented

Overload 1		 dbms_lob.get_dbfs_link_state(
lob_loc      IN  BLOB,
storage_path OUT VARCHAR2,
state        OUT PLS_INTEGER,
cached       OUT BOOLEAN);
TBD
Overload 2 dbms_lob.get_dbfs_link_state(
lob_loc      IN  CLOB CHARACTER SET ANY_CS,
storage_path OUT VARCHAR2,
state        OUT PLS_INTEGER,
cached       OUT BOOLEAN);
TBD
 
GET_DEDUPLICATE_REGIONS
The output is a collection of records identifying the deduplicated regions in a LOB
Overload 1		 dbms_lob.get_deduplicate_regions(
lob_loc      IN            BLOB,
region_table IN OUT NOCOPY BLOB_DEDUPLICATE_REGION_TAB);
TBD
Overload 2 dbms_lob.get_deduplicate_regions(
lob_loc      IN            CLOB CHARACTER SET ANY_CS,
region_table IN OUT NOCOPY CLOB_DEDUPLICATE_REGION_TAB);
DECLARE
 dest_lob CLOB;
 cdr_tab  dbms_lob.clob_deduplicate_region_tab;
BEGIN
  SELECT clobcol
  INTO dest_lob
  FROM secure_file_tab
  WHERE rid = 2;

  dbms_lob.get_deduplicate_regions(dest_lob,  cdr_tab);
  dbms_output.put_line(TO_CHAR(cdr_tab.COUNT));
END;
/
 
GET_STORAGE_LIMIT
Returns the storage limit for LOBs in your database configuration

Overload 1		 dbms_lob.get_storage_limit(lob_loc IN CLOB CHARACTER SET ANY_CS)
RETURN INTEGER DETERMINISTIC;
conn pm/pm@pdbdev

desc print_media

SELECT dbms_lob.get_storage_limit(ad_sourcetext)
FROM print_media;
Overload 2 dbms_lob.get_storage_limit(lob_loc IN BLOB)
RETURN INTEGER DETERMINISTIC;
conn pm/pm@pdbdev

desc print_media

SELECT dbms_lob.get_storage_limit(ad_photo)
FROM print_media;
 
INSTR
Returns the matching position of the nth occurrence of the pattern in the LOB

Overload 1		 dbms_lob.instr(
lob_loc IN BLOB,
pattern IN RAW,
offset  IN INTEGER := 1,
nth     IN INTEGER := 1)
RETURN INTEGER;
TBD
Overload 2 dbms_lob.instr(
lob_loc IN CLOB CHARACTER SET ANY_CS,
pattern IN VARCHAR2 CHARACTER SET lob_loc%CHARSET,
offset  IN INTEGER := 1,
nth     IN INTEGER := 1)
RETURN INTEGER;
conn pm/pm@pdbdev

SELECT dbms_lob.getlength(ad_sourcetext), dbms_lob.instr(ad_sourcetext, 'A')
FROM print_media;

SELECT dbms_lob.getlength(ad_sourcetext), dbms_lob.instr(ad_sourcetext, 'E')
FROM print_media;
Overload 3 dbms_lob.instr(
file_loc IN BFILE,
pattern  IN RAW,
offset   IN INTEGER := 1,
nth      IN INTEGER := 1)
RETURN INTEGER;
TBD
 
ISOPEN
Checks to see if the LOB was already opened using the input locator returning 1 if open, 0 if closed
Overload 1		 dbms_lob.isopen(lob_loc IN BLOB) RETURN INTEGER;
TBD
Overload 2 dbms_lob.isOpen(lob_loc IN CLOB CHARACTER SET ANY_CS)
RETURN INTEGER;
See CREATETEMPORARY Demo Above
Overload 3 dbms_lob.isOopen(file_loc IN BFILE) RETURN INTEGER;
TBD
 
ISREMOTE
Accepts a LOB and returns TRUE for remote obtained over a database link; otherwise FALSE. Raises an exception for empty and uninitialized locators.

Overload 1		 dbms_lob.isremote(lob_loc IN BLOB)
RETURN BOOLEAN;
PRAGMA RESTRICT_REFERENCES(isremote, WNDS, RNDS, WNPS, RNPS);
TBD
Overload 2 dbms_lob.isremote(lob_loc IN CLOB CHARACTER SET ANY_CS)
RETURN BOOLEAN;
PRAGMA RESTRICT_REFERENCES(isremote, WNDS, RNDS, WNPS, RNPS);
DECLARE
 cVal CLOB := 'This is a test of ISREMOTE';
BEGIN
  IF dbms_lob.isRemote(cVal) THEN
    dbms_output.put_line('Is Remote');
  ELSE
    dbms_output.put_line('Is Not Remote');
  END IF;
END;
/
 
ISSECUREFILE
Returns TRUE is a LOB has been stored in an encrypted SECUREFILE
Overload 1		 dbms_lob.isSecurefile(lob_loc IN BLOB) RETURN BOOLEAN;
See the SECUREFILEs Demo Below
Overload 2 dbms_lob.isSecurefile(lob_loc IN CLOB CHARACTER SET ANY_CS)
RETURN BOOLEAN;
TBD
 
ISTEMPORARY
Checks if the locator is pointing to a temporary LOB
Overload 1		 dbms_lob.isTemporary(lob_loc IN BLOB) RETURN INTEGER;
TBD
Overload 2 dbms_lob.isTemporary(lob_loc IN CLOB CHARACTER SET ANY_CS)
RETURN INTEGER;
DECLARE
 dest_lob CLOB;
 retVal   PLS_INTEGER;
BEGIN
  SELECT clobcol
  INTO dest_lob
  FROM secure_file_tab
  WHERE rid = 1
  FOR UPDATE;

  dbms_output.put_line(dest_lob);
  retVal := dbms_lob.isTemporary(dest_lob);
  dbms_output.put_line(TO_CHAR(retVal));
END;
/
 
LOADBLOBFROMFILE
Loads BFILE data into an internal BLOB dbms_lob.loadblobfromfile(
dest_lob    IN OUT NOCOPY BLOB,
src_bfile   IN            BFILE,
amount      IN            INTEGER,
dest_offset IN OUT        INTEGER,
src_offset  IN OUT        INTEGER);
TBD
 
LOADCLOBFROMFILE
Loads BFILE data into an internal CLOB dbms_lob.loadclobfromfile(
dest_lob     IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
src_bfile    IN            BFILE,
amount       IN            INTEGER,
dest_offset  IN OUT        INTEGER,
src_offset   IN OUT        INTEGER,
bfile_csid   IN            NUMBER,
lang_context IN OUT        INTEGER,
warning         OUT        INTEGER);
TBD
 
LOADFROMFILE
Loads BFILE data into an internal LOB

Overload 1		 dbms_lob.loadfromfile(
dest_lob    IN OUT NOCOPY BLOB,
src_lob     IN     BFILE,
amount      IN     INTEGER,
dest_offset IN     INTEGER := 1,
src_offset  IN     INTEGER := 1);
See SETCONTENTTYPE Demo Below
Overload 2 dbms_lob.loadfromfile(
dest_lob    IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
src_lob     IN            BFILE,
amount      IN            INTEGER,
dest_offset IN            INTEGER := 1,
src_offset  IN            INTEGER := 1);
See OPEN Overload 3 Demo Below
 
MOVE_TO_DBFS_LINK
Undocumented

Overload 1		 dbms_lob.move_to_dbfs_link(
lob_loc      IN OUT BLOB,
storage_path IN     VARCHAR2,
flags        IN     BINARY_INTEGER := DBFS_LINK_NOCACHE);
TBD
Overload 2 dbms_lob.move_to_dbfs_link(
lob_loc      IN OUT CLOB CHARACTER SET ANY_CS,
storage_path IN     VARCHAR2,
flags        IN     BINARY_INTEGER := DBFS_LINK_NOCACHE);
TBD
 
OPEN
Opens a LOB (internal, external, or temporary) in the indicated mode

Overload 1		 dbms_lob.open(
lob_loc   IN OUT NOCOPY BLOB,
open_mode IN     BINARY_INTEGER);
TBD
Overload 2 dbms_lob.open(
lob_loc   IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
open_mode IN            BINARY_INTEGER);
See CREATETEMPORARY Demo Above
Overload 3 dbms_lob.open(
file_loc  IN OUT NOCOPY BFILE,
open_mode IN            BINARY_INTEGER := file_readonly);
DECLARE
 bfVar   BFILE;
 clobVar CLOB;
BEGIN
  bfVar := bFileName('CTEMP', 'emp.xml');
  dbms_lob.createTemporary(clobVar, cache=>TRUE);
  dbms_lob.open(bfVar, dbms_lob.lob_readonly);
  dbms_lob.loadFromFile(dest_lob => clobVar,
                        src_lob  => bfVar,
                        amount   => dbms_lob.getLength(bfVar));
  dbms_lob.close(bfVar);
END;
/
 
READ
Reads data from the LOB starting at the specified offset

Overload 1

This demo was created by Tom Starr and donated to the library.		 dbms_lob.read(
lob_loc IN            BLOB,
amount  IN OUT NOCOPY INTEGER,
offset  IN            INTEGER,
buffer     OUT        RAW);
set linesize 151
set serveroutput on

PROMPT 'Please enter a lead_id for which to display lead_payload: '

PROMPT 'Please enter Y if this LOB is compressed: '

DECLARE
 this_compress_status VARCHAR2(1);
 l_compress_status VARCHAR2(1) := '&&this_compress_status';
 this_lead_id VARCHAR2(40);
 my_lead_id VARCHAR2(40) := lower('&&this_lead_id');
 -- my_lead_id VARCHAR2(40) := 'f99a5bd0c54310048c6e00093d002886';

 cur                 INTEGER;
 x                   INTEGER;
 my_rowcount         INTEGER;
 my_error            NUMBER;
 l_blob              BLOB;
 l_compressed_blob   BLOB;
 l_uncompressed_blob BLOB;
 l_blob_amount       INTEGER := 2000;
 l_blob_buffer       VARCHAR2(4000);
 l_blob_pos          INTEGER := 1;
 my_query            VARCHAR2(1000);
BEGIN
  dbms_output.put_line('===========================================');
  dbms_output.put_line('This is the lead: ' || my_lead_id);

  -- initialize both BLOBs to something
  l_blob := TO_BLOB('1');
  l_compressed_blob := TO_BLOB('1');
  l_uncompressed_blob := TO_BLOB('1');

  -- create query
  my_query := 'select &&mylobcolumn from ';
  my_query := concat(my_query, concat(concat('&&myschema','.'),'&&mytable'));
  my_query := concat(my_query, ' where &&myidcolumn = :x');
  dbms_output.put_line('This is the query: '||my_query);

  cur := dbms_sql.open_cursor;
  dbms_sql.parse(cur, my_query, dbms_sql.native);
  dbms_sql.bind_variable(cur,'x',my_lead_id);
  dbms_sql.define_column(cur, 1, l_blob);

  my_rowcount := dbms_sql.execute_and_fetch(cur);
  dbms_output.put_line('Got this rowcount: '|| my_rowcount);

  dbms_sql.column_value(cur,1,l_blob);
  dbms_output.put_line('Got this compress status: ' || l_compress_status);

  IF (l_compress_status = 'Y') THEN
    -- uncompress the data
    dbms_output.put_line('Compressed Length : ' || LENGTH(l_compressed_blob));

    utl_compress.lz_uncompress (src => l_blob, dst => l_uncompressed_blob);
  ELSE
    l_uncompressed_blob := l_blob;
  END IF;

  LOOP
    dbms_lob.read(l_uncompressed_blob, l_blob_amount, l_blob_pos, l_blob_buffer);
    dbms_output.put_line(utl_raw.cast_to_varchar2(l_blob_buffer));
    l_blob_pos := l_blob_pos + l_blob_amount;
  END LOOP;
  dbms_sql.close_cursor(cur);
EXCEPTION
  WHEN no_data_found THEN
    dbms_sql.close_cursor(cur);
    dbms_output.put_line(' ===========================================');
    dbms_output.put_line('End of BLOB');
    dbms_output.put_line(' ===========================================');
    -- display lengths
    dbms_output.put_line('Compressed Length : ' || LENGTH(l_compressed_blob));
    dbms_output.put_line('Uncompressed Length: ' || LENGTH(l_uncompressed_blob));
    dbms_output.put_line('Note that the BLOB above is split at 2000 byte boundries');
END;
/
Overload 2 dbms_lob.read(
lob_loc IN            CLOB CHARACTER SET ANY_CS,
amount  IN OUT NOCOPY INTEGER,
offset  IN            INTEGER,
buffer     OUT        VARCHAR2 CHARACTER SET lob_loc%CHARSET);
TBD
Overload 3 dbms_lob.read(
file_loc IN            BFILE,
amount   IN OUT NOCOPY INTEGER,
offset   IN            INTEGER,
buffer      OUT        RAW);
TBD
 
SETCONTENTTYPE
Sets the content type string for the data in the BLOB

Overload 1		 dbms_lob.setContentType(
lob_loc     IN OUT NOCOPY BLOB,
contenttype IN            VARCHAR2);
CREATE TABLE sct (
rid   NUMBER(3),
bcol  BLOB)
LOB (bcol)
STORE AS SECUREFILE bcol (
TABLESPACE uwdata
RETENTION MIN 3600
KEEP_DUPLICATES NOCOMPRESS DECRYPT CACHE READS)
TABLESPACE uwdata;

set serveroutput on

DECLARE
 src_file BFILE;
 dst_file BLOB;
 lgh_file BINARY_INTEGER;
 retval   VARCHAR2(30);
BEGIN
  src_file := bfilename('CTEMP', 'sphere.mpg');

  INSERT INTO sct
  (rid, bcol)
  VALUES
  (1, EMPTY_BLOB())
  RETURNING bcol INTO dst_file;

  SELECT bcol
  INTO dst_file
  FROM sct
  WHERE rid = 1
  FOR UPDATE;

  dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
  lgh_file := dbms_lob.getlength(src_file);
  dbms_lob.loadFromFile(dst_file, src_file, lgh_file);

  UPDATE sct
  SET bcol = dst_file
  WHERE rid = 1;

  dbms_lob.setContentType(dst_file, 'MPG Movie');
  retval := dbms_lob.getContentType(dst_file);
  dbms_output.put_line(retval);

  dbms_lob.fileclose(src_file);
END load_file;
/
Overload 2 dbms_lob.setContentType(
lob_loc     IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
contenttype IN            VARCHAR2);
TBD
 
SETOPTIONS
Enables CSCE features on a per-LOB basis, overriding the default LOB column settings

Overload 1		 dbms_lob.setOptions(
lob_loc      IN OUT NOCOPY BLOB,
option_types IN     PLS_INTEGER,
options      IN     PLS_INTEGER);

Option Types
opt_compress 1
opt_encrypt 2
opt_deduplicate 4
 
Options
compress_off 0
compress_on 1
encrypt_off 0
encrypt_on 2
deduplicate_off 0
deduplicate_on 4
TBD
Overload 2 dbms_lob.setOptions(
lob_loc      IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
option_types IN            PLS_INTEGER,
options      IN            PLS_INTEGER);
CREATE TABLE lob_demo(
rid  NUMBER(5),
ccol CLOB)
TABLESPACE uwdata
LOB (ccol) STORE AS SECUREFILE clob_data (COMPRESS HIGH TABLESPACE uwdata);

INSERT INTO lob_demo values (1, 'this is a test');
COMMIT;

SELECT *
FROM lob_demo;

DECLARE
 l_clob CLOB;
BEGIN
  SELECT ccol
  INTO l_clob
  FROM lob_demo
  WHERE rid = 1
  FOR UPDATE;

  dbms_output.put_line('Before');
  dbms_output.put_line('Compression : ' || dbms_lob.getoptions(l_clob,   dbms_lob.opt_compress));

  dbms_lob.setOptions(l_clob, dbms_lob.opt_compress, dbms_lob.compress_on);

  dbms_output.put_line('.....');
  dbms_output.put_line('After');
  dbms_output.put_line('Compression : ' || dbms_lob.getoptions(l_clob, dbms_lob.opt_compress));

  COMMIT;
END;
/
 
SET_DBFS_LINK
Undocumented

Overload 1		 dbms_lob.set_dbfs_link(
lob_loc      IN OUT BLOB,
storage_path IN     VARCHAR2);
TBD
Overload 2 dbms_lob.set_dbfs_link(
lob_loc      IN OUT CLOB CHARACTER SET ANY_CS,
storage_path IN     VARCHAR2);
TBD
 
SUBSTR
Returns part of the LOB value starting at the specified offset

Overload 1		 dbms_lob.substr(
lob_loc IN BLOB,
amount  IN INTEGER := 32767,
offset  IN INTEGER := 1)
RETURN RAW;
TBD
Overload 2 dbms_lob.substr(
lob_loc IN CLOB CHARACTER SET ANY_CS,
amount  IN INTEGER := 32767,
offset  IN INTEGER := 1)
RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET;
DECLARE
 dest_lob CLOB;
BEGIN
  SELECT clobcol
  INTO dest_lob
  FROM secure_file_tab
  WHERE rid = 1
  FOR UPDATE;

  dbms_output.put_line(dest_lob);
  dbms_output.put_line(dbms_lob.substr(dest_lob, 4, 2));
END;
/
Overload 3 dbms_lob.substr(
file_loc IN BFILE,
amount   IN INTEGER := 32767,
offset   IN INTEGER := 1)
RETURN RAW;
TBD
 
TRIM
Trims the LOB value to the specified shorter length

Overload 1		 dbms_lob.trim(
lob_loc IN OUT NOCOPY BLOB,
newlen  IN            INTEGER);
TBD
Overload 2 dbms_lob.trim(
lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
newlen  IN            INTEGER);
DECLARE
 dest_lob CLOB;
BEGIN
  SELECT clobcol
  INTO dest_lob
  FROM secure_file_tab
  WHERE rid = 1
  FOR UPDATE;

  dbms_output.put_line(dest_lob);
  dbms_lob.trim(dest_lob, 4);
  dbms_output.put_line(dest_lob);
END;
/
 
WRITE
Writes data to the LOB from a specified offset

Overload 1		 dbm_lob.write(
lob_loc IN OUT NOCOPY BLOB,
amount  IN            INTEGER,
offset  IN            INTEGER,
buffer  IN            RAW);
TBD
Overload 2 dbm_lob.write(
lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
amount  IN            INTEGER,
offset  IN            INTEGER,
buffer  IN            VARCHAR2 CHARACTER SET lob_loc%CHARSET);
DECLARE
 dest_lob CLOB;
BEGIN
  SELECT clobcol
  INTO dest_lob
  FROM secure_file_tab
  WHERE rid = 2
  FOR UPDATE;

  dbms_output.put_line(dest_lob);
  dbms_lob.write(dest_lob, 4, 6, 'zzzzzz');
  dbms_output.put_line(dest_lob);

  ROLLBACK;
END;
/
 
WRITEAPPEND
Writes a buffer to the end of a LOB

Overload 1		 dbms_lob.writeappend(
lob_loc IN OUT NOCOPY BLOB,
amount  IN            INTEGER,
buffer  IN            RAW);
TBD
Overload 2 dbms_lob.writeappend(
lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
amount  IN            INTEGER,
buffer  IN            VARCHAR2 CHARACTER SET lob_loc%CHARSET);
CREATE TABLE book (
bookid      NUMBER(5),
title       VARCHAR2(50),
description VARCHAR2(100));

INSERT INTO book
VALUES
(1, '12c Inovations', 'New Features in Oracle 12c');

CREATE TABLE author (
authorid    NUMBER(5),
author_name VARCHAR2(60));

INSERT INTO author
VALUES
(1, 'Daniel Morgan');

CREATE TABLE book_author_ie (
bookid   NUMBER(5),
authorid NUMBER(5));

INSERT INTO book_author_ie
SELECT bookid, authorid
FROM book, author;

CREATE OR REPLACE PROCEDURE xml_gen(cvar IN OUT NOCOPY CLOB) AUTHID DEFINER AS
 CURSOR c IS
 SELECT b.title, b.description, a.author_name
 FROM book b, author a, book_author_ie ie
 WHERE b.bookid = ie.bookid
 AND a.authorid = ie.authorid;
BEGIN
  FOR r IN c LOOP
    dbms_lob.writeappend(cvar, 19, '<root><book><title>');
    dbms_lob.writeappend(cvar, length(r.title), r.title);
    dbms_lob.writeappend(cvar, 14, '</title><desc>');
    dbms_lob.writeappend(cvar, length(r.description), r.description);
    dbms_lob.writeappend(cvar, 27, '</desc></book><author_name>');
    dbms_lob.writeappend(cvar, length(r.author_name), r.author_name);
    dbms_lob.writeappend(cvar, 21, '</author_name></root>');
  END LOOP;
END xml_gen;
/

set serveroutput on

DECLARE
 cvar CLOB := ' ';
BEGIN
  xml_gen(cvar);
  dbms_output.put_line(cvar);
END;
/
 
DBMS_LOB Demos
Blob Load Demo /* define the directory inside Oracle when logged on as SYS
CREATE R REPLACE DIRECTORY ctemp AS 'c:\temp\';

grant read on the directory to the Staging schema
grant read on directory ctemp to staging; */

-- the storage table for the image file
CREATE TABLE pdm (
dname  VARCHAR2(30),  -- directory name
sname  VARCHAR2(30),  -- subdirectory name
fname  VARCHAR2(30),  -- file name
iblob  BLOB);         -- image file

-- create the procedure to load the file
CREATE OR REPLACE PROCEDURE load_file (
 pdname VARCHAR2,
 psname VARCHAR2,
 pfname VARCHAR2) AUTHID DEFINER IS

 src_file BFILE;
 dst_file BLOB;
 lgh_file BINARY_INTEGER;
BEGIN
  src_file := bfilename('CTEMP', pfname);

  -- insert a NULL record to lock
  INSERT INTO pdm
  (dname, sname, fname, iblob)
  VALUES
  (pdname, psname, pfname, EMPTY_BLOB())
  RETURNING iblob INTO dst_file;

  -- lock record
  SELECT iblob
  INTO dst_file
  FROM pdm
  WHERE dname = pdname
  AND sname = psname
  AND fname = pfname
  FOR UPDATE;

  -- open the file
  dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

  -- determine length
  lgh_file := dbms_lob.getlength(src_file);

  -- read the file
  dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

  -- update the blob field
  UPDATE pdm
  SET iblob = dst_file
  WHERE dname = pdname
  AND sname = psname
  AND fname = pfname;

  -- close file
  dbms_lob.fileclose(src_file);
END load_file;
/
Save BLOB to File Demo How to save a BLOB to a file on disk in PL/SQL
From: Thomas Kyte

Use DBMS_LOB to read from the BLOB

You will need to create an external procedure to take binary data and write it to the operating system, the external procedure can be written in C. If it was CLOB data, you can use UTL_FILE to write it to the OS but UTL_FILE does not support the binary in a BLOB.

There are articles on MetaLink explaining how to do and it has a C program ready for compiling and the External Procedure stuff, i'd advise a visit.

Especially, look for Note:70110.1, Subject: WRITING BLOB/CLOB/BFILE CONTENTS TO A FILE USING EXTERNAL PROCEDURES

Here is the Oracle code cut and pasted from it. The outputstring procedure is the oracle procedure interface to the External procedure.
-------------------------------------

DECLARE
 i1    BLOB;
 len   NUMBER;
 my_vr RAW(10000);
 i2    NUMBER;
 i3    NUMBER := 10000;
BEGIN
  -- get the blob locator
  SELECT c2
  INTO i1
  FROM lob_tab
  WHERE c1 = 2;

  -- find the length of the blob column
  len := dbms_lob.getlength(i1);
  dbms_output.put_line('Column Length: ' || TO_CHAR(len));

  -- read 10000 bytes at a time
  i2 := 1;
  IF len < 10000 THEN
    -- if the col length is < 10000
    dbms_lob.read(i1, len, i2, my_vr);

    outputstring('p:\bfiles\ravi.bmp', rawtohex(my_vr), 'wb', 2*len);

    -- you have to convert the data to rawtohex format.
    -- directly sending the buffer data will not work
    -- That is the reason why we are sending the length as
    -- the double the size of the data read

    dbms_output.put_line('Read ' || to_char(len) || 'Bytes');
  ELSE
    -- if the col length is > 10000
    dbms_lob.read(i1, i3, i2, my_vr);

    outputstring('p:\bfiles\ravi.bmp', rawtohex(my_vr),'wb',2*i3);

    dbms_output.put_line('Read ' || TO_CHAR(i3) || ' Bytes ');
  END IF;

  i2 := i2 + 10000;

  WHILE (i2 < len )
  LOOP
    -- loop till entire data is fetched
    dbms_lob.read(i1, i3, i2, my_vr);

    dbms_output.put_line('Read ' || TO_CHAR(i3+i2-1) || ' Bytes');
    outputstring('p:\bfiles\ravi.bmp', rawtohex(my_vr), 'ab', 2*i3);

    i2 := i2 + 10000 ;
  END LOOP;
END;
/
Load from file demo CREATE OR REPLACE PROCEDURE read_file AUTHID CURRENT_USER IS
 src_file BFILE := bfilename('DOCUMENT_DIR', 'image.gif');
 dst_file BLOB;
 lgh_file BINARY_INTEGER;
BEGIN
  -- lock record
  SELECT bin_data
  INTO dst_file
  FROM db_image
  FOR update;

  -- open the file
  dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

  -- determine length
  lgh_file := dbms_lob.getlength(src_file);

  -- read the file
  dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

  -- update the blob field
  UPDATE db_image
  SET bin_data = dst_file;
  COMMIT;

  -- close file
  dbms_lob.fileclose(src_file);
EXCEPTION
  WHEN access_error THEN
    RAISE;
  WHEN invalid_argval THEN
    RAISE;
  WHEN invalid_directory THEN
    RAISE;
  WHEN no_data_found THEN
    RAISE;
  WHEN noexist_directory THEN
    RAISE;
  WHEN nopriv_directory THEN
    RAISE;
  WHEN open_toomany THEN
    RAISE;
  WHEN operation_failed THEN
    RAISE;
  WHEN unopened_file THEN
    RAISE;
  WHEN others THEN
    RAISE;
END read_file;
/
LOB Demo by Alberto Dell'Era /* I have already done my own tests and it doesn't. I can only retrieve 4000 as you already mentioned as opposed to the 64000 we're used to, but I think that this is a good trade off considering that we were doing almost 5000 queries at a time.

Perhaps you could consider tuning the temp tablespace extent size to retain the ability to fetch 64000 bytes. Consider this test case (9.2.0.5, 8k block size) */

CREATE TABLE don(x CLOB);

DECLARE
 l_clob clob;
BEGIN
  FOR i IN 1..10
  LOOP
    INSERT INTO don (x) VALUES (empty_clob())
    RETURNING x INTO l_clob;

    -- create a 400,000 bytes clob
    FOR i IN 1..100
    LOOP
      dbms_lob.append(l_clob, rpad ('*',4000,'*'));
    END LOOP;
  END LOOP;
END;
/

CREATE TEMPORARY TABLESPACE don_1024
TEMPFILE 'c:\temp\don_1024.dbf' SIZE 10M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1024k;

CREATE TEMPORARY TABLESPACE don_512
TEMPFILE 'c:\temp\don_512.dbf' SIZE 10M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 512k;

CREATE TEMPORARY TABLESPACE don_64
TEMPFILE 'c:\temp\don_64.dbf' SIZE 10M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 64k;

SELECT tablespace_name, initial_extent
FROM dba_tablespaces
WHERE tablespace_name LIKE ('DON%');

TABLESPACE_NAME INITIAL_EXTENT
--------------- --------------
DON_1024               1048576
DON_512                 524288
DON_64                   65536

ALTER USER uwclass TEMPORARY TABLESPACE don_1024;

-- you must exit and relog in to use the new temp tablespace

SELECT SUBSTR (x, 1, 64000) PIECE
FROM don;

SELECT COUNT(*)
FROM gv$temporary_lobs
WHERE sid = (
  SELECT sid FROM v$mystat WHERE rownum = 1);

COUNT(*)
--------
       1

-- even if we fetched 10 rows, we have only 1 temp clob at the end.

SELECT tablespace, segtype, blocks*8*1024 USED_BYTES
FROM gv$tempseg_usage
WHERE username = user;

TABLESPACE SEGTYPE     USED_BYTES
---------- ----------- ----------
DON_1024   LOB_DATA       1048576
DON_1024   LOB_INDEX      1048576

ALTER USER dellera TEMPORARY TABLESPACE don_512;

-- logout then in again

TABLESPACE SEGTYPE     USED_BYTES
---------- ----------- ----------
DON_512    LOB_DATA        524288
DON_512    LOB_INDEX       524288

ALTER USER dellera TEMPORARY TABLESPACE don_64;

-- logout then in again

TABLESPACE SEGTYPE     USED_BYTES
---------- ----------- ----------
DON_64     LOB_DATA        327680
DON_64     LOB_INDEX        65536


So by reducing the extent size we greatly reduce the space allocated to the temp lob_index. I don't know why the lob_data that should contain 64000 bytes stays to 327,680 for an extent size of 64K. Interestingly, if we select only 1 row:

SELECT SUBSTR(x, 1, 64000) PIECE
FROM don
WHERE rownum = 1;

TABLESPACE SEGTYPE     USED_BYTES
---------- ----------- ----------
DON_64     LOB_DATA        196608
DON_64     LOB_INDEX        65536

/* I don't know the reason for this. Perhaps temporary LOBs have a different (bigger) CHUNKSIZE and/or PCTVERSION or perhaps they are updated versus being 'truncated' and then inserted for each row fetched?

Obviously, changing the extent size may adversely affect sort-to-disk and hash-join-to-disk, etc, operations - even if, by using an LMT temp tablespace, the impact may (stress on *may*) be immaterial. */
Replaces All Code Occurrences Of A String With Another Within A CLOB -- 1) clob src - the CLOB source to be replaced.
-- 2) replace str - the string to be replaced.
-- 3) replace with - the replacement string.

CREATE OR REPLACE FUNCTION replaceClob(
srcClob IN CLOB,
replaceStr IN VARCHAR2,
replaceWith IN VARCHAR2)
RETURN CLOB AUTHID DEFINER IS
 vBuffer    VARCHAR2 (32767);
 l_amount   BINARY_INTEGER := 32767;
 l_pos      PLS_INTEGER := 1;
 l_clob_len PLS_INTEGER;
 newClob    CLOB := EMPTY_CLOB;
BEGIN
  -- initalize the new clob
  dbms_lob.createtemporary(newClob,TRUE);

  l_clob_len := dbms_lob.getlength(srcClob);

  WHILE l_pos < l_clob_len
  LOOP
    dbms_lob.read(srcClob, l_amount, l_pos, vBuffer);

    IF vBuffer IS NOT NULL THEN
      -- replace the text
      vBuffer := replace(vBuffer, replaceStr, replaceWith);
      -- write it to the new clob
      dbms_lob.writeappend(newClob, LENGTH(vBuffer), vBuffer);
    END IF;
    l_pos := l_pos + l_amount;
  END LOOP;

  RETURN newClob;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END replace_clob;
/

Related Topics
Advanced Compression
Built-in Functions
Built-in Packages
DBMS_COMPRESSION
DBMS_LOBUTIL
DBMS_SQL
Directories
LONG RAW to CLOB
SecureFiles
STANDARD
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx