Loading LOB data is not typically the DBA’s job, but you should be familiar with the techniques used to populate LOB columns. Developers may come to you for help with troubleshooting, performance, or space-related issues.
Loading a CLOB
First, create an Oracle database directory object that points to the OS directory in which the CLOB file is stored. This directory object is used when loading the CLOB. In this example, the Oracle directory object is named LOAD_LOB, and the OS directory is / oradata/oracle/lob:
SQL> create or replace directory load_lob as ‘/oradata/oracle/lob’;
Note For directories you can also use the same pre-existing directories for DATA_PUMP_DIR.
For reference, listed next is the DDL used to create the table in which the CLOB file is loaded:
SQL> create table patchmain( patch_id number primary key , patch_desc clob, patch_file blob) lob(patch_desc, patch_file)
store as securefile (compress low) tablespace lob_data;
This example also uses a sequence named PATCH_SEQ. Sequences are one way to manage primary keys, or you can use an IDENTITY column. Here is the sequence creation script:
SQL> create sequence patch_seq;
The following bit of code uses the DBMS_LOB package to load a text file (patch.txt) into a CLOB column. In this example, the table name is PATCHMAIN, and the CLOB column is PATCH_DESC:
SQL> declare
src_clb bfile; — point to source CLOB on file system dst_clb clob; — destination CLOB in table src_doc_name varchar2(300) := ‘patch.txt’;
src_offset integer := 1; — where to start in the source CLOB dst_offset integer := 1; — where to start in the target CLOB lang_ctx integer := dbms_lob.default_lang_ctx;
warning_msg number; — returns warning value if bad chars begin
src_clb := bfilename(‘LOAD_LOB’,src_doc_name); — assign pointer to file —
insert into patchmain(patch_id, patch_desc) — create LOB placeholder values(patch_seq.nextval, empty_clob())
returning patch_desc into dst_clb; —
dbms_lob.open(src_clb, dbms_lob.lob_readonly); — open file —
— load the file into the LOB dbms_lob.loadclobfromfile( dest_lob => dst_clb, src_bfile => src_clb,
amount => dbms_lob.lobmaxsize, dest_offset => dst_offset, src_offset => src_offset,
bfile_csid => dbms_lob.default_csid, lang_context => lang_ctx,
warning => warning_msg );
dbms_lob.close(src_clb); — close file
dbms_output.put_line(‘Wrote CLOB: ‘ || src_doc_name); end;
You can place this code in a file and execute it from the SQL command prompt. In this example, the file that contains the code is named clob.sql:
SQL> set serverout on size 1000000 SQL> @clob.sql
Here is the expected output:
Wrote CLOB: patch.txt
PL/SQL procedure successfully completed.
You can also use SQL*Loader to load the data. Here is an example control and parameter file for loading BFILE.
SQL*Loader needs a parfile and control file. The parfile calls the control file. The control file shown next, load_bfile.ctl, has the filename, table name, and details about the file such as the field separator. The parfile, load_bfile.par, has details about user connection, control file, and log files.
$ view load_bfile.ctl LOAD DATA
INFILE bfile_example.dat INTO TABLE patchmain FIELDS TERMINATED BY ‘,’
( patch_id INTEGER EXTERNAL(6), patch_file BFILE (DirName, FileName),
FileName FILLER CHAR(30), DirName FILLER CHAR(30) )
$ view load_bfile.par userid=dev1@db23pdb/Pa$$w0rd! control=load_bfile.ctl log=load_bfile.log bad=load_bfile.bad data=bfile_example.dat direct=true
After the parfile and control file are set, it is just a command line to call sqlldr:
$ sqlldr parfile=load_bfile.par
Reading BFILEs
As discussed previously, a BFILE data type is simply a column in a table that stores a pointer to an OS file. A BFILE provides you with read-only access to a binary file on disk. To access a BFILE, you must first create a directory object. Next, a table is created that contains a BFILE data type:
SQL> create table patchmain (patch_id number , patch_file bfile);
For this example, a file named patch.zip is located in the directory. You make Oracle aware of the binary file by inserting a record into the table using the directory object and the filename:
SQL> insert into patchmain values(1, bfilename(‘LOAD_LOB’,’patch.zip’));
Now, you can access the BFILE via the DBMS_LOB package. For instance, if you want to verify that the file exists or display the length of the LOB, you can do so as follows:
SQL> select dbms_lob.fileexists(bfilename(‘LOAD_LOB’,’patch.zip’)); SQL> select dbms_lob.getlength(patch_file) from patchmain;
In this manner, the binary file behaves like a BLOB. The big difference is that the binary file is not stored within the database.
Oracle lets you store large objects in databases via various LOB data types. LOBs facilitate the storage, management, and retrieval of video clips, images, movies, word documents, large text files, and so on.
