Table of Contents Previous Next


7 Built-In Packages : 7.4 DBMS_LOB

The DBMS_LOB package provides the capability to operate on large objects.
APPEND(dest_lob IN OUT, src_lob)
COMPARE(lob_1, lob_2 [, amount [, offset_1 [, offset_2 ]]])
CONVERTOBLOB(dest_lob IN OUT, src_clob, amount, dest_offset IN OUT, src_offset IN OUT, blob_csid, lang_context IN OUT, warning OUT)
CONVERTTOCLOB(dest_lob IN OUT, src_blob, amount, dest_offset IN OUT, src_offset IN OUT, blob_csid, lang_context IN OUT, warning OUT)
COPY(dest_lob IN OUT, src_lob, amount [, dest_offset [, src_offset ]])
ERASE(lob_loc IN OUT, amount IN OUT [, offset ])
GETLENGTH(lob_loc)
INSTR(lob_loc, pattern [, offset [, nth ]])
READ(lob_loc, amount IN OUT, offset, buffer OUT)
SUBSTR(lob_loc [, amount [, offset ]])
RAW, VARCHAR2
TRIM(lob_loc IN OUT, newlen)
WRITE(lob_loc IN OUT, amount, offset, buffer)
WRITEAPPEND(lob_loc IN OUT, amount, buffer)
Advanced Server's implementation of DBMS_LOB is a partial implementation when compared to Oracle's version. Only those functions and procedures listed in the table above are supported.
In the following sections, lengths and offsets are measured in bytes if the large objects are BLOBs. Lengths and offsets are measured in characters if the large objects are CLOBs.
7.4.1 APPEND
The APPEND procedure provides the capability to append one large object to another. Both large objects must be of the same type.
APPEND(dest_lob IN OUT { BLOB | CLOB }, src_lob { BLOB | CLOB })
7.4.2 COMPARE
The COMPARE procedure performs an exact byte-by-byte comparison of two large objects for a given length at given offsets. The large objects being compared must be the same data type.
status INTEGER COMPARE(lob_1 { BLOB | CLOB },
lob_2 { BLOB | CLOB }
[, amount INTEGER [, offset_1 INTEGER [, offset_2 INTEGER ]]])
If the data type of the large objects is BLOB, then the comparison is made for amount bytes. If the data type of the large objects is CLOB, then the comparison is made for amount characters. The default it the maximum size of a large object.
The CONVERTTOBLOB procedure provides the capability to convert character data to binary.
CONVERTTOBLOB(dest_lob IN OUT BLOB, src_clob CLOB,
amount INTEGER, dest_offset IN OUT INTEGER,
src_offset IN OUT INTEGER, blob_csid NUMBER,
lang_context IN OUT INTEGER, warning OUT INTEGER)
BLOB large object locator to which the character data is to be converted.
CLOB large object locator of the character data to be converted.
Number of characters of src_clob to be converted.
Position in bytes in the destination BLOB where writing of the source CLOB should begin. The first byte is offset 1.
Position in bytes in the destination BLOB after the write operation completes. The first byte is offset 1.
Position in characters in the source CLOB where conversion to the destination BLOB should begin. The first character is offset 1.
Position in characters in the source CLOB after the conversion operation completes. The first character is offset 1.
The CONVERTTOCLOB procedure provides the capability to convert binary data to character.
CONVERTTOCLOB(dest_lob IN OUT CLOB, src_blob BLOB,
amount INTEGER, dest_offset IN OUT INTEGER,
src_offset IN OUT INTEGER, blob_csid NUMBER,
lang_context IN OUT INTEGER, warning OUT INTEGER)
CLOB large object locator to which the binary data is to be converted.
BLOB large object locator of the binary data to be converted.
Number of bytes of src_blob to be converted.
Position in characters in the destination CLOB where writing of the source BLOB should begin. The first character is offset 1.
Position in characters in the destination CLOB after the write operation completes. The first character is offset 1.
Position in bytes in the source BLOB where conversion to the destination CLOB should begin. The first byte is offset 1.
Position in bytes in the source BLOB after the conversion operation completes. The first byte is offset 1.
7.4.5 COPY
The COPY procedure provides the capability to copy one large object to another. The source and destination large objects must be the same data type.
COPY(dest_lob IN OUT { BLOB | CLOB }, src_lob
{ BLOB | CLOB },
amount INTEGER
[, dest_offset INTEGER [, src_offset INTEGER ]])
Large object locator of the large object to which src_lob is to be copied. Must be the same data type as src_lob.
Large object locator of the large object to be copied to dest_lob. Must be the same data type as dest_lob.
Number of bytes/characters of src_lob to be copied.
7.4.6 ERASE
The ERASE procedure provides the capability to erase a portion of a large object. To erase a large object means to replace the specified portion with zero-byte fillers for BLOBs or with spaces for CLOBs. The actual size of the large object is not altered.
ERASE(lob_loc IN OUT { BLOB | CLOB }, amount IN OUT INTEGER
[, offset INTEGER ])
amount OUT
The GET_STORAGE_LIMIT function returns the limit on the largest allowable large object.
size INTEGER GET_STORAGE_LIMIT(lob_loc BLOB)
size INTEGER GET_STORAGE_LIMIT(lob_loc CLOB)
7.4.8 GETLENGTH
The GETLENGTH function returns the length of a large object.
amount INTEGER GETLENGTH(lob_loc BLOB)
amount INTEGER GETLENGTH(lob_loc CLOB)
Length of the large object in bytes for BLOBs or characters for CLOBs.
7.4.9 INSTR
The INSTR function returns the location of the nth occurrence of a given pattern within a large object.
position INTEGER INSTR(lob_loc { BLOB | CLOB },
pattern { RAW | VARCHAR2 } [, offset INTEGER [, nth INTEGER ]])
Pattern of bytes or characters to match against the large object, lob. pattern must be RAW if lob_loc is a BLOB. pattern must be VARCHAR2 if lob_loc is a CLOB.
Position within lob_loc to start search for pattern. The first byte/character is position 1. The default is 1.
Search for pattern, nth number of times starting at the position given by offset. The default is 1.
Position within the large object where pattern appears the nth time specified by nth starting from the position given by offset.
7.4.10 READ
The READ procedure provides the capability to read a portion of a large object into a buffer.
READ(lob_loc { BLOB | CLOB }, amount IN OUT BINARY_INTEGER,
offset INTEGER, buffer OUT { RAW | VARCHAR2 })
amount OUT
Number of bytes/characters actually read. If there is no more data to be read, then amount returns 0 and a DATA_NOT_FOUND exception is thrown.
Variable to receive the large object. If lob_loc is a BLOB, then buffer must be RAW. If lob_loc is a CLOB, then buffer must be VARCHAR2.
7.4.11 SUBSTR
The SUBSTR function provides the capability to return a portion of a large object.
data { RAW | VARCHAR2 } SUBSTR(lob_loc { BLOB | CLOB }
[, amount INTEGER [, offset INTEGER ]])
Returned portion of the large object to be read. If lob_loc is a BLOB, the return data type is RAW. If lob_loc is a CLOB, the return data type is VARCHAR2.
7.4.12 TRIM
The TRIM procedure provides the capability to truncate a large object to the specified length.
TRIM(lob_loc IN OUT { BLOB | CLOB }, newlen INTEGER)
7.4.13 WRITE
The WRITE procedure provides the capability to write data into a large object. Any existing data in the large object at the specified offset for the given length is overwritten by data given in the buffer.
WRITE(lob_loc IN OUT { BLOB | CLOB },
amount BINARY_INTEGER,
offset INTEGER, buffer { RAW | VARCHAR2 })
The number of bytes/characters in buffer to be written to the large object.
Contains data to be written to the large object. If lob_loc is a BLOB, then buffer must be RAW. If lob_loc is a CLOB, then buffer must be VARCHAR2.
The WRITEAPPEND procedure provides the capability to add data to the end of a large object.
WRITEAPPEND(lob_loc IN OUT { BLOB | CLOB },
amount BINARY_INTEGER, buffer { RAW | VARCHAR2 })
Number of bytes/characters from buffer to be appended the large object.
Data to be appended to the large object. If lob_loc is a BLOB, then buffer must be RAW. If lob_loc is a CLOB, then buffer must be VARCHAR2.

7 Built-In Packages : 7.4 DBMS_LOB

Table of Contents Previous Next