Oracle Database 10g SQL (Osborne ORACLE Press Series)

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.

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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