MySQL/Oracle XE integration: Invalid identifier problem

Von Tobias Arnhold 2.08.2010
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...