CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_LOB
Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmslob.sql
First Available 8.0

Constants
Name Data Type Value
call PLS_INTEGER 12
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
 
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;

Dependencies
SELECT name
FROM dba_dependencies
WHERE referenced_name = 'DBMS_LOB'
UNION
SELECT referenced_name
FROM dba_dependencies
WHERE name = 'DBMS_LOB';

Exceptions
Error Code Reason
ORA-21560 The argument is expecting a non-null, valid value but the argument value passed in is null, invalid, or out of range
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-22288 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
Object Privileges Execute is granted to PUBLIC
 
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 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;

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 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;
/
 
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
Overload 3 dbms_lob.close(file_loc IN OUT NOCOPY BFILE);
TBD
 
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);
TBD
 
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
 
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);
DECLARE
 clobvar CLOB := EMPTY_CLOB;
 len     BINARY_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);
  dbms_lob.writeappend(clobvar, len, x);
  dbms_lob.close(clobvar);
END;
/

Overload 2
dbms_lob.createtemporary(
lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
cache   IN            BOOLEAN,
dur     IN            PLS_INTEGER := 10);
TBD
 
EMPTY_BLOB

Null BLOB
dbms_lob.empty_blob();
CREATE TABLE ebdemo (
fid   NUMBER(3),
iclob BLOB);

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

Null CLOB
dbms_lob.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);
exec dbms_lob.fileclose(src_file);
 
FILECLOSEALL
Closes all files opened with dbms_lob.file_open dbms_lob.filecloseall;
exec dbms_lob.fileclose;
 
FILEEXISTS
Determine whether a file exists dbms_lob.fileexists(file_loc IN BFILE) RETURN INTEGER;
TBD
 
FILEGETNAME
Returns the source filename and directory given a BFILE dbms_lob.filegetname(
file_loc  IN  BFILE,
dir_alias OUT VARCHAR2,
filename  OUT VARCHAR2);
TBD
 
FILEISOPEN
Checks if the file was opened using the input BFILE locators dbms_lob.fileisopen(file_loc IN BFILE) RETURN INTEGER;
TBD
 
FILEOPEN
Open a file for reading dbms_lob.fileopen(
file_loc  IN OUT NOCOPY BFILE,
open_mode IN            BINARY_INTEGER := file_readonly);
exec dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
 
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 dbms_lob.fragment_delete(
lob_loc IN OUT NOCOPY
CLOB CHARACTER SET ANY_CS,
amount  IN     INTEGER,
offset  IN     INTEGER);
TBD
 
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 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);
TBD
 
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 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);
TBD
 
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);
TBD
 
FREETEMPORARY

Frees the temporary BLOB or CLOB in the default temporary tablespace

Overload 1
dbms_lob.freetemporary(lob_loc IN OUT NOCOPY BLOB);
conn pm/pm

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;
/
Overload 2 dbm_lob.freetemporary(
lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);
TBD
 
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;
TBD
Overload 2 dbms_lob.getchunksize(lob_loc IN CLOB CHARACTER SET ANY_CS)
RETURN INTEGER;
TBD
 
GETLENGTH

Gets the length of the LOB value


Overload 1
dbms_lob.getlength(lob_loc IN BLOB) RETURN INTEGER;
conn pm/pm

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

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;
/
 
GET_DEDUPLICATE_REGIONS (new in 11g)
Undocumented

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);
TBD
 
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
Overload 2 dbms_lob.getoptions(
lob_loc      IN
CLOB CHARACTER SET ANY_CS,
option_types IN PLS_INTEGER)
RETURN PLS_INTEGER;
TBD
 
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;
conn pm/pm

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;
conn pm/pm

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

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

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;
TBD
Overload 3 dbms_lob.isopen(file_loc IN BFILE) RETURN INTEGER;
TBD
ISSECUREFILE (new in 11g)
Returns TRUE is a LOB has been stored in an encrypted SECUREFILE

Overload 1
dbms_lob.issecurefile(lob_loc IN BLOB) RETURN BOOLEAN;
See SECUREFILES demo
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;
TBD
 
LOADBLOBFROMFILE

Loads BFILE data into an internal BLOB
dbm_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
dbm_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);
exec dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
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);
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
Overload 3 dbms_lob.open(
file_loc  IN OUT NOCOPY BFILE,
open_mode IN     BINARY_INTEGER := file_readonly);
TBD
 
READ
Reads data from the LOB starting at the specified offset

Overload 1
dbms_lob.read(
lob_loc IN     BLOB,
amount  IN OUT NOCOPY INTEGER,
offset  IN     INTEGER,
buffer     OUT RAW);
TBD
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
 
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);
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;
TBD
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);
TBD
 
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);
TBD
 
WRITEAPPEND
Writes a buffer to the end of a LOB

Overload 1
dbm_lob.writeappend(
lob_loc IN OUT NOCOPY BLOB,
amount  IN     INTEGER,
buffer  IN     RAW);
TBD

Overload 2
dbm_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, '11g Inovations', 'New Features in Oracle 11g');

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) 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 or replace directory ctemp as 'c: emp\';

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) 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 <tkyte@oracle.com>

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 avi.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 avi.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 avi.bmp',
    rawtohex(my_vr),'ab',2*i3);

    i2 := i2 + 10000 ;
  END LOOP;
END;
/

Load from file demo
CREATE OR REPLACE PROCEDURE read_file 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

  WHEN invalid_argval THEN

  WHEN invalid_directory THEN

  WHEN no_data_found THEN

  WHEN noexist_directory THEN

  WHEN nopriv_directory THEN

  WHEN open_toomany THEN

  WHEN operation_failed THEN

  WHEN unopened_file THEN

  WHEN others THEN

END read_file;
/

LOB Demo by Alberto Dell'Era
>> Actually, 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: emp\don_1024.dbf' SIZE 10M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1024k;

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

CREATE TEMPORARY TABLESPACE don_64 
TEMPFILE 'c: emp\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 gv$mystat WHERE rownum = 1);

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

(Note: 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.

FUNCTION replaceClob (
srcClob IN CLOB,
replaceStr IN VARCHAR2,
replaceWith IN VARCHAR2)
RETURN CLOB 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
    RAISE;
END;
/
 
Related Topics
DBMS_SQL
Directories
SecureFiles
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [207 users online]    © 2010 psoug.org