Oracle內建包DBMS_LOB使用說明

摘自：網路

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, ''); 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; /