MySQL/Oracle XE integration: Invalid identifier problem

I linked a MySQL table into an OracleXE database (Short How to) and discovered a really strange behavior. When I tried an usual select about the MySQL table from my sqlplus client an error occurred: ORA-00904: "last_name": Invalid identifier

Here the whole description:

-- Error:
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
Connected as test_user
 
SQL> SELECT "last_name" FROM tbl_users@MYSQL_USER u; 
 
SELECT "last_name" FROM tbl_users@MYSQL_USER u
 
ORA-00904: "last_name": ungültiger Bezeichner
 
SQL> 

-- MySQL DDL TABLE:
DROP TABLE IF EXISTS 'my_sqldb'.'tbl_users';
CREATE TABLE  'my_sqldb'.'tbl_users' (
  'u_id' int(10) unsigned NOT NULL auto_increment,
  'last_name' varchar(50) NOT NULL,
  'forename' varchar(50) NOT NULL,
  'department_id' int(10) unsigned NOT NULL,
  PRIMARY KEY  ('u_id')
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

Solution:
Query MySQL table through Oracle APEX database using Oracle database link fails
Issue while querying MySQL tables through Oracle Generic Connectivity Using ODBC

Solution description:
...I changed the character set settings to "utf8" of v5.1.6 mysql odbc driver
through "Details -> Misc Options" as suggested and finally the problem was
resolved - the query returned the correct results...

Post Tags:

MySQL ORA- OracleXE