| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to insert BLOBs / images
A copy of this was sent to "Daniel Clamage" <dclamageNOSPAM_at_telerama.com> (if that email address didn't require changing) On Sat, 5 Dec 1998 03:36:35 -0000, you wrote:
>When I ran your stored procedure load_file, I got:
>ORA-22289: cannot perform LOADFROMFILE operation on an unopened file
>on the loadfromfile.
>
In 8.0.3 and prior, you could call dbms_lob.loadfromfile without having opened the BFILE object. In 8.0.4 and up, the rule is you must open the bfile first.
The code should look something like:
for x in ( select * from demo ) loop
update demo
set theBlob = empty_blob()
where id = x.id
RETURN theBlob into l_blob;
dbms_lob.fileopen( x.theBfile );
dbms_lob.loadfromfile( l_blob, x.theBfile,
dbms_lob.getlength(x.theBfile));
dbms_lob.fileclose( x.theBfile );
end loop;
so, you need to get a bfile (example has a table full of bfiles), open it (the bfile), load from it, and then close it.
>--
>- Dan Clamage
>http://www.telerama.com/~dclamage
>If you haven't crashed the Server,
>you haven't been trying hard enough.
>Andrew Protasov wrote in message ...
>>
>>Hi,
>>
>>You should use DBMS_LOB package:
>>
>>SVRMGR> create or replace directory test_dir as 'E:\ORANT\BIN';
>>Statement processed.
>>SVRMGR> drop table test_blob;
>>Statement processed.
>>SVRMGR> create table test_blob
>> 2> (dir_name varchar2(30)
>> 3> ,file_name varchar2(255)
>> 4> ,text blob
>> 5> )
>> 6> lob(text) store as test_blob_text
>> 7> (chunk 4096 tablespace prot)
>> 8> ;
>>Statement processed.
>>SVRMGR> create or replace procedure load_file
>> 2> (d varchar2 -- directory name
>> 3> ,f varchar2 -- file name
>> 4> ) as
>> 5> l blob;
>> 6> begin
>> 7> insert into test_blob(dir_name,file_name,text)
>> 8> values(d,f,empty_blob)
>> 9> returning text into l;
>> 10>
dbms_lob.loadfromfile(l,bfilename(d,f),dbms_lob.getlength(bfilename(d,f)));
>> 11> end;
>> 12> /
>>Statement processed.
>>SVRMGR> execute load_file('TEST_DIR','MIG80.EXE');
>>Statement processed.
>>SVRMGR> execute load_file('TEST_DIR','SVRMGR30.EXE');
>>Statement processed.
>>SVRMGR> execute load_file('TEST_DIR','PLUS80.EXE');
>>Statement processed.
>>SVRMGR> commit;
>>Statement processed.
>>SVRMGR> select
>> 2> substr(dir_name ,1,30) dir_name
>> 3> ,substr(file_name,1,30) file_name
>> 4> ,dbms_lob.getlength(text) file_length
>> 5> from test_blob;
>>DIR_NAME FILE_NAME FILE_LENGT
>>------------------------------ ------------------------------ ----------
>>TEST_DIR MIG80.EXE 72704
>>TEST_DIR SVRMGR30.EXE 128000
>>TEST_DIR PLUS80.EXE 285696
>>3 rows selected.
>>
>>Andrew Protasov
>>
>>> New to Oracle, how do I insert with a BLOB (image file, word file,
>>> spreadsheet, PDF, etc).
>>>
>>> What is the syntax?
>>> Is there a procedure that I pass params to?
>>>
>>> Craig
>>>
>>>
>>>
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
-- http://govt.us.oracle.com/ -- downloadable utilities ---------------------------------------------------------------------------- Opinions are mine and do not necessarily reflect those of Oracle Corporation Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it.Received on Sat Dec 05 1998 - 00:00:00 CST
 |
 |