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

DBMS_LOB

Updated on 2022-07-29 GMT+08:00

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 LOB object.

DBMS_LOB.OPEN

Opens a LOB and returns a LOB descriptor.

DBMS_LOB.READ

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

DBMS_LOB.WRITE

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

DBMS_LOB.WRITEAPPEND

Copies contents in BUFFER area to the end part of LOB 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

Closes a LOB descriptor.

DBMS_LOB.INSTR

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

DBMS_LOB.COMPARE

Compares two LOBs or a certain part of two LOBs.

DBMS_LOB.SUBSTR

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

DBMS_LOB.TRIM

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.

DBMS_LOB.CREATETEMPORARY

Creates a temporary BLOB or CLOB.

DBMS_LOB.APPEND

Adds the content of a LOB to another LOB.

  • DBMS_LOB.GETLENGTH

    Specifies the length of a LOB type object obtained and returned by the stored procedure GETLENGTH.

    The function prototype of DBMS_LOB.GETLENGTH is:

    1
    2
    3
    4
    5
    6
    7
    DBMS_LOB.GETLENGTH (
    lob_loc    IN   BLOB)
    RETURN INTEGER;
    
    DBMS_LOB.GETLENGTH (
    lob_loc    IN   CLOB)
    RETURN INTEGER;
    
    Table 2 DBMS_LOB.GETLENGTH interface parameters

    Parameter

    Description

    lob_loc

    LOB type object whose length is to be obtained

  • DBMS_LOB.OPEN

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

    The function prototype of DBMS_LOB.OPEN is:

    1
    2
    3
    4
    5
    6
    7
    DBMS_LOB.LOB (
    lob_loc INOUT BLOB,
    open_mode IN BINARY_INTEGER);
    
    DBMS_LOB.LOB (
    lob_loc INOUT CLOB,
    open_mode IN BINARY_INTEGER);
    
    Table 3 DBMS_LOB.OPEN interface parameters

    Parameter

    Description

    lob_loc

    BLOB or CLOB descriptor that is opened

    open_mode IN BINARY_INTEGER

    Open mode (currently, DBMS_LOB.LOB_READWRITE is supported)

  • DBMS_LOB.READ

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

    The function prototype of DBMS_LOB.READ is:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    DBMS_LOB.READ (
    lob_loc     IN           BLOB,
    amount      IN           INTEGER,
    offset      IN           INTEGER,
    buffer      OUT          RAW);
    
    DBMS_LOB.READ (
    lob_loc    IN            CLOB,
    amount     IN OUT        INTEGER,
    offset     IN            INTEGER,
    buffer     OUT           VARCHAR2);
    
    Table 4 DBMS_LOB.READ interface parameters

    Parameter

    Description

    lob_loc

    LOB type object to be loaded

    amount

    Load data length

    NOTE:

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

    offset

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

    buffer

    Target buffer to store the loaded LOB contents

  • DBMS_LOB.WRITE

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

    The function prototype of DBMS_LOB.WRITE is:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    DBMS_LOB.WRITE (
    lob_loc    IN OUT     BLOB,
    amount     IN         INTEGER,
    offset     IN         INTEGER,
    buffer     IN         RAW);
    
    DBMS_LOB.WRITE (
    lob_loc   IN OUT      CLOB,
    amount    IN          INTEGER,
    offset    IN          INTEGER,
    buffer    IN          VARCHAR2);
    
    Table 5 DBMS_LOB.WRITE interface parameters

    Parameter

    Description

    lob_loc

    LOB 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 LOB contents, that is, the offset bytes to initial position of LOB contents.

    NOTE:

    If the offset is shorter than 1 or longer than the maximum length of LOB 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 LOB according to the specified length.

    The function prototype of DBMS_LOB.WRITEAPPEND is:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    DBMS_LOB.WRITEAPPEND (
    lob_loc     IN OUT     BLOB,
    amount      IN         INTEGER,
    buffer      IN         RAW);
    
    DBMS_LOB.WRITEAPPEND (
    lob_loc     IN OUT     CLOB,
    amount      IN         INTEGER,
    buffer      IN         VARCHAR2);
    
    Table 6 DBMS_LOB.WRITEAPPEND interface parameters

    Parameter

    Description

    lob_loc

    LOB 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 function prototype of DBMS_LOB.COPY is:

    1
    2
    3
    4
    5
    6
    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 7 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 function prototype of DBMS_LOB.ERASE is:

    1
    2
    3
    4
    DBMS_LOB.ERASE (
    lob_loc          IN OUT   BLOB,
    amount           IN OUT   INTEGER,
    offset           IN       INTEGER DEFAULT 1);
    
    Table 8 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 LOB according to the specified length and initial position offset.

    The function prototype of DBMS_LOB.CLOSE is:

    1
    2
    3
    4
    5
    DBMS_LOB.CLOSE(
    src_lob       IN              BLOB);
    
    DBMS_LOB.CLOSE (
    src_lob      IN               CLOB);
    
    Table 9 DBMS_LOB.CLOSE interface parameters

    Parameter

    Description

    src_loc

    LOB type object to be disabled

  • DBMS_LOB.INSTR

    This function returns the Nth occurrence position in LOB. If invalid values are entered, NULL is returned. The invalid values include offset < 1 or offset > LOBMAXSIZE, nth < 1, and nth > LOBMAXSIZE.

    The function prototype of DBMS_LOB.INSTR is:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    DBMS_LOB.INSTR (
    lob_loc     IN     BLOB,
    pattern     IN     RAW,
    offset      IN     INTEGER := 1,
    nth         IN     INTEGER := 1)
    RETURN INTEGER;
    
    DBMS_LOB.INSTR (
    lob_loc    IN     CLOB,
    pattern    IN     VARCHAR2 ,
    offset     IN     INTEGER := 1,
    nth        IN     INTEGER := 1)
    RETURN INTEGER;
    
    Table 10 DBMS_LOB.INSTR interface parameters

    Parameter

    Description

    lob_loc

    LOB descriptor to be searched for

    pattern

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

    offset

    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.

    nth

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

  • DBMS_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 CLOB is smaller than the second, -1 is returned. If the first CLOB is larger than the second, 1 is returned.
    • If any of the amount, offset_1, and offset_2 parameters is invalid, NULL is returned. The valid offset range is 1 to LOBMAXSIZE.

    The function prototype of DBMS_LOB.READ is:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    DBMS_LOB.COMPARE (
    lob_1     IN     BLOB,
    lob_2     IN     BLOB,
    amount    IN     INTEGER := DBMS_LOB.LOBMAXSIZE,
    offset_1  IN     INTEGER := 1,
    offset_2  IN     INTEGER := 1)
    RETURN INTEGER;
    
    DBMS_LOB.COMPARE (
    lob_1     IN     CLOB,
    lob_2     IN     CLOB,
    amount    IN     INTEGER := DBMS_LOB.LOBMAXSIZE,
    offset_1  IN     INTEGER := 1,
    offset_2  IN     INTEGER := 1)
    RETURN INTEGER;
    
    Table 11 DBMS_LOB.COMPARE interface parameters

    Parameter

    Description

    lob_1

    First LOB descriptor to be compared

    lob_2

    Second LOB descriptor to be compared

    amount

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

    offset_1

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

    offset_2

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

  • DBMS_LOB.SUBSTR

    This function reads the substring of a LOB and returns the number of read bytes or the number of characters. If amount > 1, amount < 32767, offset < 1, or offset > LOBMAXSIZE, NULL is returned.

    The function prototype of DBMS_LOB.SUBSTR is:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    DBMS_LOB.SUBSTR (
    lob_loc     IN     BLOB,
    amount      IN     INTEGER := 32767,
    offset      IN     INTEGER := 1)
    RETURN RAW;
    
    DBMS_LOB.SUBSTR (
    lob_loc    IN     CLOB,
    amount     IN     INTEGER := 32767,
    offset     IN     INTEGER := 1)
    RETURN VARCHAR2;
    
    Table 12 DBMS_LOB.SUBSTR interface parameters

    Parameter

    Description

    lob_loc

    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.

    offset

    Number of bytes or characters to be read.

    buffer

    Number of characters or bytes offset from the start position.

  • DBMS_LOB.TRIM

    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 LOB, an exception occurs.

    The function prototype of DBMS_LOB.TRIM is:

    1
    2
    3
    4
    5
    6
    7
    DBMS_LOB.TRIM (
    lob_loc     IN OUT     BLOB,
    newlen      IN         INTEGER);
    
    DBMS_LOB.TRIM (
    lob_loc    IN          OUT CLOB,
    newlen     IN          INTEGER);
    
    Table 13 DBMS_LOB.TRIM interface parameters

    Parameter

    Description

    lob_loc

    BLOB type object 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.

  • DBMS_LOB.CREATETEMPORARY

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

    The function prototype of DBMS_LOB.CREATETEMPORARY is:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    DBMS_LOB.CREATETEMPORARY (
    lob_loc    IN OUT      BLOB,
    cache      IN          BOOLEAN,
    dur        IN          INTEGER);
    
    DBMS_LOB.CREATETEMPORARY (
    lob_loc    IN OUT     CLOB,
    cache      IN         BOOLEAN,
    dur        IN         INTEGER);
    
    Table 14 DBMS_LOB.CREATETEMPORARY interface parameters

    Parameter

    Description

    lob_loc

    LOB descriptor

    cache

    This parameter is used only for syntax compatibility.

    dur

    This parameter is used only for syntax compatibility.

  • DBMS_LOB.APPEND

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

    The function prototype of DBMS_LOB.APPEND is:

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

    Parameter

    Description

    dest_lob

    LOB descriptor to be written

    src_lob

    LOB descriptor to be read

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