ORA-06502: PL/SQL: numeric or value error: raw variable length too long: Convert Varchar2 to Blob with utl_raw.cast_to_raw

advertisements

_____________________________________________________________________________________________________________________

Error Description:

Oracle datatype conversion from VARCHAR2 to BLOB using the function utl_raw.cast_to_raw  is getting failed with following error.

SQL>  update bl_test set TR_PIC=utl_raw.cast_to_raw(TR_NAME) WHERE LENGTH(TR_NAME)>2000;

 update bl_test set TR_PIC=utl_raw.cast_to_raw(TR_NAME) WHERE LENGTH(TR_NAME)>2000

                           *

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error: raw variable length too long

ORA-06512: at "SYS.UTL_RAW", line 224

Cause & Solution

Maximum length allowed for VARCHAR2 in SQL is 4000 bytes. But during the raw casting 1 byte of RAW will be casted internally to VARCHAR2 resulting a 2bytes VARCHAR. So the varchar field of length > 2000 will get failed during the SQL update statement. But in PL/SQL block the maximum length allowed for VARCHAR2 is 32767. So it can be achieved through the PL/SQL.

Here is the example and PL/SQL block to update a BLOB column with VARCHAR2.

My table name is bl_test with varchar2 column of length 4000 and it has 64008 rows.

SQL> desc bl_test

 Name                                      Null?    Type

 ----------------------------------------- -------- -------------

 TR_NAME                                            VARCHAR2(4000)

Adding a temporary blob column which can be renamed after datatype conversion.

SQL> alter table bl_test add tr_pic blob;

Table altered.

SQL> desc bl_test

 Name                                      Null?    Type

 ----------------------------------------- -------- --------------

 TR_NAME                                            VARCHAR2(4000)

 TR_PIC                                             BLOB

SQL> select count(*) from bl_test;

  COUNT(*)

----------

     64008

SQL> select count(*) from bl_test where length(tr_name)>2000;

  COUNT(*)

----------

       233

SQL> select count(*) from bl_test where length(tr_name)<2000;

  COUNT(*)

----------

     60521

The records which are having less than or equal to 2000 length can be converted using the normal update statement with utl_raw.cast_to_raw function.

SQL> update bl_test set TR_PIC=utl_raw.cast_to_raw(TR_NAME) WHERE LENGTH(TR_NAME)<=2000;

60521 rows updated.

The records which are having more than 2000 can be converted using following script. If you are going to try with following PLSQL block, you just need to modify the table name, column names as per your requirement.

TR_NAME=Current VARCHAR2 column

TR_PIC=temporary/new blob column

BL_TEST=table name

PL/SQL Block

set serveroutput on

declare

   cursor cur1 is

      select TR_NAME, TR_PIC from BL_TEST WHERE LENGTH(TR_NAME)>2000 FOR

UPDATE;

   tempblob    blob;

   tempname    varchar2(4000);

  begin

    open cur1;

    if cur1%isopen then

      loop

        fetch cur1 into tempname,tempblob ;

        exit when cur1%notfound;

        dbms_lob.write(tempblob,length(tempname),1,utl_raw.cast_to_raw(substr(tempname,1,length(tempname))));

      end loop;

      close cur1;

    end if;

  end;

/

PL/SQL procedure successfully completed.

_____________________________________________________________________________________________________________________

Website Stats