Using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 -
64bit

We must use the LONG database because of an issue with the reporting
tool we are using.


jimmyb> desc p_email_corr
Name Type Nullable Default Comments
------------- ------ -------- ------- --------
EMAIL_CORR_ID NUMBER Y
BODY LONG Y


jimmyb> desc email_corr
Name Type Nullable Default Comments
----------------- ------------------ -------- ------- --------
EMAIL_CORR_ID
NUMBER
BODY CLOB Y


jimmyb> insert into p_email_corr

2 select email_corr_id, body

3 from email_corr

4 where email_corr_id = 1671568;

select email_corr_id, body

*
ERROR at line 2:

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

Isn't the maximum size for LONG 2GB ?
Does anyone know of a work around?

On Wed, 24 Oct 2007 17:47:15 -0000, "jimmy.brock"
<jimmybrock@gmail.com> wrote:

>Using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 -
>64bit
>
>We must use the LONG database because of an issue with the reporting
>tool we are using.
>
>
>jimmyb> desc p_email_corr
>Name Type Nullable Default Comments
>------------- ------ -------- ------- --------
>EMAIL_CORR_ID NUMBER Y
>BODY LONG Y
>
>
>jimmyb> desc email_corr
>Name Type Nullable Default Comments
>----------------- ------------------ -------- ------- --------
>EMAIL_CORR_ID
>NUMBER
>BODY CLOB Y
>
>
>jimmyb> insert into p_email_corr
>
> 2 select email_corr_id, body
>
> 3 from email_corr
>
> 4 where email_corr_id = 1671568;
>
>select email_corr_id, body
>
> *
>ERROR at line 2:
>
>ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion
>(actual: 4214, maximum: 4000)
>
>Isn't the maximum size for LONG 2GB ?
>Does anyone know of a work around?

You have implicit conversion to VARCHAR2, maximum length 4000 bytes.

I would recommend the following solutions and in this order
1 Trash the reporting tool and sue the vendor
2 Append the long in chunks of 4k. Sample code available at
http://asktom.oracle.com

--
Sybrand Bakker
Senior Oracle DBA

Sybrand,

I like #1 best. But I don't think management would go for it. The
reporting tool we are using is SQR, which has an issue with CLOB data
types. Ironically, SQR is owned by PeopleSoft - which means SQR is an
Oracle company.

Thanks - appending data into the LONG column seems like the best
solution. I really don't want to use Java to fix this issue.

jimmy.brock wrote:
> Sybrand,
>
> I like #1 best. But I don't think management would go for it. The
> reporting tool we are using is SQR, which has an issue with CLOB data
> types. Ironically, SQR is owned by PeopleSoft - which means SQR is an
> Oracle company.
>
> Thanks - appending data into the LONG column seems like the best
> solution. I really don't want to use Java to fix this issue.

Then expect the version of the tool you are using to be dumped in 2008.
Oracle has a low tolerance for such nonsense.
Doesn't mean it doesn't exist.
But the tolerance is still quite low.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org