Database Reference
In-Depth Information
•
On line 9, we open the LOB. This will allow us to read it.
•
On lines 11 and 12, we load the entire contents of the operating system file
/tmp/test.txt
into
the LOB locator we just inserted. We use
DBMS_LOB.GETLENGTH()
to tell the
LOADFROMFILE()
routine how many bytes of the
BFILE
to load (all of them).
Lastly, on line 14, we close the
BFILE
we opened, and the
CLOB
is loaded.
If we had attempted to use
dir1
instead of
DIR1
in the preceding example, we would have encountered the
following error:
•
EODA@ORA12CR1> declare
...
6 returning theclob into l_clob;
7
8 l_bfile := bfilename( 'dir1', 'test.txt' );
9 dbms_lob.fileopen( l_bfile );
...
15 end;
16 /
declare
*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at line 9
This is because the directory
dir1
does not exist—
DIR1
does. If you prefer to use directory names in mixed case,
you should use quoted identifiers when creating them as we did for
dir2
. This will allow you to write code as
shown here:
EODA@ORA12CR1> declare
2 l_clob clob;
3 l_bfile bfile;
4 begin
5 insert into demo values ( 1, empty_clob() )
6 returning theclob into l_clob;
7
8 l_bfile := bfilename( 'dir2', 'test.txt' );
9 dbms_lob.fileopen( l_bfile );
10
11 dbms_lob.loadfromfile( l_clob, l_bfile,
12 dbms_lob.getlength( l_bfile ) );
13
14 dbms_lob.fileclose( l_bfile );
15 end;
16 /
PL/SQL procedure successfully completed.
