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:
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:
After the execution of the above procedure, the status of table ‘product’ is given below:
select * from products;
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 –
