Chet Justice wrote:
> Oracle Database 10g Release 10.1.0.2.0 - Production
> Operating System: Windows Server 2003
>
> table definition on the remote db (db2: Database server = DB2 OS/390
> 6.1.2)
>
> Name Null? Type
> ----------------------- -------- ----------------
> CAS_SQ_R NOT NULL NUMBER(10)
> CAS_CHRON_SQ_R NUMBER(10)
> CHRON_X VARCHAR2(8000)
>
> If I do a describe from the local db, the VARCHAR2(8000) shows up as a
> LONG.
>
> Name Null? Type
> ----------------------- -------- ----------------
> CAS_SQ_R NOT NULL NUMBER(10)
> CAS_CHRON_SQ_R NUMBER(10)
> CHRON_X LONG
>
> table created on the host db:
> CREATE TABLE t (x CLOB);
>
> INSERT INTO t
> SELECT chron_x
> FROM reppd.pfs_cas_chron_note_at_HSN
> WHERE cas_chron_sq_r = 6478152000;
>
> ERROR at line 2:
> ORA-00997: illegal use of LONG datatype
>
> So I tried TO_LOB() but according to Note: 153326.1 that is not
> possible across a db link:
>
> INSERT INTO t
> SELECT TO_LOB(chron_x)
> FROM reppd.pfs_cas_chron_note_at_HSN
> WHERE cas_chron_sq_r = 6478152000;
>
> ERROR at line 2:
> ORA-00997: illegal use of LONG datatype
>
> If I try to use LONG as the datatype on the local DB, same problem.
>
>>From Note: 119489.1 I tried this:
>
> COPY FROM testing/testing_at_testing TO testing/testing_at_testing -
> append t -
> using SELECT chron_x FROM reppd.pfs_cas_chron_note_at_hsn WHERE
> cas_chron_sq_r = 6478152000;
>
> CPY-0012: Datatype cannot be copied
>
> I can write a simple Java app that would simply move the data over, but
> I would much rather do it in Oracle.
>
> I have full control over the local db and no control over the remote
> db.
>
> Anyone have a suggestion as how to proceed?
>
> Thanks.
>
> Chet
>
iirc, you can *update* across a dblink...
So create as select CAS_SQ_R, CAS_CHRON_SQ_R, null,
followed by an update.
I know - two way pass where a single should do, but...
no varchar2(8000) in oracle.
--
Regards,
Frank van Bortel
Received on Tue Apr 26 2005 - 13:19:03 CDT
