We help IT Professionals succeed at work.

Cause of ORA-22835

West100
West100 asked
on
I am trying to run a Oracle View on 11g and am running into a ORA-22835 error.  Not sure what is causing this error or how debug and resolve the issue?  Could this be somehow related to datatype formats etc??  

Thanks
Comment
Watch Question

Most Valuable Expert 2011
Top Expert 2012

Commented:
can you post your code where the error occurs?

Author

Commented:
Here is the error msg from SQLDeveloper:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 6494, maximum: 4000)
22835. 00000 -  "Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: %s, maximum: %s)"
*Cause:    An attempt was made to convert CLOB to CHAR or BLOB to RAW, where
           the LOB size was bigger than the buffer limit for CHAR and RAW
           types.
           Note that widths are reported in characters if character length
           semantics are in effect for the column, otherwise widths are
           reported in bytes.
*Action:   Do one of the following
           1. Make the LOB smaller before performing the conversion,
           for example, by using SUBSTR on CLOB
           2. Use DBMS_LOB.SUBSTR to convert CLOB to CHAR or BLOB to RAW.
Most Valuable Expert 2011
Top Expert 2012

Commented:
thanks, but I know what the error is

what is the code that is causing it?
awking00Information Technology Specialist

Commented:
>>I am trying to run a Oracle View <<
Not sure what you mean by trying to run a view (create it, query it, or what?). What steps did you take that led up to the error?

Author

Commented:
I believe this maybe the code snippet code causing the problem is :

(select ID,  
        (length(dt1) + length(descr1) + 1 + -- tab char
         nvl(length(dt2),0) +  nvl(length(descr2),0)+ 2 -- tab, cr chars
          + nvl(length(more),0)) l,
      (dt1||chr(9)||descr1||
           CASE when dt2 IS NULL then ''
            else (chr(13)||
                  dt2||chr(9)||substr(descr2,1,(4000 -(length(descr1)+nvl(length(more),0)))))
            end
            ||
            decode(more,'X','',chr(13)||more))   stuff_info
 from
  (select ID,
          max(decode(x,1,dt)) dt1,
          max(decode(x,1,substr(dscr,1,3900))) descr1,
          max(decode(x,2,dt)) dt2,
          max(decode(x,2,dscr)) descr2,
          case
            when max(decode(x,3,dt)) IS NULL
            then 'X' else '(more)'
           end AS more
   from
    (select s.id_number ID,
            row_number() over (Partition by s.id_number order by s.stuff_date desc) x,
            -- if note-text(clob) else brief_note (255 char) else description (60 char)
            decode(nvl(to_char(s.note_text),' '),' ',
                decode(s.brief_note,' ',s.description,s.brief_note),
              to_char(s.note_text)) dscr,
            trunc(s.note_date) dt
     from stuff s
     where s.stuff_type = 'FY')
   group by ID))
Most Valuable Expert 2011
Top Expert 2012

Commented:
this will fail if note_text is > 4000 bytes


to_char(s.note_text)


to convert a clob safely to a varchar2 value,  use dbms_lob.substr(s.note_text,4000,1)

Author

Commented:
In my case ...

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 6494, maximum: 4000)

How can I still get that remaining 2494 char's? Using the substr to convert a clob appears to give me 4000 char's I need to get all 6494?

Thanks
Most Valuable Expert 2011
Top Expert 2012
Commented:
if you need all of the clob contents you must leave the data as a clob

or split it into two columns


dbms_lob.substr(note_text,4000,1) first_part,   dbms_log.substr(note_text,4000,4001) second_part   -- it's okay to overselect, it'll just grab whatever is left
Most Valuable Expert 2011
Top Expert 2012

Commented:
why the C?