Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 7.3 DBMS_LOB

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

7.3 DBMS_LOB

The DBMS_LOB package provides the capability to operate on large objects.

Table 7-3 DBMS_LOB Functions/Procedures

Function/Procedure

Function or Procedure

Return Type

Description

APPEND(dest_lob IN OUT, src_lob)

Procedure

n/a

Appends one large object to another.

COMPARE(lob_1, lob_2 [, amount [, offset_1 [, offset_2 ]]])

Function

INTEGER

Compares two large objects.

CONVERTOBLOB(dest_lob IN OUT, src_clob, amount, dest_offset IN OUT, src_offset IN OUT, blob_csid, lang_context IN OUT, warning OUT)

Procedure

n/a

Converts character data to binary.

CONVERTTOCLOB(dest_lob IN OUT, src_blob, amount, dest_offset IN OUT, src_offset IN OUT, blob_csid, lang_context IN OUT, warning OUT)

Procedure

n/a

Converts binary data to character.

COPY(dest_lob IN OUT, src_lob, amount [, dest_offset [, src_offset ]])

Procedure

n/a

Copies one large object to another.

ERASE(lob_loc IN OUT, amount IN OUT [, offset ])

Procedure

n/a

Erase a large object.

GET_STORAGE_LIMIT(lob_loc)

Function

INTEGER

Get the storage limit for large objects.

GETLENGTH(lob_loc)

Function

INTEGER

Get the length of the large object.

INSTR(lob_loc, pattern [, offset [, nth ]])

Function

INTEGER

Get the position of the nth occurrence of a pattern in the large object starting at offset.

READ(lob_loc, amount IN OUT, offset, buffer OUT)

Procedure

n/a

Read a large object.

SUBSTR(lob_loc [, amount [, offset ]])

Function

RAW, VARCHAR2

Get part of a large object.

TRIM(lob_loc IN OUT, newlen)

Procedure

n/a

Trim a large object to the specified length.

WRITE(lob_loc IN OUT, amount, offset, buffer)

Procedure

n/a

Write data to a large object.

WRITEAPPEND(lob_loc IN OUT, amount, buffer)

Procedure

n/a

Write data from the buffer to the end of a large object.

The following table lists the public variables available in the package.

Table 7-4 DBMS_LOB Public Variables

Public Variables

Data Type

Value

compress off

INTEGER

0

compress_on

INTEGER

1

deduplicate_off

INTEGER

0

deduplicate_on

INTEGER

4

default_csid

INTEGER

0

default_lang_ctx

INTEGER

0

encrypt_off

INTEGER

0

encrypt_on

INTEGER

1

file_readonly

INTEGER

0

lobmaxsize

INTEGER

1073741823

lob_readonly

INTEGER

0

lob_readwrite

INTEGER

1

no_warning

INTEGER

0

opt_compress

INTEGER

1

opt_deduplicate

INTEGER

4

opt_encrypt

INTEGER

2

warn_inconvertible_char

INTEGER

1

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.3.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 })

Parameters

dest_lob

Large object locator for the destination object. Must be the same data type as src_lob.

src_lob

Large object locator for the source object. Must be the same data type as dest_lob.

7.3.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 ]]])

Parameters

lob_1

Large object locator of the first large object to be compared. Must be the same data type as lob_2.

lob_2

Large object locator of the second large object to be compared. Must be the same data type as lob_1.

amount

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.

offset_1

Position within the first large object to begin the comparison. The first byte/character is offset 1. The default is 1.

offset_2

Position within the second large object to begin the comparison. The first byte/character is offset 1. The default is 1.

status

Zero if both large objects are exactly the same for the specified length for the specified offsets. Non-zero, if the objects are not the same. NULL if amount, offset_1, or offset_2 are less than zero.

7.3.3 CONVERTTOBLOB

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)

Parameters

dest_lob

BLOB large object locator to which the character data is to be converted.

src_clob

CLOB large object locator of the character data to be converted.

amount

Number of characters of src_clob to be converted.

dest_offset IN

Position in bytes in the destination BLOB where writing of the source CLOB should begin. The first byte is offset 1.

dest_offset OUT

Position in bytes in the destination BLOB after the write operation completes. The first byte is offset 1.

src_offset IN

Position in characters in the source CLOB where conversion to the destination BLOB should begin. The first character is offset 1.

