Re: How to insert BLOB field into the table?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 24 Oct 1999 20:47:31 -0400
Message-ID: <r5wTOKdgI=o9iVZlCz8U586Rg+Ub@4ax.com>

A copy of this was sent to ppakorn_at_my-deja.com (if that email address didn't require changing) On Sun, 24 Oct 1999 21:08:31 GMT, you wrote:

>I created a table that has BOLB as datatype as shown below.
>
>SQL> desc candidate_bio;
> Name Null? Type
>------------------------------------ -------- ----------
> CANID NUMBER
> RESUME BLOB
> PHOTO BINARY FILE LOB
>
>SQL> select * from candidate_bio;
>SP2-0678: Column or attribute type can not be displayed by SQL*Plus
>
>Can somebody tell me how can I insert BLOB data(resume and photo) to
>this table, and browse it?
>

it is not a blob, but a BFILE

scott_at_8i> create table x ( y bfile, z blob );

Table created.

scott_at_8i> desc x

 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 Y                                            BINARY FILE LOB
 Z                                            BLOB

BFILES must be OPENED before they can be displayed (the REQUIRE procedural logic). You cannot 'select' them in sqlplus.

Here is an example of loading a BFILE type, showing you cannot select it (cause it is not open), and that you can show it either in hex or in raw binary using utl_raw (utl_raw is a supplied package, it may not be installed -- its part of replication -- if you look in your $ORACLE_HOME/rdbms/admin, you'll find *raw* (a spec and body). install these using svrmgrl when connected as SYS or INTERNAL (only sys or internal))

tkyte_at_8i> create table demo
  2 ( id int primary key,
  3 theBfile bfile
  4 )
  5 /

Table created.

tkyte_at_8i>
tkyte_at_8i> create or replace directory my_files as '/export/home/tkyte/public_html';

Directory created.

tkyte_at_8i>
tkyte_at_8i> insert into demo values ( 1, bfilename( 'MY_FILES', 'aria.gif' ) );

1 row created.

tkyte_at_8i>
tkyte_at_8i> select id, utl_raw.cast_to_varchar2( dbms_lob.substr( theBfile, 20, 1 ) ) from demo;
select id, utl_raw.cast_to_varchar2( dbms_lob.substr( theBfile, 20, 1 ) ) from demo

                                     *

ERROR at line 1:

ORA-22289: cannot perform FILEREAD operation on an unopened file or LOB
ORA-06512: at "SYS.DBMS_LOB", line 680
ORA-06512: at line 1


tkyte_at_8i> 

tkyte_at_8i>
tkyte_at_8i> begin

  2          for x in ( select * from demo ) loop
  3                  dbms_lob.fileopen( x.theBfile );
  4                  dbms_output.put_line( dbms_lob.substr( x.theBfile, 25, 1 )
);
  5                  dbms_output.put_line( utl_raw.cast_to_varchar2(
dbms_lob.substr( x.theBfile, 25, 1 )));
  6          end loop;

  7 end;
  8 /
4749463837611A012C00B30000C0C0C0FF00310000FFFFFFFF GIF87a,

PL/SQL procedure successfully completed.

>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Oct 24 1999 - 19:47:31 CDT