EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials DBMS Tutorial DBMS_LOB
 

DBMS_LOB

Updated April 1, 2023

DBMS_LOB

 

 

Definition of DBMS_LOB

DBMS_LOB is a package that provides the suborograms to the user in order to operate on BLOBs, CLOBs and NCLOBs. This package can also be used to manipulate and access the LOB values in both external and internal locations of storage. This package provides the read-only operations for BFILEs. Any of the subprograms of DBMS_LOB called from the anonymous PL/SQL block uses the privileges of the current user for execution, similar to its stored procedure called uses the privileges of the owner of stored procedure for its execution. This package is under SYS.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

Below given is the basic syntax of using the DBMS_LOB package in a database management system:

In order to ensure whether the created procedure needs definer’s rights or the invoker’s right, the user needs to set the AUTHID while creating the procedure similar to the one given below:

CREATE PROCEDURE proc_name authid current_user . . .
Or
CREATE PROCEDURE proc_name authid definer . . .

How does dbms_lob work in dbms?

Some of the important points explaining the working of DBMS_LOB package in Oracle are given below:

1. The operations which are performed by DBMS_LOB package are performed by the current calling user rather than the package owner.

2. Secure access can be provided to the BFILEs using the directory feature which is provided in the BFILENAME function.

3. Oracle provides the facility to define the AUTHID at the time of procedure creation in order to indicate whether they want the invoker’s right or definer’s right.

4. In order to use the LOB in the database, the first thing is to use SQL DDL in order to define the table that contains LOB columns.

5. The parameters like length, offset, and amount for the subprograms which are operating on BLOBs and BFILES need to be specified in bytes.

6. The parameters like length, offset, and amount for the subprograms which are operating on CLOBs need to be specified in characters.

7. No negative offsets from the trail of LOB objects are allowed, only positive and absolute values are allowed.

8. Procedures having the NULL values for the LOB parameters returns an exception.

9. NO_DATA_FOUND exception is displayed to indicate the End Of Lob condition which is raised when the user attempts to read beyond the end Of the Lob.

10. If not stated, the default value of the offset is 1, indicating the first byte of BLOB or BFILES. In the case of CLOB, it indicates the first character.

11. For the RAW and VARCHAR2 parameters of the DBMS_LOB, the upper limit is 32767 bytes whereas charbuf can hold 3000 single-byte characters.

The following data types are used by DBMS_LOB package:

S.No. Datatype Description
1. INTEGER It specifies the size of the buffer, the amount to access( on characters for CLOBs and NCLOBs and bytes for BLOBs), or the offset into a LOB
2. BLOB It specifies the source or destination binary BLOBs.
3. CLOB It specifies the source or destination character CLOBs.
4. RAW It specifies the source or destination RAW buffer (It is used with BLOBs).
5. VARCHAR2 It specifies the source or destination character buffer (used with CLOBs and NCLOBs).
6. BFILE It specifies the large binary object which is stored outside the database.

Common Exception raised while working with the dbms_lob package:

S.No. Exception name Description
1. INVALID_ARGVAL It specifies that the input value is either null, invalid, or out of the range. The argument is expecting a not null value.
2. NO_DATA_FOUND It is not a hard error. It is an end-of-lob indicator for looping read operations.
3. ACCESS_ERROR It specifies that the user is trying to write access data to the lob where lob size is limited to 4GB.
4. VALUE_ERROR It is an error for invalid values for the given subprogram parameters.
5. CONTENTTYPE_TOOLONG It specifies that the length of the content-type string is too long than the maximum length specified. To rectify it, the length of the content-type string needs to be modified.
6. OPEN_TOOMANY It specifies that the number of open files is more than the limit.
7. INVALID_DIRECTORY It specifies that the directory which is used for the current operation is invalid. Either it is wrongly opened or is modified by DBA since last access.

S.No. Exception name Description

1. INVALID_ARGVAL It specifies that the input value is either null, invalid, or out of the range. The argument is expecting a not null value.

2. NO_DATA_FOUND It is not a hard error. It is an end-of-lob indicator for looping read operations.

3. ACCESS_ERROR It specifies that the user is trying to write access data to the lob where lob size is limited to 4GB.

4. VALUE_ERROR It is an error for invalid values for the given subprogram parameters.

5. CONTENTTYPE_TOOLONG It specifies that the length of the content-type string is too long than the maximum length specified. To rectify it, the length of the content-type string needs to be modified.

6. OPEN_TOOMANY It specifies that the number of open files is more than the limit.

7. INVALID_DIRECTORY It specifies that the directory which is used for the current operation is invalid. Either it is wrongly opened or is modified by DBA since the last access.

Examples

Below given are some of the examples showing the practical implementation of lob in DBMS:

Example #1

Consider the following table ‘products’ is created:

create table products (pid int, pdetails clob);

One row is inserted in the table with the details:

insert into products(pid,pdetails) values(1001, 'xyzabc’);

The below given procedure is created in which the clob values are appended:

declare
a_new1 clob;
a_new2 clob;
begin
a_new1 := 'raghav';
select pdetails into a_new2 from products where pid = 1001;
dbms_output.put_line('before append, value of a_new1 is ' || a_new1);
dbms_output.put_line('before append value of a_new2 is ' || a_new2);
dbms_lob.append(a_new1, a_new2);
dbms_output.put_line('After append value of a_new1 is ' || a_new1);
dbms_output.put_line('After append value of a_new2 is ' || a_new2);
insert into products values (1002, a_new1);
end;

Output:

DBMS_LOB 1

After the execution of the above procedure, the status of table ‘product’ is given below:

select * from products;

DBMS_LOB 2

Explanation:

In the above program, a table with the name ‘products’ is created. Table ‘products’ have the columns ‘pid’ and ‘pdetails’ with the data type as int and clob respectively. Data is inserted in the table ‘product’ using the simple insert command. Now the stored procedure is created with the two variables as ‘a_new1’ and “a_new2’ with the data type as ‘clob’. Value of ‘a_new1’ is declared as ‘raghav’. Data of the table ‘products’ is retrieved in the variables and their original values are printed using the ‘dbms_output.put_line’ statement on the console. Now the append operation is performed by using the append function and their respective values are printed on the console. Final values are inserted in the table ‘products’. To view the current status of the table ‘products’, a select query is run and as a result, 2 rows are found in the table with the relevant data.

Conclusion

The above description clearly explains what the dbms_lob package is and how it works in Oracle to operate on BLOBs, CLOBs and NCLOBs. This package provides all the necessary things which are required to deal with large objects. For a programmer, it is important to understand these concepts clearly as they are the building blocks when it comes to working on real databases containing large objects.

Recommended Articles

We hope that this EDUCBA information on “DBMS_LOB” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. DBMS Locks
  2. DBMS 3 tier Architecture
  3. DBMS Features
  4. DBMS Components

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW