F.16. dbms_lob — operate on large objects

F.16. dbms_lob — operate on large objects
Prev	Up	Appendix F. Additional Supplied Modules and Extensions Shipped in `postgrespro-ent-16-contrib`	Home	Next

F.16. dbms_lob — operate on large objects #

F.16.1. Installation
F.16.2. Data Types
F.16.3. Utility Functions
F.16.4. dbms_lob Functions and Procedures
F.16.5. Example

dbms_lob is a Postgres Pro extension that allows operating on LOBs: BLOB, CLOB, BFILE, and temporary LOBs. The extension can be used to access and manipulate specific parts of a LOB or complete LOBs. The functionality provided by this module overlaps substantially with the functionality of Oracle's DBMS_LOB package.

Note

Note that the dbms_lob extension depends on the pgpro_sfile extension so the latter must be installed first. For more information, see its documentation.

F.16.1. Installation #

The dbms_lob extension is a built-in extension included into Postgres Pro Enterprise. To enable dbms_lob, create the extension using the following query:

CREATE EXTENSION dbms_lob;

F.16.2. Data Types #

The dbms_lob extension works with several data types:

The bfile type is provided by pgpro_bfile.
```
CREATE TYPE BFILE AS (
    dir_id int,
    file_name text
);
```
Table F.12. bfile Parameters
Parameter Description
dir_id The ID of the directory where the bfile is stored.
file_name The name of the file to read the bfile from.

Parameter	Description
`dir_id`	The ID of the directory where the `bfile` is stored.
`file_name`	The name of the file to read the `bfile` from.

The blob type stores binary data and has the same interface as Oracle's BLOB. It is provided by pgpro_sfile.

CREATE TYPE dbms_lob.blob AS (
    temp_data   bytea,
    mime        text,
    sf          @extschema:pgpro_sfile@.sfile
);

Table F.13. blob Parameters

Parameter	Description
`sf`	`sfile` object on disk, which contains BLOB.
`temp_data`	Temporary BLOB data stored in memory.
`mime`	Auxiliary data defining the type of data stored in BLOB.

The clob type is the equivalent of Oracle's CLOB and NCLOB. Only the UTF-8 encoding is supported.

CREATE TYPE CLOB AS (
    t       text,
    istemp  bool,
    mime    text
);

Table F.14. clob Parameters

Parameter	Description
`t`	`text` object on disk, which stores data.
`istemp`	Defines if the object is temporary.
`mime`	Auxiliary data defining the type of data stored in CLOB.

F.16.3. Utility Functions #

bfilename(dirname text, filename text) returns bfile #: Creates a bfile object associated with a physical file in the file system. Here dirname is the name of the directory object created with bfile_directory_create() where the file filename is located.
empty_blob() returns blob #: Creates an empty blob object, which contains an sfile without data. It can be populated with data using write functions.
empty_clob() returns clob #: Creates an empty clob object, which contains an empty string. It can be populated with data using write functions.
to_blob(b bytea) returns blob to_blob(f bfile, mime_type text) returns blob #: Converts a bytea object to a blob. If the original file is bfile, the mime data type can be specified.
to_clob(t text) returns clob to_clob(t varchar) returns clob to_clob(b bfile, int csid, mime text) returns clob #: Converts text objects to clob objects. If the original file is bfile, its data is read and converted into clob. Only the UTF-8 encoding is supported.
to_raw(b blob) returns clob to_raw(b bfile) returns clob #: Copies the data from a blob or bfile file into a bytea. Only the first GB of data is processed.

F.16.4. dbms_lob Functions and Procedures #

F.16.4.1. Opening and Closing LOBs #

