Database Reference
In-Depth Information
Now, we want to load some data into either a
BLOB
or a
CLOB
. The method for doing so is rather easy, for example:
EODA@ORA12CR1> create table demo
2 ( id int primary key,
3 theClob clob
4 )
5 /
Table created.
EODA@ORA12CR1> host echo 'Hello World!' > /tmp/test.txt
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( 'DIR1', '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.
EODA@ORA12CR1> select dbms_lob.getlength(theClob), theClob from demo
2 /
DBMS_LOB.GETLENGTH(THECLOB) THECLOB
--------------------------- ---------------
13 Hello World!
Walking through the preceding code we see:
CLOB
to an
EMPTY_CLOB()
, and retrieve
its value in one call. With the exception of temporary LOBs, LOBs live in the database—we
cannot write to a LOB variable without having a pointer to either a temporary LOB or a LOB
that is already in the database. An
EMPTY_CLOB()
is not a
NULL CLOB
; it is a valid non-
NULL
pointer to an empty structure. The other thing this did for us was to get a LOB locator, which
points to data in a row that is locked. If we were to have selected this value out without locking
the underlying row, our attempts to write to it would fail because LOBs must be locked prior to
writing (unlike other structured data). By inserting the row, we have, of course, locked the row.
If we were modifying an existing row instead of inserting, we would have used
SELECT FOR
UPDATE
to retrieve and lock the row.
•
On lines 5 and 6, we create a row in our table, set the
BFILE
object. Note how we use
DIR1
in uppercase—this is key, as we
will see in a moment. This is because we are passing to
BFILENAME()
the
name
of an object,
not the object itself. Therefore, we must ensure the name matches the case Oracle has stored
for this object.
•
On line 8, we create a
