Re: LONG RAW to BLOB conversion

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 13 Feb 1999 00:13:18 GMT
Message-ID: <36cbc1f2.19158288@192.86.155.100>

A copy of this was sent to "Dave North" <dnorth_at_baynetworks.com> (if that email address didn't require changing) On Fri, 12 Feb 1999 16:27:56 -0500, you wrote:

>Hello,
> We have a table with a long raw currently in it that we'd like to
>convert to a blob. I know the long raw is less than 32k so I'm hoping to
>just add a new column to the table, select the long into a raw and then use
>write to dump it into the blob. Funny thing is, the code doesn't
>complile..gives me a numeric error on the write. Here's what I'm trying to
>do:
>

[snip -- original code below]

>/
>
>
>I think the theory is sound but I must be missing something here. ANy
>ideas?
>
>Cheers
>
>Dave
>
>
>dnorth_at_baynetworks.com
>

You probably just altered the table and added a blob column right? so all of the blobs are NULL. NULL is not valid for a dbms_lob.write. You need to set them to an empty lob. Even if you did, you are not locking the row above and would need to do that. So, how to rewrite your code to work? See below. I've taken a table of mine with a long raw, altered it to have a blob column and then tried your way. Get the numeric or value error. rewrote it a little and it works...

SQL> desc image

 Name                            Null?    Type
 ------------------------------- -------- ----
 NAME                            NOT NULL VARCHAR2(255)
 MIME_TYPE                                VARCHAR2(30)
 IMG_SIZE                                 NUMBER
 IMAGE                                    LONG RAW

SQL> alter table image add blob_col blob; Table altered.

SQL> DECLARE

  2      lob_loc BLOB;
  3      buffer RAW(32000);
  4      amt BINARY_INTEGER := 32000;
  5      pos INTEGER := 1;

  6 BEGIN
  7 /* this works fine */

  8      select image into buffer
  9      from image
 10      where name='row_2';

 11
 11 /* get the BLOB locator */

 12      select blob_col into lob_loc
 13      from image
 14      where name='row_2';

 15
 15 /* ack..numeric error here...*/
 16 DBMS_LOB.WRITE(lob_loc, amt, pos, buffer);  17
 17 END;
 18 /
DECLARE
*
ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_LOB", line 518
ORA-06512: at line 16

the above fails since lob_loc is NULL, not an empty_blob(). It would also fail on the write since the row is not locked. It would also fail on the write unless the blob column happened to be exactly 32000 bytes (amt isn't set correctly). Below is code that gets around all of that:

SQL> DECLARE

  2      lob_loc BLOB;
  3      buffer RAW(32000);
  4      amt BINARY_INTEGER := 32000;
  5      pos INTEGER := 1;

  6 BEGIN
  7 /* this works fine */

  8      select image into buffer
  9      from image
 10      where name='row_2';

 11
 11 /* get the BLOB locator */

 12          update image
 13             set blob_col = empty_blob()
 14           where name='row_2'
 15           returning blob_col into lob_loc;
 16  
 16          amt := utl_raw.length( buffer );

 17
 17 /* ack..numeric error here...*/
 18 DBMS_LOB.WRITE(lob_loc, amt, pos, buffer);  19
 19 END;
 20 /

PL/SQL procedure successfully completed.

We update the column to an empty_blob() -- that locks the row and gets the blob initialized. We also use the returning clause to read the value of the lob locator out when we update it. We then set amt to the correct value using utl_raw.length() (if utl_raw is not installed on your system -- its used by advanced replication and gateways -- look in $oracle_home/rdbms/admin for "*raw*". you'll find a .sql and .plb. install them as SYS or INTERNAL only (no other user) using svrmgrl and you'll have utl_raw). Now we can write the lob.  

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

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Feb 12 1999 - 18:13:18 CST