Home » SQL & PL/SQL » SQL & PL/SQL » ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4907, maximum: 4000) (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit)
ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4907, maximum: 4000) [message #350305] Wed, 24 September 2008 12:29 Go to next message
Kshah
Messages: 3
Registered: September 2008
Location: Jacksonville, FL
Junior Member
Recently I came across this issue, which gives me an error as following.

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

Now I am running a very simple query against one of the View in our Database.
Query is:

Select Prj_Num,
PM_Comments
From ProjectDetails
Where Rel = ‘2008 10’

What I have discovered so far is one of the field in this view name “PM_Comments” has more than 4000 bytes of information in it, Which is not supported by tools I have available on my computer.

I have Oracle SQL Plus, SQL plus Worksheet, Access and Excel installed on my machine.

DBA related to this database are stating that the field is working fine and query is executing without error since they are using TOAD for SQL, which does have capabilities to read more than 4000 bytes.

What I have figured out so far is “PM_Comments” is a LOB and SQL plus is having trouble reading this information more than 4000 bytes in one field of information.

Because of this diagnosis, I have tried using following queries but it did not help much either. And I am looking for similar solution.

Select Prj_Num,
Substr (PM_Comments, 1, 4000)
From ProjectDetails
Where Rel = ‘2008 10’

Select Prj_Num,
DBMS_LOB.Substr(PM_Comments, 4000, 1)
From ProjectDetails
Where Rel = ‘2008 10’

But both of the above mentioned queries did not work either.
and I get the same ORA-22835 Error.

I do not need all of the information in “PM_Comments” field, I only need about first 1000 characters of it.

I need a help from someone on this issue. If you know any other query or any other solution how I can read this information, and generate/export a excel worksheet from the data.

Please see and let me know if anyone can help me in this regard, I really appreciate any feedback/comments/concerns in the same matter.
Re: ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4907, maximum: 40 [message #350315 is a reply to message #350305] Wed, 24 September 2008 13:11 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
There is no Oracle error number ORA-22835 in version 10.1.0.4.2

I can do a
select susbtr(clobfield,1,4000) from tab;
with no problem in SQL*Plus.
I have also created a view on the table and SELECTed from it with no problem.

[Updated on: Wed, 24 September 2008 13:13]

Report message to a moderator

Re: ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4907, maximum: 40 [message #350323 is a reply to message #350315] Wed, 24 September 2008 14:00 Go to previous messageGo to next message
Kshah
Messages: 3
Registered: September 2008
Location: Jacksonville, FL
Junior Member
thanks for the quick response.

I have tried both substr and DBMS_LOB.substr...but none seem to work...do you think that this is some other kind of trouble? please help.
Re: ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4907, maximum: 40 [message #350341 is a reply to message #350323] Wed, 24 September 2008 15:27 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
ok, It seems 10.2.0.1.0 has this error number
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.


You may have an NLS setting causing the problem. From the solution, it looks like you might have multibyte character in use. Try substr(1,3800) then start bumping up the number by trial and error.
Re: ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4907, maximum: 40 [message #350350 is a reply to message #350341] Wed, 24 September 2008 15:46 Go to previous messageGo to next message
Kshah
Messages: 3
Registered: September 2008
Location: Jacksonville, FL
Junior Member
I actually tried the same with DBMS_LOB.substr(Clobfield,40,1) and also with substr(clobfield,1,40) but receiving the same error. I am not sure what is the NLS setting, and not sure how to change NLS settings...

I am sorry for keep coming back to this.
Re: ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4907, maximum: 40 [message #350571 is a reply to message #350350] Thu, 25 September 2008 08:08 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
show parameter nls
from a DBA account.
Re: ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4907, maximum: 40 [message #350887 is a reply to message #350350] Fri, 26 September 2008 18:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9099
Registered: November 2002
Location: California, USA
Senior Member
In earlier versions, when you used TO_CHAR on a CLOB column with over 4000 characters, it automatically truncated the results to 4000 characters. In current versions, when you use TO_CHAR on a CLOB column with over 4000 characters, it produces the error that you are getting. Somewhere in your view syntax, it must have used TO_CHAR on a CLOB column that contains data with more than 4000 characters, so that when you try to select from the view using a current version, it produces the error. This can be fixed adding SUBSTR within the TO_CHAR in the view creation. I have provided a brief demo below.

-- test table and data:
SCOTT@orcl_11g> CREATE TABLE ProjectDetails_tab (pm_comments CLOB)
  2  /

Table created.

SCOTT@orcl_11g> INSERT INTO ProjectDetails_tab SELECT RPAD ('*', 4000, '*') FROM DUAL
  2  /

1 row created.

SCOTT@orcl_11g> UPDATE ProjectDetails_tab SET pm_comments = pm_comments || RPAD ('*', 907, '*')
  2  /

1 row updated.


-- can select from the base table without problems:
SCOTT@orcl_11g> SELECT LENGTH (pm_comments) FROM ProjectDetails_tab
  2  /

LENGTH(PM_COMMENTS)
-------------------
               4907

SCOTT@orcl_11g> SELECT SUBSTR (pm_comments, 1, 40) FROM ProjectDetails_tab
  2  /

SUBSTR(PM_COMMENTS,1,40)
--------------------------------------------------------------------------------
****************************************

SCOTT@orcl_11g> SELECT DBMS_LOB.SUBSTR (pm_comments, 40, 1) FROM ProjectDetails_tab
  2  /

DBMS_LOB.SUBSTR(PM_COMMENTS,40,1)
--------------------------------------------------------------------------------
****************************************


-- cannot select from the base table using TO_CHAR on the large CLOB column:
SCOTT@orcl_11g> SELECT TO_CHAR (pm_comments) AS pm_comments FROM ProjectDetails_tab
  2  /
SELECT TO_CHAR (pm_comments) AS pm_comments FROM ProjectDetails_tab
       *
ERROR at line 1:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual:
4907, maximum: 4000)


-- can create a view using TO_CHAR on a large CLOB column,
-- but cannot select from that view:
SCOTT@orcl_11g> CREATE OR REPLACE VIEW ProjectDetails_view AS
  2  SELECT TO_CHAR (pm_comments) AS pm_comments FROM ProjectDetails_tab
  3  /

View created.

SCOTT@orcl_11g> DESC ProjectDetails_view
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PM_COMMENTS                                        VARCHAR2(4000)

SCOTT@orcl_11g> SELECT pm_comments FROM ProjectDetails_view
  2  /
SELECT pm_comments FROM ProjectDetails_view
       *
ERROR at line 1:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual:
4907, maximum: 4000)


SCOTT@orcl_11g> SELECT SUBSTR (pm_comments, 1, 40) FROM ProjectDetails_view
  2  /
SELECT SUBSTR (pm_comments, 1, 40) FROM ProjectDetails_view
               *
ERROR at line 1:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual:
4907, maximum: 4000)


SCOTT@orcl_11g> SELECT DBMS_LOB.SUBSTR (pm_comments, 40, 1) FROM ProjectDetails_view
  2  /
SELECT DBMS_LOB.SUBSTR (pm_comments, 40, 1) FROM ProjectDetails_view
                        *
ERROR at line 1:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual:
4907, maximum: 4000)


-- if you recreate the view using SUBSTR within TO_CHAR,
-- then everything works as desired:
SCOTT@orcl_11g> CREATE OR REPLACE VIEW ProjectDetails_view AS
  2  SELECT TO_CHAR (SUBSTR (pm_comments, 1, 4000)) AS pm_comments FROM ProjectDetails_tab
  3  /

View created.

SCOTT@orcl_11g> DESC ProjectDetails_view
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PM_COMMENTS                                        VARCHAR2(4000)

SCOTT@orcl_11g> SELECT SUBSTR (pm_comments, 1, 40) FROM ProjectDetails_view
  2  /

SUBSTR(PM_COMMENTS,1,40)
----------------------------------------
****************************************

SCOTT@orcl_11g> SELECT DBMS_LOB.SUBSTR (pm_comments, 40, 1) FROM ProjectDetails_view
  2  /

DBMS_LOB.SUBSTR(PM_COMMENTS,40,1)
--------------------------------------------------------------------------------
****************************************

SCOTT@orcl_11g>

Re: ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4907, maximum: 40 [message #510373 is a reply to message #350887] Sat, 04 June 2011 07:37 Go to previous messageGo to next message
pdthedba
Messages: 3
Registered: June 2011
Junior Member
Good explanation Barbara.

Can I pls ask is there a limitation on 4000 chars or using the method above I cud even select all 4907 chars if I wanted?

Thanks.
Re: ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4907, maximum: 40 [message #510374 is a reply to message #510373] Sat, 04 June 2011 07:40 Go to previous messageGo to next message
pdthedba
Messages: 3
Registered: June 2011
Junior Member
To be precise, I meant in this :

CREATE OR REPLACE VIEW ProjectDetails_view AS SELECT TO_CHAR (SUBSTR (pm_comments, 1, 4000)) AS pm_comments FROM ProjectDetails_tab

Could you use TO_CHAR (SUBSTR (pm_comments, 1, 4907) instead pls while creating the view?

Thanks.
Re: ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4907, maximum: 40 [message #510375 is a reply to message #510374] Sat, 04 June 2011 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, SQL can return maximum 4000 bytes for a (VAR)CHAR datatype.

Regards
Michel
Re: ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4907, maximum: 40 [message #510425 is a reply to message #510375] Sun, 05 June 2011 16:39 Go to previous message
pdthedba
Messages: 3
Registered: June 2011
Junior Member
Thanks!
Previous Topic: can't see to compile the proc
Next Topic: How old are you
Goto Forum:
  


Current Time: Sat Nov 02 12:31:42 CDT 2024