+ Reply to Thread
Results 1 to 4 of 4

Inserting CLOB into LONG column

  1. Inserting CLOB into LONG column

    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?


  2. Re: Inserting CLOB into LONG column

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

  3. Re: Inserting CLOB into LONG column

    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.



  4. Re: Inserting CLOB into LONG column

    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

+ Reply to Thread