#

| Chapter 8 Managing Large Objects |

## 8.3 DBMS_LOB Interface

This section describes the programs available through the DBMS_LOB packages in several categories.

### 8.3.1 Working with BFILEs

The following sections describe the programs in the DBMS_LOB package that perform operations on BFILE objects.

#### 8.3.1.1 The DBMS_LOB.FILEEXISTS function

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

#### 8.3.1.1.1 Exceptions

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

#### 8.3.1.1.2 Restrictions

The FILEEXISTS function asserts a purity level with the RESTRICT_REFERENCES pragma.

>      PRAGMA RESTRICT_REFERENCES (fileexists, WNDS, RNDS, WNPS, RNPS);

#### 8.3.1.1.3 Examples

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
>        ----------------
>                       1

Calls 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).

#### 8.3.1.2 The DBMS_LOB.FILEGETNAME procedure

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

#### 8.3.1.2.1 Exceptions

The following VALUE_ERROR exception is raised if the file_loc parameter contains an improper value (e.g., NULL):

>     INVALID_ARGVAL

#### 8.3.1.2.2 Examples

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.

#### 8.3.1.3 The DBMS_LOB.FILEOPEN procedure

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

#### 8.3.1.3.1 Exceptions

The following types of exceptions can be raised by the FILEOPEN procedure:

NOEXIST_DIRECTORY

  The directory alias associated with file_loc does not exist.

OPEN_TOOMANY

  The number of open files exceeds the SESSION_MAX_OPEN_FILES limit.

INVALID_ARGVAL

  The open_mode value is not FILE_READONLY.

INVALID_OPERATION

  The file does not exist or the user does not have privileges to access the file.

VALUE_ERROR

  The file_loc parameter contains an improper value (e.g., NULL).

#### 8.3.1.3.2 Examples

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.

#### 8.3.1.4 The DBMS_LOB.FILEISOPEN function

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.

#### 8.3.1.4.1 Exceptions

The FILEISOPEN function will raise the VALUE_ERROR exception if the file_loc parameter contains an improper value (e.g., NULL).

#### 8.3.1.4.2 Restrictions

The program asserts a purity level with the RESTRICT_REFERENCES pragma.

>     PRAGMA RESTRICT_REFERENCES (fileisopen, WNDS, RNDS, WNPS, RNPS);

#### 8.3.1.4.3 Examples

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.

#### 8.3.1.5 The DBMS_LOB.FILECLOSE procedure

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.

#### 8.3.1.5.1 Exceptions

The FILECLOSE procedure may raise a VALUE_ERROR exception if the file_loc parameter contains an improper value (e.g., NULL).

#### 8.3.1.5.2 Examples

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.

#### 8.3.1.6 The DBMS_LOB.FILECLOSEALL procedure

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;

#### 8.3.1.6.1 Exceptions

The FILECLOSEALL procedure raises an UNOPENED_FILE exception if no files are open.

#### 8.3.1.6.2 Examples

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

#### 8.3.1.7 The DBMS_LOB.LOADFROMFILE procedure

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

#### 8.3.1.7.1 Exceptions

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.

#### 8.3.1.7.2 Examples

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

### 8.3.2 Reading and Examining LOBs

The following sections describe the programs in the DBMS_LOB package that are used to read and examine LOBs.

#### 8.3.2.1 The DBMS_LOB.COMPARE function

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.

#### 8.3.2.1.1 Exceptions

The COMPARE function may raise the following exceptions:

NOEXIST_DIRECTORY

  For BFILEs.

UNOPENED_FILE

  For BFILEs. Files must be open before comparison.

NOPRIV_DIRECTORY

  For BFILEs.

INVALID_DIRECTORY

  For BFILEs.

INVALID_OPERATION

  For BFILEs.

#### 8.3.2.1.2 Restrictions

The program asserts a purity level with the RESTRICT_REFERENCES pragma.

>     PRAGMA RESTRICT_REFERENCES (compare, WNDS, RNDS, WNPS, RNPS);

#### 8.3.2.1.3 Examples

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

#### 8.3.2.2 The DBMS_LOB.GETLENGTH function

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.

#### 8.3.2.2.1 Restrictions

The program asserts a purity level with the RESTRICT_REFERENCES pragma.

>     PRAGMA RESTRICT_REFERENCES (getlength, WNDS, RNDS, WNPS, RNPS);

#### 8.3.2.2.2 Examples

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

#### 8.3.2.3 The DBMS_LOB.READ procedure

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

#### 8.3.2.3.1 Exceptions

The READ procedure may raise any of the following exceptions:

