1 Reply Latest reply: Jan 22, 2013 5:06 AM by Kgronau-Oracle RSS

    How can I cast a mySQL text data type in varchar2 (ORA-997)?

      Dear All,

      I am using ODBC connection to connect mysql from oracle.
      Now I want to read some records from a table ("buildings") of the mySQL database (via the SQL Developer).
      For that I have to check the value of a column "Remark" of type text in the where clause:

      select t."ID"
      from buildings@test_dblink t
      where t."Remark" = 'Value1' ; -- "Remark" is a text column in mySQL and 'Value1' is a literal

      After running the query I get the error ORA-00997 ("illegal use of LONG datatype").

      How can I cast the mySQL text data type in the oracle varchar2 or char or clob data type?

      My Oracle database is 11gR2 on Windows and MySQL is 5.0.51 on windows, gateway is .

      Thanks for any help.

      Edited by: IZETU on 23.01.2013 00:41
        • 1. Re: How I can convert the mySQL text data type in varchar2 (ORA-997)?
          Once you query the column through the gateway the column is mapped to an Oracle equivalent based on the ODBC data types being returned for the column. The mapping is described in the manual:
          Oracle® Database Gateway for ODBC
          User’s Guide
          11g Release 2 (11.2)

          => Appendix A Data Type Conversion
          So there's no way to change the mapping for a certain column unless you change the data type on the source side. So best would be if you could change the mapping to varchar, but the risk is that you might loos some data.
          So what you can do is you can define on the MySQl server side a view that splits the text column into separate chunks (substr) and instead of selecting now from the table directly use the view with the chunks for this column.

          - Klaus