[Chapter 8] 8.3 DBMS_LOB Interface

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