Re: Inserting CLOB into LONG column

On Wed, 24 Oct 2007 17:47:15 -0000, "jimmy.brock" <jimmybrock_at_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