1

In my project, I need to synchronize data from MySql to Oracle.

So, I tried to use Materialized View as below.

  • step 1. make ODBC drive of MySql
  • step 2. make DBlink to Mysql using ODBC
  • step 3. make MView using DBlink

But in step3, I got a error message as below,

  • ORA-00997: illegal use of LONG datatype

Data type of APP_DESC and VERS_DESC in Mysql table is "text", and without that column, MView was made successfully.

Is it impossible to make and MView, about "text" column in dblink?

I need a help.

thanks in advance.

Mview Script is as follows:

create materialized view mv_meta_info 
build immediate 
refresh complete start with (sysdate) next (sysdate+1/1440) as 
SELECT APP_TYPE, APP_ID, VERS, APP_NM, CATE_ID, APP_URL, APP_DESC, 
       VERS_DESC, DOWN_CNT, HITS, PRICE, APP_SIZE, RATE, STATUS, 
       NEW_YN, RELEASE_DT, REG_ID, REG_DT, UDT_ID, UDT_DT, 
       SEED_APP_LIST, SEED_CATE_LIST 
FROM meta_info@DBLINK4MYSQL;
San
  • 4,508
  • 1
  • 13
  • 19
user3358517
  • 43
  • 1
  • 4
  • version is as below. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production. – user3358517 Feb 27 '14 at 03:56
  • Posting `create materialized view...` script can help a lot to find out the problem. – San Feb 27 '14 at 04:22
  • create materialized view mv_meta_info build immediate refresh complete start with (sysdate) next (sysdate+1/1440) as SELECT APP_TYPE ,APP_ID ,VERS ,APP_NM ,CATE_ID ,APP_URL ,APP_DESC /* text column*/ ,VERS_DESC /* text column*/ ,DOWN_CNT ,HITS ,PRICE ,APP_SIZE ,RATE ,STATUS ,NEW_YN ,RELEASE_DT ,REG_ID ,REG_DT ,UDT_ID ,UDT_DT ,SEED_APP_LIST ,SEED_CATE_LIST FROM meta_info@DBLINK4MYSQL; – user3358517 Feb 27 '14 at 04:27
  • Which columns is text? – San Feb 27 '14 at 04:30
  • And I've tried "TO_LOB" function on text column. Then, I got a message like this. "ORA-22992 : cannot use LOB locators selected from remote tables" – user3358517 Feb 27 '14 at 04:31
  • APP_DESC and VERS_DESC is the text in Mysql. – user3358517 Feb 27 '14 at 04:32
  • Did you tried casting datatypes? I mean `cast(APP_DESC as varchar2(4000))` in select statement. – San Feb 27 '14 at 04:54
  • I tried cast as you said. then, I got a messag like this. "ORA-00932: inconsistent datatypes: expected NUMBER got LONG" – user3358517 Feb 27 '14 at 05:09
  • I gave up about mview and decide to use [symmetricds](http://www.symmetricds.org/). Thanks @San. – user3358517 Feb 28 '14 at 09:03

1 Answers1

0

can you please try HS_NLS_NCHAR = UCS2 in gateway .ini file.

Chandra Mohan
  • 29
  • 5