Oracle PL/SQL, Database Link, and Heterogeneous Services Fails: ORA-02070: database MSAccessODBC64bit does not support TO_NUMBER in this context

Question

I am using Oracle's Heterogeneous Services (HS). I have a database link to a Microsoft Access table called employee. From an Oracle connection using SQL*Plus, I can describe the table's columns and datatypes (desc employee). I can also select from the remote table and receive the records.

However, when using an anonymous block, I receive the Oracle error:

ORA-02070: database MSAccessODBC64 does not support TO_NUMBER in this context

MSAccessODBC64 is my configured ODBC DSN in Windows 7.

The code:

declare
   my_record_position number := 60109;
   my_record employee@MSAccessODBC64bit%rowtype;
begin
   select
      *
   into
      my_record
   from
      eemployee@MSAccessODBC64bit ---->> When I remove the database link, it works
   where
      staff_number = my_record_position ---->> when I remove the where clause, it works
   order by
      staff_number asc;
   dbms_output.put_line(my_record.staff_number);
end;
/

The error:

   declare
      *
   ERROR at line 1:
   ORA-02070: database MSAccessODBC64 does not support TO_NUMBER in this context
   ORA-06512: at line 5

Is there some sort of issue or option required to make HS work? What am I overlooking?

When I use a substitution variable, it works and displays the employee id.

define emp = 60109;
declare
   my_record_position number := 60109;
   my_record employee@MSAccessODBC64bit%rowtype;
begin
   select
      *
   into
      my_record
   from
      employee@MSAccessODBC64bit
   where
      staff_number = &emp
   order by
      staff_number asc;
   dbms_output.put_line('emp=' || my_record.staff_number);
end;
/
emp=60109

PL/SQL procedure successfully completed.

===========initMSAccessODBC64bit.ora for ODBC==============

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

#
# HS init parameters
#
HS_DESCRIBE_CACHE_HWM   = 4000
HS_FDS_CONNECT_INFO     = MSAccessODBC64bit
HS_FDS_TRACE_LEVEL      = DEBUG
HS_OPEN_CURSORS         = 50
HS_RPC_FETCH_REBLOCKING = ON
HS_RPC_FETCH_SIZE       = 10000
HS_FDS_FETCH_ROWS       = 100
HS_TRANSACTION_MODEL    = READ_ONLY

#This was supposed to fix ORA-02070: database MSAccessODBC64bit does not
#support TO_number in this context
#This didn't work
#EnableWCharSupport      = 0 

Answer 1

Solved! The HS and ODBC Oracle services are limited in scope and usage in terms of cursor sql statements that can be performed. The workaround is to use the virtual DBMS_HS_PASSTHROUGH package. This will allow you to submit queries to the remote database using pl/sql variable and bind variables. The example below is a replacement for the above code.

Example:

declare
   v_cursor           BINARY_INTEGER;
   my_record_position number := 1234;
   my_record          employees@MSAccessODBC64bit%rowtype;
   my_empid           employees.empid@MSAccessODBC64bit%type;
begin
   v_cursor := DBMS_HS_PASSTHROUGH.open_cursor@MSAccessODBC64bit;
   DBMS_HS_PASSTHROUGH.parse@MSAccessODBC64bit
      (
      v_cursor,
      'select * from employee where empid = ' || my_record_position 
      || 'order by empid asc'
      );
   WHILE DBMS_HS_PASSTHROUGH.fetch_row@MSAccessODBC64bit(v_cursor) > 0
   LOOP
      DBMS_HS_PASSTHROUGH.get_value@MSAccessODBC64bit(v_cursor, 1,my_record.empid);
      dbms_output.put_line('empid=' || my_record.empid);
   end loop;
end;
/

Answer 2

The error indicates that this comparison --

staff_number = my_record_position

-- is comparing disparate data types, which leads to an automatic attempt by the querying DBMS to convert one or both to data types which may be compared. For instance, you cannot directly compare a CHAR to a NUMBER; one must be CAST to the other -- which the TO_NUMBER suggests is happening.

TO_NUMBER is an Oracle-specific function, which Access doesn't support. You can probably get around this by adding a couple of CASTs to your query, e.g. --

CAST (staff_number AS INT) = CAST (my_record_position AS INT)

This adds a bit of unnecessary overhead if either is already an INT, but that may not matter for your use.