• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Stored Procedures
  5. Advanced Packages
  6. DBMS_LOB

DBMS_LOB

Related Interfaces

Table 1 provides all interfaces supported by the DBMS_LOB package.

Table 1 DBMS_LOB

API

Description

DBMS_LOB.GETLENGTH

Obtains and returns the specified length of a BLOB type object.

DBMS_LOB.READ

Loads a part of BLOB contents to BUFFER area according to the specified length and initial position offset.

DBMS_LOB.WRITE

Copies contents in BUFFER area to BLOB according to the specified length and initial position offset.

DBMS_LOB.WRITEAPPEND

Copies contents in BUFFER area to the end part of BLOB according to the specified length.

DBMS_LOB.COPY

Copies contents in BLOB to another BLOB according to the specified length and initial position offset.

DBMS_LOB.ERASE

Deletes contents in BLOB according to the specified length and initial position offset.

DBMS_LOB.CLOSE

Disables the enabled contents of BLOB according to the specified length and initial position offset.

  • DBMS_LOB.GETLENGTH

    Specified length of a BLOB type object obtained and returned by the stored procedure GETLENGTH.

    The DBMS_LOB.GETLENGTH function prototype is:

    DBMS_LOB.GETLENGTH (
    lob_loc    IN   BLOB)
    RETURN INTEGER;
    Table 2 DBMS_LOB.GETLENGTH interface parameters

    Parameter

    Description

    lob_loc

    BLOB type object whose length is to be obtained

  • DBMS_LOB.READ

    The stored procedure READ loads a part of BLOB contents to BUFFER according to the specified length and initial position offset.

    The DBMS_LOB.READ function prototype is:

    DBMS_LOB.READ (
    lob_loc     IN           BLOB,
    amount      IN           INTEGER,
    offset      IN           INTEGER,
    buffer      OUT          RAW);
    Table 3 DBMS_LOB.READ interface parameters

    Parameter

    Description

    lob_loc

    BLOB type object to be loaded

    amount

    Load data length

    NOTE:

    If the length of loaded data is negative, DWS forcibly converts the negative number to a sign-free number. If the BLOB type object is shorter than the sign-free number, the procedure loads the BLOB type object. Otherwise, the procedure loads the sign-free number.

    offset

    Indicates where to start loading the BLOB contents, that is, the offset bytes to initial position of BLOB contents.

    buffer

    Target buffer to store the loaded BLOB contents

  • DBMS_LOB.WRITE

    The stored procedure WRITE copies contents in BUFFER to BLOB variables according to the specified length and initial position offset.

    The DBMS_LOB.WRITE function prototype is:

    DBMS_LOB.WRITE (
    lob_loc    IN OUT     BLOB,
    amount     IN         INTEGER,
    offset     IN         INTEGER,
    buffer     IN         RAW);
    Table 4 DBMS_LOB.WRITE interface parameters

    Parameter

    Description

    lob_loc

    BLOB type object to be written

    amount

    Write data length

    NOTE:

    If the write data is shorter than 1 or longer than the contents to be written, an error is reported.

    offset

    Indicates where to start writing the BLOB contents, that is, the offset bytes to initial position of BLOB contents.

    NOTE:

    If the offset is shorter than 1 or longer than the maximum length of BLOB type contents, an error is reported.

    buffer

    Content to be written

  • DBMS_LOB.WRITEAPPEND

    The stored procedure WRITEAPPEND copies contents in BUFFER to the end part of BLOB according to the specified length.

    The DBMS_LOB.WRITEAPPEND function prototype is:

    DBMS_LOB.WRITEAPPEND (
    lob_loc     IN OUT     BLOB,
    amount      IN         INTEGER,
    buffer      IN         RAW);
    Table 5 DBMS_LOB.WRITEAPPEND interface parameters

    Parameter

    Description

    lob_loc

    BLOB type object to be written

    amount

    Write data length

    NOTE:

    If the write data is shorter than 1 or longer than the contents to be written, an error is reported.

    buffer

    Content to be written

  • DBMS_LOB.COPY

    The stored procedure COPY copies contents in BLOB to another BLOB according to the specified length and initial position offset.

    The DBMS_LOB.COPY function prototype is:

    DBMS_LOB.COPY (
    dest_lob      IN OUT     BLOB,
    src_lob       IN         BLOB,
    amount        IN         INTEGER,
    dest_offset   IN         INTEGER  DEFAULT 1,
    src_offset    IN         INTEGER  DEFAULT 1);
    Table 6 DBMS_LOB.COPY interface parameters

    Parameter

    Description

    dest_lob

    BLOB type object to be pasted

    src_lob

    BLOB type object to be copied

    amount

    Length of the copied data

    NOTE:

    If the copied data is shorter than 1 or longer than the maximum length of BLOB type contents, an error is reported.

    dest_offset

    Indicates where to start pasting the BLOB contents, that is, the offset bytes to initial position of BLOB contents.

    NOTE:

    If the offset is shorter than 1 or longer than the maximum length of BLOB type contents, an error is reported.

    src_offset

    Indicates where to start copying the BLOB contents, that is, the offset bytes to initial position of BLOB contents.

    NOTE:

    If the offset is shorter than 1 or longer than the length of source BLOB, an error is reported.

  • DBMS_LOB.ERASE

    The stored procedure ERASE deletes contents in BLOB according to the specified length and initial position offset.

    The DBMS_LOB.ERASE function prototype is:

    DBMS_LOB.ERASE (
    lob_loc          IN OUT   BLOB,
    amount           IN OUT   INTEGER,
    offset           IN       INTEGER DEFAULT 1);
    Table 7 DBMS_LOB.ERASE interface parameters

    Parameter

    Description

    lob_loc

    BLOB type object whose contents are to be deleted

    amount

    Length of contents to be deleted

    NOTE:

    If the deleted data is shorter than 1 or longer than the maximum length of BLOB type contents, an error is reported.

    offset

    Indicates where to start deleting the BLOB contents, that is, the offset bytes to initial position of BLOB contents.

    NOTE:

    If the offset is shorter than 1 or longer than the maximum length of BLOB type contents, an error is reported.

  • DBMS_LOB.CLOSE

    The procedure CLOSE disables the enabled contents of BLOB according to the specified length and initial position offset.

    The DBMS_LOB.CLOSE function prototype is:

    DBMS_LOB.CLOSE(
    src_lob       IN              BLOB,);
    Table 8 DBMS_LOB.CLOSE interface parameters

    Parameter

    Description

    src_loc

    Indicates the BLOB type object to be disabled.

