[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8236
  • Last Modified:

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.
0
DanBenedek
Asked:
DanBenedek
  • 5
  • 3
  • 2
2 Solutions
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now