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@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@RHORC> @insert_word_doc.sql

Table created.

Procedure created.

PL/SQL procedure successfully completed.

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

jill@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@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@RHORC> @gen_ins.sql
jill@RHORC> set termout on
jill@RHORC> set feedback on
jill@RHORC> !more out.sql
insert into demo values (1,,utl_raw.cast_to_raw('?Ia!?a

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                                                      ?

@   ?      ?
2      ?      o  ^L   P?+??eC        U      ?  "
 ?  ^L           |      ?  0   '      ?  x   ?      ?  (   ?     2
              2  0   ?              ?      b  D   ?      ?
i      ?      ?                                      ?
   ?      ?      I      I                                      a
                                  ?      ?      ?
'      ?      ?      ?      ?                            ?  ?
D                    ?
     ?      ?      ?      ?      ?      ?      yyyy
^L                  '));

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

Thanks.