server's character set is used. Source_Locator is the locator of the source LOB being copied. Amount is how much of the LOB you intend to copy. Dest_Start_Position and Source_Start_Position are the locations in the LOB to copy from and copy to. A value of 1 indicates the starting position of the LOB.

The EMPTY_BLOB Function

To add a BLOB to a table, you need to assign a locator to the BLOB with the EMPTY_BLOB function.

The Syntax of the EMPTY_BLOB Function

FUNCTION EMPTY_BLOB();
     RETURN Locator;

Locator is the locator for the BLOB returned by the function.

The EMPTY_CLOB Function

To add a CLOB to a table, you need to assign a locator to the CLOB with the EMPTY_CLOB function.

The Syntax for the EMPTY_CLOB Function

FUNCTION EMPTY_CLOB();
     RETURN Locator;

This function returns a locator for the CLOB, defined in the syntax as Locator.

The ERASE Procedure

The ERASE procedure allows you to erase all or part of a LOB.

The Syntax for the ERASE Procedure

PROCEDURE ERASE(BLOB_Locator IN OUT BLOB,
                       Amount IN OUT INTEGER,
                          Start_Position IN INTEGER := 1);


PROCEDURE APPEND(CLOB_Locator IN OUT CLOB ,
                       Amount IN OUT INTEGER,
                          Start_Position IN INTEGER := 1);

BLOB/CLOB_Locator is the locator assigned to the LOB. Amount is how much of the LOB you want to erase. You could use the GETLENGTH function to return the length and specify this as the amount in order to erase the contents of the entire LOB. Start_Position is the starting position from which you want to erase part or all of the LOB. A value of 1 is the beginning of the LOB.

The GETLENGTH Function

The GETLENGTH function returns the actual length of the objects in bytes.

The Syntax for the GETLENGTH Function

FUNCTION GETLENGTH (BLOB_Locator)
     RETURN Length_Integer;

FUNCTION GETLENGTH (CLOB_Locator CHARACTER SET Set_Desired)
     RETURN Length_Integer;

The parameter BLOB/CLOB_Locator is the locator assigned to the LOB. The Set_Desired parameter is the Oracle character set you want to use. The function returns an INTEGER of the length of the LOB or NULL if the locator is NULL.

The INSTR Function

The INSTR function allows you to match a pattern against the nth occurrence in the LOB starting from the offset specified.

The Syntax for the INSTR Function

FUNCTION INSTR(BLOB_Locator,
                Pattern IN RAW,
                   Starting_Location IN INTEGER := 1,
                Nth_Occurrence IN INTEGER := 1)
RETURN Status_Integer;

FUNCTION INSTR(CLOB_Locator CHARACTER SET Set_Desired,
                Pattern IN VARCHAR2 CHARACTER SET CLOB_Locator%CHARSET,
                     Starting_Location IN INTEGER := 1,
                Nth_Occurrence IN INTEGER := 1)
RETURN Status_Integer;

The parameter BLOB/CLOB_Locator is the BFILE locator assigned to the LOB. The Set_Desired parameter is the character set you want to use. If not specified, the character set used for the Oracle database will be used. The Pattern of type RAW or VARCHAR2 is the pattern you want to match in the LOB. Starting_Location is the position in the LOB where you want to start your search for a match. Nth_Occurrence is the nth time a match has been made in the LOB. The function returns a value of 0 if the pattern is not found, it returns the offset from the start of the LOB where the match was found, or it returns a value of NULL if any of the parameters are NULL or invalid.

The READ Procedure

The READ procedure allows you to read part or all of a LOB into a buffer.

The Syntax for the READ Procedure

PROCEDURE READ(BLOB_Locator,
                  Read_Amount IN BINARY_INTEGER,
                    Starting_Location IN INTEGER,
                  Buffer OUT RAW);

PROCEDURE READ(CLOB_Locator CHARACTER SET Set_Desired,
                  Read_Amount IN BINARY_INTEGER,
                      Starting_Location IN INTEGER,
                  Buffer OUT VARCHAR2 CHARACTER SET CLOB_Locator%CHARSET);

The parameter BLOB/CLOB_Locator is the locator assigned to the LOB. The Set_Desired parameter is the character set you want to use. If no character set is specified, the Oracle server's character set is used. The second parameter, Read_Amount, is the number of bytes you will read from the LOB into the buffer. The Buffer parameter stores the data from the READ procedure. Starting_Location is the location you want to start reading from the LOB. The VALUE_ERROR exception is raised if any of the parameters are NULL. The INVALID_ARGVAL exception is raised if any of the arguments are invalid. NO_DATA_FOUND is raised if you have reached the end of the LOB.

The SUBSTR Function

The SUBSTR function allows you to extract a specified amount of bytes from a LOB.

The Syntax for the SUBSTR Function

FUNCTION SUBSTR(BLOB_Locator,
                        Read_Amount IN BINARY_INTEGER,
                            Starting_Location IN INTEGER := 1)
RETURN RAW;

FUNCTION SUBSTR(CLOB_Locator CHARACTER SET Set_Desired,
                        Read_Amount IN BINARY_INTEGER,
                            Starting_Location IN INTEGER := 1)
RETURN VARCHAR2 CHARACTER SET CLOB_Locator%CHARSET;

The parameter BLOB/CLOB_Locator is the locator assigned to the LOB. The Set_Desired parameter is the character set you want to use. If no character set is specified, the Oracle server's character set is used. Read_Amount is the number of bytes you want to extract from the LOB. Starting_Location is the position in the LOB where you want to start your extraction. The function will return a RAW value if successful for a BLOB or VARCHAR2 for a CLOB.

The TRIM Procedure

The TRIM procedure allows you to reduce the LOB to the length specified.

The Syntax for the TRIM Procedure

PROCEDURE TRIM(BLOB_Locator,New_Length IN INTEGER);

PROCEDURE TRIM(CLOB_Locator,New_Length IN INTEGER);

The parameter BLOB/CLOB_Locator is the locator assigned to the LOB. The variable New_Length is the new length desired for the LOB.

The WRITE Procedure

If you can read a LOB, you should be able to write to a LOB. The WRITE procedure allows you to write to a LOB.