Forum Stats

  • 3,740,436 Users
  • 2,248,255 Discussions


Oracle : How to truncate a section of the text(more than 4000 character) in BLOB by searching start

User_QH2BL Member Posts: 3 Blue Ribbon

I have start and end string , i want to first truncate the paragraph from start to end string (which is greater than 4000 cHARACTER). I used below but that works only with the number position but does not work with the string search. I know the error is because the string coming out is greater than 2000. If i restrict to 2000 then it works but my truncated string is around 19000 character length.

utl_raw.cast_to_varchar2(dbms_lob.substr(raw_data, 4005, 1))

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



  • evgenyg
    evgenyg Member Posts: 332 Bronze Badge

    varchar2 maximum size is 4000 bytes, not characters, therefore the amount of characters that can fit into varchar2 is depends on yours character set .

    So one of the possibilities, probably, is to loop over your string (which is CLOB isn't?) each time take out the equivalent of 4000 bytes.


  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,499 Black Diamond

    BLOB is just a sequence of bytes while character is one or more bytes depending of character set. So by using dbms_lob.substr(raw_data,4005,1) you are possibly breaking a character in parts and therefore risking runing into ORA-29275: partial multibyte character. Also, does blob represent characters in same character set as database character set? If not, then character set conversion is needed.You need to find out what is max bytes per character for your database character set. Then TRUNC(4000 / max-bytes-per-character) will be VARCHAR2 max size in characters you can use to guarantee any string will fit. Then convert BLOB to CLOB first:


    Then use:

    DBMS_LOB.SUBSTR(clob_data,TRUNC(4000 / max-bytes-per-character),1)


Sign In or Register to comment.