error ORA-00960

[billbarl]

I run a query in Access to an Oracle 7.3 data base and get ORA-00960. The person in the next cubicle runs it on his PC, and does not get the error. We turn on Tracing, and my sql.log file contains:
fff9c8bb:fff9ce1b EXIT SQLExecDirect with return code -1 (SQL_ERROR)
HSTMT 0x02d113e8
UCHAR * 0x027018c0 [ -3] "SELECT "NOUN" ,"EMPAC"."EIM_NOUN_QUALIFIERS"."NOUN","EMPAC"."EIM_NOUN_QUALIFIERS"."QUAL" FROM "EMPAC"."EIM_NOUN_QUALIFIERS" ORDER BY "NOUN" \ 0"
SDWORD -3

DIAG [37000] [Oracle][ODBC Oracle Driver][Oracle OCI]ORA-00960: ambiguous column naming in select list. (960)

The sql.log file for the other person contains:
fff7799b:fff77fb3 EXIT SQLExecDirect with return code 0 (SQL_SUCCESS)
HSTMT 0x039713e8
UCHAR * 0x038718c0 [ -3] "SELECT "NOUN" ,"EMPAC"."EIM_NOUN_QUALIFIERS"."NOUN","EMPAC"."EIM_NOUN_QUALIFIERS"."QUAL" FROM "EMPAC"."EIM_NOUN_QUALIFIERS" ORDER BY "EMPAC"."EIM_NOUN_QUALIFIERS"."NOUN" \ 0"
SDWORD -3

The difference is his code generates the ORDER BY statement with a fully qualified name, whereas on my PC the name is not fully qualified.

What would cause Access to generate different code on my PC as opposed to his PC? We have run this same query on 2 other PC's, and it works on one while the other gets the same error that I do. I am on Access 97 R2, the other 3 PC's are all on Access 97 R1.

 

[Guest_imported]

I remember a good friend telling me about Oracle SQL and it construction. It's a few years ago now, however these are some possible tips on helping identify the problem,

a) Are you both using the same version of the application i.e Both Oracle and Access application itself?

b) Oracle can use something called "aliases". This saves the lazy DBA writing repetitive SQL statements. I cannot remember whether the aliases are assigned to "roles" (i.e like departments within a business) and "users", however it would be useful to get your local DBA to investigate the permissions between your colleagues login and yours on the Oracle end.

You could possibly narrow the confusion factor by logging in on eachothers machine. If your logon works on his machine, I would say it's something to do with the setup of the machines (or installed software/drivers) itself. If it doesn't, it's the configuration of your user account within the database system.

 

[billbarl]

There are 4 people that have tried to run this single query from the same Access data base; 3 of them are on Access 97 R1, one is on R2. They all log into the same Oracle data base using the identical ID and password. (We are all developers / system administrators).

The SQL view in Access looks identical on all 4 PC's. But on the 2 that work, the sql.log file shows an Ordered By statement that does not appear in the sql.log file for the 2 that don't work.

So I think you're correct about some problem with the software or drivers, but we don't have a clue as to what might be missing or erroneous.

 

[billbarl]

I fixed the problem by downloading and installing Jet35SP3. Now my query works.