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.
DanBenedekAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

haimaviCommented:
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.
0
DanBenedekAuthor Commented:
It doesn't work. It gives me

ORA-6502: PL/SQL: numeric or value error: raw variable length too long
0
haimaviCommented:
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);
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DanBenedekAuthor Commented:
But my blob has 5546. What will happen with the rest of the blob?
0
haimaviCommented:
you can run the command twice.
one for the first part and the second run configure the substr to start from the last character

utl_raw.cast_to_varchar2( dbms_lob.substr( BLOB_LOCATOR, 4000,1);
utl_raw.cast_to_varchar2( dbms_lob.substr( BLOB_LOCATOR, 2000,4001);
0
DanBenedekAuthor Commented:
but i need it to do in a select so there is no way i can do the select twice
0
slightwv (䄆 Netminder) Commented:
>>ORA-6502: PL/SQL: numeric or value error: raw variable length too long

outside PL/SQL varchar2 is limited to 4000 characters. You should be able to create a function which will allow a 32K varchar2 and return a CLOB.
 
Then call the function in your select.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DanBenedekAuthor Commented:
but i need it to be VARCHAR2  in select because it will be taken from the database and returned as a string.
0
slightwv (䄆 Netminder) Commented:
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.
0
DanBenedekAuthor Commented:
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);
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.