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  |
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 #350341 is a reply to message #350323] |
Wed, 24 September 2008 15:27  |
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 #350887 is a reply to message #350350] |
Fri, 26 September 2008 18:31 |
 |
Barbara Boehmer
Messages: 9097
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>
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jun 15 15:17:50 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