open(file_loc IN OUT bfile, open_mode IN int) open(lob_loc IN OUT blob, open_mode IN int) open(lob_loc IN OUT clob, open_mode IN int) #: open(bfile) opens a bfile object. The open_mode parameter specifies if the file is to be open in read/write or read-only mode. For bfile, only read-only mode is supported (0). The functions open(blob) and open(clob) do nothing and exist only for syntax compatibility.
isopen(file_loc IN bfile) isopen(lob_loc IN blob) isopen(lob_loc IN clob) #: isopen(bfile) checks if a bfile object is open. Returns 1 if the LOB is open, otherwise 0. The functions isopen(blob) and isopen(clob) always return 1 and exist only for syntax compatibility.
close(file_loc IN OUT bfile) close(lob_loc IN OUT blob) close(lob_loc IN OUT clob) #: close(bfile) checks if a bfile object is open, and if it is, closes it. The functions close(blob) and close(clob) do nothing and exist only for syntax compatibility.
createtemporary(lob_loc IN OUT blob, cache IN bool, dur IN int default 10) createtemporary(lob_loc IN OUT clob, cache IN bool, dur IN int default 10) #: Creates a temporary LOB, with blob data stored as bytea and clob data stored as text.
freetemporary(lob_loc IN OUT blob) freetemporary(lob_loc IN OUT clob) #: Releases resources associated with the temporary LOB.

F.16.4.2. Reading LOBs #

getlength(file_loc IN bfile) getlength(lob_loc IN blob) getlength(lob_loc IN clob) #: Returns the length of a blob or bfile in bytes or a clob in characters.
read(file_loc IN bfile, amount IN OUT int, offset IN int, buffer OUT bytea) read(lob_loc IN blob, amount IN OUT int, offset IN int, buffer OUT bytea) read(lob_loc IN clob, amount IN OUT int, offset IN int, buffer OUT text) #: Reads a piece of a LOB, and writes the specified amount of bytes (blob/bfile) or characters (clob) into the buffer parameter, starting from an absolute offset from the beginning of the LOB. Note that offset 1 should be specified to read from the beginning.
get_storage_limit(lob_loc IN blob) get_storage_limit(lob_loc IN clob) #: Returns the LOB storage limit for the specified LOB.
substr(file_loc IN bfile, amount IN int, offset IN int) substr(lob_loc IN blob, amount IN int, offset IN int) substr(lob_loc IN clob, amount IN int, offset IN int) #: Returns amount of bytes (blob/bfile) or characters (clob) of a LOB, starting from an absolute offset from the beginning of the LOB.
instr(file_loc IN bfile, pattern IN int, offset IN bigint default 1, nth IN bigint default 1) instr(lob_loc IN blob, pattern IN int, offset IN bigint default 1, nth IN bigint default 1) instr(lob_loc IN clob, pattern IN int, offset IN bigint default 1, nth IN bigint default 1) #: Returns the matching position of the nth occurrence of the pattern in the LOB, starting from the specified offset. Returns 0 if the pattern is not found. Only the first GB of data is searched.

F.16.4.3. Updating LOBs #

write(lob_loc IN OUT blob, amount IN int, offset IN bigint, buffer IN bytea) write(lob_loc IN OUT clob, amount IN int, offset IN int, buffer IN text) #: Writes a specified amount of data into an internal LOB, starting from an absolute offset from the beginning of the LOB. The data is written from the buffer parameter. If the specified offset is beyond the end of the data currently in the LOB, then zero-byte fillers (blob) or spaces (clob) are inserted.
writeappend(lob_loc IN OUT blob, amount IN int, buffer IN bytea) writeappend(lob_loc IN OUT clob, amount IN int, buffer IN text) #: Writes a specified amount of data to the end of an internal LOB. The data is written from the buffer parameter.
erase(lob_loc IN OUT blob, amount IN OUT int, offset IN bigint default 1) erase(lob_loc IN OUT clob, amount IN OUT int, offset IN int default 1) #: Erases an entire internal LOB or part of an internal LOB. When data is erased from the middle of a LOB, zero-byte fillers (temporary blob) or spaces (clob) are written. Non-temporary blob objects can only be deleted as a whole.
trim(lob_loc IN OUT blob, newlen IN bigint) trim(lob_loc IN OUT clob, newlen IN int) #: Trims the value of the internal LOB to the length you specify in the newlen parameter. Specify the length in bytes for temporary blob, and specify the length in characters for clob. For non-temporary blob, works only if the new length is 0, which means erasing the object.

F.16.4.4. Operations with Multiple LOBs #

