Updated on 2023-10-23 GMT+08:00
DBE_LOB

DBE_LOB

Interface Description

Table 1 provides all interfaces supported by the DBE_LOB package.

Table 1 DBE_LOB

Interface

Description

DBE_LOB.GET_LENGTH

Obtains and returns the specified length of a LOB.

DBE_LOB.OPEN

Opens a LOB and returns a LOB descriptor.

DBE_LOB.READ

Loads a part of LOB content to the buffer based on the specified length and initial position offset.

DBE_LOB.WRITE

Copies content in the buffer to a LOB based on the specified length and initial position offset.

DBE_LOB.WRITE_APPEND

Copies content in the buffer to the end part of a LOB based on the specified length.

DBE_LOB.COPY

Copies content in a BLOB to another BLOB based on the specified length and initial position offset.

DBE_LOB.ERASE

Deletes content in a BLOB based on the specified length and initial position offset.

DBE_LOB.CLOSE

Closes a LOB descriptor.

DBE_LOB.MATCH

Returns the position of the Nth occurrence of a character string in a LOB.

DBE_LOB.COMPARE

Compares two LOBs or a certain part of two LOBs.

DBE_LOB.SUBSTR

Reads the substring of a LOB and returns the number of read bytes or the number of characters.

DBE_LOB.STRIP

Truncates the LOB of a specified length. After the execution is complete, the length of the LOB is set to the length specified by the newlen parameter.

DBE_LOB.CREATE_TEMPORARY

Creates a temporary BLOB or CLOB.

DBE_LOB.APPEND

Adds the content of a LOB to another LOB.

DBE_LOB.FREETEMPORARY

Deletes a temporary BLOB or CLOB.

DBE_LOB.FILEOPEN

Opens a database-external file and returns a file descriptor.

DBE_LOB.FILECLOSE

Closes an external file opened by FILEOPEN.

DBE_LOB.LOADFROMFILE

Reads a database-external file to a BLOB file.

DBE_LOB.LOADBLOBFROMFILE

Reads a database-external file to a BLOB file.

DBE_LOB.LOADCLOBFROMFILE

Reads a database-external file to a CLOB file.

DBE_LOB.CONVERTTOBLOB

Converts a CLOB file to a BLOB file.

DBE_LOB.CONVERTTOCLOB

