If you are not using SQL Loader, you *will* need a directory object, eg, Here's a code snippet for you - see how this goes
SQL> declare
2 l_blob blob;
3 l_clob clob;
4 l_bfile bfile;
5 begin
6 insert into demo values ( 1, empty_blob(), empty_clob() )
7 returning theBlob, theClob into l_blob, l_clob;
8
9 l_bfile := bfilename( 'MY_FILES', 'asktom.rtf' );
10 dbms_lob.fileopen( l_bfile );
11 dbms_lob.loadfromfile( l_blob, l_bfile, dbms_lob.getlength( l_bfile ) );
12 dbms_lob.fileclose( l_bfile );
13
14 l_bfile := bfilename( 'MY_FILES', 'asktom.txt' );
15 dbms_lob.fileopen( l_bfile );
16 dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength( l_bfile ) );
17 dbms_lob.fileclose( l_bfile );
18 end;
19 /
Depending on your characterset, you might want to use DBMS_LOB.LOADCLOBFROMFILE instead of DBMS_LOB.LOADFROMFILE
If you are using SQL Loader, you can use a fully qualified name to pick up the file from the directory, eg
load data
infile *
replace
into table test_lob
fields terminated by ','
(
lob_id char(1),
lob_file FILLER char,
MY_LOB LOBFILE(lob_file) TERMINATED BY EOF
)
begindata
1,c:\data\doc\acct1.txt
2,c:\data\doc\acct2.txt