src_offset OUT

Position in characters in the source CLOB after the conversion operation completes. The first character is offset 1.

blob_csid

Character set ID of the converted, destination BLOB.

lang_context IN

Language context for the conversion. The default value of 0 is typically used for this setting.

lang_context OUT

Language context after the conversion completes.

warning

0 if the conversion was successful, 1 if an inconvertible character was encountered.

7.3.4 CONVERTTOCLOB

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)

Parameters

dest_lob

CLOB large object locator to which the binary data is to be converted.

src_blob

BLOB large object locator of the binary data to be converted.

amount

Number of bytes of src_blob to be converted.

dest_offset IN

Position in characters in the destination CLOB where writing of the source BLOB should begin. The first character is offset 1.

dest_offset OUT

Position in characters in the destination CLOB after the write operation completes. The first character is offset 1.

src_offset IN

Position in bytes in the source BLOB where conversion to the destination CLOB should begin. The first byte is offset 1.

src_offset OUT

Position in bytes in the source BLOB after the conversion operation completes. The first byte is offset 1.

blob_csid

Character set ID of the converted, destination CLOB.

lang_context IN

Language context for the conversion. The default value of 0 is typically used for this setting.

lang_context OUT

Language context after the conversion completes.

warning

0 if the conversion was successful, 1 if an inconvertible character was encountered.

7.3.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 ]])

Parameters

dest_lob

Large object locator of the large object to which src_lob is to be copied. Must be the same data type as src_lob.

src_lob

Large object locator of the large object to be copied to dest_lob. Must be the same data type as dest_lob.

amount

Number of bytes/characters of src_lob to be copied.

dest_offset

Position in the destination large object where writing of the source large object should begin. The first position is offset 1. The default is 1.

src_offset

Position in the source large object where copying to the destination large object should begin. The first position is offset 1. The default is 1.

7.3.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 ])

Parameters

lob_loc

Large object locator of the large object to be erased.

amount IN

Number of bytes/characters to be erased.

amount OUT

Number of bytes/characters actually erased. This value can be smaller than the input value if the end of the large object is reached before amount bytes/characters have been erased.

offset

Position in the large object where erasing is to begin. The first byte/character is position 1. The default is 1.

7.3.7 GET_STORAGE_LIMIT

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)

Parameters

size

Maximum allowable size of a large object in this database.

lob_loc

This parameter is ignored, but is included for compatibility.

7.3.8 GETLENGTH

The GETLENGTH function returns the length of a large object.

amount INTEGER GETLENGTH(lob_loc BLOB)

amount INTEGER GETLENGTH(lob_loc CLOB)

Parameters

lob_loc

Large object locator of the large object whose length is to be obtained.

amount

Length of the large object in bytes for BLOBs or characters for CLOBs.

7.3.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 ]])

Parameters

lob_loc

Large object locator of the large object in which to search for pattern.

pattern

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.

offset

Position within lob_loc to start search for pattern. The first byte/character is position 1. The default is 1.

nth

Search for pattern, nth number of times starting at the position given by offset. The default is 1.

position

Position within the large object where pattern appears the nth time specified by nth starting from the position given by offset.

7.3.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 })

Parameters

lob_loc

Large object locator of the large object to be read.

amount IN

Number of bytes/characters to read.

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.

offset

Position to begin reading. The first byte/character is position 1.

buffer

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.3.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 ]])

Parameters

lob_loc

Large object locator of the large object to be read.

amount

Number of bytes/characters to be returned. Default is 32,767.

offset

Position within the large object to begin returning data. The first byte/character is position 1. The default is 1.

data

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

Parameters

lob_loc

Large object locator of the large object to be trimmed.

newlen

Number of bytes/characters to which the large object is to be trimmed.

7.3.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 })

Parameters

lob_loc

Large object locator of the large object to be written.

amount

The number of bytes/characters in buffer to be written to the large object.

offset

The offset in bytes/characters from the beginning of the large object (origin is 1) for the write operation to begin.

buffer

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.

7.3.14 WRITEAPPEND

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 })

Parameters

lob_loc

Large object locator of the large object to which data is to be appended.

amount

Number of bytes/characters from buffer to be appended the large object.

buffer

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.

Previous PageTable Of ContentsNext Page