Re: How to insert BLOBs / images

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

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 CST