Using Large Objects in PLSQL

Using Large Objects in PL/SQL

In this section, you ll learn how to use LOBs from within PL/SQL. To do this, you use the methods in the DBMS_LOB package that comes with the Oracle database. Table 14-1 summarizes the methods in the DBMS_LOB package.

Table 14-1: DBMS_LOB Methods

Method	Description
APPEND(dest_lob, src_lob)	Adds the contents of the source LOB to the end of the destination LOB
CLOSE(lob_loc)	Closes a previously opened LOB
COMPARE(lob1, lob2, amount, offset1, offset2)	Compares two entire LOBs or parts of two LOBs
COPY(dest_lob, src_lob, amount, dest_offset, src_offset)	Copies all or part of the source LOB to the destination LOB
CREATETEMPORARY(lob, cache, duration)	Creates a temporary BLOB or CLOB and its corresponding index in the user 's default temporary tablespace
ERASE(lob, amount, offset)	Erases all or part of a LOB
FILECLOSE( bfile )	Closes a BFILE
FILECLOSEALL()	Closes all previously opened BFILE s
FILEEXISTS(bfile)	Checks if a file exists on the server
FILEGETNAME(bfile, dir_alias, filename)	Gets the directory alias and file name
FILEISOPEN(bfile)	Checks if the file is open
FILEOPEN(bfile, open_mode)	Opens a file
FREETEMPORARY(lob)	Frees the temporary BLOB or CLOB in the default temporary tablespace of the user
GETCHUNKSIZE(lob)	Gets the amount of space used in the LOB chunk to store the LOB value
GET_STORAGE_LIMIT()	Gets the storage limit of a LOB
GETLENGTH(lob)	Gets the length of the LOB value
INSTR(lob, pattern, offset, n)	Gets the matching position of the nth occurrence of the pattern in the LOB
ISOPEN(lob)	Checks if the LOB was already opened using the input locator
ISTEMPORARY(lob)	Checks if the locator is pointing to a temporary LOB
LOADFROMFILE(dest_lob, src_bfile, amount, dest_offset, src_offset)	Loads BFILE data into an internal LOB
LOADBLOBFROMFILE(dest_lob, src_bfile, amount, dest_offset, src_offset)	Loads BFILE data into an internal BLOB
LOADCLOBFROMFILE(dest_lob, src_bfile, amount, dest_offset, src_offset, src_csid, lang_context, warning)	Loads BFILE data into an internal CLOB
OPEN(lob, open_mode)	Opens a LOB (internal, external, or temporary) in the indicated mode
READ(lob, amount, offset, buffer)	Reads data into the buffer from the LOB starting at the specified offset
SUBSTR(lob, amount, offset)	Reads part of the LOB value starting at the specified offset
TRIM(lob, newlen)	Trims the LOB value to the specified shorter length
WRITE(lob, amount, offset, buffer)	Writes data from the buffer to the LOB at the specified offset
WRITEAPPEND(lob, amount, buffer)	Writes data from the buffer to the end of a LOB

In the following sections, you ll learn more about the methods shown in Table 14-1. You ll also see examples of some of the methods.

Note  

The following sections show example methods that are created by the lob_schema.sql script.

READ()

You use READ() to read data from the LOB starting at the specified offset. There are three versions of READ() and they have the following syntax:

 DBMS_LOB.READ(  lob  IN BLOB,  amount  IN OUT NOCOPY BINARY_INTEGER,  offset  IN INTEGER,  buffer  OUT RAW); DBMS_LOB.READ(  lob  IN CLOB CHARACTER SET ANY_CS,  amount  IN OUT NOCOPY BINARY_INTEGER,  offset  IN INTEGER,  buffer  OUT VARCHAR2 CHARACTER SET  lob  %CHARSET); DBMS_LOB.READ(  bfile  IN BFILE,  amount  IN OUT NOCOPY BINARY_INTEGER,  offset  IN INTEGER,  buffer  OUT RAW); 

where

• lob is the CLOB or BLOB to read from.

• bfile is the BFILE to read from.

• amount is the number of characters to read from the CLOB or the number of bytes to read from the BLOB or BFILE .

• offset is the offset in characters to read from the CLOB or the number of bytes to read from the BLOB or BFILE (offset starts at 1).

• buffer is the storage variable where the output from the read is to be stored.

• CHARACTER SET ANY_CS specifies any character set.

• CHARACTER SET lob %CHARSET is the character set of lob .

Table 14-2 shows the exceptions thrown by READ() .

Table 14-2: Exceptions Thrown by READ()

Exception	Thrown When
VALUE_ERROR	Any of the input parameters are null.
INVALID_ARGVAL	Either: amount < 1 amount > MAXBUFSIZE amount > capacity of buffer in bytes or characters offset < 1 offset > LOBMAXSIZE
NO_DATA_FOUND	The end of the LOB was reached and there are no more bytes or characters to read from the LOB.

The following initialize_clob() procedure performs the following tasks :

• Accepts an IN OUT parameter named clob_par of type CLOB , which is initialized in the procedure.

• Accepts an IN parameter named id_par of type INTEGER , which specifies the ID of the CLOB to select.

• Selects clob_column from clob_content into clob_par where id is id_par .

   CREATE OR REPLACE PROCEDURE initialize_clob(clob_par IN OUT CLOB,  id_par IN INTEGER) IS BEGIN  SELECT clob_column  INTO clob_par  FROM clob_content  WHERE id = id_par; END initialize_clob; / 

The following initialize_blob() procedure performs the following tasks:

• Accepts an IN OUT parameter named blob_par of type BLOB .

• Accepts an IN parameter named id_par of type INTEGER .

• Selects blob_column from blob_content into blob_par where id is id_par .

   CREATE OR REPLACE PROCEDURE initialize_blob(blob_par IN OUT BLOB,  id_par IN INTEGER) IS BEGIN  SELECT blob_column  INTO blob_par  FROM blob_content  WHERE id = id_par; END initialize_blob; / 

The following read_clob_example() procedure performs the following tasks:

• Calls initialize_clob() to initialize clob_var .

• Uses READ() to read the contents of clob_var into a VARCHAR2 variable named char_buffer_var .

