Re: Convert Long Raw to BLOB - unload.tar.gz (0/1)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 22 May 1999 18:42:44 GMT
Message-ID: <374ef7c8.12065479@newshost.us.oracle.com>

A copy of this was sent to "Josef Huber" <josef.huber_at_ennstal.at> (if that email address didn't require changing) On Sat, 22 May 1999 20:08:33 +0200, you wrote:

>Oracle 8.0.5
>Can i convert my long raws to blobs ?
>My long raw's are larger then 65K
>
>Any Suggestion
>
>Thanx
>Joe
>

Well, in Oracle8i, release 8.1 you can:

create table new_table as select c1, c2, c3, ... to_lob(long_raw_column) from old_table....

In 8.0, one method is to unload the long raws to files in the OS and then use dbms_lob.loadfromfile to reload them.

Lets say you have a program "unload" that works like:

$ ./unload
usage: ./unload user/pass_at_db 'select LONG_RAW, FILE_NAME ....'

(you do have such a program -- i attached it. makefiles for nt and unix included. you need to supply the c compiler. winzip 6.0 and up can decompress/untar the attached unload.tar.gz file (make sure the attachement is named that -- unload.tar.gz) )

So, if you have a table T with a primary key "X number" and a long raw column "image" you would run:

$ ./unload scott/tiger 'select image, x from t'

and that would create files named after 'X' in the current directory -- a file per row. then, you could use plsql code such as (not tested -- i didn't compil the following stuff but the concept is there):

• every column EXCEPT image create table table new_table as select X, c1, c2, c3, .... from T;

alter table new_table add image blob;

create or replace directory my_files as '/export/home/tkyte/public_html';

create or replace procedure load_image( p_x in number )

	l_blob	blob;
	l_bfile	bfile;

begin

    update new_table set image = empty_blob() where x = p_x     returning image into l_blob;

• Case Counts, i really mean 'MY_FILES' l_bfile := bfilename( 'MY_FILES', p_x ); dbms_lob.fileopen( l_bfile );

	dbms_lob.loadfromfile( l_blob, l_bfile, 
						   dbms_lob.getlength( l_bfile ) );

	dbms_lob.fileclose( l_bfile );

    commit;
end;
/

begin

   for x in ( select x from T ) loop

      load_image( x.x );
   end loop;
end;
/

to load them.

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat May 22 1999 - 13:42:44 CDT