 | Chapter 8 Managing Large Objects |  |
This section describes the programs available through the DBMS_LOB packages in several categories.
The following sections describe the programs in the DBMS_LOB package that perform operations on BFILE objects.
The FILEEXISTS function indicates whether the given BFILE locator points to a file that exists in the operating system. Here's the specification for this program:
FUNCTION DBMS_LOB.FILEEXISTS ( file_loc IN BFILE ) RETURN INTEGER;
The file_loc parameter is the name of the file locator. The function returns one of the following values:
Value | Description |
|---|---|
0 | The specified file does not exist |
1 | The specified file exists |
One of the following exceptions may be raised if the file_loc parameter contains an improper value (e.g., NULL):
DBMS_LOB.NOEXIST_DIRECTORY DBMS_LOB.NOPRIV_DIRECTORY DBMS_LOB.INVALID_DIRECTORY
The FILEEXISTS function asserts a purity level with the RESTRICT_REFERENCES pragma.
PRAGMA RESTRICT_REFERENCES (fileexists, WNDS, RNDS, WNPS, RNPS);
This block uses the FILEEXISTS function to see if chapter01.txt exists in the BOOK_TEXT directory:
DECLARE
book_file_loc BFILE := NULL;
book_file_exists BOOLEAN := FALSE;
BEGIN
book_file_loc := BFILENAME( 'BOOK_TEXT', 'chapter01.txt' );
book_file_exists := DBMS_LOB.FILEEXISTS( book_file_loc ) = 1;
IF book_file_exists
THEN
DBMS_OUTPUT.PUT_LINE
('chapter01.txt exists in BOOK_TEXT directory');
ELSE
DBMS_OUTPUT.PUT_LINE
('chapter01.txt does not exist in BOOK_TEXT directory');
END IF;
END;
/This is the output of the script:
chapter01.txt exists in BOOK_TEXT directory
The following example selects the file locator for chapter01.txt from the my_book_files table and checks to see if the file exists:
INSERT INTO my_book_files ( file_descr, book_file )
VALUES ('Chapter 1', BFILENAME('BOOK_TEXT', 'chapter01.txt') );
DECLARE
book_file_loc BFILE := NULL;
book_file_exists BOOLEAN := FALSE;
BEGIN
book_file_loc := book_file ('Chapter 1');
IF book_file_loc IS NOT NULL
THEN
book_file_exists := DBMS_LOB.FILEEXISTS( book_file_loc ) = 1;
END IF;
IF book_file_exists
THEN
DBMS_OUTPUT.PUT_LINE('Chapter 1 exists');
ELSE
DBMS_OUTPUT.PUT_LINE('Chapter 1 does not exist');
END IF;
END;
/This is the output of the script:
Chapter 1 exists
FILEEXISTS raises a VALUE_ERROR exception when passed a NULL file locator, so you should always include conditional logic and an exception section.
The next example raises the NOEXIST_DIRECTORY exception. This can occur if the directory alias does not exist, or if the user has not been granted READ privilege on the directory.
DECLARE
book_file_loc BFILE := NULL;
book_file_exists BOOLEAN := FALSE;
BEGIN
book_file_loc := BFILENAME( 'NON_EXISTENT_DIRECTORY', 'chapter01.txt' );
book_file_exists := DBMS_LOB.FILEEXISTS( book_file_loc ) = 1;
IF book_file_exists
THEN
DBMS_OUTPUT.PUT_LINE('chapter01.txt exists');
ELSE
DBMS_OUTPUT.PUT_LINE('chapter01.txt does not exist');
END IF;
END;
/Running this script results in this unhandled exception:
ORA-22285: non-existent directory or file for FILEEXISTS operation
If the directory exists and READ privileges have been granted to the user, but the specified file does not exist, FILEEXISTS returns zero.
DECLARE
book_file_loc BFILE := NULL;
BEGIN
book_file_loc := BFILENAME( 'BOOK_TEXT', 'non_existent_file.txt');
IF DBMS_LOB.FILEEXISTS( book_file_loc ) = 0
THEN
DBMS_OUTPUT.PUT_LINE('non_existent_file.txt does not exist');
END IF;
END;
/This script produces the following:
non_existent_file.txt does not exist
FILEEXISTS can be called from SQL, for example:
SELECT DBMS_LOB.FILEEXISTS ( BFILENAME ('BOOK_TEXT','chapter01.txt') ) fileexists
FROM DUAL;
FILEEXISTS
----------------
1Calls to FILEEXISTS should trap and handle the NOEXIST_DIRECTORY exception (directory alias does not exist) and the VALUE_ERROR exception (input file locator is NULL).
Given a file locator, the FILEGETNAME procedure determines its associated directory alias and filename. The specification for this program follows:
PROCEDURE DBMS_LOB.FILEGETNAME
( file_loc IN BFILE,
dir_alias OUT VARCHAR2,
filename OUT VARCHAR2 );Parameters are summarized in the following table.
Parameter | Description |
|---|---|
file_loc | File locator |
dir_alias | Directory alias for the file locator |
filename | File name for the file locator |
The following VALUE_ERROR exception is raised if the file_loc parameter contains an improper value (e.g., NULL):
INVALID_ARGVAL
The following example uses FILEGETNAME to get the directory alias and filename for the "Chapter 1" row in the my_book_files table:
INSERT INTO my_book_files (file_descr, book_file)
VALUES ( 'Chapter 1', BFILENAME('BOOK_TEXT', 'chapter01.txt') );
DECLARE
book_file_exists BOOLEAN := FALSE;
book_file_loc BFILE := NULL;
book_file_dir VARCHAR2(30) := NULL;
book_file_name VARCHAR2(2000) := NULL;
BEGIN
book_file_loc := book_file ('Chapter 1');
IF book_file_loc IS NOT NULL
THEN
book_file_exists := DBMS_LOB.FILEEXISTS( book_file_loc ) = 1;
END IF;
IF book_file_exists
THEN
DBMS_LOB.FILEGETNAME
(book_file_loc, book_file_dir, book_file_name);
DBMS_OUTPUT.PUT_LINE ('File name is: ' || book_file_name);
DBMS_OUTPUT.PUT_LINE
('File is in Oracle directory: ' || book_file_dir);
ELSE
DBMS_OUTPUT.PUT_LINE('Chapter 1 does not exist');
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('OTHERS Exception ' || sqlerrm );
END;
/This is the output of the script:
File name is: chapter01.txt File is in Oracle directory: BOOK_TEXT
FILEGETNAME raises a VALUE_ERROR exception when passed a NULL file locator, so be sure to check the value of the file locator and/or include an exception handler.
Note that FILEGETNAME does not actually confirm that the physical file and directory alias exist. This can be done via FILEEXISTS.
Given a file locator, the FILEOPEN procedure opens the BFILE for read-only access. Here's the header for this program:
PROCEDURE DBMS_LOB.FILEOPEN
( file_loc IN OUT BFILE,
open_mode IN BINARY_INTEGER := FILE_READONLY )Parameters are summarized in the following table.
Parameter | Purpose |
|---|---|
file_loc | File locator for the file to be opened |
open_mode | Indicates that file access will be read-only; this parameter can be omitted from calls to FILEOPEN because the program assigns a default value of FILE_READONLY |
The following types of exceptions can be raised by the FILEOPEN procedure:
The directory alias associated with file_loc does not exist.
The number of open files exceeds the SESSION_MAX_OPEN_FILES limit.
The open_mode value is not FILE_READONLY.
The file does not exist or the user does not have privileges to access the file.
The file_loc parameter contains an improper value (e.g., NULL).
This example uses the FILEOPEN procedure to check whether chapter01.txt exists in the BOOK_TEXT directory, then opens and closes the file:
DECLARE
book_file_loc BFILE := NULL;
book_file_exists BOOLEAN := FALSE;
BEGIN
book_file_loc := BFILENAME('BOOK_TEXT','chapter01.txt');
book_file_exists := DBMS_LOB.FILEEXISTS( book_file_loc ) = 1;
IF book_file_exists
THEN
DBMS_OUTPUT.PUT_LINE('chapter01.txt exists');
DBMS_OUTPUT.PUT_LINE('opening the file');
DBMS_LOB.FILEOPEN( book_file_loc );
DBMS_OUTPUT.PUT_LINE('closing the file');
DBMS_LOB.FILECLOSE( book_file_loc );
END IF;
END;
/This is the output of the script:
chapter01.txt exists opening the file closing the file
The FILEOPEN procedure raises a VALUE_ERROR exception when passed to a NULL file. The procedure raises a NOEXIST_DIRECTORY exception when passed to a file locator associated with a nonexistent directory alias.
Note that it is possible to open the same file using two different file locators, for example:
DECLARE
book_file_loc_1 BFILE := NULL;
book_file_loc_2 BFILE := NULL;
book_file_exists BOOLEAN := FALSE;
BEGIN
book_file_loc_1 := BFILENAME('BOOK_TEXT','chapter01.txt');
book_file_loc_2 := BFILENAME('BOOK_TEXT','chapter01.txt');
book_file_exists := DBMS_LOB.FILEEXISTS( book_file_loc_1 ) = 1;
IF book_file_exists
THEN
DBMS_OUTPUT.PUT_LINE('chapter01.txt exists');
DBMS_OUTPUT.PUT_LINE('opening the file via loc_1');
DBMS_LOB.FILEOPEN( book_file_loc_1 );
DBMS_OUTPUT.PUT_LINE('opening the file via loc_2');
DBMS_LOB.FILEOPEN( book_file_loc_2 );
DBMS_OUTPUT.PUT_LINE('closing the file via loc_1');
DBMS_LOB.FILECLOSE( book_file_loc_1 );
DBMS_OUTPUT.PUT_LINE('closing the file via loc_2');
DBMS_LOB.FILECLOSE( book_file_loc_2 );
END IF;
END;
/This is the output of the script:
chapter01.txt exists opening the file via loc_1 opening the file via loc_2 closing the file via loc_1 closing the file via loc_2
To avoid exceeding the SESSION_MAX_OPEN_FILES limit, include a matching call to the FILECLOSE procedure for each BFILE that is opened. When an exception occurs after opening a file, it is possible that execution continues without closing the file (i.e., the matching call to FILECLOSE is not executed due to abnormal termination). In this case, the file remains open, and we run the risk of exceeding the SESSION_MAX_OPEN_FILES limit. It is good practice to include a call to the FILECLOSEALL procedure within an exception handler whenever FILEOPEN is used. See the "Section 8.3.1.6, "The DBMS_LOB.FILECLOSEALL procedure"" section for details.
The FILEISOPEN function indicates whether the file was opened via the input file locator given by file_loc. The header for this program is,
FUNCTION DBMS_LOB.FILEISOPEN ( file_loc IN BFILE ) RETURN INTEGER;
where file_loc is the file locator for the file to be opened. The function returns one of the following values:
Value | Description |
|---|---|
0 | The file is not open via the given file locator. |
1 | The file is open via the given file locator. |
The FILEISOPEN function will raise the VALUE_ERROR exception if the file_loc parameter contains an improper value (e.g., NULL).
The program asserts a purity level with the RESTRICT_REFERENCES pragma.
PRAGMA RESTRICT_REFERENCES (fileisopen, WNDS, RNDS, WNPS, RNPS);
The following example uses the FILEISOPEN function to check whether chapter01.txt in the BOOK_TEXT directory is open. It opens the file if it is not already open.
DECLARE
book_file_loc BFILE := NULL;
book_file_exists BOOLEAN := FALSE;
BEGIN
book_file_loc := BFILENAME('BOOK_TEXT','chapter01.txt');
book_file_exists := DBMS_LOB.FILEEXISTS( book_file_loc ) = 1;
IF book_file_exists
THEN
DBMS_OUTPUT.PUT_LINE('chapter01.txt exists');
IF DBMS_LOB.FILEISOPEN( book_file_loc) = 1
THEN
DBMS_OUTPUT.PUT_LINE('file is open');
ELSE
DBMS_OUTPUT.PUT_LINE('file is not open');
DBMS_OUTPUT.PUT_LINE('opening the file');
DBMS_LOB.FILEOPEN( book_file_loc );
IF DBMS_LOB.FILEISOPEN( book_file_loc) = 1
THEN
DBMS_OUTPUT.PUT_LINE('file is open');
DBMS_OUTPUT.PUT_LINE('closing the file');
DBMS_LOB.FILECLOSE( book_file_loc );
END IF;
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('OTHERS Exception ' || sqlerrm );
END;
/This is the output of the script:
chapter01.txt exists file is not open opening the file file is open closing the file
The next example assigns two file locators to the same file, chapter01.txt, in the BOOK_TEXT directory. It opens the file using the first locator. When called with the first locator, FILEISOPEN indicates that the file is open. However, FILEISOPEN indicates that the file is not open when called with the second locator. Hence, FILEISOPEN indicates whether a file is open with respect to a specific locator.
DECLARE
book_file_loc_1 BFILE := NULL;
book_file_loc_2 BFILE := NULL;
book_file_exists BOOLEAN := FALSE;
PROCEDURE check_open (loc IN BFILE, descr IN VARCHAR2)
IS
BEGIN
IF DBMS_LOB.FILEISOPEN (loc) = 1
THEN
DBMS_OUTPUT.PUT_LINE ('file is open via ' || descr);
ELSE
DBMS_OUTPUT.PUT_LINE ('file is not open via ' || descr);
END IF;
END
BEGIN
book_file_loc_1 := BFILENAME ('BOOK_TEXT', 'chapter01.txt');
book_file_loc_2 := BFILENAME ('BOOK_TEXT', 'chapter01.txt');
book_file_exists := DBMS_LOB.FILEEXISTS( book_file_loc_1 ) = 1;
IF book_file_exists
THEN
DBMS_OUTPUT.PUT_LINE('chapter01.txt exists');
IF DBMS_LOB.FILEISOPEN( book_file_loc_1 ) = 1
THEN
DBMS_OUTPUT.PUT_LINE('file is open via loc_1');
ELSE
DBMS_OUTPUT.PUT_LINE('file is not open via loc_1');
DBMS_OUTPUT.PUT_LINE('opening the file via loc_1');
DBMS_LOB.FILEOPEN( book_file_loc_1 );
check_open (book_file_loc_1, 'loc_1');
check_open (book_file_loc_2, 'loc_2');
DBMS_OUTPUT.PUT_LINE('closing the file via loc_1');
DBMS_LOB.FILECLOSE( book_file_loc_1 );
END IF;
END IF;
END;
/This is the output of the script:
chapter01.txt exists file is not open via loc_1 opening the file via loc_1 file is open via loc_1 file is not open via loc_2 closing the file via loc_1
FILEISOPEN raises a VALUE_ERROR exception when passed a NULL file locator. On the other hand, FILEISOPEN does not raise an exception when passed a file locator having a nonexistent directory alias or nonexistent file.
The FILECLOSE procedure is used to close a file that has been opened via the input file locator indicated by the file_loc parameter. The header for this program is,
PROCEDURE DBMS_LOB.FILECLOSE ( file_loc IN OUT BFILE );
where file_loc is the file locator for the file to be opened.
The FILECLOSE procedure may raise a VALUE_ERROR exception if the file_loc parameter contains an improper value (e.g., NULL).
The following example demonstrates that FILECLOSE can be called with a locator for a file that has not been opened:
DECLARE
book_file_loc BFILE := NULL;
book_file_exists BOOLEAN := FALSE;
BEGIN
book_file_loc := BFILENAME('BOOK_TEXT','chapter01.txt');
book_file_exists := DBMS_LOB.FILEEXISTS( book_file_loc ) = 1;
IF book_file_exists
THEN
DBMS_OUTPUT.PUT_LINE('chapter01.txt exists');
IF DBMS_LOB.FILEISOPEN( book_file_loc ) = 1
THEN
DBMS_OUTPUT.PUT_LINE('file is open');
ELSE
DBMS_OUTPUT.PUT_LINE('file is not open');
END IF;
DBMS_OUTPUT.PUT_LINE('closing the file');
DBMS_LOB.FILECLOSE( book_file_loc );
END IF;
END;
/This is the output of the script:
chapter01.txt exists file is not open closing the file
The FILECLOSE procedure can be called with a locator for a nonexistent file or directory, for example:
DECLARE
book_file_loc BFILE := NULL;
BEGIN
book_file_loc :=
BFILENAME('NON_EXISTENT_DIRECTORY','non_existent_file.txt');
DBMS_OUTPUT.PUT_LINE
('closing non_existent_file.txt in NON_EXISTENT_DIRECTORY');
DBMS_LOB.FILECLOSE( book_file_loc );
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('OTHERS Exception ' || sqlerrm );
END;
/This is the output of the script:
closing non_existent_file.txt in NON_EXISTENT_DIRECTORY
FILECLOSE raises a VALUE_ERROR exception when passed a NULL file locator.
See the FILEOPEN and FILECLOSEALL sections for other examples of FILECLOSE usage.
The FILECLOSEALL procedure is used to close all BFILEs that are open within a session. Here's the header for this program:
PROCEDURE DBMS_LOB.FILECLOSEALL;
The FILECLOSEALL procedure raises an UNOPENED_FILE exception if no files are open.
When an exception occurs after opening a file, it is possible that execution continues without closing the file (i.e., a matching call to FILECLOSE is not executed due to abnormal termination of a block). In this case, the file remains open, and we run the risk of exceeding the SESSION_MAX_OPEN_FILES limit. It is good practice to include a call to FILECLOSEALL within an exception handler whenever FILEOPEN is used.
The following anonymous block shows how you might construct an exception section that will close any open files:
DECLARE
book_file_loc BFILE := NULL;
book_file_exists BOOLEAN := FALSE;
x NUMBER;
BEGIN
book_file_loc := BFILENAME('BOOK_TEXT','chapter01.txt');
book_file_exists := DBMS_LOB.FILEEXISTS( book_file_loc ) = 1;
IF book_file_exists
THEN
DBMS_OUTPUT.PUT_LINE ('opening chapter01.txt');
DBMS_LOB.FILEOPEN (book_file_loc);
/* Intentionally raise a ZERO_DIVIDE exception */
x := 1 / 0;
DBMS_LOB.FILECLOSE( book_file_loc );
END IF;
EXCEPTION
WHEN OTHERS
THEN
BEGIN
DBMS_OUTPUT.PUT_LINE ('Clean up using FILECLOSEALL');
DBMS_LOB.FILECLOSEALL;
EXCEPTION
WHEN DBMS_LOB.UNOPENED_FILE
THEN
DBMS_OUTPUT.PUT_LINE
('No files to close, raising the UNOPENED_FILE exception.');
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('OTHERS Exception ' || sqlerrm );
END;
END;
/This is the output of the script:
opening chapter01.txt Clean up using FILECLOSEALL
The LOADFROMFILE procedure is used to load all or part of a external LOB (source BFILE) to a destination internal LOB. This is the procedure used to load binary data stored in operating system files into internal LOBs, which reside in the database. The specification for this program is overloaded as follows:
PROCEDURE DBMS_LOB.LOADFROMFILE
(dest_lob IN OUT BLOB | CLOB CHARACTER SET ANY_CS,
src_lob IN BFILE,
amount IN INTEGER,
dest_offset IN INTEGER := 1,
src_offset IN INTEGER := 1);The overloaded specification allows LOADFROMFILE to be used with BLOBs or CLOBs. The clause ANY_CS in the second specification allows acceptance of either CLOB or NCLOB locators as input.
Parameters are summarized in the following table.
Parameter | Description |
|---|---|
dest_lob | Locator for the destination internal LOB |
src_lob | File locator for the source external LOB |
amount | Number of bytes to copy from the source BFILE |
dest_offset | Location of the byte (BLOB) or character (CLOB, NCLOB) in the destination LOB at which the copy operation begins; the default value is 1 |
src_offset | Location of the byte in the source BFILE at which the load operation begins; the default value is 1 |
The LOADFROMFILE procedure raises a VALUE_ERROR exception if dest_lob, src_lob, or amount are NULL or invalid.
An INVALID_ARGVAL exception is raised if any of the following conditions are true:
src_offset < 1 or src_offset > LOBMAXSIZE
dest_offset < 1 or dest_offset > LOBMAXSIZE
amount < 1 or amount > LOBMAXSIZE
LOADFROMFILE raises the ORA-22993 exception (specified input amount is greater than actual source amount) if the end of the source BFILE is reached before the specified amount of bytes has been copied.
The following example loads the CLOB chapter_text column of the my_book_text table with the contents of the first 100 bytes of the file chapter01.txt in the BOOK_TEXT directory.
Note that the update of the chapter_text column occurs without the issue of an UPDATE statement. LOADFROMFILE accomplishes this via the chapter_text locator, which has been selected FOR UPDATE. LOB locators and DBMS_LOB allow changes to LOB columns in Oracle tables without issuing INSERT or UPDATE statements.
If you want to take this approach, you must lock the row that contains the LOB prior to modification. The best way to obtain this lock is to use the FOR UPDATE clause in the SELECT statement (in this example, this translates to calling the book_text_forupdate function):
INSERT INTO my_book_text (chapter_descr, chapter_text)
VALUES ('Chapter 1', EMPTY_CLOB());
COMMIT;
DECLARE
v_text_loc CLOB;
v_file_loc BFILE;
BEGIN
v_text_loc := book_text_forupdate ('Chapter 1');
v_file_loc := BFILENAME('BOOK_TEXT','chapter01.txt');
DBMS_LOB.LOADFROMFILE (v_text_loc, v_file_loc, 100);
COMMIT;
END;
/
SET LONG 100
COL chapter_descr FOR A15
COL chapter_text FOR A40 WORD_WRAPPED
SELECT chapter_descr, chapter_text
FROM my_book_text
WHERE chapter_descr = 'Chapter 1';This is the output of the script:
CHAPTER_DESCR CHAPTER_TEXT
--------------- ----------------------------------------
Chapter 1 It was a dark and stormy night.
Suddenly a scream rang out. An
EXCEPTION had not been handled.The next example also loads the CLOB chapter_text column of the my_book_text table with the contents of the first 100 bytes of the file chapter01.txt in the BOOK_TEXT directory. This time, the LOB locator is not selected FOR UPDATE, but has been returned via the RETURNING clause.
DECLARE
v_text_loc CLOB;
v_file_loc BFILE;
BEGIN
INSERT INTO my_book_text (chapter_descr, chapter_text)
VALUES ('Chapter 1', EMPTY_CLOB )
RETURNING chapter_text INTO v_text_loc;
v_file_loc := BFILENAME('BOOK_TEXT','chapter01.txt');
DBMS_LOB.LOADFROMFILE(v_text_loc, v_file_loc, 100);
COMMIT;
END;
/
SET LONG 100
COL chapter_descr FOR A15
COL chapter_text FOR A40 WORD_WRAPPED
SELECT chapter_descr, chapter_text
FROM my_book_text
WHERE chapter_descr = 'Chapter 1';This is the output of the script:
CHAPTER_DESCR CHAPTER_TEXT
--------------- ----------------------------------------
Chapter 1 It was a dark and stormy night.
Suddenly a scream rang out. An
EXCEPTION had not been handled.This example loads the BLOB diagram column of the by_book_diagrams table with the contents of the file ch01_01.bmp in the IMAGES directory. The LOB locator has been returned via the RETURNING clause.
Note that the update of the diagram column occurs without the issue of an UPDATE statement. LOADFROMFILE accomplishes this via the diagram locator, which has been returned by the RETURNING clause. LOB locators and DBMS_LOB allow changes to LOB columns in Oracle tables without issuing INSERT or UPDATE statements.
DECLARE
v_file_loc BFILE;
v_diagram_loc BLOB;
v_diagram_size INTEGER;
BEGIN
v_file_loc := BFILENAME('IMAGES','ch01_01.bmp');
v_diagram_size := DBMS_LOB.GETLENGTH(v_file_loc);
DBMS_OUTPUT.PUT_LINE('Diagram size: ' || v_diagram_size);
DBMS_OUTPUT.PUT_LINE('Inserting Empty Diagram Row');
INSERT INTO my_book_diagrams (chapter_descr, diagram_no, diagram)
VALUES ( 'Chapter 1', 1, EMPTY_BLOB )
RETURNING diagram INTO v_diagram_loc;
DBMS_OUTPUT.PUT_LINE('Loading Diagram From File');
DBMS_LOB.LOADFROMFILE(v_diagram_loc, v_file_loc, v_diagram_size);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('OTHERS Exception ' || sqlerrm);
END;
/
SELECT chapter_descr,
diagram_no,
dbms_lob.getlength(diagram) diagram_size
FROM my_book_diagrams
WHERE chapter_descr = 'Chapter 1';This script produces the following:
Diagram size: 481078 Inserting Empty Diagram Row Loading Diagram From File CHAPTER_DESCR DIAGRAM_NO DIAGRAM_SIZE --------------- ---------- ------------ Chapter 1 1 481078
The following sections describe the programs in the DBMS_LOB package that are used to read and examine LOBs.
The COMPARE function is used to compare two LOBs that are of the same type. Parts of LOBs can also be compared. The specification for this program takes the following forms for each LOB type that may be compared:
FUNCTION DBMS_LOB.COMPARE
(lob_1 IN BLOB | CLOB CHARACTER SET ANY_CS,
lob_2 IN BLOB | CLOB CHARACTER SET ANY_CS,
amount IN INTEGER := 4294967295,
offset_1 IN INTEGER := 1,
offset_2 IN INTEGER := 1)
RETURN INTEGER;
FUNCTION DBMS_LOB.COMPARE
(lob_1 IN BFILE,
lob_2 IN BFILE,
amount IN INTEGER,
offset_1 IN INTEGER := 1,
offset_2 IN INTEGER := 1)
RETURN INTEGER;The overloaded specification allows COMPARE to be used with all types of LOBs. The clause ANY_CS in the specification allows either CLOB or NCLOB locators as input.
Parameters are summarized in the following table.
Parameter | Description |
|---|---|
lob_1 | Locator for the first LOB to be compared |
lob_2 | Locator for the second LOB to be compared |
amount | Number of bytes (BFILE, BLOB) or characters (CLOB, NCLOB) to compare |
offset_1 | Location of the byte (BFILE, BLOB) or character (CLOB, NCLOB) in the first LOB at which the comparison begins; the default value is 1 |
offset_2 | Location of the byte (BFILE, BLOB) or character (CLOB, NCLOB) in the second LOB at which the comparison begins; the default value is 1 |
The function returns one of the following values:
Value | Description |
|---|---|
Zero | LOBs match exactly over the offsets and amountspecified. |
Not Zero | LOBs do not match exactly over the offsets and amountspecified. |
NULL | Either amount, offset_1oroffset_2,is less than 1, or amount, offset_1, or offset_2is greater thanLOBMAXSIZE. |
The COMPARE function may raise the following exceptions:
For BFILEs.
For BFILEs. Files must be open before comparison.
For BFILEs.
For BFILEs.
For BFILEs.
The program asserts a purity level with the RESTRICT_REFERENCES pragma.
PRAGMA RESTRICT_REFERENCES (compare, WNDS, RNDS, WNPS, RNPS);
The following example compares two BFILE locators that are pointing to the same file. Note that for BFILEs we must provide a number of bytes (in the amount parameter) to compare, which is determined via the GETLENGTH function. Note also that for BFILES we must first open the files.
DECLARE
v_file_loc_1 BFILE;
v_file_1_length INTEGER;
v_file_loc_2 BFILE;
BEGIN
v_file_loc_1 := BFILENAME ('IMAGES', 'ourlogo.bmp');
v_file_loc_2 := BFILENAME ('IMAGES', 'ourlogo.bmp');
DBMS_LOB.FILEOPEN(v_file_loc_1);
DBMS_LOB.FILEOPEN(v_file_loc_2);
v_file_1_length := DBMS_LOB.GETLENGTH( v_file_loc_1);
IF DBMS_LOB.COMPARE
( v_file_loc_1, v_file_loc_2, v_file_1_length) = 0
THEN
DBMS_OUTPUT.PUT_LINE('file_loc_1 equals file_loc_2');
ELSE
DBMS_OUTPUT.PUT_LINE('file_loc_1 is not equal to file_loc_2');
END IF;
DBMS_LOB.FILECLOSEALL;
END;
/This is the output of the script:
file_loc_1 equals file_loc_2
This example compares two diagrams from the my_book_diagrams table:
DECLARE
CURSOR diagram_cur (num IN INTEGER)
IS
SELECT diagram
FROM my_book_diagrams
WHERE chapter_descr = 'Chapter 1'
AND diagram_no = num;
v_diagram_1_loc BLOB;
v_diagram_2_loc BLOB;
BEGIN
OPEN diagram_cur (1);
FETCH diagram_cur INTO v_diagram_1_loc;
CLOSE diagram_cur;
OPEN diagram_cur (2);
FETCH diagram_cur INTO v_diagram_1_loc;
CLOSE diagram_cur;
IF DBMS_LOB.COMPARE (v_diagram_1_loc, v_diagram_2_loc) = 0
THEN
DBMS_OUTPUT.PUT_LINE ('diagrams are equal');
ELSE
DBMS_OUTPUT.PUT_LINE ('diagrams are different');
END IF;
END;
/This is the output of the script:
diagrams are different
The GETLENGTH function returns the length of the input LOB. The length is in bytes for BFILEs and BLOBs, and in characters for CLOBs and NCLOBs. The headers for this program, for each corresponding LOB type, are the following:
FUNCTION DBMS_LOB.GETLENGTH (lob_loc IN BLOB) RETURN INTEGER; FUNCTION DBMS_LOB.GETLENGTH (lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER; FUNCTION DBMS_LOB.GETLENGTH (lob_loc IN BFILE) RETURN INTEGER;
The lob_loc parameter is the locator of the LOB whose length is to be determined.
The overloaded specification allows GETLENGTH to be used with all types of LOBs. The clause ANY_CS in the specification allows either CLOB or NCLOB locators as input.
The function returns the length (in bytes or characters) of the input LOB, or it returns NULL if the input LOB is NULL or invalid.
The program asserts a purity level with the RESTRICT_REFERENCES pragma.
PRAGMA RESTRICT_REFERENCES (getlength, WNDS, RNDS, WNPS, RNPS);
The following example gets the size in bytes of the file ch01_01.bmp in the IMAGES directory:
DECLARE
v_file_loc BFILE;
v_diagram_size INTEGER;
BEGIN
v_file_loc := BFILENAME('IMAGES','ch01_01.bmp');
v_diagram_size := DBMS_LOB.GETLENGTH(v_file_loc);
DBMS_OUTPUT.PUT_LINE('Diagram size: ' || v_diagram_size);
END;
/This is the output of the script:
Diagram size: 481078
This example gets the size in characters of "Chapter 1" from the my_book_text table:
DECLARE
v_text_loc CLOB;
BEGIN
v_text_loc := book_text ('Chapter 1');
DBMS_OUTPUT.PUT_LINE
('Length of Chapter 1: ' || DBMS_LOB.GETLENGTH(v_text_loc));
END;
/This is the output of the script:
Length of Chapter 1: 100
The READ procedure provides piece-wise read access to a LOB. A specified number of bytes (BFILE, BLOB) or characters (CLOB, NCLOB) is read into the buffer, starting from a specified location. The number of bytes or characters actually read by the operation is returned. The headers for this program, corresponding to each type, are the following:
PROCEDURE DBMS_LOB.READ
(lob_loc IN BLOB | BFILE,
amount IN OUT BINARY_INTEGER,
offset IN INTEGER,
buffer OUT RAW);
PROCEDURE DBMS_LOB.READ
(lob_loc IN CLOB CHARACTER SET ANY_CS,
amount IN OUT BINARY_INTEGER,
offset IN INTEGER,
buffer OUT VARCHAR2 CHARACTER SET lob_loc%CHARSET);
The overloaded specification allows READ to be used with all types of LOBs. The term ANY_CS in the specification allows either CLOB or NCLOB locators as input.
The READ procedure and the DBMS_LOB.SUBSTR function provide similar functionality. READ is a procedure, while SUBSTR is a function. However, READ will raise NO_DATA_FOUND and INVALID_ARGVAL exceptions, while SUBSTR will ignore these exceptions and return NULL when they occur. DBMS_LOB.SUBSTR can also be called from within a SQL statement, but READ cannot be, since it is a procedure.
Parameters are summarized in the following table.
Parameter | Description |
|---|---|
lob_loc | A locator for the LOB to be read |
amount | Number of bytes (BFILE, BLOB) or characters (CLOB, NCLOB) to read; the number of bytes or characters actually read by the operation is returned in amount |
offset | Location of the byte (BFILE, BLOB) or character (CLOB, NCLOB) in the LOB at which the read begins |
buffer | Buffer where the results of the read operation are placed |
The READ procedure may raise any of the following exceptions:
lob_loc, amount, or offset is NULL.
One of the following conditions exists:
amount< 1 or amount> 32767
offset< 1 or offset> LOBMAXSIZE
size of amount> size of buffer
The end of the LOB is reached.
For BFILEs, files must be open before the read.
For BFILEs.
For BFILEs.
For BFILEs.
For BFILEs.
The following example reads the first 60 characters of the CLOB chapter_text column of the my_book_texttable using the "Chapter 1" row:
DECLARE
v_text_loc CLOB;
v_text_amt BINARY_INTEGER := 60;
v_text_buffer VARCHAR2(60);
BEGIN
v_text_loc := book_text ('Chapter 1');
DBMS_LOB.READ (v_text_loc, v_text_amt, 1, v_text_buffer);
DBMS_OUTPUT.PUT_LINE('Chapter 1: ' || v_text_buffer);
END;
/This is the output of the script:
Chapter 1: It was a dark and stormy night. Suddenly a scream rang out.
The next example reads sixty characters at a time from the CLOB chapter_text column of the my_book_text table using the "Chapter 1" row. Note that the loop continues until READ raises the NO_DATA_FOUND exception.
DECLARE
v_text_loc CLOB;
v_text_amt BINARY_INTEGER := 60;
v_text_pos INTEGER := 1;
v_text_buffer VARCHAR2(60);
BEGIN
v_text_loc := book_text ('Chapter 1');
LOOP
DBMS_LOB.READ
(v_text_loc, v_text_amt, v_text_pos, v_text_buffer);
/* process the text and prepare to read again */
DBMS_OUTPUT.PUT_LINE('Chapter 1: ' || v_text_buffer);
v_text_pos := v_text_pos + v_text_amt;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE('End of Chapter Reached.');
END;
/This script produces the following:
Chapter 1: It was a dark and stormy night. Suddenly a scream rang out. Chapter 1: An EXCEPTION had not been handled. End of Chapter Reached.
Note that the maximum size of a VARCHAR2 or RAW variable is 32767 bytes. This is the size limit of the buffer to be used with READ.
The SUBSTR function provides piece-wise access to a LOB. The specified number of bytes (BFILE, BLOB) or characters (CLOB, NCLOB) is returned, starting from the specified location. The headers for this program, corresponding to each LOB type, are the following:
FUNCTION DBMS_LOB.SUBSTR
(lob_loc IN BLOB | BFILE,
amount IN INTEGER := 32767,
offset IN INTEGER := 1)
RETURN RAW;
FUNCTION 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;The overloaded specification allows SUBSTR to be used with all types of LOBs. The term ANY_CS in the specification allows either CLOB or NCLOB locators as input.
The SUBSTR function and DBMS_LOB.READ procedure provide similar functionality. READ is a procedure, while SUBSTR is a function. However, READ will raise NO_DATA_FOUND and INVALID_ARGVAL exceptions, while SUBSTR will ignore these exceptions when they occur and will return NULL. SUBSTR can also be called from within a SQL statement, but DBMS_LOB.READ cannot, since it is a procedure.
Parameters are summarized in the following table.
Parameter | Description |
|---|---|
lob_loc | Locator for the LOB to be read |
amount | Number of bytes (BFILE, BLOB) or characters (CLOB, NCLOB) to read |
offset | Location of the byte (BFILE, BLOB) or character (CLOB, NCLOB) in the LOB at which the read begins |
The SUBSTR function returns a NULL value for any of the following conditions:
One of the parameters is NULL or invalid
amount< 1 or amount> 32767
offset< 1 or offset> LOBMAXSIZE
The SUBSTR function may raise one of the following exceptions:
For BFILEs. Files must be open before the SUBSTR operation.
For BFILEs.
For BFILEs.
For BFILEs.
For BFILEs.
The program asserts a purity level with the RESTRICT_REFERENCES pragma.
PRAGMA RESTRICT_REFERENCES (substr, WNDS, RNDS, WNPS, RNPS);
The following example reads the first 60 characters of the CLOB chapter_text column of the my_book_texttable using the "Chapter 1" row:
DECLARE
v_text_loc CLOB;
v_text_amt BINARY_INTEGER := 60;
v_text_buffer VARCHAR2(60);
BEGIN
v_text_loc := book_text ('Chapter 1');
v_text_buffer := DBMS_LOB.SUBSTR (v_text_loc, v_text_amt, 1);
DBMS_OUTPUT.PUT_LINE ('Chapter 1: ' || v_text_buffer);
END;
/This is the output of the script:
Chapter 1: It was a dark and stormy night. Suddenly a scream rang out.
The next example reads 60 characters at a time from the CLOB chapter_text column of the my_book_text table using the "Chapter 1" row. Note that the loop continues until SUBSTR returns NULL (i.e., SUBSTR does not raise the NO_DATA_FOUND exception).
DECLARE
v_text_loc CLOB;
v_text_amt BINARY_INTEGER := 60;
v_text_pos INTEGER := 1;
v_buffer VARCHAR2(60);
BEGIN
v_text_loc := book_text ('Chapter 1');
LOOP
v_buffer := DBMS_LOB.SUBSTR (v_text_loc, v_text_amt, v_text_pos);
EXIT WHEN v_buffer IS NULL;
/* process the text and prepare to read again */
DBMS_OUTPUT.PUT_LINE('Chapter 1: ' || v_buffer);
v_text_pos := v_text_pos + v_text_amt;
END LOOP;
END;
/This is the output of the script:
Chapter 1: It was a dark and stormy night. Suddenly a scream rang out. Chapter 1: An EXCEPTION had not been handled.
The INSTR function returns the matching offset location of the Nthoccurrence of the given pattern in the LOB. It returns zero if the pattern is not found. The headers for this program, corresponding to each LOB type, are the following:
FUNCTION DBMS_LOB.INSTR
(lob_loc IN BLOB | BFILE,
pattern IN RAW,
offset IN INTEGER := 1,
nth IN INTEGER := 1)
RETURN INTEGER;
FUNCTION 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;
The overloaded specification allows INSTR to be used with all types of LOBs. The clause ANY_CS in the specification allows either CLOB or NCLOB locators as input.
Parameters are summarized in the following table.
Parameter | Description |
|---|---|
lob_loc | A locator for the LOB to be searched |
pattern | The pattern to search for in the LOB |
offset | Location of the byte (BFILE, BLOB) or character (CLOB, NCLOB) in the LOB at which the search begins |
nth | Search for the Nth occurrence of the given pattern in the LOB |
The INSTR function returns NULL for any of the following conditions:
file_loc or pattern is NULL
one of the parameters is invalid
offset < 1 or offset > LOBMAXSIZE
nth < 1 or nth > LOBMAXSIZE
The INSTR function may raise any of the following exceptions:
For BFILEs. Files must be open before the INSTR operation.
For BFILEs.
For BFILEs.
For BFILEs.
For BFILEs.
The program asserts a purity level with the RESTRICT_REFERENCES pragma.
PRAGMA RESTRICT_REFERENCES (instr, WNDS, RNDS, WNPS, RNPS);
The following example searches for the first occurrence of the string "dark" in the first sixty characters of the CLOB chapter_text column of the "Chapter 1" row of the my_book_texttable:
DECLARE
v_text_loc CLOB;
v_text_buffer VARCHAR2(60);
v_text_pattern VARCHAR2(60) := 'dark';
BEGIN
v_text_loc := book_text ('Chapter 1');
v_text_buffer := DBMS_LOB.SUBSTR (v_text_loc, 60, 1);
DBMS_OUTPUT.PUT_LINE ('buffer: ' || v_text_buffer);
DBMS_OUTPUT.PUT_LINE
('location of "' || v_text_pattern || '": ' ||
DBMS_LOB.INSTR(v_text_loc, v_text_pattern));
END;
/This is the output of the script:
buffer: It was a dark and stormy night. Suddenly a scream rang out. location of "dark": 10
The following sections describe the programs in the DBMS_LOB package that are used to update or alter BLOB, CLOB, and NCLOB object types.
The APPEND procedure appends the contents of a source internal LOB to a destination internal LOB. The headers for this program, corresponding to each LOB type, are the following:
PROCEDURE DBMS_LOB.APPEND
(dest_lob IN OUT BLOB,
src_lob IN BLOB);
PROCEDURE DBMS_LOB.APPEND
(dest_lob IN OUT CLOB CHARACTER SET ANY_CS,
src_lob IN CLOB CHARACTER SET dest_lob%CHARSET);The overloaded specification allows APPEND to be used with BLOBs, CLOBs, and NCLOBs. The term ANY_CS in the specification allows either CLOB or NCLOB locators as input. APPEND cannot be used with BFILEs because access to BFILEs is read-only.
Parameters are summarized in the following table.
Parameter | Description |
|---|---|
dest_lob | A locator for the destination LOB |
src_lob | A locator for the source LOB |
The program may raise any of the following exceptions:
Either of the LOBs is NULL.
dest_lob is not locked for update.
The following example shows that the destination LOB must be selected FOR UPDATE before calling APPEND. We attempt to append the chapter_textfor "Chapter 2" to the chapter_textfor "Chapter 1."
DECLARE
v_text_loc CLOB;
v_text_buffer VARCHAR2(60);
v_text_pattern VARCHAR2(60) := 'dark';
BEGIN
v_dest_loc := book_text ('Chapter 1');
v_src_loc := book_text ('Chapter 2');
DBMS_LOB.APPEND (v_dest_loc, v_src_loc);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('OTHERS Exception ' || sqlerrm);
END;
/This is the output of the script:
OTHERS Exception ORA-22920: row containing the LOB value is not locked
In the next example we append the chapter_textfor "Chapter" to the chapter_textfor "Chapter 1." We display the appended text, roll back the changes, and display the original text. Internal LOBs can participate in database transactions.
DECLARE
v_text_loc CLOB;
v_text_buffer VARCHAR2(60);
v_text_pattern VARCHAR2(60) := 'dark';
BEGIN
v_dest_loc := book_text_forupdate ('Chapter 1');
v_src_loc := book_text ('Chapter 2');
DBMS_LOB.APPEND (v_dest_loc, v_src_loc);
END;
/
SET LONG 200
COL chapter_descr FOR A15
COL chapter_text FOR A40 WORD_WRAPPED
@compare_text ('Chapter 1');This is the output of the script:
CHAPTER_DESCR CHAPTER_TEXT
--------------- ----------------------------------------
Chapter 1 It was a dark and stormy night.
Suddenly a scream rang out. An
EXCEPTION had not been handled. The sun
shone brightly the following morning.
All traces of the storm had disappeared.
Rollback complete.
CHAPTER_DESCR CHAPTER_TEXT
--------------- ----------------------------------------
Chapter 1 It was a dark and stormy night.
Suddenly a scream rang out. An
EXCEPTION had not been handled.The COPY procedure copies all or part of the contents of a source internal LOB to a destination internal LOB. An offset location in each LOB can be specified. The headers for this program, corresponding to each LOB type, are the following:
PROCEDURE DBMS_LOB.COPY
(dest_lob IN OUT BLOB,
src_lob IN BLOB,
amount IN INTEGER,
dest_offset IN INTEGER := 1,
src_offset IN INTEGER := 1);
PROCEDURE DBMS_LOB.COPY
(dest_lob IN OUT 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);The overloaded specification allows COPY to be used with BLOBs, CLOBs, and NCLOBs. The term ANY_CS in the specification allows either CLOB or NCLOB locators as input. COPY cannot be used with BFILEs, because access to BFILEs is read-only.
Parameters are summarized in the following table.
Parameter | Description |
|---|---|
dest_lob | Locator for the destination LOB |
src_lob | Locator for the source LOB |
amount | Number of bytes (BLOB) or characters (CLOB, NCLOB) to copy |
dest_offset | Location of the byte (BLOB) or character (CLOB, NCLOB) in the destination LOB at which the copy operation begins; the default value is 1 |
src_offset | Location of the byte (BLOB) or character (CLOB, NCLOB) in the source LOB at which the copy operation begins; the default value is 1 |
The COPY procedure may raise one of the following exceptions:
One or both LOBs are NULL or invalid.
One of the following conditions exists:
src_offset < 1 or src_offset > LOBMAXSIZE
dest_offset <1 or dest_offset > LOBMAXSIZE
amount < 1 or amount > LOBMAXSIZE
dest_lob is not locked for update.
In the following example, the text "Suddenly a scream rang out..." is copied from the "Chapter 1" row of the my_book_text table to the "Chapter 2" row. Note that the COPY operation replaces (i.e., does not append) existing text. We display the copied text, roll back the changes, and display the original text. Internal LOBs can participate in database transactions.
SET LONG 200
COL chapter_descr FOR A15
COL chapter_text FOR A40 WORD_WRAPPED
SELECT chapter_descr, chapter_text
FROM my_book_text;
DECLARE
v_text_loc CLOB;
v_text_buffer VARCHAR2(60);
v_text_pattern VARCHAR2(60) := 'dark';
BEGIN
v_dest_loc := book_text_forupdate ('Chapter 2');
v_src_loc := book_text ('Chapter 1');
DBMS_LOB.COPY(v_dest_loc, v_src_loc, 63, 47 ,34);
END;
/
@compare_text ('Chapter 2');This is the output of the script:
CHAPTER_DESCR CHAPTER_TEXT
--------------- ----------------------------------------
Chapter 1 It was a dark and stormy night.
Suddenly a scream rang out. An
EXCEPTION had not been handled.
Chapter 2 The sun shone brightly the following
morning. All traces of the storm had
disappeared.
PL/SQL procedure successfully completed.
CHAPTER_DESCR CHAPTER_TEXT
--------------- ----------------------------------------
Chapter 2 The sun shone brightly the following
morning. Suddenly a scream rang out. An
EXCEPTION had not been handled.
Rollback complete.
CHAPTER_DESCR CHAPTER_TEXT
--------------- ----------------------------------------
Chapter 2 The sun shone brightly the following
morning. All traces of the storm had
disappeared.The ERASE procedure removes all or part of the contents of an internal LOB. An offset location in the LOB can be specified. In the middle of a LOB, spaces are written for CLOBs and NCLOBs, and zero-byte filler is written for BLOBs.
PROCEDURE DBMS_LOB.ERASE
(lob_loc IN OUT BLOB | CLOB CHARACTER SET ANY_CS,
amount IN OUT INTEGER,
offset IN INTEGER := 1);The overloaded specification allows ERASE to be used with BLOBs, CLOBs, and NCLOBs. The term ANY_CS in the specification allows either CLOB or NCLOB locators as input. ERASE cannot be used with BFILEs because access to BFILEs is read-only.
Parameters are summarized in the following table.
Parameter | Description |
|---|---|
lob_loc | Locator for the LOB to be erased |
amount | Number of bytes (BLOB) or characters (CLOB, NCLOB) to erase |
offset | Location of the byte (BLOB) or character (CLOB, NCLOB) in the LOB at which the erase operation begins; the default value is 1 |
The ERASE procedure may raise any of the following exceptions:
lob_loc or amount is NULL or invalid.
One of the following conditions exists:
amount < 1 or amount > LOBMAXSIZE
offset < 1 or offset > LOBMAXSIZE
dest_lob is not locked for update.
In the following example, we erase the string "brightly" from the "Chapter 2" chapter_textcolumn in the my_book_text table. Note that the string is replaced with spaces. We display the erased text, roll back the changes, and display the original text. Internal LOBs can participate in database transactions.
SET LONG 200
COL chapter_descr FOR A15
COL chapter_text FOR A40 WORD_WRAPPED
SELECT chapter_descr, chapter_text
FROM my_book_text
WHERE chapter_descr = 'Chapter 2';
DECLARE
v_dest_loc CLOB;
v_erase_amt INTEGER;
BEGIN
v_dest_loc := book_text_forupdate ('Chapter 2');
v_erase_amt := 9;
DBMS_LOB.ERASE(v_dest_loc, v_erase_amt, 15);
END;
/
@compare_text ('Chapter 2');This is the output of the script:
CHAPTER_DESCR CHAPTER_TEXT
--------------- ----------------------------------------
Chapter 2 The sun shone brightly the following
morning. All traces of the storm had
disappeared.
PL/SQL procedure successfully completed.
CHAPTER_DESCR CHAPTER_TEXT
--------------- ----------------------------------------
Chapter 2 The sun shone the following
morning. All traces of the storm had
disappeared.
Rollback complete.
CHAPTER_DESCR CHAPTER_TEXT
--------------- ----------------------------------------
Chapter 2 The sun shone brightly the following
morning. All traces of the storm had
disappeared.The TRIM procedure trims the contents of an internal LOB to a specified length. The headers for this program, corresponding to each LOB type, are the following:
PROCEDURE DBMS_LOB.TRIM
(lob_loc IN OUT BLOB|CLOB CHARACTER SET ANY_CS,
newlen IN INTEGER);The overloaded specification allows TRIM to be used with BLOBs, CLOBs, and NCLOBs. The term ANY_CS in the specification allows either CLOB or NCLOB locators as input. TRIM cannot be used with BFILEs because access to BFILEs is read-only.
The parameters for this program are summarized in the following table.
Parameter | Description |
|---|---|
lob_loc | Locator for the LOB to be erased |
newlen | Number of bytes (BLOB) or characters (CLOB, NCLOB) to remain in the LOB |
The TRIM procedure may raise any of the following exceptions:
lob_loc or newlen is NULL or invalid.
newlen < 0 or newlen > LOBMAXSIZE.
dest_lob is not locked for update.
In the following example, we trim the "Chapter 1" chapter_textcolumn in the my_book_text table to 31 characters. We display the trimmed text, roll back the changes, and display the original text. Internal LOBs can participate in database transactions.
SET LONG 200
COL chapter_descr FOR A15
COL chapter_text FOR A40 WORD_WRAPPED
SELECT chapter_descr, chapter_text
FROM my_book_text
WHERE chapter_descr = 'Chapter 1';
DECLARE
v_text_loc CLOB;
BEGIN
v_text_loc := book_text ('Chapter 1');
DBMS_LOB.TRIM (v_text_loc, 31);
END;
/
@compare_text ('Chapter 1');This is the output of the script:
CHAPTER_DESCR CHAPTER_TEXT
--------------- ----------------------------------------
Chapter 1 It was a dark and stormy night.
Suddenly a scream rang out. An
EXCEPTION had not been handled.
PL/SQL procedure successfully completed.
CHAPTER_DESCR CHAPTER_TEXT
--------------- ----------------------------------------
Chapter 1 It was a dark and stormy night.
Rollback complete.
CHAPTER_DESCR CHAPTER_TEXT
--------------- ----------------------------------------
Chapter 1 It was a dark and stormy night.
Suddenly a scream rang out. An
EXCEPTION had not been handled.The WRITE procedure writes a given number of bytes (BLOB) or characters (CLOB, NCLOB) to an internal LOB, beginning at a specified offset. The contents of the write operation are taken from the buffer. WRITE replaces (overlays) any data that exists in the LOB at the offset. The headers for this program, for each corresponding LOB type, are the following:
PROCEDURE DBMS_LOB.WRITE
(lob_loc IN OUT BLOB,
amount IN BINARY_INTEGER,
offset IN INTEGER,
buffer IN RAW);
PROCEDURE DBMS_LOB.WRITE
(lob_loc IN OUT CLOB CHARACTER SET ANY_CS,
amount IN BINARY_INTEGER,
offset IN INTEGER,
buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET);The overloaded specification allows WRITE to be used with BLOBs, CLOBs, and NCLOBs. The term ANY_CS in the specification allows either CLOB or NCLOB locators as input. WRITE cannot be used with BFILEs, because access to BFILEs is read-only.
Parameters are summarized in the following table.
Parameter | Description |
|---|---|
lob_loc | A locator for the target LOB |
amount | Number of bytes (BLOB) or characters (CLOB, NCLOB) to be written |
offset | The location of the byte (BLOB) or character (CLOB, NCLOB) in the LOB at which the write begins |
buffer | Buffer holding the contents of the write operation |
The WRITE procedure may raise any of the following exceptions:
lob_loc, amount, or offset is NULL or invalid.
One of the following conditions exists:
amount < 1 or amount > 32767
offset < 1 or offset > LOBMAXSIZE
In the following example, we write the string "The End" to the end of the "Chapter 2" chapter_textcolumn in the my_book_text table. We display the new text, roll back the changes, and display the original text. Internal LOBs can participate in database transactions.
SET LONG 200
COL chapter_descr FOR A15
COL chapter_text FOR A40 WORD_WRAPPED
SELECT chapter_descr, chapter_text
FROM my_book_text
WHERE chapter_descr = 'Chapter 2';
DECLARE
v_text_loc CLOB;
v_offset INTEGER;
v_buffer VARCHAR2(100);
BEGIN
v_text_loc := book_text_forupdate ('Chapter 1');
v_offset := DBMS_LOB.GETLENGTH (v_text_loc) + 3;
v_buffer := 'The End.';
DBMS_LOB.WRITE (v_text_loc, 8, v_offset, v_buffer);
END;
/
@compare_text ('Chapter 2');This is the output of the script:
CHAPTER_DESCR CHAPTER_TEXT
--------------- ----------------------------------------
Chapter 2 The sun shone brightly the following
morning. All traces of the storm had
disappeared.
PL/SQL procedure successfully completed.
CHAPTER_DESCR CHAPTER_TEXT
--------------- ----------------------------------------
Chapter 2 The sun shone brightly the following
morning. All traces of the storm had
disappeared. The End.
Rollback complete.
CHAPTER_DESCR CHAPTER_TEXT
--------------- ----------------------------------------
Chapter 2 The sun shone brightly the following
morning. All traces of the storm had
disappeared. |  | |
| 8.2 LOB Concepts |  | 9. Datatype Packages |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.