2

After upgrading from Oracle 11/12 to 18/19 I get this error: ORA-01036: illegal variable name/number.

The error occurred in a query like this:

SELECT * FROM (SELECT * FROM TABLE) MY_TABLE WHERE ROWNUM <= :P_ROWNUM

(Subquery + binding parameters)

The identical query works properly with the Oracle 11.2.0.4 or 12.1.0.2 client. It fails with the Oracle Client 18c or 19c.

PS: The Oracle Server is version 18c 64x for Windows.

I use Delphi 10.1.2 with ADO components (dbGO). I also tried with Delphi 13.3.3 but the behavior is the same.

It seems to be a problem in the Oracle OLE DB provider (ORAOLEDB).

If I don't use ADO but DevArt Unidac all worked as expected.

Someone can help me?

Max

Massimiliano Fabris
  • 21
  • 3
  • did you see the answer here: https://stackoverflow.com/questions/21375288/ora-01036-illegal-variable-name-number-when-running-query-through-c-sharp – hotfix Dec 03 '19 at 18:41

1 Answers1

0

Your query is fine. We ran into a similar issue when migrating from 12.1 to 19. In our case, we have a custom OLE DB provider that interfaces with OraOLEDB (and others) using the Microsoft OLE DB Provider Templates (ATL). When attempting to upgrade from 12.1.x to 19c we started seeing the strange and confusing "ORA-01036: illegal variable name/number" error for parameterized SELECT queries. The queries would succeed the first time they were executed but subsequent executions would fail when all we did was change the parameter value (the queries were executed sequentially). I went on a wild goose chase trying to diagnose the issue and finally decided it had to be an Oracle caching bug of some kind. Yesterday, I experimented with the cache-related connection string attributes and found that adding the MetaDataCacheSize attribute and setting its value to 0 (zero) resolved the issue for us. None of the current Oracle patches appear to address this issue, at least none of those that mention the ORA-01036 error.

mcessna
  • 11
  • 2
  • The issue has never been solved, since v12.2 and it still exists in v19. We started migration from OraOleDB to the Unidac components and this was the "best" workaround we found. With OraOleDB, the error doesn't occur if the query has the LockType set to adLockReadOnly. Unfortunately, Delphi components set as default the optimistic lock even though the query will never be used for writing. – Massimiliano Fabris Apr 27 '22 at 08:07