compare(lob_1 IN bfile, lob_2 IN bfile, amount IN bigint, offset_1 IN bigint default 1, offset_2 IN bigint default 1) returns int compare(lob_1 IN blob, lob_2 IN blob, amount IN int default 1024*1024*1024-8, offset_1 IN bigint default 1, offset_2 IN bigint default 1) returns int compare(lob_1 IN clob, lob_2 IN clob, amount IN int default (1024*1024*1024-8)/2, offset_1 IN int default 1, offset_2 IN int default 1) returns int #: Compares two entire LOBs or parts of two LOBs. You can only compare LOBs of the same datatype. For bfile and blob, binary comparison is performed. For clob, files are compared according to the current database collation.
append(lob_1 IN OUT blob, lob_2 IN blob) append(lob_1 IN OUT clob, lob_2 IN clob) #: Appends the contents of a source internal LOB to a destination LOB. It appends the complete source LOB.
copy(dest_lob IN OUT blob, src_lob IN blob, amount IN bigint, dest_offset IN bigint default 1, src_offset IN bigint default 1) returns int copy(dest_lob IN OUT clob, src_lob IN clob, amount IN int, dest_offset IN int default 1, src_offset IN int default 1) returns int #: Copies all, or a part of, a source internal LOB to a destination internal LOB. You can specify the offsets for both the source and destination LOBs, and the number of bytes or characters to copy.
converttoblob(dest_lob IN OUT blob, src_clob IN clob, amount IN int, dest_offset IN OUT bigint, src_offset IN OUT int, blob_csid IN int, lang_context IN OUT int, warning OUT int) #: Reads character data from a source clob, converts the character data to the specified character set, writes the converted data to a destination blob in binary format, and returns the new offsets. Only the UTF-8 encoding is supported.
converttoclob(dest_lob IN OUT clob, src_blob IN blob, amount IN int, dest_offset IN OUT int, src_offset IN OUT bigint, blob_csid IN int, lang_context IN OUT int, warning OUT int) #: Reads binary data from a source blob, converts it into UTF-8 encoding, and writes the converted character data to a destination clob.

F.16.4.5. Legacy API #

fileexists(file_loc IN bfile) returns int #: Finds out if a specified bfile locator points to a file that actually exists on the server file system. Implemented as bfile_fileexists.
fileopen(file_loc IN OUT bfile, open_mode IN int) returns int #: Opens the specified bfile for read-only access. Implemented as bfile_open.
fileisopen(file_loc IN bfile) returns int #: Finds out whether the specified bfile was opened.
loadfromfile(dest_lob IN OUT blob, src_bfile IN bfile, amount IN int default 1024*1024*1024-8, dest_offset IN bigint default 1, src_offset IN bigint default 1) #: Converts data from the specified bfile to blob.
fileclose(file_loc IN OUT bfile) #: Closes the previously opened bfile. Implemented as bfile_close.
filecloseall() #: Closes all bfile files opened in the session. Implemented as bfile_close_all.
filegetname(file_loc IN bfile, dir_alias OUT text, filename OUT text) #: Determines the directory object and filename. This function only indicates the directory object name and filename assigned to the locator, not if the physical file or directory actually exists. Implemented as bfile_directory_get_alias_by_id.

F.16.4.6. Other Operations #

loadblobfromfile(dest_lob IN OUT blob, src_bfile IN bfile, amount IN int default 1024*1024*1024-8, dest_offset IN bigint default 1, src_offset IN bigint default 1) returns int #: Synonym for loadfromfile().
loadclobfromfile(dest_lob IN OUT clob, src_bfile IN bfile, amount IN int, dest_offset IN OUT int, src_offset IN OUT bigint, bfile_csid IN int, lang_context IN OUTint, warning OUTint) #: Loads data from a bfile to an internal clob.
setcontenttype(lob_loc IN OUT blob, contenttype IN text) setcontenttype(lob_loc IN OUT clob, contenttype IN text) #: Sets the content type string associated with the LOB.
getcontenttype(lob_loc IN blob) returns text getcontenttype(lob_loc IN clob) returns text #: Returns the content type string associated with the LOB.
getchunksize(lob_loc IN blob) returns text getchunksize(lob_loc IN clob) returns text #: Returns the amount of space used in the LOB chunk to store the LOB value.

F.16.5. Example #

Below is the example of how the dbms_lob extension works.

DO
$$
DECLARE
  cur_clob  dbms_lob.clob;
  buffer    text;
  amount    int := 3000;
