PB8.02. and ODBC Error ORA-00904 invalid identifier

[Neil Rothschild]

I have a very strange situation where my app was running just fine using a
somehwat old datadirect oracle odbc driver version 3.70 against Oracle 9.02.
The db was updated to oracle 9.2 and now I get the following error (this is
from the pbtrace.log):

1b00428): CANCEL: (0 MilliSeconds)
(1b00428): EXECUTE:
(1b00428): Select user_id FROM user_registry WHERE session_number =15621
(15 MilliSeconds)
(1b00428):
Error 904 (rc -1) : SQLSTATE = S0022
[MERANT][ODBC Oracle driver][Oracle]ORA-00904: "SESSION_NUMBER": invalid
identifier

(1b00428): SELECT count ( distinct network_id ) from user_registry where
application ='collect' AND network_id <> 'PEMIACSH1' (0 MilliSeconds)
(1b00428):
Error 904 (rc -1) : SQLSTATE = S0022
[MERANT][ODBC Oracle driver][Oracle]ORA-00904: "NETWORK_ID": invalid
identifier

Also got a 3rd message referencing a column name "ENVIRONMENT", same
ora-00904 error.

There a number of GOOD sql statements run via the same connection. I know I
have a connection, I know I am successfully sending most sql to the
database; it is just hung up on these particular sql statements.

This app has been running against oracle via this odbc driver for years.
ANybody have a clue why this could be happening? I have down system and
many unhappy users! Any suggestions would be appreciated.

Neil

[Ken Balakrishnan]

Oracle added a new view user_registry in version 9.2 which apparently is
taking precedence over whatever "user_registry" object your sql is trying to
access.

Ken

"Neil Rothschild" <nrothschild@nospam_nmctech.com> wrote in message
news:3fddfc1a@forums-1-dub...

[Neil Rothschild]

Ken,

Thank you!

I had my client execute the same sql using sqlplus while logged in as the
same user. How could that be?

Regards,
Neil

"Ken Balakrishnan" <kNOe...@cps92.com> wrote in message
news:3fde0227@forums-2-dub...

[Ken Balakrishnan]

Not sure. I think Oracle first tries to resolve an unqualified object name
in the current schema, then looks for a public synonym -- is it possible
that you're altering the current_schema for the user's session in your app?

Ken

"Neil Rothschild" <nrothschild@nospam_nmctech.com> wrote in message

news:3fde03e5$1@forums-1-dub...

[Neil Rothschild]

Just had a long conversation with my client. All my tables are owned by
appowner and the app logs in as appuser. Obviously a set of synonyms have
to exist; otherwise the tables and sequences are not found. He says he
exported his full prod db and imported it to a test environment, where all
the synonyms are owned by the log in user. That implies that the synonyms
were, up to now, private synonyms of the log in user, as they should be.

In the production db, all the relevent synonyms for appowner's objects are
PUBLIC. The synonym for user_registry was replaced by oracle and the
public user_registry synonym pointed to sys.user_registry, ,ost likely as a
result of Oracle's scripts. I'm not sure why Oracle's script was run after
mine, but possibly my public synonym failed on teh import due to security
issues (maybe the logged in user doing the import did not have authority to
replace Oracle's synonym).

It all makes sense except they say no one changed the synonyms.

To answer your question, I am not doing anything in my app to alter the
schema. Based on the synonym issue, I would have expected the results.
The mystery is how my client logged into sqlplus as appuser and ran my sql
successfully. I suspect he might have logged in as the table owner; at this
point the users are up and running so that is what counts, plus I know for
the future about the naming conflict.

Again, I really appreciate your help. Not having 9.2 on hand to play with,
I was totally in the dark.

Regards,
Neil

"Ken Balakrishnan" <kNOe...@cps92.com> wrote in message

news:3fde085b$1@forums-1-dub...