Skip to Main Content
  • Questions

  • How to trim a BLOB file without using DBMS_LOB.SUBSTR

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ajay.

Asked: November 19, 2015 - 6:53 pm UTC

Last updated: November 20, 2015 - 11:46 pm UTC

Version: 12.1.0.22

Viewed 1000+ times

You Asked

Hi ,

I have converted a BLOB file to varchar using 'utl_raw.cast_to_varchar2'. As the number of characters in the BLOB file is more that 2000 i am getting the error as 'ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion'.

I tried trimming it using the 'DBMS_LOB.SUBSTR'.

Ex : utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(XYZ,1,2000))

But i wanted to know if there is any alternate way to trim the BLOB file whose length is more than 2000.

i want to try converting BLOB to varchar2 without using 'DBMS_LOB.SUBSTR' . Could you please help me in this , this will be very helpful

Thanks,
Ajay V

and Connor said...

Why is there a problem using DBMS_LOB.SUBSTR ? Thats we we HAVE a dbms_lob package - to manage lobs.

By the way, we basically messed up (in my opinion) when we implemented SUBSTR in DBMS_LOB because its like this:

FUNCTION SUBSTR RETURNS RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET                         NUMBER(38)              IN     DEFAULT


Its the AMOUNT first and THEN the offset.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Required alternate way

Ajay venkatesan, November 20, 2015 - 12:58 pm UTC

Due to some restrictions i am supposed to use the alternate method to convert the BLOB to varchar2

Kindly let me know if we have any other alternative approach


This will be very helpful
Connor McDonald
November 20, 2015 - 11:46 pm UTC

OK then, you could always use C

http://docs.oracle.com/database/121/LNPCC/pc_16lob.htm#LNPCC016

or you could ODP.Net

http://docs.oracle.com/database/121/ODPNT/OracleBlobClass.htm#ODPNT3952



But my advice - start looking around for a better employer. Any place that puts arbitrary restrictions on the technology they've paid for doesnt sound like its going anywhere positive.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here