Converts a BLOB file to a CLOB file.

  • DBE_LOB.GET_LENGTH

    The stored procedure GET_LENGTH obtains and returns the size of a specified LOB. The maximum size is 2 GB.

    The prototype of the DBE_LOB.GET_LENGTH function is as follows:

    1
    2
    3
    4
    5
    6
    7
    DBE_LOB.GET_LENGTH (
    lob    IN   BLOB)
    RETURN INTEGER;
    
    DBE_LOB.GET_LENGTH (
    lob    IN   CLOB)
    RETURN INTEGER;
    
    Table 2 DBE_LOB.GET_LENGTH interface parameters

    Parameter

    Description

    lob

    BLOB/CLOB whose length is to be obtained

  • DBE_LOB.OPEN

    This stored procedure opens a LOB and returns a LOB descriptor. This process is used only for compatibility.

    The prototype of the DBE_LOB.OPEN function is as follows:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    DBE_LOB.OPEN (
    lob    INOUT   BLOB
    );
    
    DBE_LOB.OPEN (
    lob  INOUT    CLOB
    );
    
    DBE_LOB.OPEN (
    bfile dbe_lob.bfile,
    open_mode text DEFAULT 'null'::text
    );
    
    Table 3 DBE_LOB.OPEN interface parameters

    Parameter

    Description

    lob

    BLOB or CLOB that is opened

  • DBE_LOB.READ

    The stored procedure READ loads a part of LOB content to the buffer based on the specified length and initial position offset.

    The prototype of the DBE_LOB.READ function is as follows:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    DBE_LOB.READ (
    lob     IN     BLOB,
    len     IN     INTEGER,
    start   IN     INTEGER,
    buffer  OUT    RAW);
    
    DBE_LOB.READ (
    lob     IN     CLOB,
    len     INOUT  INTEGER,
    start   IN     INTEGER,
    buffer  OUT    VARCHAR2);
    
    Table 4 DBE_LOB.READ interface parameters

    Parameter

    Description

    lob

    BLOB/CLOB to be read

    len

    Read size. A maximum of 32767 characters are supported.

    NOTE:

    If the length is negative, the error message "ERROR: argument 2 is null, invalid, or out of range." is displayed.

    start

    Indicates where to start reading the LOB content, that is, the offset bytes to initial position of LOB content.

    buffer

    Target buffer to store the read LOB content

    The value of lob can be a CLOB with a size greater than 1 GB. The maximum size is 2 GB. The maximum value of buffer is 32 KB and cannot be greater than the value of len.

  • DBE_LOB.WRITE

    The stored procedure WRITE copies content in the buffer to LOB variables based on the specified length and initial position.

    The prototype of the DBE_LOB.WRITE function is as follows:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    DBE_LOB.WRITE (
    dest_lob   INOUT    BLOB,
    len        IN       INTEGER,
    start      IN       INTEGER,
    src_lob    IN       RAW);
    
    DBE_LOB.WRITE (
    dest_lob   INOUT   CLOB,
    len        IN       INTEGER,
    start      IN       INTEGER,
    src_lob    IN       VARCHAR2);
    
    Table 5 DBE_LOB.WRITE interface parameters

    Parameter

    Description

    dest_lob

    BLOB/CLOB to be written

    len

    Write size. A maximum of 32767 characters are supported.

    NOTE:

    If the length of written content is shorter than 1 or longer than the length of content to be written, an error is reported.

    start

    Indicates where to start writing the LOB content, that is, the offset bytes to initial position of LOB content.

    NOTE:

    If the offset value is less than 1, an error is reported. If the offset value is greater than the maximum length of LOB type contents, no error is reported.

    src_lob

    Content to be written

    The value of dest_lob can be a CLOB with a size greater than 1 GB. The maximum size is 2 GB. The maximum value of src_lob is 1 GB.

  • DBE_LOB.WRITE_APPEND

    The stored procedure WRITE_APPEND copies content in the buffer to the end part of a LOB based on the specified length.

    The prototype of the DBE_LOB.WRITE_APPEND function is as follows:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    DBE_LOB.WRITE_APPEND (
    dest_lob    INOUT    BLOB,
    len         IN        INTEGER,
    src_lob     IN        RAW);
    
    DBE_LOB.WRITE_APPEND (
    dest_lob    INOUT     CLOB,
    len         IN        INTEGER,
    src_lob     IN        VARCHAR2);
    
    Table 6 DBE_LOB.WRITE_APPEND interface parameters

    Parameter

    Description

    dest_lob

    BLOB/CLOB to be written

    len

    Write size. A maximum of 32767 characters are supported.

    NOTE:

    If the length of written content is shorter than 1 or longer than the length of content to be written, an error is reported.

    src_lob

    Content to be written

  • DBE_LOB.COPY

    The stored procedure COPY copies content in a BLOB to another BLOB based on the specified length and initial position offset.

    The prototype of the DBE_LOB.COPY function is as follows:

    1
    2
    3
    4
    5
    6
    DBE_LOB.COPY (
    dest_lob     INOUT    BLOB,
    src_lob      IN        BLOB,
    len          IN        INTEGER,
    dest_start   IN        INTEGER  DEFAULT 1,
    src_start    IN        INTEGER  DEFAULT 1);
    
    Table 7 DBE_LOB.COPY interface parameters

    Parameter

    Description

    dest_lob

    BLOB to be pasted

    src_lob

    BLOB to be copied

    len

    Length of copied content

    NOTE:

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

    dest_start

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

    NOTE:

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

    src_start

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

    NOTE:

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

  • DBE_LOB.ERASE

    The stored procedure ERASE deletes content in BLOB based on the specified length and initial position offset.

    The prototype of the DBE_LOB.ERASE function is as follows:

    1
    2
    3
    4
    DBE_LOB.ERASE (
    lob        INOUT   BLOB,
    len        INOUT   INTEGER,
    start      IN      INTEGER DEFAULT 1);
    
    Table 8 DBE_LOB.ERASE interface parameters

    Parameter

    Description

    lob

    BLOB whose content is to be deleted

    len

    Length of content to be deleted

    NOTE:

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

    start

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

    NOTE:

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

  • DBE_LOB.CLOSE

    Closes a LOB descriptor.

    The prototype of the DBE_LOB.CLOSE function is as follows:

    1
    2
    3
    4
    5
    6
    7
    8
    DBE_LOB.CLOSE(
    lob      IN      BLOB);
    
    DBE_LOB.CLOSE (
    lob      IN      CLOB);
    
    DBE_LOB.CLOSE (
    file      IN      INTEGER);
    
    Table 9 DBE_LOB.CLOSE interface parameters

    Parameter

    Description

    lob

    BLOB/CLOB to be disabled

  • DBE_LOB.MATCH

    This function returns the Nth occurrence position of pattern in a LOB. NULL is returned for any of the following conditions: offset < 1 or offset > LOBMAXSIZE; nth < 1 or nth > LOBMAXSIZE

    The prototype of the DBE_LOB.MATCH function is as follows:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    DBE_LOB.MATCH (
    lob              IN     BLOB,
    pattern          IN     RAW,
    start_index      IN     INTEGER  DEFAULT 1,
    match_index      IN     INTEGER  DEFAULT 1)
    RETURN INTEGER;
    
    DBE_LOB.MATCH (
    lob              IN     CLOB,
    pattern          IN     VARCHAR2 ,
    start_index      IN     INTEGER  DEFAULT 1,
    match_index      IN     INTEGER  DEFAULT 1)
    RETURN INTEGER;
    
    Table 10 DBE_LOB.MATCH interface parameters

    Parameter

    Description

    lob

    BLOB/CLOB descriptor to be searched for

    pattern

    Matched pattern. It is RAW for BLOB and TEXT for CLOB.

    start_index

    For BLOB, the absolute offset is in the unit of byte. For CLOB, the offset is in the unit of character. The matching start position is 1.

    match_index

    Number of pattern matching times. The minimum value is 1.

  • DBE_LOB.COMPARE

    This function compares two LOBs or a certain part of two LOBs.

    • If the two parts are equal, 0 is returned. Otherwise, a non-zero value is returned.
    • If the first LOB is smaller than the second, -1 is returned. If the first LOB is larger than the second, 1 is returned.
    • If any of the len, start1, and start2 parameters is invalid, NULL is returned. The valid offset range is 1 to LOBMAXSIZE.

    The prototype of the DBE_LOB.COMPARE function is as follows:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    DBE_LOB.COMPARE (
    lob1     IN     BLOB,
    lob2     IN     BLOB,
    len      IN     INTEGER DEFAULT DBE_LOB.LOBMAXSIZE,
    start1   IN     INTEGER DEFAULT 1,
    start2   IN     INTEGER DEFAULT 1)
    RETURN INTEGER;
    
    DBE_LOB.COMPARE (
    lob1     IN     CLOB,
    lob2     IN     CLOB,
    len      IN     INTEGER DEFAULT DBE_LOB.LOBMAXSIZE,
    start1   IN     INTEGER DEFAULT 1,
    start2   IN     INTEGER DEFAULT 1)
    RETURN INTEGER;
    
    Table 11 DBE_LOB.COMPARE interface parameters

    Parameter

    Description

    lob1

    First BLOB/CLOB to be compared

    lob2

    Second BLOB/CLOB to be compared

    len

    Number of characters or bytes to be compared. The maximum value is DBE_LOB.LOBMAXSIZE.

    start1

    Offset of the first LOB descriptor. The initial position is 1.

    start2

    Offset of the second LOB descriptor. The initial position is 1.

  • DBE_LOB.SUBSTR

    This function reads the substring of a LOB and returns the number of read bytes or the number of characters. NULL is returned for any of the following conditions: value of amount < 1 or value of amount > 32767; value of offset < 1 or value of offset > value of LOBMAXSIZE

    The prototype of the DBE_LOB.SUBSTR function is as follows:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    DBE_LOB.SUBSTR (
    lob        IN     BLOB,
    len        IN     INTEGER DEFAULT  32767,
    start      IN     INTEGER DEFAULT  1)
    RETURN RAW;
    
    DBE_LOB.SUBSTR (
    lob        IN     CLOB,
    len        IN     INTEGER DEFAULT  32767,
    start      IN     INTEGER DEFAULT  1)
    RETURN VARCHAR2;
    
    Table 12 DBE_LOB.SUBSTR interface parameters

    Parameter

    Description

    lob

    LOB descriptor of the substring to be read. For BLOB, the return value is the number of read bytes. For CLOB, the return value is the number of characters.

    len

    Number of bytes or characters to be read.

    start

    Number of characters or bytes offset from the start position.

  • DBE_LOB.STRIP

    This stored procedure truncates the LOB of a specified length. After this stored procedure is executed, the length of the LOB is set to the length specified by the newlen parameter. If an empty LOB is truncated, no execution result is displayed. If the specified length is longer than the length of the LOB, an exception occurs.

    The prototype of the DBE_LOB.STRIP function is as follows:

    1
    2
    3
    4
    5
    6
    7
    DBE_LOB.STRIP (
    lob       IN OUT     BLOB,
    newlen    IN       INTEGER);
    
    DBE_LOB.STRIP (
    lob       IN OUT     CLOB,
    newlen    IN        INTEGER);
    
    Table 13 DBE_LOB.STRIP interface parameters

    Parameter

    Description

    lob

    BLOB to be read

    newlen

    After truncation, the new LOB length for BLOB is in the unit of byte and that for CLOB is in the unit of character.

  • DBE_LOB.CREATE_TEMPORARY

    This stored procedure creates a temporary BLOB or CLOB and is used only for syntax compatibility.

    The prototype of the DBE_LOB.CREATE_TEMPORARY function is as follows:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    DBE_LOB.CREATE_TEMPORARY (
    locator           INOUT      BLOB,
    cache             IN         BOOLEAN,
    keep_alive_time   IN         INTEGER);
    
    DBE_LOB.CREATE_TEMPORARY (
    locator           INOUT      CLOB,
    cache             IN         BOOLEAN,
    keep_alive_time   IN         INTEGER);
    
    Table 14 DBE_LOB.CREATE_TEMPORARY interface parameters

    Parameter

    Description

    locator

    LOB descriptor

    cache

    Used only for syntax compatibility.

    keep_alive_time

    Used only for syntax compatibility.

  • DBE_LOB.APPEND

    The stored procedure APPEND loads a part of BLOB content to the buffer based on the specified length and initial position offset.

    The prototype of the DBE_LOB.APPEND function is as follows:

    1
    2
    3
    4
    5
    6
    7
    DBE_LOB.APPEND (
    dest_lob    INOUT       BLOB,
    src_lob     IN          BLOB);
    
    DBE_LOB.APPEND (
    dest_lob    INOUT       CLOB,
    src_lob     IN          CLOB);
    
    Table 15 DBE_LOB.APPEND interface parameters

    Parameter

    Description

    dest_lob

    BLOB/CLOB to be written

    src_lob

    BLOB/CLOB to be read

    The value of dest_lob can be a CLOB with a size greater than 1 GB. The maximum size is 2 GB. The maximum value of src_lob is 1 GB.

  • DBE_LOB.FREETEMPORARY

    The stored procedure is used to release LOB files created by CREATE_TEMPORARY.

    The prototype of the DBE_LOB.FREETEMPORARY function is as follows:

    1
    2
    3
    4
    5
    DBE_LOB.FREETEMPORARY (
    lob_loc    INOUT       BLOB);
    
    DBE_LOB.FREETEMPORARY (
    lob_loc    INOUT       CLOB);
    
    Table 16 DBE_LOB.FREETEMPORARY interface parameters

    Parameter

    Description

    lob_loc

    BLOB or CLOB to be released.

  • DBE_LOB.FILEOPEN

    This function is used to open a database-external file of the BFILE type and return the file descriptor corresponding to the file.

    The BFILE type is defined as follows:

    1
    2
    3
    DBE_LOB.BFILE (
    directory    text,
    filename     text);
    

    The prototype of the DBE_LOB.FILEOPEN function is as follows:

    1
    2
    3
    4
    DBE_LOB.FILEOPEN (
    file          IN    DBE_LOB.BFILE,
    open_mode     IN    text)
    RETURN integer;
    
    Table 17 DBE_LOB.FILEOPEN parameters

    Parameter

    Description

    file

    Specifies the database-external file to be opened. The BFILE type contains the file path and file name.

    open_mode

    Specifies the file open mode (w, r, or a).

  • DBE_LOB.FILECLOSE

    This function is used to close an external BFILE file.

    The prototype of the DBE_LOB.FILECLOSE function is as follows:

    1
    2
    DBE_LOB.FILECLOSE (
    file     IN    integer);
    
    Table 18 DBE_LOB.FILECLOSE interface parameters

    Parameter

    Description

    file

    Specifies the database-external file to be closed (the file descriptor is returned by FILEOPEN).

  • DBE_LOB.LOADFROMFILE

    This is used to read an external BFILE file to a BLOB file.

    The prototype of the DBE_LOB.LOADFROMFILE function is as follows:

    1
    2
    3
    4
    5
    6
    7
    DBE_LOB.LOADFROMFILE (
    dest_lob      IN    BLOB,
    src_file     IN    INTEGER,
    amount        IN    INTEGER,
    dest_offset   IN    INTEGER,
    src_offset    IN    INTEGER)
    RETURN raw;
    
    Table 19 DBE_LOB.LOADFROMFILE interface parameters

    Parameter

    Description

    dest_lob

    Target BLOB file. The BFILE file will be read to this file.

    src_bfile

    Source BFILE file to be read.

    amount

    Size of a BLOB file. If the size of a file exceeds this threshold, the file will not be saved to the BLOB file.

    dest_offset

    Offset length of the BLOB file. If dest_offset is set to 1, data is loaded from the start position of the file. The rest may be deduced by analogy.

    src_offset

    Offset length of the BFILE file. If src_offset is set to 1, data is read from the start position of the file. The rest may be deduced by analogy.

    The size of the file to be imported can exceed 1 GB. The maximum value of amount is the value of LOBMAXSIZE (4 GB). When the value of src_offset or dest_offset is greater than that of LOBMAXSIZE, or the value of amount is greater than that of LOBMAXSIZE, an error is reported for the advanced package.

  • DBE_LOB.LOADBLOBFROMFILE

    This is used to read an external BFILE file to a BLOB file.

    The prototype of the DBE_LOB.LOADBLOBFROMFILE function is as follows:

    1
    2
    3
    4
    5
    6
    7
    DBE_LOB.LOADBLOBFROMFILE (
    dest_lob      IN    BLOB,
    src_file     IN    INTEGER,
    amount        IN    INTEGER,
    dest_offset   IN    INTEGER,
    src_offset    IN    INTEGER)
    RETURN raw;
    
    Table 20 DBE_LOB.LOADBLOBFROMFILE interface parameters

    Parameter

    Description

    dest_lob

    Target BLOB file. The BFILE file will be read to this file.

    src_bfile

    Source BFILE file to be read.

    amount

    Size of a BLOB file. If the size of a file exceeds this threshold, the file will not be saved to the BLOB file.

    dest_offset

    Offset length of the BLOB file. If dest_offset is set to 1, data is loaded from the start position of the file. The rest may be deduced by analogy.

    src_offset

    Offset length of the BFILE file. If src_offset is set to 1, data is read from the start position of the file. The rest may be deduced by analogy.

  • DBE_LOB.LOADCLOBFROMFILE

    This is used to read an external BFILE file to a CLOB file.

    The prototype of the DBE_LOB.LOADCLOBFROMFILE function is as follows:

    1
    2
    3
    4
    5
    6
    7
    DBE_LOB.LOADCLOBFROMFILE (
    dest_lob      IN    CLOB,
    src_file     IN    INTEGER,
    amount        IN    INTEGER,
    dest_offset   IN    INTEGER,
    src_offset    IN    INTEGER)
    RETURN raw;
    
    Table 21 DBE_LOB.LOADCLOBFROMFILE interface parameters

    Parameter

    Description

    dest_lob

    Target CLOB file. The BFILE file will be read to this file.

    src_bfile

    Source BFILE file to be read.

    amount

    Size of a CLOB file. If the size of a file exceeds this threshold, the file will not be saved to the CLOB file.

    dest_offset

    Offset length of the CLOB file. If dest_offset is set to 1, data is loaded from the start position of the file. The rest may be deduced by analogy.

    src_offset

    Offset length of the BFILE file. If src_offset is set to 1, data is read from the start position of the file. The rest may be deduced by analogy.

  • DBE_LOB.CONVERTTOBLOB

    This function is used to convert a CLOB file to a BLOB file.

    The prototype of the DBE_LOB.CONVERTTOBLOB function is as follows:
    1
    2
    3
    4
    5
    6
    7
    DBE_LOB.CONVERTTOBLOB(
    dest_blob     IN   BLOB,
    src_clob      IN   CLOB,
    amount       IN   INTEGER default 32767,
    dest_offset  IN   INTEGER default 1,
    src_offset   IN   INTEGER default 1)
    RETURN raw;
    
    Table 22 DBE_LOB.CONVERTTOBLOB interface parameters

    Parameter

    Description

    dest_lob

    Target BLOB file, which is converted from a CLOB file.

    src_bfile

    Source CLOB file to be read.

    amount

    Size of a BLOB file. If the size of a file exceeds this threshold, the file will not be saved to the BLOB file.

    dest_offset

    Offset length of the BLOB file. If dest_offset is set to 1, data is loaded from the start position of the file. The rest may be deduced by analogy.

    src_offset

    Offset length of the CLOB file. If src_offset is set to 1, data is read from the start position of the file. The rest may be deduced by analogy.

  • DBE_LOB.CONVERTTOCLOB

    This function is used to convert a BLOB file to a CLOB file.

    The prototype of the DBE_LOB.CONVERTTOCLOB function is as follows:
    1
    2
    3
    4
    5
    6
    7
    DBE_LOB.CONVERTTOCLOB(
    dest_clob     IN   CLOB,
    src_blob      IN   BLOB,
    amount       IN   INTEGER default 32767,
    dest_offset  IN   INTEGER default 1,
    src_offset   IN   INTEGER default 1)
    RETURN text;
    
    Table 23 DBE_LOB.CONVERTTOCLOB interface parameters

    Parameter

    Description

    dest_lob

    Target CLOB file, which is converted from a BLOB file.

    src_bfile

    Source BLOB file to be read.

    amount

    Size of a CLOB file. If the size of a file exceeds this threshold, the file will not be saved to the CLOB file.

    dest_offset

    Offset length of the CLOB file. If dest_offset is set to 1, data is loaded from the start position of the file. The rest may be deduced by analogy.

    src_offset

    Offset length of the BLOB file. If src_offset is set to 1, data is read from the start position of the file. The rest may be deduced by analogy.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- Obtain the length of a string.
SELECT DBE_LOB.GET_LENGTH('12345678');

DECLARE
myraw  RAW(100);
amount INTEGER :=2;
buffer INTEGER :=1;
begin
DBE_LOB.READ('123456789012345',amount,buffer,myraw);
dbe_output.print_line(myraw);
end;
/

CREATE TABLE blob_Table (t1 blob);
CREATE TABLE blob_Table_bak (t2 blob);
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 := dbe_raw.cast_from_varchar2_to_raw(str);
amount := dbe_raw.get_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;

DBE_LOB.WRITE(dest, amount, 1, source);
DBE_LOB.WRITE_APPEND(dest, amount, source);

DBE_LOB.ERASE(dest, a, 1);
DBE_OUTPUT.PRINT_LINE(a);
DBE_LOB.COPY(copyto, dest, amount, 10, 1);
perform DBE_LOB.CLOSE(dest);
RETURN;
END;
/

-- Delete the table.
DROP TABLE blob_Table;
DROP TABLE blob_Table_bak;