Re: How to insert BLOBs / images

From: Daniel Clamage <dclamageNOSPAM_at_telerama.com>
Date: 1998/12/05
Message-ID: <3668f259.0_at_paperboy.telerama.com>#1/1


When I ran your stored procedure load_file, I got: ORA-22289: cannot perform LOADFROMFILE operation on an unopened file on the loadfromfile.

--
- 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
>>
>>
>>
Received on Sat Dec 05 1998 - 00:00:00 CET

Original text of this message