Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive
On this page

DBE_LOB

Updated on 2024-10-14 GMT+08:00

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.FREETEMPORARY

Deletes a temporary BLOB or CLOB.

DBE_LOB.APPEND

Adds the content of a LOB to another LOB.

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 specified length of a LOB.

    The function prototype of DBE_LOB.GET_LENGTH 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 function prototype of DBE_LOB.OPEN 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 function prototype of DBE_LOB.READ 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

    Length of read content

    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

  • 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

    Length of written content

    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

  • 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 function prototype of DBE_LOB.WRITE_APPEND 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

    Length of written content

    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 function prototype of DBE_LOB.COPY 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

    The stored procedure CLOSE closes the LOB descriptor that has been opened.

    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 function prototype of DBE_LOB.MATCH 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 function prototype of DBE_LOB.COMPARE 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: amount > 1 or amount < 32767; offset < 1 or offset > 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 function prototype of DBE_LOB.CREATE_TEMPORARY 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 function prototype of DBE_LOB.APPEND 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

  • DBE_LOB.FREETEMPORARY

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

    The DBE_LOB.FREETEMPORARY function prototype 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 DBE_LOB.FILEOPEN function prototype 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 DBE_LOB.FILECLOSE function prototype 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.

  • 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) 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 := 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;

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback