| 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, '');
<br /> <span style="color:#0000ff;">dbms_lob.writeappend</span>(cvar, length(r.title), r.title);
<br /> <span style="color:#0000ff;">dbms_lob.writeappend</span>(cvar, 14, '');
dbms_lob.writeappend(cvar, length(r.description), r.description);
dbms_lob.writeappend(cvar, 27, '');
dbms_lob.writeappend(cvar, length(r.author_name), r.author_name);
dbms_lob.writeappend(cvar, 21, '');
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:\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) 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 <>
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 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:\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 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;
/ |
發表文章