BEGIN
  cur_clob := dbms_lob.empty_clob();
  cur_clob.t := 'just some sample text';
  raise notice 'clob length: %', dbms_lob.getlength(cur_clob);
  call dbms_lob.read(cur_clob, amount, 1, buffer);
  raise notice 'all clob read: %', buffer;
  amount := 6;
  call dbms_lob.read(cur_clob, amount, 4, buffer);
  raise notice 'clob read from 4 position for 6 symbols: %', buffer;
  raise notice 'storage limit: %', dbms_lob.get_storage_limit(cur_clob);
  raise notice 'clob substr from 6 position for 8 symbols: %', dbms_lob.substr(cur_clob, 8, 6);
  raise notice 'third postion of letter s in clob: %', dbms_lob.instr(cur_clob, 's', 1, 3);

  call dbms_lob.write(cur_clob, 6, 4, 'foobar');
  raise notice 'new clob contents: %', cur_clob.t;
  call dbms_lob.write(cur_clob, 3, 25, 'baz');
  raise notice 'new clob contents: %', cur_clob.t;

  call dbms_lob.writeappend(cur_clob, 4, 'test');
  raise notice 'new clob contents: %', cur_clob.t;

  amount := 3;
  call dbms_lob.erase(cur_clob, amount, 2);
  raise notice 'amount of symbols deleted: %', amount;
  raise notice 'new clob contents: %', cur_clob.t;
  call dbms_lob.erase(cur_clob, amount, 30);
  raise notice 'amount of symbols deleted: %', amount;
  raise notice 'new clob contents: %', cur_clob.t;

  call dbms_lob.trim_(cur_clob, 22);
  raise notice 'new clob contents: %', cur_clob.t;
END;
$$;
--output
NOTICE:  clob length: 21
NOTICE:  all clob read: just some sample text
NOTICE:  clob read from 4 position for 6 symbols: t some
NOTICE:  storage limit: 536870908
NOTICE:  clob substr from 6 position for 8 symbols: some sam
NOTICE:  third postion of letter s in clob: 11
NOTICE:  new clob contents: jusfoobar sample text
NOTICE:  new clob contents: jusfoobar sample text   baz
NOTICE:  new clob contents: jusfoobar sample text   baztest
NOTICE:  amount of symbols deleted: 3
NOTICE:  new clob contents: j   oobar sample text   baztest
NOTICE:  amount of symbols deleted: 2
NOTICE:  new clob contents: j   oobar sample text   bazte
NOTICE:  new clob contents: j   oobar sample text

And here is how working with LOBs located in a database may look like:

-- Create a table and add data
CREATE TABLE dbms_lob_test (id INTEGER GENERATED ALWAYS AS IDENTITY, blob_col DBMS_LOB.BLOB);
INSERT INTO dbms_lob_test (blob_col) VALUES (dbms_lob.to_blob(decode('d6b7a686ab4d4e9c5d2cbf49db6bc0f1', 'hex')));

DO $$
DECLARE
  v_lob_loc DBMS_LOB.BLOB;
  v_buffer BYTEA;
  v_amount INTEGER := 32700;
  v_offset BIGINT := 1;
  v_length BIGINT;
BEGIN
  SELECT (blob_col).* INTO v_lob_loc FROM dbms_lob_test WHERE id=1;
  CALL dbms_lob.open(v_lob_loc, 0); -- Optional for DBMS_LOB.BLOB
  SELECT DBMS_LOB.getlength(v_lob_loc) into v_length;
  RAISE NOTICE 'BLOB len=%', v_length;
  CALL dbms_lob.read(v_lob_loc, v_amount, v_offset, v_buffer);
  RAISE NOTICE 'Read % bytes', v_amount;
  RAISE NOTICE 'Buffer: %', encode(v_buffer, 'hex');
  CALL dbms_lob.close(v_lob_loc); -- Optional for DBMS_LOB.BLOB
END $$;

The output will look as follows:

BLOB len=16
Read 16 bytes
Buffer: d6b7a686ab4d4e9c5d2cbf49db6bc0f1

Prev	Up	Next
dblink_build_sql_update	Home	F.17. dict_int — example full-text search dictionary for integers