Re: manipulating blobs in sqlplus
- From: Brian Peasland <dba@xxxxxxxxxxxxxxxxxxx>
- Date: Thu, 30 Aug 2007 12:14:01 -0500
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@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
?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 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@xxxxxxxxxxxxxxxxxxx
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.com
.
- Prev by Date: Re: Oracle permissions issue Oracle 9.2.0.7/Win 2003
- Next by Date: Re: Oracle NULL vs '' revisited
- Previous by thread: Separate Index or Composite Indice ?
- Next by thread: 10g or 11g ?
- Index(es):
Relevant Pages
|
