On Aug 30, 11:14 am, Brian Peasland <d..._at_nospam.peasland.net> wrote:
> 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 theblobis 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 theblobfield
> > (unlike the 32767 byte limit for lobs in Oracle).
>
> > In Oracle,sqlplusjust won't display ablobto 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 andblobvalue 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 gotBLOB
>
> > 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.
>
> Prior to 11g, you could not see theBLOBvalues in SQL*Plus...which as
> you say, does make some sense. If theBLOBis 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 theBLOBvalues 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 theBLOBcolumn(s). Then, I used DTS to
> update the Oracle table with theBLOBcolumn 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'sBLOBvalue and PK column from the
> source table. Update the destination table with theBLOBvalue. Repeat
> for each row.
>
> HTH,
> Brian
>
> --
> ===================================================================
>
> Brian Peasland
> d...@nospam.peasland.nethttp://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 fromhttp://www.teranews.com- Hide quoted text -
>
> - Show quoted text -
Brian,
Thanks for the feedback. I am looking forward to trying this out in
11g. Your idea for migrating makes sense. The thing is that I need to
script a process to do this as part of the installation of our
software at the customer site. I think I am going to have to do
something similar with a pass-throgh program there, but was doing due
dilligence to make sure I hadn't overlooked a simpler solution.
Thanks,
Jill
Received on Fri Aug 31 2007 - 18:08:16 CDT
