Generally LOB( BLOBs , CLOBs) are not inserted in a table with normal insertion method. To insert a LOB object we have to move with certain steps. PFB steps to insert a LOB object in Oracle Table :-
1. Create a Table having a LOB column.
SQL> create table LOB_INSERT
(
LOB_id number(12),
LOB_name varchar2(80),
file_size number,
LOB_file blob
) ;
Table created.
2. Create a directory in FS so that Oracle can access files in that directory.
SQL> create or replace directory LOB as '/home/oracle';
NOTE :- Directory oracle has to be physically present in FS.
1. Create a Table having a LOB column.
SQL> create table LOB_INSERT
(
LOB_id number(12),
LOB_name varchar2(80),
file_size number,
LOB_file blob
) ;
Table created.
2. Create a directory in FS so that Oracle can access files in that directory.
SQL> create or replace directory LOB as '/home/oracle';
Directory created.
3. Now copy files in oracle directory which you want to insert in LOB_INSERT table and then run the below script to insert LOB objects in LOB_INSERT table.
SQL> declare
l_size number;
2 3 l_file_ptr bfile;
4 l_blob blob;
5 begin
6 l_file_ptr := bfilename('LOB ', 'health_check.html');
7 dbms_lob.fileopen(l_file_ptr);
8 l_size := dbms_lob.getlength(l_file_ptr);
9 for ctr in 1 .. 3 loop
10 insert into LOB_INSERT
11 (
12 LOB_id ,
13 LOB_name,
14 file_size,
15 LOB_file
16 )
17 values
18 (
19 ctr,
20 'filenumber '||ctr,
21 null,
22 empty_blob()
23 )
24 returning LOB_file into l_blob;
25 dbms_lob.loadfromfile(l_blob, l_file_ptr, l_size);
26 end loop;
27 commit;
28 dbms_lob.close(l_file_ptr);
29 end;
30 /
PL/SQL procedure successfully completed.
This procedure will insert three records health_check.html file in LOB_INSERT table.
SQL> select * from LOB_INSERT;
LOB_ID LOB_NAME FILE_SIZE LOB_FILE
---------- ------------------------------ ---------- ----------------------------------------
1 filenumber 1 3C68746D6C3E0A3C686561643E0A3C6D65746120
687474702D65717569763D22436F6E74656E742D
547970652220636F6E74656E743D22746578742F
68746D6C3B20636861727365743D5554462D3822
2 filenumber 2 3C68746D6C3E0A3C686561643E0A3C6D65746120
687474702D65717569763D22436F6E74656E742D
547970652220636F6E74656E743D22746578742F
68746D6C3B20636861727365743D5554462D3822
3 filenumber 3 3C68746D6C3E0A3C686561643E0A3C6D65746120
LOB_ID LOB_NAME FILE_SIZE LOB_FILE
---------- ------------------------------ ---------- ----------------------------------------
687474702D65717569763D22436F6E74656E742D
547970652220636F6E74656E743D22746578742F
68746D6C3B20636861727365743D5554462D3822
This way we can insert LOB files in Oracle Table.
I hope this article helped you.
Regards,
Amit Rath
No comments:
Post a Comment