Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle Error ORA-00972; Identifier Too Long for Oracle tables that are linked through MS Access 97

Posted on 2004-03-30
2
Medium Priority
?
8,458 Views
Last Modified: 2007-12-19
Description of the issue
We have an issue in which end users cannot filter or sort Microsoft Access tables that are links to Oracle tables for which the schema+tablename exceeds 30 characters. When the end users attempt to sort tables with long names they get the error ORA-00972; Identifier Too Long.

The Oracle knowledgebase, metalink.oracle.com, describes why our software developers are able to filter and sort on linked tables that have names longer than 30 characters while our end users cannot. The reason is that our software developers have Visual Studio 6.0 SP5 installed on their PCs whereas our end users do not.

Things that I have already tried
What our end users were running and what had been tried prior to finding the solution that was stated above:
Windows 2000 SP 3 (Updating to SP4 did not resolve the issue.)
Oracle Client 8.0.5 or 8.1.7 (Updating to 8.1.7 did not resolve the issue.)
MDAC 2.4, 2.5, 2.6, 2.7 (Updating to MDAC 2.7 Refresh or MDAC 2.8 did not resolve the issue.)
Access 97 SR-2
Microsoft ODBC for Oracle, MSORCL32.DLL, version 2.573.9001.00 dated 2/19/2002 (Updating to Microsoft ODBC for Oracle, MSORCL32.DLL, version 2.575.1022.00 dated 2/20/2003 by installing MDAC 2.7 refresh followed by installing MDAC 2.8 did not resolve the issue.)

One way that I found to correct the issue
As a test, to see if the information from Oracle was correct, I tried installing VS 6.0 SP 5 on one of our end user’s PC that was experiencing this issue and the issue went away just as Oracle said that it would. After uninstalling VS 6.0 SP 5 from the end user’s PC the issue was still resolved.  This tells me that VS 6.0 SP5 is updating some DLLs or OCXs or something that stays behind after the uninstall of the Visual Studio application.

What I would like to know
Installing and uninstalling Visual Studio is a time consuming process and although this solution corrects the issue I would prefer not to have to go this route.  I would prefer to know what DLL or OCX or whatever is being installed that is resulting in the correction to this issue so that we can simply distribute only those DLLs, OCXs or whatever to the end users’ PCs.  Installing some sort of Service Pack, Service Release, or deploying a hotfix would be acceptable as well.

If anyone knows what the “Simple” fix is for this it would be greatly appreciated.  I even tried asking Microsoft technical support directly using my MSDN Universal Subscription and they were unable to provide a proper solution so now I challange you, as the leading technical experts, to find a more optimal solution than installing and then uninstalling Visual Studio 6.0 SP5.

Information regarding the error ORA-00972 that was obtained from the Oracle knowledge base:
•      fact: Oracle ODBC Driver
•      fact: MS Access
•      fact: MS Windows NT
•      symptom: Select against table fails
•      symptom: ORA-00972: identifier is too long
•      cause: When Access adds the schema name onto the tablename this increases the total length of the tablename. In the case of longer tablenames this can increase the length of the tablename to over 30 characters. Then when the tablename is sent over to the database, it will return with the ORA-972 error, since the table exceeded the maximum length of a tablename that the database can use. The same problem will not occur in ODBC Test or SQL*Plus when accessing the same table, since both of the applications parse the schema name and table name as two separate entries. Also, there is no way in Access 97 to change how the application links the tablenames. Note: At the time this was reproduced MS Visual Basic 5.0 and MS Visual C++ 5.0 was installed onto the machine. The error could be reproduced with ODBC Driver version 2.5.3.1.0, 2.5.3.1.6, and 8.0.5.0.0. Currently, since Visual Studio 6.0 sp 2 has been installed the error no longer occurs.
fix: The Schemaname + Tablename should be less than 30 characters long.
0
Comment
Question by:petersongt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 

Accepted Solution

by:
petersongt earned 0 total points
ID: 10748521
I have determined that msjet35.dll version 3.51.3328.0 corrects the issue. I found the following MSDN article that provides a link to SP3 for MSJET 3.5 that includes the required version of this Dll.
 
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q172/7/33.asp&NoWebContent=1
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question