• Outputs the contents of char_buffer_var .

   CREATE OR REPLACE PROCEDURE read_clob_example(id_par IN INTEGER) IS  clob_var CLOB;  char_buffer_var VARCHAR2(50);  offset_var INTEGER := 1;  amount_var INTEGER := 50; BEGIN  initialize_clob(clob_var, id_par);  DBMS_LOB.READ(clob_var, amount_var, offset_var, char_buffer_var);  DBMS_OUTPUT.PUT_LINE('char_buffer_var = '  char_buffer_var);  DBMS_OUTPUT.PUT_LINE('amount_var = '  amount_var); END read_clob_example; / 

The following read_blob_example() procedure performs the following tasks:

• Calls initialize_blob() to initialize blob_var .

• Calls READ() to read the contents of blob_var into a RAW variable named binary_buffer_var .

• Outputs the contents of binary_buffer_var .

   CREATE OR REPLACE PROCEDURE read_blob_example(id_par IN INTEGER) IS  blob_var BLOB;  binary_buffer_var RAW(25);  offset_var INTEGER := 1;  amount_var INTEGER := 25; BEGIN  initialize_blob(blob_var, id_par);  DBMS_LOB.READ(blob_var, amount_var, offset_var, binary_buffer_var);  DBMS_OUTPUT.PUT_LINE('binary_buffer_var = '  binary_buffer_var);  DBMS_OUTPUT.PUT_LINE('amount_var = '  amount_var); END read_blob_example; / 

The following example connects as lob_user , turns the server output on, and calls read_clob_example() and read_blob_example() :

  CONNECT lob_user/lob_password   SET SERVEROUTPUT ON   CALL read_clob_example(1);  char_buffer_var = Creeps in this petty pace amount_var = 25 Call completed.  CALL read_blob_example(1);  binary_buffer_var = 100111010101011111 amount_var = 9 Call completed. 

WRITE()

You use WRITE() to write data to the LOB at a specified offset. There are two versions of WRITE() and they have the following syntax:

 DBMS_LOB.WRITE(  lob  IN OUT NOCOPY BLOB,  amount  IN BINARY_INTEGER,  offset  IN INTEGER,  buffer  IN RAW); DBMS_LOB.WRITE(  lob  IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,  amount  IN BINARY_INTEGER,  offset  IN INTEGER,  buffer  IN VARCHAR2 CHARACTER SET  lob  %CHARSET); 

where

• lob is the CLOB or BLOB to write to.

• amount is the number of characters to write to the CLOB or the number of bytes to write to the BLOB .

• offset is the offset in characters to write to the CLOB or the number of bytes to write to the BLOB (offset starts at 1).

• buffer is the storage variable where the input to the write is to be read from.

Table 14-3 shows the exceptions thrown by WRITE() .

Table 14-3: Exceptions Thrown by WRITE()

Exception	Thrown When
VALUE_ERROR	Any of the input parameters are null or invalid.
INVALID_ARGVAL	Either: amount < 1 amount > MAXBUFSIZE offset < 1 offset > LOBMAXSIZE

The following write_example() procedure performs the following tasks:

• Accepts an IN parameter named id_par of type INTEGER .

• Selects clob_column into a CLOB variable named clob_var from clob_content where id is id_par . The select uses the FOR UPDATE clause to lock the row for update. FOR UPDATE is used because clob_var will be written to using WRITE() .

• Calls read_clob_example() to read and display the contents of clob_var .

• Calls WRITE() to write the contents of a VARCHAR2 variable named char_buffer_var to clob_var .

• Calls read_clob_example() again to read and display the contents of clob_var .

• Performs a ROLLBACK to undo the write.

   CREATE OR REPLACE PROCEDURE write_example(id_par IN INTEGER) IS  clob_var CLOB;  char_buffer_var VARCHAR2(10) := 'pretty';  offset_var INTEGER := 7;  amount_var INTEGER := 6; BEGIN  SELECT clob_column  INTO clob_var  FROM clob_content  WHERE id = 1  FOR UPDATE;  read_clob_example(1);  DBMS_LOB.WRITE(clob_var, amount_var, offset_var, char_buffer_var);  read_clob_example(1);  ROLLBACK; END write_example; / 

The following example calls write_example() :

  CALL write_example(1);  char_buffer_var = Creeps in this petty pace amount_var = 25 char_buffer_var = Creepsprettyis petty pace amount_var = 25 

APPEND()

You use APPEND() to add the contents of the source LOB to the end of the destination LOB. There are two versions of APPEND() and they have the following syntax:

 DBMS_LOB.APPEND(  dest_lob  IN OUT NOCOPY BLOB,  src_lob  IN BLOB); DBMS_LOB.APPEND(  dest_lob  IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,  src_lob  IN CLOB CHARACTER SET dest_lob%CHARSET); 

where

• dest_lob is the destination LOB to which content is added.

• src_lob is the source LOB from which content is copied .

Table 14-4 shows the exception thrown by APPEND() .

Table 14-4: Exception Thrown by APPEND()

Exception	Thrown When
VALUE_ERROR	Either dest_lob or src_lob is null.

The following append_example() procedure performs the following tasks:

• Selects clob_column into src_clob_var from clob_content where id is 2.

• Selects clob_column into dest_clob_var from clob_content where id is 1 for update.

• Calls read_clob_example() to read and display the contents of dest_clob_var .

• Calls APPEND() to add the contents of src_clob_var to dest_clob_var .

• Calls read_clob_example() to read and display the contents of dest_clob_var , which now contains the contents of src_clob_var at the end.

• Performs a ROLLBACK to undo the append.

   CREATE OR REPLACE PROCEDURE append_example IS  src_clob_var CLOB;  dest_clob_var CLOB; BEGIN  SELECT clob_column  INTO src_clob_var  FROM clob_content  WHERE id = 2;  SELECT clob_column  INTO dest_clob_var  FROM clob_content  WHERE id = 1  FOR UPDATE;  read_clob_example(1);  DBMS_LOB.APPEND(dest_clob_var, src_clob_var);  read_clob_example(1);  ROLLBACK; END append_example; / 

The following example calls append_example() :

  CALL append_example();  char_buffer_var = Creeps in this petty pace amount_var = 25 char_buffer_var = Creeps in this petty pace from day to day amount_var = 41 

CLOSE()

