Re: long raw to Blob

From: mark tomlinson <marktoml_at_gdi.net>
Date: Thu, 16 Apr 1998 16:45:19 GMT
Message-ID: <3536355c.158004768@newshost.us.oracle.com>

Bulletin - Convert LONG data into LOB data using PL/SQL.

The loadlob.sql PL/SQL program demonstrates how to convert a LONG to a LOB by using the DBMS_LOB.LOADFROMFILE method.

Steps to convert a LONG to a LOB --------------------------------

1. You can leave the long column in the old table and add a new LOB

column using the ALTER TABLE command. The ALTER TABLE command is not

able to change the type of a LONG column to a LOB column. LONG and LOB
columns are 2 distinct datatypes so it is not possible to assign a LONG column to a LOB column.
2. Write the data in the LONG or LONG RAW to a flat file. 3. Use a variety of ways to get flat file data into a LOB.

Use CREATE DIRECTORY to point to the directory where the BFILE

(flat file) was written.
b. Using either OCI8 or PL/SQL to copy the data from the server

      side flat file into the LOB:   
      1. The OCI8 command OCILobLoadFromFile or the PL/SQL command 
         DBMS_LOB.LOADFROMFILE() are the fastest ways to copy  
         from a server side operating system flat file to a lob.  
      2. The OCI8 command OCILobWrite from a server side external
procedure.  
         The flat file will be on the server side even if the program
which 
         calls the server side external procedure is run from the
client.  
         This is the second fastest way to transfer from a  
         server side operation system flat file to a lob.   
      3. The OCI8 OCILobWrite command.  
         This method is used in the bull_lob program listed below. 
         This may not be the fastest way to convert a long to a lob.  
         If the program is run on a client machine then the long data

         on the server will be written to a client machine flat file 
         and then the client flat file will be written back to the  
         server lob column. This involves 2 trips across the network 
         if it is run as a client program.

4. The user will need to do their own character set conversions for CLOBS
and NCLOBS because the flat file or BFILE will store the data as binary
or raw data.
In 8.0.3 the export/import utility is not capable of converting from LONGS TO LOBS.
The example below shows the PL/SQL version of method 3.b.1 listed above for loading a long which has been written to a flat file named /tmp/sound_clip into a LOB column. There is a separate bulletin which addresses how to convert a LONG to a LOB using the OCI8 OCILobWrite command. Steps to run the loadlob.sql plsql script

Create the file sound_clip with the following contents and copy it to the /tmp directory: sound_clip: ----------- abcdefghijklmnopqrstuvwxyz 2. Run the following sql script % sqlplus scott/tiger @loadlob loadlob.sql ----------- set echo on; connect sys/change_on_install; grant all on dbms_lob to scott; grant create any directory to scott; connect scott/tiger; drop directory some_dir_alias; create directory some_dir_alias as '/tmp'; drop table multimedia; /* CREATE THE TABLE */ create table multimedia ( id number, video_clip clob default empty_clob(), audio_clip clob default null, some_file bfile default null ) ;
/* LOAD DATA INTO THE TABLE */
/* Insert 10 rows into the table which defaults to initializing
- the video_clip to empty and the audio_clip and some_file to null. */ /* The fast way to do this is to use array inserts with oci
- (see OCIBindArrayOfStruct)
- The not so fast way is to use a loop in plsql as follows. */ declare loop_count integer; begin loop_count := 1; while loop_count <= 10 loop insert into multimedia (id) values (loop_count); loop_count := loop_count + 1; end loop; end; / /* Initialize the first audio clip to the actual value.
- Then copy this value to all rows in the table. */ declare ac clob; amount integer; a_file bfile := BFILENAME('SOME_DIR_ALIAS', 'sound_clip'); begin update multimedia set audio_clip = empty_clob() where id = 1 returning audio_clip into ac;
  /* Open the server side file that contains the audio clip, load it
  into
  - the clob and then close the file. Note, assume that the audio clip
  - is only 32,000 bytes long and that it starts at position 1 in the file. */ dbms_lob.fileopen(a_file, dbms_lob.file_readonly); amount := 26;
    /* note that the destination and source offsets default to 1 */
    dbms_lob.loadfromfile(ac, a_file, amount); dbms_lob.fileclose(a_file); commit;
    /* update all rows in the table to the audio clip we just loaded. */

update multimedia set audio_clip =

        (select audio_clip from multimedia where id = 1)  
        where audio_clip is null;  end;  /

select id, audio_clip from multimedia; 3. The output should resemble:
SQL> @loadlob SQL> set echo on; SQL> connect sys/change_on_install; Connected.

SQL> grant all on dbms_lob to scott;  Grant succeeded.  
SQL> grant create any directory to scott;  Grant succeeded.  
SQL> connect scott/tiger; Connected. SQL> drop directory

some_dir_alias;
Directory dropped. SQL> create directory some_dir_alias as '/tmp'; Directory created. SQL> drop table multimedia; Table dropped. SQL>

SQL> /* CREATE THE TABLE */ SQL> SQL> create table multimedia 2 (   3 id number, 4 video_clip clob default empty_clob(),
  5 audio_clip clob default null, 6 some_file bfile default null
  7 ) ; Table created. SQL> SQL> SQL> /* LOAD DATA INTO THE TABLE */
SQL> /* Insert 10 rows into the table which defaults to initializing DOC> * the video_clip to empty and the audio_clip and some_file to null.
DOC> */ SQL> SQL> /* The fast way to do this is to use array inserts with oci
DOC> * (see OCIBindArrayOfStruct)
DOC> * The not so fast way is to use a loop in plsql as follows. DOC> */ SQL>
SQL> declare 2 loop_count integer; 3 begin 4 loop_count := 1;

  5  	while loop_count <= 10 loop 
  6  	  insert into multimedia (id) values (loop_count); 
  7  	  loop_count := loop_count + 1;   8  	end loop;   9  end;

10 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> /* Initialize the first audio clip to the actual value. DOC> * Then copy this value to all rows in the table. DOC> */ SQL> declare
  2 ac clob; 3 amount integer;   4 a_file bfile := BFILENAME('SOME_DIR_ALIAS', 'sound_clip'); 5 begin
  6 update multimedia set audio_clip = empty_clob() where id = 1 returning
  7 audio_clip into ac; 8
  8 /* Open the server side file that contains the audio clip, load it into
  9 * the clob and then close the file. Note, assume that the audio clip
10 * is only 32,000 bytes long and that it starts at position 1 in the file.
11 */ 12 dbms_lob.fileopen(a_file, dbms_lob.file_readonly); 13 amount := 26;
14 /* note that the destination and source offsets default to 1 */

15 dbms_lob.loadfromfile(ac, a_file, amount); 16 dbms_lob.fileclose(a_file); 17 commit; 18 18 /* update all rows in the table to the audio clip we just loaded. */
19 update multimedia set audio_clip =

 20  	     (select audio_clip from multimedia where id = 1) 
 21  	     where audio_clip is null;  22  end;  23  /

PL/SQL procedure successfully completed. SQL> SQL> select id, audio_clip from multimedia;

AUDIO_CLIP

abcdefghijklmnopqrstuvwxyz

AUDIO_CLIP

abcdefghijklmnopqrstuvwxyz

AUDIO_CLIP

abcdefghijklmnopqrstuvwxyz

AUDIO_CLIP

abcdefghijklmnopqrstuvwxyz

10 rows selected. SQL> SQL> quit Received on Thu Apr 16 1998 - 11:45:19 CDT