Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ODBC 10.2 test connection success but MSAccess/Office throws ORA-12154

mikesiczJan 17 2013 — edited Jun 25 2013
I've run into a difficult-to-solve problem. I'm troubleshooting an MSAccess to Oracle connection via Oracle ODBC driver issue.

MSAccess was giving ORA "long" datatype error (can't remember error number) when using 11.2.1 driver against a years-old, tried-n-true app that was previously well behaved (only diff is use of that app with newer Oracle ODBC driver). Web search revealed problems with 11.2 ODBC driver and MSOffice tools so decided to swith to older ODBC driver. Had 10.2 ODBC driver also installed so switched to that. Now get ORA-12154 every time I try to connect with MSAccess via Oracle ODBC. Using the ODBC Admin (32 bit) allows SUCCESSFUL connection as does tnsping & other tools that use Oracle Net. Only MSAccess (or other MSOffice tools) do not.

I've verified no other tnsnames.ora files or sqlnet.ora files available and have even gone as far as completely removing all things Oracle from the PC and reinstalling only the 10.2 client (custom mode; installed only the Oracle NET and ODBC 10.2 driver). I've turned on tracing to ADMIN level and read the trace files. trace for MSAccess call vs trace for ODBC administrator call yields nearly identical trace files till "niotns" step where the ODBC admin proceeds to connect and the MSAccess call just gives a few other coments then stops. NO apparent error messages. tnsnames IS found so don't understand why giving ORA-12154.

All tools connect (tnsping works, SQL*Plus connects, ODBC admin test sucessful, TOAD connects) EXCEPT MSAccess. Problem is I have many, many MSAccess legacy apps/tools and cannot go without the fully working ODBC connection between MSAccess and Oracle.

Help... desparate for new ideas to try since I've spent many hours searching and tried all known troubleshooting steps.

Any ideas appreciated.

Thanks,

Mike

Comments

fac586
V Rickert wrote:
What would cause Oracle to insert duplicate rows into a table? Could a join of two tables in the initial query assigned to an application page cause ORacle to insert an extra row into a table when an update to data value occurs? I have no insert triggers and no foreign keys assigned to the table. I am not sure what would cause Oracle to assume that an insert of a row must occur. I want to prevent that insert.
Is there an APEX dimension to this? If so, tell us the full APEX and DB versions, and provide full details of what the APEX app is doing (a debug trace of page accept processing is the obvious place to start).

The most likely explanations are:

1. There is no duplicate in the table but there is a join problem in the query reporting on it, resulting in the appearance of a duplicate in the reults. Have you confirmed that the duplicate is really a physical row in the table?

2. There is an APEX page/application process containing an insert on the table that is unexpectedly running on page accept due to it having no condition or an incorrect condition. This will be visible in the Debug trace.
InoL
What would cause Oracle to insert duplicate rows into a table?
Nothing. It's is the programmer that created bad code (if duplicate rows are not supposed to be inserted). If there is no table trigger, than some other code must be responsible for the insert.

BTW. Is this related to your other post:
2533049
Here you did actually create a trigger B-)

Edited by: InoL on May 2, 2013 12:08 PM
V Rickert
I'm thinking it is the use of a unique INDEX on a table that is causing APEX to insert extra rows. The INDEX is not the primary key. Example: A row in an INVOICE table contains invoice information such as invoice number, vendor, date, purchase order number, Work Order number, etc. A sequence number is the primary key as all other data items can be updated by the user. To prevent the same invoice number from being duplicated, a unique composite key/index was created on Invoice, Vendor, and Work Order. If the user, however, needs to change the work order, then APEX creates another row instead of updating the current row. I think the index is the issue and I am investigating that theory.
InoL
If the user, however, needs to change the work order, then APEX creates another row instead of updating the current row.
What kind of page did you create in Apex? Is it a form with report based on the INVOICE table?
How did you specify the Primary Key Type? Managed by database (ROWID) or Primary key columns? In the 2nd case, check if you specified the correct PK column and PK generation method.

Just another question:
a unique composite key/index was created on Invoice, Vendor, and Work Order
Is Invoice the sequence generated invoice number (the PK)? In that case creating a unique index on these 3 columns is not very useful, since Invoice is already always unique. Not that this would explain your duplicate record, though.

Edited by: InoL on May 3, 2013 12:52 PM
V Rickert
The APEX report is a form with report based upon a sql query of the Invoice Table. It contains Header HTML page items and then a detail section where you 'Add a Row' for detail (that part works fine.) The primary key specified is not the APEX-defined ROWID, but a Sequence(Invoice.Seq_id) with associated trigger that I have specified. (So technically the row is not a duplicate as the Invoice.Seq_id is unique. It is the invoice_number that is repeated.) I think I need a unique index on Invoice_number to prevent the 'duplicates'? I do not have that specified.
InoL
Sorry, I'm a bit confused now.
where you 'Add a Row'
So, you are adding a row? Before you had a problem with updating a row.
I think I need a unique index on Invoice_number to prevent the 'duplicates'?
You could do that, but it will just give you an error message. However, it could point to the process that is trying to insert a record when you are only updating a record.

One more thing:
a form with report based upon a sql query of the Invoice Table
So, you are not using the INVOICE table directly as a source? What is the query you are using? It's not a view by any chance, with INSTEAD OF triggers?

Edited by: InoL on May 3, 2013 3:22 PM
V Rickert
I'm sorry. I am just not explaining myself very well! :(

Here is the scenario.
The invoice table contains a sequence number as the primary key. The sequence number is unique and never duplicated. SO technically, APEX/ORACLE is not erroneously entering rows into the table. My problem is: We cannot use 'invoice_number' as the primary key because it is a vendor-issued invoice number and you never know if Vendor A will use the same invoice number as Vendor B. I am trying to prevent an exact replica of Invoice Number/Vendor A showing up on two different rows: Example Seq_no = 1, invoice=ABC, Vendor=A; seq_no=2, invoice=ABC,Vendor=A. Technically, this is not a duplicate. But logically in our system, it is. Therefore, I am hoping that an index on Invoice/Vendor will prevent the invoice/vendor combination from being replicated at all. So, question: will a unique index on invoice_number/vendor prevent 'duplicates'?

Edited by: V Rickert on May 3, 2013 1:29 PM
InoL
will a unique index on invoice_number/vendor prevent 'duplicates'?
Yes it will.
But is is something completely different than your original post, where, from my understanding, you said that a row was inserted when you updated a record.
V Rickert
The effect to the user is - it is! The user does not see the sequnce_id, only the fact that the invoice number and vendor now occur twice! So for them, the users, duplicates exist. Sorry for the confusion!

Edited by: V Rickert on May 3, 2013 2:04 PM
1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 23 2013
Added on Jan 17 2013
#odbc, #windows-and-net
3 comments
6,276 views