Re: Inserting into Oracle CLOBS

[Alan Ljungberg <Alan.Ljungberg@CNPL.ENBRIDGE.COM>]

Are you inserting row by row or are you doing a nested select insert?  The
reason I ask is we had the same problem with the LONGCHAR data type.

For example
!=============================
Begin-SQL
Insert into [$TBLOWNER1].[$TABLENAME]
(EMPLID,
EMPL_RCD,
EFFDT,
EVALUATION_TYPE,
REVIEWER_ID,
COMMENTS)
select
EMPLID,
EMPL_RCD#,
EFFDT,
EVALUATION_TYPE,
REVIEWER_ID,
COMMENTS
from [$TBLOWNER2].[$TABLENAME]
End-SQL
!=============================
Will not work,
where as the following does....
!=============================
Begin-SELECT
EMPLID
EMPL_RCD#
EFFDT
EVALUATION_TYPE
REVIEWER_ID
COMMENTS
    DO Insert1
FROM [$TBLOWNER2].[$TABLENAME]

END-SELECT

BEGIN-SQL
COMMIT
END-SQL
END-PROGRAM
!**************************
Begin-Procedure Insert1

BEGIN-SQL
  INSERT INTO [$TBLOWNER1].[$TABLENAME](EMPLID, EMPL_RCD, EFFDT,
EVALUATION_TYPE, REVIEWER_ID, COMMENTS)
  VALUES (&EMPLID, &EMPL_RCD#, &EFFDT, &EVALUATION_TYPE, &REVIEWER_ID,
&COMMENTS)
End-SQL

END-Procedure !Insert1
!==================================

-Alan.




                    Jill Hughes
                    <jihughes@HOTM        To:     SQR-USERS@list.iex.net
                    AIL.COM>              cc:
                    Sent by:              Subject:     Inserting into Oracle 
CLOBS
                    "Discussion of
                    SQR, Brio
                    Software's
                    database
                    reporting
                    language"
                    <SQR-USERS@lis
                    t.iex.net>


                    02/14/2002
                    10:01 AM
                    Please respond
                    to sqr-users






Hi Everyone,

I'm trying to do an insert of a lengthy string into an Oracle 8i CLOB
column
(character LOB). I've inserted it successfully into a LONG column before
but
when I try to insert it into a CLOB instead I get the error:

ORA-03113: end-of-file on communication channel
(SQR 5528) ORACLE OROL error -1041 in cursor 0:
   ORA-01041: internal error. hostdef extension doesn't exist

This error only occurs for strings over around 4000 bytes (even though the
CLOB storage is set to not inline).

Has anyone else had this problem?

Jill





_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com