You use CLOSE() to close a previously opened LOB. There are three versions of CLOSE() and they have the following syntax:

 DBMS_LOB.CLOSE(  lob  IN OUT NOCOPY BLOB); DBMS_LOB.CLOSE(  lob  IN OUT NOCOPY CLOB CHARACTER SET ANY_CS); DBMS_LOB.CLOSE(  lob  IN OUT NOCOPY BFILE); 

where

• lob is the LOB to be closed.

COMPARE()

You use COMPARE() to compare two entire LOBs or parts of two LOBs.

 DBMS_LOB.COMPARE(  lob1  IN BLOB,  lob2  IN BLOB,  amount  IN INTEGER := 4294967295,  offset1  IN INTEGER := 1,  offset2  IN INTEGER := 1) RETURN INTEGER; DBMS_LOB.COMPARE(  lob1  IN CLOB CHARACTER SET ANY_CS,  lob2  IN CLOB CHARACTER SET lob_1%CHARSET,  amount  IN INTEGER := 4294967295,  offset1  IN INTEGER := 1,  offset2  IN INTEGER := 1) RETURN INTEGER; DBMS_LOB.COMPARE(  lob1  IN BFILE,  lob2  IN BFILE,  amount  IN INTEGER,  offset1  IN INTEGER := 1,  offset2  IN INTEGER := 1) RETURN INTEGER; 

where

• lob1 and lob2 are the LOBs to compare.

• amount is the number of characters when reading from a CLOB , and the number of bytes when reading from a BLOB or BFILE .

• offset1 and offset2 are the offsets in characters or bytes in lob1 and lob2 to start the comparison.

COMPARE() returns

• 0 if the LOBs are identical.

• 1 if the LOBs aren t identical.

• Null if:

  • amount < 1

  • amount > LOBMAXSIZE (Note: LOBMAXSIZE is the maximum size of the LOB)

  • offset1 or offset2 < 1

  • offset1 or offset2 > LOBMAXSIZE

Table 14-5 shows the exceptions thrown by COMPARE() .

Table 14-5: Exceptions Thrown by COMPARE()

Exception	Thrown When
UNOPENED_FILE	The file hasn't been opened yet.
NOEXIST_DIRECTORY	The directory doesn't exist.
NOPRIV_DIRECTORY	You don't have privileges to access the directory.
INVALID_DIRECTORY	The directory is invalid.
INVALID_OPERATION	The file exists, but you don't have privileges to access the file.

The following compare_example() procedure performs the following tasks:

• Selects clob_column into clob_var1 from clob_content where id is 1.

• Selects clob_column into clob_var2 from clob_content where id is 2 for update.

• Calls COMPARE() to compare the contents of clob_var1 with clob_var2 . COMPARE() returns 1 because the contents of clob_var1 and clob_var2 are different.

• Calls COMPARE() to compare the contents of clob_var1 with clob_var1 . COMPARE() returns 0 because the contents are the same.

   CREATE OR REPLACE PROCEDURE compare_example IS  clob_var1 CLOB;  clob_var2 CLOB;  return_var INTEGER; BEGIN  SELECT clob_column  INTO clob_var1  FROM clob_content  WHERE id = 1;  SELECT clob_column  INTO clob_var2  FROM clob_content  WHERE id = 2;  DBMS_OUTPUT.PUT_LINE('Comparing clob_var1 with clob_var2');  return_var := DBMS_LOB.COMPARE(clob_var1, clob_var2);  DBMS_OUTPUT.PUT_LINE('return_var = '  return_var);  DBMS_OUTPUT.PUT_LINE('Comparing clob_var1 with clob_var1');  return_var := DBMS_LOB.COMPARE(clob_var1, clob_var1);  DBMS_OUTPUT.PUT_LINE('return_var = '  return_var); END compare_example; / 

The following example calls compare_example() :

  CALL compare_example();  Comparing clob_var1 with clob_var2 return_var = 1 Comparing clob_var1 with clob_var1 return_var = 0 

Notice return_var is 1 when comparing clob_var1 with clob_var2 , which indicates the LOBs are different. return_var is 0 when comparing clob_var1 with clob_var1 , which indicates the LOBs are identical.

COPY()

You use COPY() to copy all or part of the source LOB to the destination LOB.

 DBMS_LOB.COPY(  dest_lob  IN OUT NOCOPY BLOB,  src_lob  IN BLOB,  amount  IN INTEGER,  dest_offset  IN INTEGER := 1,  src_offset  IN INTEGER := 1); DBMS_LOB.COPY(  dest_lob  IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,  src_lob  IN CLOB CHARACTER SET  dest_lob  %CHARSET,  amount  IN INTEGER,  dest_offset  IN INTEGER := 1,  src_offset  IN INTEGER := 1); 

where

• dest_lob and src_lob are the LOBs to copy to and read from.

• amount is the number of characters when reading from a CLOB , and the number of bytes when reading from a BLOB or BFILE .

• dest_offset and src_offset are the offsets in characters or bytes in dest_lob and src_lob to start the copy.

Table 14-6 shows the exceptions thrown by COPY() .

Table 14-6: Exceptions Thrown by COPY()

Exception	Thrown When
VALUE_ERROR	Any of the parameters are null.
INVALID_ARGVAL	Either: src_offset < 1 dest_offset < 1 src_offset > LOBMAXSIZE dest_offset > LOBMAXSIZE amount < 1 amount > LOBMAXSIZE

The following copy_example() procedure performs the following tasks:

• Selects clob_column into src_clob_var from clob_content where id is 2.

• Selects clob_column into dest_clob_var from clob_content where id is 1 for update.

• Calls read_clob_example() to read and display the contents of dest_clob_var .

• Calls COPY() to copy part of the contents of src_clob_var to dest_clob_var .

• Calls read_clob_example() again to read and display the contents of dest_clob_var .

• Performs a ROLLBACK to undo the copy.

   CREATE OR REPLACE PROCEDURE copy_example IS  src_clob_var CLOB;  dest_clob_var CLOB;  src_offset_var INTEGER := 1;  dest_offset_var INTEGER := 7;  amount_var INTEGER := 5; BEGIN  SELECT clob_column  INTO src_clob_var  FROM clob_content  WHERE id = 2;  SELECT clob_column  INTO dest_clob_var  FROM clob_content  WHERE id = 1  FOR UPDATE;  read_clob_example(1);  DBMS_LOB.COPY(dest_clob_var, src_clob_var, amount_var,  dest_offset_var, src_offset_var);  read_clob_example(1);  ROLLBACK; END copy_example; / 

