EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials DBMS Tutorial DBMS_LOB
Secondary Sidebar
DBMS Tutorial
  • DBMS
    • What is DBMS?
    • Introduction To DBMS
    • DBMS ER Diagram
    • DBMS_RANDOM
    • DBMS_XPLAN
    • DBMS join
    • DBMS Functions
    • Data Administrator in DBMS
    • Checkpoint in DBMS
    • DBMS Table
    • Mapping Constraints in DBMS
    • DBMS Canonical Cover
    • DBMS Log-Based Recovery
    • DBMS Multivalued Dependency
    • View Serializability in DBMS
    • DBMS Concepts
    • DBMS Constraints
    • DBMS_Scheduler
    • B+ Tree in DBMS
    • DBMS_LOB
    • dbms entity
    • DBMS Foreign Key
    • DBMS Users
    • DBMS_Metadata.get_ddl
    • Relational Algebra in DBMS
    • DBMS Components
    • DBMS Features
    • DBMS Models
    • DBMS Relational Model
    • Hashing in DBMS
    • DBMS network model
    • Relationship in DBMS
    • ER Model in DBMS
    • Data Models in DBMS
    • Static Hashing in DBMS
    • Advantages of DBMS
    • dbms_output.put_line
    • DBMS Data Dictionary
    • dbms_xplan.display_cursor
    • Normal Forms in DBMS
    • DBMS helps achieve
    • DBMS 3 tier Architecture
    • Relational Calculus in DBMS
    • Serializability in DBMS
    • File Organization in DBMS
    • DBMS Transaction Processing
    • States of Transaction in DBMS
    • Functional Dependency in DBMS
    • Generalization in DBMS
    • Data Independence in DBMS
    • Lock Based Protocols in DBMS
    • Deadlock in DBMS
    • Integrity Constraints in DBMS
    • Concurrency Control in DBMS
    • Validation Based Protocol in DBMS
    • DBMS Locks
    • Normalization in DBMS
    • Transaction Property in DBMS
    • Specialization in DBMS
    • Aggregation in DBMS
    • Types of DBMS

DBMS_LOB

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.

Syntax:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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

This is a guide to DBMS_LOB. Here we discuss the definition, syntax, How does dbms_lob work in dbms with examples. You may also look at the following articles to learn more –

  1. DBMS Locks
  2. DBMS 3 tier Architecture
  3. DBMS Features
  4. DBMS Components
Popular Course in this category
SQL Training Program (10 Courses, 8+ Projects)
  10 Online Courses |  8 Hands-on Projects |  80+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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 Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more