manipulating blobs in sqlplus

From: JillS <jillsalo_at_gmail.com>
Date: Thu, 30 Aug 2007 08:53:29 -0700
Message-ID: <1188489209.103538.178300@22g2000hsm.googlegroups.com>

I am working for a software dev company doing a port to Oracle (from SQL Server). Currently, when deploying the initial software (or changes to the software) the database changes are made using sql scripts. DML handles changes to the data. This issue is that the software comes with preloaded documents (blobs). In SQL Server, they generate the DML scipts - for example inserts would be like this select 'insert into tab1 values (' || colA || ',' || colB || ')' from tab1;
In SQL Server, this works for blobs. The value of the blob is spit out as a stream of hex, which the insert statement can interpret when run at the client side. There is also no size limit on the blob field (unlike the 32767 byte limit for lobs in Oracle).

In Oracle, sqlplus just won't display a blob to the screen (which actually makes sense since it is binary data). But this means I can't duplicate the funtionality. I have tried generating the insert statement with utl_raw.cast_to varchar2 and then inserting with utl_raw.cast_to_raw, but this doesn't work (I have cut and paste below).

So far they are against delivering the actual documents to the client site outside of the database and have a script load them in. Other than this, I can only think of creating a temporary table with the table_name, column_name and blob value and then inserting with null values and using pl/sql to update and load the blobs.

Am I overlooking a simpler solution?

Here is what I have tried:
jill_at_RHORC> get insert_word_doc.sql
  1 create table demo
  2 ( id int primary key,
  3 theClob blob,
  4 theBlob blob
  5 )
  6 /
  7 create or replace
  8 procedure load_a_file( p_id in number,

  9                         p_filename in varchar2 )
 10  as
 11      l_blob    blob;
 12      l_bfile   bfile;
 13  begin
 14      insert into demo (id, theBlob) values ( p_id, empty_blob() )
 15      returning theBlob into l_blob;
 16      l_bfile := bfilename( 'JSALO_DIR', p_filename );
 17      dbms_lob.fileopen( l_bfile );
 18      dbms_lob.loadfromfile( l_blob, l_bfile,
 19                             dbms_lob.getlength( l_bfile ) );
 20      dbms_lob.fileclose( l_bfile );

 21 end;
 22 /
 23* exec load_a_file( 1, 'testword.doc' );  24
jill_at_RHORC> @insert_word_doc.sql

Table created.

Procedure created.

PL/SQL procedure successfully completed.

jill_at_RHORC> select theblob from demo;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus

jill_at_RHORC> select 'insert into demo (id, theblob) values ('||id||','|| chr(39)||theblob||chr(39)||'));' from demo; select 'insert into demo (id, theblob) values ('||id||','||chr(39)|| theblob||chr(39)||'));' from demo

                                                    *

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got BLOB

jill_at_RHORC> get gen_ins.sql
  1 set termout off
  2 set feedback off
  3 set pages 0
  4 spool out.sql
  5 select 'insert into demo values ('||id||','|| null||',utl_raw.cast_to_raw('||chr(39)||   6 utl_raw.cast_to_varchar2(dbms_lob.substr(theblob,2000,1))   7 ||chr(39)||'));' from demo;
  8* spool off
  9

jill_at_RHORC> @gen_ins.sql
jill_at_RHORC> set termout on
jill_at_RHORC> set feedback on
jill_at_RHORC> !more out.sql
insert into demo values (1,,utl_raw.cast_to_raw('?Ia!?a


>  ?y                   0          2      ?yyy    /   yy

yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyiYA #`       ??             L
bjbj!!                      .      L
              yy         yy
yy                 ?     ?      ?  ?      ?      ?      ?      ?
 U      U      U      U  ^L   ?  ^L
'  ?   ?      ?      ?      ?      ?      ?      ?      ?      |     ?
      ?      ?      ?      ?      ?  $   Y  h  E  t
I                     ?      ?                      ?      ?
   ?      ?      I              ?      ?      ?              ?
a     i      i      i      ?  ?   ?      ?
?      ?      |
i                                                      ?
|              i      i  V   n
 
@   ?      ?
2      ?      o  ^L   P?+??eC        U      ?  "
  ?  ^L           |      ?  0   '      ?  x   ?      ?  (   ?     2
               2  0   ?              ?      b  D   ?      ?
i      ?      ?                                      ?
    ?      ?      I      I                                      a
                                   ?      ?      ?
 '      ?      ?      ?      ?                            ?  ?
D                    ?
      ?      ?      ?      ?      ?      ?      yyyy
^L                  '));

jill_at_RHORC> @out
string """ missing terminating quote ("). ERROR:
ORA-01756: quoted string not properly terminated

Thanks. Received on Thu Aug 30 2007 - 10:53:29 CDT