The following example calls copy_example() :

  CALL copy_example();  char_buffer_var = Creeps in this petty pace amount_var = 25 char_buffer_var = Creeps fromhis petty pace amount_var = 25 

CREATETEMPORARY()

You use CREATETEMPORARY() to create a temporary BLOB or CLOB and its corresponding index in the user s default temporary tablespace.

 DBMS_LOB.CREATETEMPORARY(  lob  IN OUT NOCOPY BLOB,  cache  IN BOOLEAN,  duration  IN PLS_INTEGER := 10); DBMS_LOB.CREATETEMPORARY (  lob  IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,  cache  IN BOOLEAN,  duration  IN PLS_INTEGER := 10); 

where

• lob is the LOB to create.

• cache specifies whether the LOB should be read into the buffer cache.

• duration is either SESSION or CALL , which indicates whether the temporary LOB is removed at the end of the session or call. The default is SESSION .

Table 14-7 shows the exception thrown by CREATETEMPORARY() .

Table 14-7: Exception Thrown by CREATETEMPORARY()

Exception	Thrown When
VALUE_ERROR	The lob parameter is null.

The following temporary_lob_example() procedure performs the following tasks:

• Calls CREATETEMPORARY() to create a temporary CLOB named clob_var .

• Calls WRITE() to write the contents of a VARCHAR2 variable named char_buffer_var to clob_var .

• Calls ISTEMPORARY() to check if clob_var is temporary.

• Calls READ() to read the contents of clob_var into char_buffer_var .

• Displays the contents of char_buffer_var .

• Calls FREETEMPORARY() to free clob_var .

   CREATE OR REPLACE PROCEDURE temporary_lob_example IS  clob_var CLOB;  amount_var INTEGER := 19;  offset_var INTEGER := 1;  char_buffer_var VARCHAR2(19) := 'Juliet is the sun'; BEGIN  DBMS_LOB.CREATETEMPORARY(clob_var, TRUE);  DBMS_LOB.WRITE(clob_var, amount_var, offset_var, char_buffer_var);  IF (DBMS_LOB.ISTEMPORARY(clob_var) = 1) THEN  DBMS_OUTPUT.PUT_LINE('clob_var is temporary');  END IF;  DBMS_LOB.READ(clob_var, amount_var, offset_var, char_buffer_var);  DBMS_OUTPUT.PUT_LINE('char_buffer_var = '  char_buffer_var);  DBMS_LOB.FREETEMPORARY(clob_var); END temporary_lob_example; / 

The following example calls temporary_lob_example() :

  CALL temporary_lob_example();  clob_var is temporary char_buffer_var = Juliet is the sun 

ERASE()

You use ERASE() to remove all or part of a LOB.

 DBMS_LOB.ERASE(  lob  IN OUT NOCOPY BLOB,  amount  IN OUT NOCOPY INTEGER,  offset  IN INTEGER := 1); DBMS_LOB.ERASE(  lob  IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,  amount  IN OUT NOCOPY INTEGER,  offset  IN INTEGER := 1); 

where

• lob is the LOB to erase.

• amount is the number of characters when reading from a CLOB , and the number of bytes when reading from a BLOB or BFILE .

• offset is the offset in characters or bytes in lob to start the erasure.

Table 14-8 shows the exceptions thrown by ERASE() .

Table 14-8: Exceptions Thrown by ERASE()

Exception	Thrown When
VALUE_ERROR	Any of the parameters are null.
INVALID_ARGVAL	Either: amount < 1 amount > LOBMAXSIZE offset < 1 offset > LOBMAXSIZE

The following erase_example() procedure performs the following tasks:

• Selects clob_column into clob_var from clob_content where id is 1 for update.

• Calls read_clob_example() to read and display the contents of clob_var .

• Calls ERASE() to erase part of clob_var .

• Calls read_clob_example() again to read and display the contents of clob_var .

• Performs a ROLLBACK to undo the erase.

   CREATE OR REPLACE PROCEDURE erase_example IS  clob_var CLOB;  offset_var INTEGER := 2;  amount_var INTEGER := 5; BEGIN  SELECT clob_column  INTO clob_var  FROM clob_content  WHERE id = 1  FOR UPDATE;  read_clob_example(1);  DBMS_LOB.ERASE(clob_var, amount_var, offset_var);  read_clob_example(1);  ROLLBACK; END erase_example; / 

The following example calls erase_example() :

  CALL erase_example();  char_buffer_var = Creeps in this petty pace amount_var = 25 char_buffer_var = C in this petty pace amount_var = 25 

FILECLOSE()

You use FILECLOSE() to close a BFILE .

 DBMS_LOB.FILECLOSE(  bfile  IN OUT NOCOPY BFILE); 

where

• bfile is the BFILE to close.

Table 14-9 shows the exceptions thrown by FILECLOSE() .

Table 14-9: Exceptions Thrown by FILECLOSE()

Exception	Thrown When
VALUE_ERROR	The bfile parameter is null.
UNOPENED_FILE	The file hasn't been opened yet.
NOEXIST_DIRECTORY	The directory doesn't exist.
NOPRIV_DIRECTORY	You don't have privileges to access the directory.
INVALID_DIRECTORY	The directory is invalid.
INVALID_OPERATION	The file exists, but you don't have privileges to access the file.

You ll see an example of FILECLOSE() later in the section on LOADFROMFILE() .

FILECLOSEALL()

You use FILECLOSEALL() to close all BFILE objects.

 DBMS_LOB.FILECLOSEALL; 

Table 14-10 shows the exception thrown by FILECLOSEALL() .

Table 14-10: Exception Thrown by FILECLOSEALL()

Exception	Thrown When
UNOPENED_FILE	No files have been opened in the session.

FILEEXISTS()

You use FILEEXISTS() to check if a file exists on the server.

 DBMS_LOB.FILEEXISTS(  bfile  IN BFILE) RETURN INTEGER; 

where

• bfile is the pointer to the file to check exists.

FILEEXISTS() returns

• 0 if the file doesn t exist.

