convert db2 varchar2(8000) to oracle blob

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 Received on Tue Apr 26 2005 - 12:34:36 CDT