Re: manipulating blobs in sqlplus
- From: JillS <jillsalo@xxxxxxxxx>
- Date: Fri, 31 Aug 2007 16:08:16 -0700
On Aug 30, 11:14 am, Brian Peasland <d...@xxxxxxxxxxxxxxxxxxx> 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@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 gotBLOB
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
?y 0 2 ?yyy / yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
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@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...@xxxxxxxxxxxxxxxxxxxxxxx://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
.
- Prev by Date: Re: _DISTRIBUTED_LOCK_TIMEOUT parameter
- Next by Date: Re: Cache Hit Ratio from system views
- Previous by thread: Re: _DISTRIBUTED_LOCK_TIMEOUT parameter
- Next by thread: Re: Cache Hit Ratio from system views
- Index(es):
Relevant Pages
|
Loading
