ORA-00997: illegal use of LONG datatype (Migration Data LOB column)

SQL> CREATE GLOBAL TEMPORARY TABLE TMP_scan ON COMMIT PRESERVE ROWS as select FIRMNUMBER,CODE,PSCAN

NEDIMAGE,NFINANCIALYEAR from ldbo.CLIENTSCANNEDIMAGE@cmldlink;

CREATE GLOBAL TEMPORARY TABLE TMP_scan ON COMMIT PRESERVE ROWS as select FIRMNUMBER,CODE,PSCANNEDIMA

*

ERROR at line 1:

ORA-00997: illegal use of LONG datatype

SQL> create table CLIENTSCANNEDIMAGE as SELECT /*+ index(FIRMNUMBER,NFINANCIALYEAR,CODE) */ * from

ldbo.CLIENTSCANNEDIMAGE@cmldlink where 1=0;

create table CLIENTSCANNEDIMAGE as SELECT /*+ index(FIRMNUMBER,NFINANCIALYEAR,CODE) */ * from ldbo.

*

ERROR at line 1:

ORA-00997: illegal use of LONG datatype

SQL>

SQL> INSERT INTO CLIENTSCANNEDIMAGE SELECT /*+ index(FIRMNUMBER,NFINANCIALYEAR,CODE) */ FIRMNUMBER,

CODE,PSCANNEDIMAGE,NFINANCIALYEAR from ldbo.CLIENTSCANNEDIMAGE@cmldlink;

INSERT INTO CLIENTSCANNEDIMAGE SELECT /*+ index(FIRMNUMBER,NFINANCIALYEAR,CODE) */ FIRMNUMBER,CODE,

ERROR at line 1:

ORA-00997: illegal use of LONG datatype

SQL> DECLARE

2 CURSOR c IS

3 select FIRMNUMBER, CODE, PSCANNEDIMAGE, NFINANCIALYEAR from ldbo.CLIENTSCANNEDIMAGE@cmldlink;

4 rc c%ROWTYPE;

5 BEGIN

6 OPEN c;

7 LOOP

8 FETCH c INTO rc;

9 EXIT WHEN c%NOTFOUND;

10 INSERT INTO CLIENTSCANNEDIMAGE

11 ( FIRMNUMBER, CODE, PSCANNEDIMAGE, NFINANCIALYEAR )

12 VALUES ( rc.FIRMNUMBER, rc.CODE, rc.PSCANNEDIMAGE, rc.NFINANCIALYEAR );

13 END LOOP;

14 COMMIT;

15 END;

16 /

DECLARE

*

ERROR at line 1:

ORA-01406: fetched column value was truncated

---------------------Solution----------------------

at sqlplus>

use copy command remember enter after hypen (-) in sqlplus

copy from ldbo/ldbo@nbs1112srv -

create CLIENTSCANNEDIMAGE2 using select * from CLIENTSCANNEDIMAGE;

--------------------or------------------

CREATE TABLE "DPCDSL"."CLIENTSCANNEDIMAGE1"

(

"FIRMNUMBER" CHAR(10 BYTE) NOT NULL ENABLE,

"CODE" CHAR(10 BYTE) NOT NULL ENABLE,

"PSCANNEDIMAGE" LONG RAW,

"NFINANCIALYEAR" NUMBER(4,0) NOT NULL ENABLE

)

PCTFREE 15 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE

(

INITIAL 10485760 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT

)

TABLESPACE "USR" ;

copy from ldbo/ldbo@nbs1112srv -

insert CLIENTSCANNEDIMAGE1 using select * from CLIENTSCANNEDIMAGE;

----------