• 1 if the file exists.

Table 14-11 shows the exceptions thrown by FILEEXISTS() .

Table 14-11: Exceptions Thrown by FILEEXISTS()

Exception	Thrown When
VALUE_ERROR	The bfile parameter is null.
NOEXIST_DIRECTORY	The directory doesn't exist.
NOPRIV_DIRECTORY	You don't have privileges to access the directory.
INVALID_DIRECTORY	The directory is invalid.

You ll see an example of FILEEXISTS() later in the section on LOADFROMFILE() .

FILEGETNAME()

You use FILEGETNAME() to get the directory alias and file name.

 DBMS_LOB.FILEGETNAME(  bfile  IN BFILE,  dir_alias  OUT VARCHAR2,  filename  OUT VARCHAR2); 

where

• bfile is the BFILE that points to the file.

• dir_alias is the directory alias.

• filename is the name of the file.

Table 14-12 shows the exceptions thrown by FILEGETNAME() .

Table 14-12: Exceptions Thrown by FILEGETNAME()

Exception	Thrown When
VALUE_ERROR	Any of the input parameters are null or invalid.
INVALID_ARGVAL	The dir_value or filename parameters are null.

You ll see an example of FILEGETNAME() later in the section on LOADFROMFILE() .

FILEISOPEN()

You use FILEISOPEN() to check if a file is open.

 DBMS_LOB.FILEISOPEN(  bfile  IN BFILE) RETURN INTEGER; 

where

• bfile is the pointer to the file.

FILEISOPEN() returns:

• 0 if the file isn t open.

• 1 if the file is open.

Table 14-13 shows the exceptions thrown by FILEISOPEN() .

Table 14-13: Exceptions Thrown by FILEISOPEN()

Exception	Thrown When
NOEXIST_DIRECTORY	The directory doesn't exist.
NOPRIV_DIRECTORY	You don't have privileges to access the directory.
INVALID_DIRECTORY	The directory is invalid.
INVALID_OPERATION	The file doesn't exist or you don't have access privileges on the file.

You ll see an example of FILEISOPEN() later in the section on LOADFROMFILE() .

FILEOPEN()

You use FILEOPEN() to open a file.

 DBMS_LOB.FILEOPEN(  bfile  IN OUT NOCOPY BFILE,  open_mode  IN BINARY_INTEGER := DBMS_LOB.FILE_READONLY); 

where

• bfile is the BFILE that points to the file.

• open_mode indicates the open mode. The default (and currently only open mode) is DBMS_LOB.FILE_READONLY , which indicates the file may only be read from.

Table 14-14 shows the exceptions thrown by FILEOPEN() .

Table 14-14: Exceptions Thrown by FILEOPEN()

Exception	Thrown When
VALUE_ERROR	Any of the input parameters are null or invalid.
INVALID_ARGVAL	The open_mode is not set to FILE_READONLY .
OPEN_TOOMANY	An attempt is made to open more than SESSION_MAX_OPEN_FILES files. SESSION_MAX_OPEN_FILES is an initialization parameter.
NOEXIST_DIRECTORY	The directory doesn't exist.
INVALID_DIRECTORY	The directory is invalid.
INVALID_OPERATION	The file exists, but you don't have privileges to access the file.

You ll see an example of FILEOPEN() later in the section on LOADFROMFILE() .

FREETEMPORARY()

You use FREETEMPORARY() to free the temporary BLOB or CLOB in the default temporary tablespace of the user.

 DBMS_LOB.FREETEMPORARY (  lob  IN OUT NOCOPY BLOB);  DBMS_LOB.FREETEMPORARY (  lob  IN OUT NOCOPY CLOB CHARACTER SET ANY_CS); 

where

• lob is the lob to be freed.

Table 14-15 shows the exception thrown by FREETEMPORARY() .

Table 14-15: Exception Thrown by FREETEMPORARY()

Exception	Thrown When
VALUE_ERROR	Any of the input parameters are null or invalid.

You saw an example of FREETEMPORARY() earlier in the section on CREATETEMPORARY() .

GETCHUNKSIZE()

