1 Reply Latest reply: Mar 25, 2014 6:25 AM by Billy~Verreynne RSS

    ORA 00997 Illegal use of long - when using LOV in interactive report

    kgamble

      I am running apex 4.2 on oracle xe and selecting data from a remote database (sql server) into an interactive report.  Everything works fine.

       

      When I change a report column to display text based on a named LOV-  dynamic LOV is a select from a lookup table on the remote database, I get


      ORA-20001: get_dbms_sql_cursor error ORA-00997: illegal use of LONG datatype


      The columns returned in the LOV from the the remote lookup table are varchar(50).  There are no LONG datatypes anywhere in either the remote or the local database schemas that the application is using (apart form APex internal stuff).


      I can workaround this by doing the join to the lookup table in the remote view but I am wondering why I get such an error.


      Anyone?


      Thanks


      Kathryn

        • 1. Re: ORA 00997 Illegal use of long - when using LOV in interactive report
          Billy~Verreynne

          LONG is not only an ugly and rigid data type, but also deprecated in favour of LOBs. So it should be avoided (unless you deal with the system data dictionary).

           

          Weird though that you are seeing it (somehow) from a remote table.

           

          Two possible things to look at.

           

          I assume Oracle's Heterogeneous ODBC Agent is used for SQL-Server connectivity. The DSN used to SQL-Server can be configured ito how the ODBC interface treats data/types/etc from the remote database. I would have a look at it to ensure that LONGs are not somehow derived by the ODBC driver (from a SQL-Server memofield column for example).

           

          Secondly, explicit data conversions can be used on SQL-Server and/or Oracle - enforcing string data types that will translate into actual varchar2 (max size of 4000 bytes) on the Oracle side.

           

          Safest would be to do this on the SQL-Server as a view - and then use that view from Oracle.

           

          Not sure what the syntax/method on SQL-Server is, but in Oracle the CAST( .. as VARCHAR2(n) ) function would be used.