| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: manipulating blobs in sqlplus
JillS wrote:
> 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 '));
Prior to 11g, you could not see the BLOB values in SQL*Plus...which as you say, does make some sense. If the BLOB is a .wav file, how does a .wav file "look" in a text-based utility? I have read, but haven't confirmed it yet, that 11g does let you see the BLOB values in SQL*Plus.
Anyway...when I had to move BLOBs from SQL Server to Oracle, I used a pass-through program. The first thing I did was to move all of the table's columns, except for the BLOB column(s). Then, I used DTS to update the Oracle table with the BLOB column value read from SQL Server, using the Primary Key of the table to determine the row to update.
You can also write code using something like Perl DBI to interface with the two databases. Read one row's BLOB value and PK column from the source table. Update the destination table with the BLOB value. Repeat for each row.
HTH,
Brian
-- =================================================================== Brian Peasland dba_at_nospam.peasland.net http://www.peasland.net Remove the "nospam." from the email address to email me. "I can give it to you cheap, quick, and good. Now pick two out of the three" - Unknown -- Posted via a free Usenet account from http://www.teranews.comReceived on Thu Aug 30 2007 - 12:14:01 CDT
 |
 |