You use GETCHUNKSIZE() to get the amount of space used in the LOB chunk to store the LOB value.

 DBMS_LOB.GETCHUNKSIZE(  lob  IN BLOB) RETURN INTEGER; DBMS_LOB.GETCHUNKSIZE(  lob  IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER; 

where

• lob is the LOB to get the chunk size for.

GETCHUNKSIZE() returns

• The chunk size in bytes for a BLOB .

• The chunk size in characters for a CLOB .

Table 14-16 shows the exception thrown by GETCHUNKSIZE() .

Table 14-16: Exception Thrown by GETCHUNKSIZE()

Exception	Thrown When
VALUE_ERROR	The lob parameter is null.

You ll see an example of GETCHUNKSIZE() later in the section on LOADFROMFILE() .

GET_STORAGE_LIMIT()

You use GET_STORAGE_LIMIT() to get the storage limit of a LOB.

 DBMS_LOB.GET_STROAGE_LIMIT() RETURN INTEGER; 

GET_STORAGE_LIMIT() returns

• The maximum allowable size of for a LOB.

GETLENGTH()

You use GETLENGTH() to gets the length of the LOB value.

 DBMS_LOB.GETLENGTH(  lob  IN BLOB) RETURN INTEGER; DBMS_LOB.GETLENGTH(  lob  IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER; DBMS_LOB.GETLENGTH(  bfile  IN BFILE) RETURN INTEGER; 

where

• lob is the BLOB or CLOB to get the length of.

• bfile is the BFILE to get the length of.

GETLENGTH() returns

• The length in bytes for a BLOB or BFILE .

• The length in characters for a CLOB .

Table 14-17 shows the exception thrown by GETLENGTH() .

Table 14-17: Exception Thrown by GETLENGTH()

Exception	Thrown When
VALUE_ERROR	The lob or bfile parameter is null.

You ll see an example of GETLENGTH() later in the section on LOADFROMFILE() .

INSTR()

You use INSTR() to get the matching position of the n th occurrence of the pattern in the LOB.

 DBMS_LOB.INSTR(  lob  IN BLOB,  pattern  IN RAW,  offset  IN INTEGER := 1,  n  IN INTEGER := 1) RETURN INTEGER; DBMS_LOB.INSTR(  lob  IN CLOB CHARACTER SET ANY_CS,  pattern  IN VARCHAR2 CHARACTER SET  lob  %CHARSET,  offset  IN INTEGER := 1,  n  IN INTEGER := 1) RETURN INTEGER; DBMS_LOB.INSTR (  bfile  IN BFILE,  pattern  IN RAW,  offset  IN INTEGER := 1,  n  IN INTEGER := 1) RETURN INTEGER; 

where

• lob is the BLOB or CLOB to read from.

• bfile is the BFILE to read from.

• pattern is the pattern to search for. The pattern is a group of RAW bytes for a BLOB or BFILE , and a VARCHAR2 character string for a CLOB . The maximum size of the pattern is 16,383 bytes.

• offset is the offset in bytes for a BLOB or BFILE and the characters for a CLOB at which the pattern matching is to start. The first byte or character is numbered as 1.

• n is the occurrence of pattern in the LOB content to search for.

INSTR() returns

• The offset of the start of the pattern (if found).

• Zero if the pattern isn t found.

• Null if:

  • Any of the IN parameters are null or invalid

  • offset < 1 or offset > LOBMAXSIZE

  • n < 1 or n > LOBMAXSIZE

Table 14-18 shows the exceptions thrown by INSTR() .

Table 14-18: Exception Thrown by INSTR()

Exception	Thrown When
VALUE_ERROR	Any of the input parameters are null or invalid.
UNOPENED_FILE	The BFILE isn't open.
NOEXIST_DIRECTORY	The directory doesn't exist.
NOPRIV_DIRECTORY	You don't have privileges on the directory.
INVALID_DIRECTORY	The directory is invalid.
INVALID_OPERATION	The file exists, but you don't have privileges to access the file.

The following instr_example() procedure performs the following tasks:

• Creates a VARCHAR2 variable named char_buffer_var containing the string It is the east and Juliet is the sun .

• Calls CREATETEMPORARY() to create a temporary CLOB named clob_var .

• Calls WRITE() to write the contents of a VARCHAR2 variable named char_buffer_var to clob_var .

• Calls READ() to read the contents of clob_var into char_buffer_var .

• Calls INSTR() to search clob_var for the second occurrence of is , which returns 29.

• Calls INSTR() to search clob_var for the first occurrence of Moon , which returns 0 because Moon doesn t appear in clob_var .

• Calls FREETEMPORARY() to free clob_var .

   CREATE OR REPLACE PROCEDURE instr_example IS  clob_var CLOB;  char_buffer_var VARCHAR2(50) :=  'It is the east and Juliet is the sun';  pattern_var VARCHAR2(5);  offset_var INTEGER := 1;  amount_var INTEGER := 38;  occurrence_var INTEGER;  return_var INTEGER; BEGIN  DBMS_LOB.CREATETEMPORARY(clob_var, TRUE);  DBMS_LOB.WRITE(clob_var, amount_var, offset_var, char_buffer_var);  DBMS_LOB.READ(clob_var, amount_var, offset_var, char_buffer_var);  DBMS_OUTPUT.PUT_LINE('char_buffer_var = '  char_buffer_var);  DBMS_OUTPUT.PUT_LINE('Searching second ''is''');  pattern_var := 'is';  occurrence_var := 2;  return_var := DBMS_LOB.INSTR(clob_var, pattern_var, offset_var, occurrence_var);  DBMS_OUTPUT.PUT_LINE('return_var = '  return_var);  DBMS_OUTPUT.PUT_LINE('Searching for ''Moon''');  pattern_var := 'Moon';  occurrence_var := 1;  return_var := DBMS_LOB.INSTR(clob_var, pattern_var, offset_var, occurrence_var);  DBMS_OUTPUT.PUT_LINE('return_var = '  return_var);  DBMS_LOB.FREETEMPORARY(clob_var); END instr_example; / 

The following example calls instr_example() :

  CALL instr_example();  char_buffer_var = It is the east and Juliet is the sun Searching second 'is' return_var = 29 Searching for 'Moon' return_var = 0 

ISOPEN()

You use ISOPEN() to check if the LOB was already opened using the input locator.

 DBMS_LOB.ISOPEN(  lob  IN BLOB) RETURN INTEGER; DBMS_LOB.ISOPEN(  lob  IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER; DBMS_LOB.ISOPEN(  bfile  IN BFILE) RETURN INTEGER; 

where

• lob is the BLOB or CLOB to check.

• bfile is the BFILE to check.

ISOPEN() returns

• 0 if the LOB isn t open.

• 1 if the LOB is open.

Table 14-19 shows the exception thrown by ISOPEN() .

Table 14-19: Exception Thrown by ISOPEN()

Exception	Thrown When
VALUE_ERROR	The lob or bfile parameter is null or invalid.

ISTEMPORARY()

You use ISTEMPORARY() to check if the locator is pointing to a temporary LOB.

 DBMS_LOB.ISTEMPORARY(  lob  IN BLOB) RETURN INTEGER; DBMS_LOB.ISTEMPORARY (  lob  IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER; 

where

• lob is the BLOB or CLOB to check.

ISTEMPORARY() returns

• 0 if the LOB isn t temporary.

• 1 if the LOB is temporary.

Table 14-20 shows the exception thrown by ISTEMPORARY() .

Table 14-20: Exception Thrown by ISTEMPORARY()

Exception	Thrown When
VALUE_ERROR	The lob parameter is null or invalid.

You saw an example of ISTEMPORARY() earlier in the section on CREATETEMPORARY() .

LOADFROMFILE()

You use LOADFROMFILE() to load BFILE data into an internal LOB.

 DBMS_LOB.LOADFROMFILE(  dest_lob  IN OUT NOCOPY BLOB,  src_bfile  IN BFILE,  amount  IN INTEGER,  dest_offset  IN INTEGER := 1,  src_offset  IN INTEGER := 1); DBMS_LOB.LOADFROMFILE(  dest_lob  IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,  src_bfile  IN BFILE,  amount  IN INTEGER,  dest_offset  IN INTEGER := 1,  src_offset  IN INTEGER := 1); 

where

• dest_lob is the target LOB into which the data is to be loaded.

• src_bfile is the source BFILE from which the data is to be read.

• amount is the number of bytes to load from src_bfile .

• dest_offset is the offset in bytes or characters in dest_lob to start the load (offset starts at 1).

• src_offset is the offset in bytes in src_bfile to start reading (offset starts at 1).

Table 14-21 shows the exceptions thrown by LOADFROMFILE() .

Table 14-21: Exceptions Thrown by LOADFROMFILE()

Exception	Thrown When
VALUE_ERROR	Any of the input parameters are null or invalid.
INVALID_ARGVAL	Either: src_offset < 1 dest_offset < 1 src_offset > LOBMAXSIZE dest_offset > LOBMAXSIZE amount < 1 amount > LOBMAXSIZE

The following file_example() procedure performs the following tasks:

• Creates a temporary lob named dest_clob_var .

• Calls FILEEXISTS() to check if the file exits.

• If the file exists, calls FILEISOPEN() to check if the file is open.

• If the file is not open, calls FILEOPEN() to open the BFILE , storing the locator in src_bfile_var .

• Calls FILEGETNAME() to get the name of the file and the directory alias.

• Calls GETCHUNKSIZE() to get the chunk size from dest_clob_var .

• Calls GETLENGTH() to get the length from src_bfile_var and stores it in length_var .

• While the number of characters read (stored in chars_read_var ) is less than length_var :

  • If length_var minus chars_read is less than amount_var ( amount_var is initially set to 20), sets amount_var to length_var minus chars_read_var . This is done because LOADFROMFILE() expects amount_var to be less than or equal to the content read from src_bfile_var .

  • Loads amount_var characters from clob_var using LOADFROMFILE() .

  • Calls READ() to read the contents of dest_clob_var into char_buffer_var .

  • Calls PUT_LINE() to display the contents of char_buffer_var .

  • Adds amount_var to chars_read_var .

• Calls FILECLOSE() to close src_bfile_var .

• Calls FREETEMPORARY() to free dest_clob_var .

   CREATE OR REPLACE PROCEDURE file_example IS  src_bfile_var BFILE;  dir_alias_var VARCHAR2(50);  filename_var VARCHAR2(50);  chunk_size_var INTEGER;  length_var INTEGER;  chars_read_var INTEGER;  dest_clob_var CLOB;  amount_var INTEGER := 20;  dest_offset_var INTEGER := 1;  src_offset_var INTEGER := 1;  char_buffer_var VARCHAR2(20); BEGIN  SELECT bfile_column  INTO src_bfile_var  FROM bfile_content  WHERE id = 1;  DBMS_LOB.CREATETEMPORARY(dest_clob_var, TRUE);  IF (DBMS_LOB.FILEEXISTS(src_bfile_var) = 1) THEN   IF (DBMS_LOB.FILEISOPEN(src_bfile_var) = 0) THEN   DBMS_LOB.FILEOPEN(src_bfile_var);    DBMS_LOB.FILEGETNAME(src_bfile_var, dir_alias_var, filename_var);  DBMS_OUTPUT.PUT_LINE('Directory alias = '  dir_alias_var);   DBMS_OUTPUT.PUT_LINE('Filename = '  filename_var);   chunk_size_var := DBMS_LOB.GETCHUNKSIZE(dest_clob_var);   DBMS_OUTPUT.PUT_LINE('Chunk size = '  chunk_size_var);    length_var := DBMS_LOB.GETLENGTH(src_bfile_var);  DBMS_OUTPUT.PUT_LINE('Length = '  length_var);  chars_read_var := 0;   WHILE (chars_read_var < length_var) LOOP    IF (length_var - chars_read_var < amount_var) THEN    amount_var := length_var - chars_read_var;    END IF;    DBMS_LOB.LOADFROMFILE(dest_clob_var, src_bfile_var,    amount_var, dest_offset_var,    src_offset_var + chars_read_var);   DBMS_LOB.READ(dest_clob_var, amount_var, src_offset_var, char_buffer_var);    DBMS_OUTPUT.PUT_LINE('char_buffer_var = '  char_buffer_var);   chars_read_var := chars_read_var + amount_var;   END LOOP;   END IF;  END IF;  DBMS_LOB.FILECLOSE(src_bfile_var);  DBMS_LOB.FREETEMPORARY(dest_clob_var); END file_example; / 

The following example calls file_example() :

  CALL file_example();  Directory alias = SAMPLE_FILES_DIR Filename = textContent.txt Chunk size = 4036 Length = 416 char_buffer_var = To-morrow, and to-mo char_buffer_var = rrow, and to-morrow, char_buffer_var =  Creeps in this pet char_buffer_var = ty pace from day to char_buffer_var = day, To the last sy char_buffer_var = llable of recorded t char_buffer_var = ime; And all our ye char_buffer_var = sterdays have lighte char_buffer_var = d fools The way to char_buffer_var = a dusty death. Out, char_buffer_var = out, brief candle! char_buffer_var = Life's but a walking char_buffer_var = shadow; a poor play char_buffer_var = er, That struts and char_buffer_var = frets his hour upon char_buffer_var = the stage, And the char_buffer_var = n is heard no more: char_buffer_var = it is a tale Told b char_buffer_var = y an idiot, full of char_buffer_var = sound and fury, Sig char_buffer_var = nifying nothing. 

LOADBLOBFROMFILE()

You use LOADBLOBFROMFILE() to load BFILE data into an internal BLOB .

 DBMS_LOB.LOADBLOBFROMFILE(  dest_blob  IN OUT NOCOPY BLOB,  src_bfile  IN  BFILE,  amount  IN    INTEGER,  dest_offset  IN OUT  INTEGER := 1,  src_offset  IN OUT  INTEGER := 1); 

where

• dest_blob is the target BLOB into which the data is to be loaded.

• src_bfile is the source BFILE from which the data is to be read.

• amount is the number of bytes to load from src_bfile .

• dest_offset is the offset in bytes or characters in dest_lob to start the load (offset starts at 1).

• src_offset is the offset in bytes in src_bfile to start reading (offset starts at 1).

Table 14-22 shows the exceptions thrown by LOADBLOBFROMFILE() .

Table 14-22: Exceptions Thrown by LOADBLOBFROMFILE()

Exception	Thrown When
VALUE_ERROR	Any of the input parameters are null or invalid.
INVALID_ARGVAL	Either: src_offset < 1 dest_offset < 1 src_offset > LOBMAXSIZE dest_offset > LOBMAXSIZE amount < 1 amount > LOBMAXSIZE

LOADCLOBFROMFILE()

You use LOADCLOBFROMFILE() to load BFILE data into an internal CLOB .

 DBMS_LOB.LOADCLOBFROMFILE(  dest_clob  IN OUT NOCOPY CLOB,  src_bfile  IN   BFILE,  amount  IN   INTEGER,  dest_offset  IN OUT   INTEGER,  src_offset  IN OUT INTEGER,  src_csid  IN  NUMBER,  lang_context  IN OUT  INTEGER,  warning  OUT     INTEGER); 

where

• dest_blob is the target BLOB into which the data is to be loaded.

• src_bfile is the source BFILE from which the data is to be read.

• amount is the number of bytes to load from src_bfile .

• dest_offset is the offset in bytes or characters in dest_lob to start the load (offset starts at 1).

• src_offset is the offset in bytes in src_bfile to start reading (offset starts at 1).

• src_csid is the character set of src_bfile .

• lang_context is the language context of the load (the default is 0, which means the default language context is used).

• warning is a warning message that contains information if there was a problem with the load. An example problem is a character in src_bfile cannot be converted to a character in dest_lob .

Table 14-23 shows the exceptions thrown by LOADCLOBFROMFILE() .

Table 14-23: Exceptions Thrown by LOADCLOBFROMFILE()

Exception	Thrown When
VALUE_ERROR	Any of the input parameters are null or invalid.
INVALID_ARGVAL	Either: src_offset < 1 dest_offset < 1 src_offset > LOBMAXSIZE dest_offset > LOBMAXSIZE amount < 1 amount > LOBMAXSIZE

OPEN()

You use OPEN() to open a LOB (internal, external, or temporary) in the indicated mode.

 DBMS_LOB.OPEN(  lob  IN OUT NOCOPY BLOB,  open_mode  IN   BINARY_INTEGER); DBMS_LOB.OPEN(  lob  IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,  open_mode  IN    BINARY_INTEGER); DBMS_LOB.OPEN(  bfile  IN OUT NOCOPY BFILE,  open_mode  IN   BINARY_INTEGER := DBMS_LOB.FILE_READONLY); 

where

• lob is the BLOB or CLOB to open.

• bfile is the BFILE to open.

• open_mode indicates the open mode. The default is DBMS_LOB.FILE_READONLY which indicates the LOB may only be read from. DBMS_LOB.FILE_READWRITE indicates the LOB may read from and written to.

Table 14-24 shows the exception thrown by OPEN() .

Table 14-24: Exception Thrown by OPEN()

Exception	Thrown When
VALUE_ERROR	Any of the input parameters are null or invalid.

SUBSTR()

You use SUBSTR() to read part of the LOB value starting at the specified offset.

 DBMS_LOB.SUBSTR(  lob  IN BLOB,  amount  IN INTEGER := 32767,  offset  IN INTEGER := 1) RETURN RAW; DBMS_LOB.SUBSTR (  lob  IN CLOB CHARACTER SET ANY_CS,  amount  IN INTEGER := 32767,  offset  IN INTEGER := 1) RETURN VARCHAR2 CHARACTER SET  lob  %CHARSET; DBMS_LOB.SUBSTR (  bfile  IN BFILE,  amount  IN INTEGER := 32767,  offset  IN INTEGER := 1) RETURN RAW; 

where

• lob is the CLOB or BLOB to read from.

• bfile is the BFILE to read from.

• amount is the number of characters to read from the CLOB or the number of bytes to read from the BLOB or BFILE .

• offset is the offset in characters to read from the CLOB or the number of bytes to read from the BLOB or BFILE (offset starts at 1).

SUBSTR() returns

• RAW data when reading from a BLOB or BFILE .

• VARCHAR2 data when reading from a CLOB .

• Null if:

  • Any of the IN parameters are null

  • amount < 1

  • amount > 32767

  • offset < 1

  • offset > LOBMAXSIZE

Table 14-25 shows the exceptions thrown by SUBSTR() .

Table 14-25: Exceptions Thrown by SUBSTR()

Exception	Thrown When
VALUE_ERROR	Any of the input parameters are null or invalid.
UNOPENED_FILE	The BFILE isn't open.
NOEXIST_DIRECTORY	The directory doesn't exist.
NOPRIV_DIRECTORY	You don't have privileges on the directory.
INVALID_DIRECTORY	The directory is invalid.
INVALID_OPERATION	The file exists, but you don't have privileges to access the file.

TRIM()

You use TRIM() to cut off the LOB value to the specified shorter length.

 DBMS_LOB.TRIM(  lob  IN OUT NOCOPY BLOB,  newlen  IN  INTEGER); DBMS_LOB.TRIM(  lob  IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,  newlen  IN   INTEGER); 

where

• lob is the BLOB or CLOB to open.

• newlen is the new length of the BLOB in bytes or characters of the CLOB .

Table 14-26 shows the exceptions thrown by TRIM() .

Table 14-26: Exceptions Thrown by TRIM()

Exception	Thrown When
VALUE_ERROR	The lob parameter is null.
INVALID_ARGVAL	Either: newlen < newlen > LOBMAXSIZE

WRITEAPPEND()

You use WRITEAPPEND() to write data from the buffer to the end of a LOB.

 DBMS_LOB.WRITEAPPEND(  lob  IN OUT NOCOPY BLOB,  amount  IN    BINARY_INTEGER,  buffer  IN    RAW); DBMS_LOB.WRITEAPPEND(  lob  IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,  amount  IN   BINARY_INTEGER,  buffer  IN    VARCHAR2 CHARACTER SET  lob  %CHARSET); 

where

• lob is the CLOB or BLOB to write to.

• amount is the number of characters to write to the CLOB or the number of bytes to write to the BLOB .

• buffer is the storage variable where the input to the write is to be read from.

Table 14-27 shows the exceptions thrown by WRITEAPPEND() .

Table 14-27: Exceptions Thrown by WRITEAPPEND()

Exception	Thrown When
VALUE_ERROR	Any of the input parameters are null or invalid.
INVALID_ARGVAL	Either: amount < 1 amount > MAXBUFSIZE