Re: How to insert BLOBs / images

From: Andrew Protasov <oracle_at_protasov.kiev.ua>
Date: 1998/12/05
Message-ID: <ABx7GQsK42_at_protasov.kiev.ua>#1/1

Hi,

What Oracle server did you try to execute procedure on? I tested it with Oracle 8.0.3 and all was OK. But you can try another version of my procedure (with fileopen):

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>  bf bfile;
     7> begin
     8>  savepoint s_load_file;
     9>  bf:=bfilename(d,f);
    10>  insert into test_blob(dir_name,file_name,text)
    11>   values(d,f,empty_blob)
    12>   returning text into l;
    13>  dbms_lob.fileopen(bf);
    14>  dbms_lob.loadfromfile(l,bf,dbms_lob.getlength(bf));
    15>  dbms_lob.fileclose(bf);
    16> exception when others then
    17>  if dbms_lob.fileisopen(bf)<>0 then
    18>   dbms_lob.fileclose(bf);
    19>  end if;
    20>  rollback to s_load_file;
    21>  raise;
    22> end;
    23> /

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

> 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