asked on

better solution than utl_raw.cast_to_varchar2

Is there a better way to convert a blob to varchar2.

The problem with the method utl_war.cast_to_varchar2 is that my blob column is trimmed to 1956 characters (probably is 2000 but has some special characters in it). The blob has for example 5546 length.

haimavi

try use it like that:
utl_raw.cast_to_varchar2( dbms_lob.substr( BLOB_LOCATOR, 32000, 1 ) );

the biggest varchar2 variable is 32k so you will use dbms_lob.substr to get 32k chunks of it
at a time.

DanBenedek

ASKER

It doesn't work. It gives me

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

haimavi

sorry,
change 32000 to the limit of your varchar

utl_raw.cast_to_varchar2( dbms_lob.substr( BLOB_LOCATOR, 2000,1);
or
utl_raw.cast_to_varchar2( dbms_lob.substr( BLOB_LOCATOR, 4000,1);

DanBenedek

ASKER

But my blob has 5546. What will happen with the rest of the blob?

SOLUTION

haimavi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DanBenedek

ASKER

but i need it to do in a select so there is no way i can do the select twice

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DanBenedek

ASKER

but i need it to be VARCHAR2 in select because it will be taken from the database and returned as a string.

slightwv (䄆 Netminder)

Then you are limited to 4000 characters.

What specifically limits you to VARCHAR2 over a CLOB? With every new release of Oracle CLOBs are treated more and more like VARCHAR2. I believe eventually you really won't be able to tell the difference.

DanBenedek

ASKER

I have created a function that returns a 4000 characters string using something like:
utl_raw.cast_to_varchar2( dbms_lob.substr( BLOB_LOCATOR, 4000,1);
utl_raw.cast_to_varchar2( dbms_lob.substr( BLOB_LOCATOR, 2000,4001);