Text and images on the database server's file system can be loaded into CLOB and BLOB columns without leaving PL/SQL. A BFILE points to the file through a directory object, and DBMS_LOB.LOADCLOBFROMFILE and LOADBLOBFROMFILE copy its contents into a LOB, converting text to the database character set on the way.
Code for This Guide
The main examples are in the examples/pkg-dbms-lob folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_lob.loadclobfromfile(dest_lob, src_bfile, amount, dest_offset_in_out,
src_offset_in_out, bfile_csid, lang_context_in_out,
warning_out);
dbms_lob.loadblobfromfile(dest_lob, src_bfile, amount, dest_offset_in_out,
src_offset_in_out);BFILE_CSID is the character set of the file, such as NLS_CHARSET_ID('AL32UTF8'). Open the BFILE with FILEOPEN before loading and close it afterward.
The examples read welcome.txt and logo.png from the NIMBUS_FILES directory, which the NIMBUS setup creates; the files come from the setup/nimbus/files folder of the repository. The text goes into a POLICIES table:
Setup:
create table policies (id number primary key, body clob); insert into policies values (1, empty_clob()); commit;
Load Text and an Image
Example:
declare
v_file bfile := bfilename('NIMBUS_FILES', 'welcome.txt');
v_image bfile := bfilename('NIMBUS_FILES', 'logo.png');
v_text clob;
v_logo blob;
v_dest integer := 1;
v_src integer := 1;
v_lang integer := dbms_lob.default_lang_ctx;
v_warning integer;
begin
update policies set body = empty_clob() where id = 1 returning body into v_text;
dbms_lob.fileopen(v_file);
dbms_lob.loadclobfromfile(v_text, v_file, dbms_lob.lobmaxsize, v_dest, v_src,
nls_charset_id('AL32UTF8'), v_lang, v_warning);
dbms_lob.fileclose(v_file);
dbms_output.put_line('text loaded: ' || dbms_lob.getlength(v_text) || ' characters');
dbms_lob.createtemporary(v_logo, true);
v_dest := 1; v_src := 1;
dbms_lob.fileopen(v_image);
dbms_lob.loadblobfromfile(v_logo, v_image, dbms_lob.lobmaxsize, v_dest, v_src);
dbms_lob.fileclose(v_image);
dbms_output.put_line('image loaded: ' || dbms_lob.getlength(v_logo) || ' bytes, starts '
|| rawtohex(dbms_lob.substr(v_logo, 4, 1)));
rollback;
end;
/Output:
text loaded: 156 characters image loaded: 117 bytes, starts 89504E47 PL/SQL procedure successfully completed.
The text file is loaded into the BODY column of policy 1, through the locator that RETURNING gives, and the image into a temporary BLOB. The first four bytes, 89504E47, are the PNG signature. The block rolls back, so the table keeps its old value.
Offsets and Warnings
The offsets are IN OUT: after loading, they point just past what was copied, ready for the next call.
Example:
declare
v_file bfile := bfilename('NIMBUS_FILES', 'welcome.txt');
v_text clob;
v_dest integer := 1;
v_src integer := 1;
v_lang integer := dbms_lob.default_lang_ctx;
v_warn integer;
begin
dbms_lob.createtemporary(v_text, true);
dbms_lob.fileopen(v_file);
dbms_lob.loadclobfromfile(v_text, v_file, dbms_lob.lobmaxsize, v_dest, v_src,
nls_charset_id('AL32UTF8'), v_lang, v_warn);
dbms_lob.fileclose(v_file);
dbms_output.put_line('next character position: ' || v_dest);
dbms_output.put_line('next byte in the file: ' || v_src);
dbms_output.put_line('warning: ' || v_warn || ' (0 = no conversion problems)');
dbms_output.put_line('first line: ' || substr(v_text, 1, instr(v_text, chr(10)) - 1));
dbms_lob.freetemporary(v_text);
end;
/Output:
next character position: 157 next byte in the file: 157 warning: 0 (0 = no conversion problems) first line: Welcome aboard Nimbus Air! PL/SQL procedure successfully completed.
After 156 characters from 156 bytes, both offsets are 157. WARNING is 0 when every character converted cleanly, and DBMS_LOB.WARN_INCONVERTIBLE_CHAR when some did not.
Things to Know
- Reading a BFILE needs READ on the directory object; the file must be on the database server, not the client.
- DBMS_LOB.LOBMAXSIZE as the amount loads the whole file.
- Set the offsets back to 1 before loading another file into a new LOB.
- Drop the POLICIES table when you no longer need it.
Related Guides
Conclusion
DBMS_LOB.LOADCLOBFROMFILE loads a text file into a CLOB with character set conversion, and LOADBLOBFROMFILE loads any file into a BLOB as bytes. Open the BFILE, load with LOBMAXSIZE, check the warning, and close the file.
