by Kam-Hung Soh (kamhung dot soh at gmail dot com) 2007/07/28 15:35:06
Oracle can store large amounts of data, such as files, in LOBs. After importing this data into your Oracle tables, some tools allow you to export individual LOBs into the file system. If you want to export many LOBs at once, you can write a PL/SQL program. In this article, I describe how I set up a test environment to import and export LOBs by …
You should be familiar with Oracle utilities SQL Plus and SQL Loader to follow this article. See Programming PL/SQL for a detailed discussion on how Oracle stores LOBs.
Below is the definition of a simple table with a LOB column for images. You can load other types of files; I chose images because I was thinking of loading them into a database for another project.
create table image (
name varchar(40),
data blob
);
comment on table image is 'Image test table';
comment on column image.name is 'Filename of the image';
comment on column image.data is 'LOB data of the image';
You can import LOBs using SQL Loader by creating a SQL Loader control file such as image.ctl using the definition below and editing the lines after the begindata (line 13) with the name of some test files. In my sample, files for the first three names exist (lines 14 to 16) while the fourth file name (line 17) is a file that does not exist.
1 -- Image test
2 -- Use: sqlldr test/test@xe control=Image.ctl
3 options (log=Image.log, bad=Image.bad)
4 load data
5 infile *
6 into table image
7 replace
8 fields terminated by ','
9 (
10 name,
11 data lobfile(name) terminated by eof
12 )
13 begindata
14 camera.jpg
15 2001_hal1.jpg
16 DSCF0832.jpg
17 does_not_exist.jpg
To import data into your table using this control file, enter the following statement in your command shell:
sqlldr user/password control=image.ctl
SQL Loader writes a log to image.log. Check the log file, image.log, to ensure that all your files were loaded successfully. In my case, I expected one row to refer to a file does not exist, so I found this message in my log file:
… SQL*Loader-502: unable to open data file 'does_not_exist.jpg' for field DATA table IMAGE SQL*Loader-553: file not found SQL*Loader-509: System error: The system cannot find the file specified. … Total logical records skipped: 0 Total logical records read: 4 Total logical records rejected: 1 Total logical records discarded: 0
Another check is to use a SQL query. Note that SQL Loader created a row for a non-existent file in the fourth row, with a length of 0.
SQL> select name, dbms_lob.getlength(data) from image; NAME DBMS_LOB.GETLENGTH(DATA) -------------------- ------------------------ camera.jpg 39929 2001_hal1.jpg 7995 DSCF0832.jpg 980302 does_not_exist.jpg 0
A LOB can't be written directly into a file, so its data is copied into a RAW object, then the RAW object is written to a file. However, the size of a RAW object is limited to 32767 bytes, and a loop is required to write the LOB's data incrementally in blocks of 32767 bytes. Below is a PL/SQL procedure write_lob() that implements this process.
1 -- $Id: 001b.php,v 1.1 2007/07/01 09:03:16 hung Exp $
2 create or replace procedure write_lob(
3 dir_name in varchar,
4 file_name in varchar,
5 file_data in blob
6 )
7 is
8 file_ref utl_file.file_type;
9 lob_size number;
10 raw_max_size constant number := 32767;
11 buffer raw(32767);
12 buffer_offset number := 1; -- Position in stream
13 buffer_length number; -- Amount of data to read and write
14 begin
15 lob_size := dbms_lob.getlength(file_data);
16 -- dbms_output.put_line(dir_name || ',' || file_name || ',' || lob_siz
e);
17 file_ref := utl_file.fopen(dir_name, file_name, 'WB', raw_max_size);
18 buffer_length := raw_max_size;
19 while buffer_offset < lob_size loop
20 if buffer_offset + raw_max_size > lob_size then
21 buffer_length := lob_size - buffer_offset;
22 end if;
23 -- dbms_output.put_line(buffer_offset || ',' || buffer_length);
24 dbms_lob.read(file_data, buffer_length, buffer_offset, buffer);
25 utl_file.put_raw(file_ref, buffer, true);
26 buffer_offset := buffer_offset + buffer_length;
27 end loop;
28 utl_file.fclose(file_ref);
29 end write_lob;
30 /
Lines 2 to 6: This procedure takes three arguments: an Oracle directory (see next section below), the output file name, and a pointer to a LOB.
Lines 7 to 13: Define a RAW datatype buffer. An Oracle RAW datatype in PL/SQL is 32767 bytes.
Lines 14 to 18: Find the size of the LOB to write and open a binary file stream.
Lines 19 to 27: Write a LOB's data incrementally to a file.
Lines 28 to 29: Close the file.
Lines 16 and 23 are just debugging statements.
Note: if you are using OracleXE, you have to allow 'public' to execute the utl_file package using the following statement (see Steve Feuer's article) about this issue).
GRANT EXECUTE on SYS.UTL_FILE TO PUBLIC
You can now write LOB data using the write_lob() procedure by running the following PL/SQL program in SQL Plus:
1 @write_lob.sql;
2 set serveroutput on
3
4 create or replace directory TEST_DIR as 'C:\Users\hung\Documents\Program
ming\Oracle\LOB';
5
6 declare
7 cursor image_cur is select * from image where dbms_lob.getlength(data)
> 0;
8 begin
9 for image_rec in image_cur
10 loop
11 write_lob('TEST_DIR', image_rec.name, image_rec.data);
12 end loop;
13 end;
14 /
Line 1 loads the write_lob() procedure.
Line 2 instructs SQL Plus to write output to the command shell.
Line 4 creates an Oracle directory object called TEST_DIR and associates it with a folder in the file system. For security reasons, functions in the utl_file package only write to a defined directory.
Line 6 to 7 defines an explicit cursor called image_cur. In this cursor, only rows in the image table with more than 0 data length are selected. If you leave out the WHERE clause, write_lob() will create zero-length files.
Lines 8 to 13 iterate through all records in this cursor and calls write_lob() for each record.
If there are no errors when you executed this program, all your LOBs should be successfully exported to your file system.