Examples

-- Obtain the length of the character string.
SELECT DBMS_LOB.GETLENGTH('12345678');

DECLARE
myraw  RAW(100);
amount INTEGER :=2;
buffer INTEGER :=1;
begin
DBMS_LOB.READ('123456789012345',amount,buffer,myraw);
dbms_output.put_line(myraw);
end;
/

CREATE TABLE blob_Table (t1 blob) DISTRIBUTE BY REPLICATION;
CREATE TABLE blob_Table_bak (t2 blob) DISTRIBUTE BY REPLICATION;
INSERT INTO blob_Table VALUES('abcdef');
INSERT INTO blob_Table_bak VALUES('22222');

DECLARE
str varchar2(100) := 'abcdef';
source raw(100);
dest blob;
copyto blob;
amount int;
PSV_SQL varchar2(100);
PSV_SQL1 varchar2(100);
a int :=1;
len int;
BEGIN
source := utl_raw.cast_to_raw(str);
amount := utl_raw.length(source);

PSV_SQL :='select * from blob_Table for update';
PSV_SQL1 := 'select * from blob_Table_bak for update';

EXECUTE IMMEDIATE PSV_SQL into dest;
EXECUTE IMMEDIATE PSV_SQL1 into copyto;

DBMS_LOB.WRITE(dest, amount, 1, source);
DBMS_LOB.WRITEAPPEND(dest, amount, source);

DBMS_LOB.ERASE(dest, a, 1);
DBMS_OUTPUT.PUT_LINE(a);
DBMS_LOB.COPY(copyto, dest, amount, 10, 1);
DBMS_LOB.CLOSE(dest);
RETURN;
END;
/

--Delete the temporary table and stored procedure.
DROP TABLE blob_Table;
DROP TABLE blob_Table_bak;