VALUE_ERROR

  lob_loc, amount, or offset is NULL.

INVALID_ARGVAL

  One of the following conditions exists:

- amount< 1 or amount> 32767

- offset< 1 or offset> LOBMAXSIZE

- size of amount> size of buffer

NO_DATA_FOUND

  The end of the LOB is reached.

UNOPENED_FILE

  For BFILEs, files must be open before the read.

NOEXIST_DIRECTORY

  For BFILEs.

NOPRIV_DIRECTORY

  For BFILEs.

INVALID_DIRECTORY

  For BFILEs.

INVALID_OPERATION

  For BFILEs.

#### 8.3.2.3.2 Examples

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.

#### 8.3.2.4 The DBMS_LOB.SUBSTR function

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

#### 8.3.2.4.1 Exceptions

The SUBSTR function may raise one of the following exceptions:

UNOPENED_FILE

  For BFILEs. Files must be open before the SUBSTR operation.

NOEXIST_DIRECTORY

  For BFILEs.

NOPRIV_DIRECTORY

  For BFILEs.

INVALID_DIRECTORY

  For BFILEs.

INVALID_OPERATION

  For BFILEs.

#### 8.3.2.4.2 Restrictions

The program asserts a purity level with the RESTRICT_REFERENCES pragma.

>      PRAGMA RESTRICT_REFERENCES (substr, WNDS, RNDS, WNPS, RNPS);

#### 8.3.2.4.3 Examples

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.

#### 8.3.2.5 The DBMS_LOB.INSTR function

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

#### 8.3.2.5.1 Exceptions

The INSTR function may raise any of the following exceptions:

UNOPENED_FILE

  For BFILEs. Files must be open before the INSTR operation.

NOEXIST_DIRECTORY

  For BFILEs.

NOPRIV_DIRECTORY

  For BFILEs.

INVALID_DIRECTORY

  For BFILEs.

INVALID_OPERATION

  For BFILEs.

#### 8.3.2.5.2 Restrictions

The program asserts a purity level with the RESTRICT_REFERENCES pragma.

>        PRAGMA RESTRICT_REFERENCES (instr, WNDS, RNDS, WNPS, RNPS);

#### 8.3.2.5.3 Example

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

### 8.3.3 Updating BLOBs, CLOBs, and NCLOBs

The following sections describe the programs in the DBMS_LOB package that are used to update or alter BLOB, CLOB, and NCLOB object types.

#### 8.3.3.1 The DBMS_LOB.APPEND procedure

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

#### 8.3.3.1.1 Exceptions

The program may raise any of the following exceptions:

VALUE_ERROR

  Either of the LOBs is NULL.

ORA-22920

  dest_lob is not locked for update.

#### 8.3.3.1.2 Examples

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.

#### 8.3.3.2 The DBMS_LOB.COPY procedure

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

#### 8.3.3.2.1 Exceptions

The COPY procedure may raise one of the following exceptions:

VALUE_ERROR

  One or both LOBs are NULL or invalid.

INVALID_ARGVAL

  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

ORA-22920

  dest_lob is not locked for update.

#### 8.3.3.2.2 Example

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.

#### 8.3.3.3 The DBMS_LOB.ERASE procedure

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

#### 8.3.3.3.1 Exceptions

The ERASE procedure may raise any of the following exceptions:

VALUE_ERROR

  lob_loc or amount is NULL or invalid.

INVALID_ARGVAL

  One of the following conditions exists:

- amount < 1 or amount > LOBMAXSIZE

- offset < 1 or offset > LOBMAXSIZE

ORA-22920

  dest_lob is not locked for update.

#### 8.3.3.3.2 Example

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.

#### 8.3.3.4 The DBMS_LOB.TRIM procedure

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

#### 8.3.3.4.1 Exceptions

The TRIM procedure may raise any of the following exceptions:

VALUE_ERROR

  lob_loc or newlen is NULL or invalid.

INVALID_ARGVAL

  newlen < 0 or newlen > LOBMAXSIZE.

ORA-22920

  dest_lob is not locked for update.

#### 8.3.3.4.2 Example

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.

#### 8.3.3.5 The DBMS_LOB.WRITE procedure

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

#### 8.3.3.5.1 Exceptions

The WRITE procedure may raise any of the following exceptions:

VALUE_ERROR

  lob_loc, amount, or offset is NULL or invalid.

INVALID_ARGVAL

  One of the following conditions exists:

- amount < 1 or amount > 32767

- offset < 1 or offset > LOBMAXSIZE

#### 8.3.3.5.2 Example

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.