Oracle ODBC On Windows95 (Don't ask) :)

ORACLE ODBC BRAIN DUMP from stevegardiner_at_csi.com

The following are random notes for getting ODBC access to Oracle databases. These notes are for Win2000 and MS/Access and MS/Excel. They should not be much different for Win95.

INSTALL ORACLE CLIENT
I used cd #a840505-01. This has ODBC Driver 8.1.6.0.0. You can get a later one from Metalink. I have 8.1.66.
The biggest problem is if you have multiple oracle products installed (with different oracle releases) on one PC. Some Oracle product can share Oracle Homes on the PC registry, some can't. See Metalink notes. 70215.1, 66464.1, 65995.1. Oracle PC client software that is before release 8.0.4 (uses orainst installer) usually can't share PC registration entries and must be installed in one Oracle Home. Oracle PC client software that is after release 8.0.4 (Oracle Universal Installer java based) is pretty good about multiple Oracle products. Using Oracle Universal installer choose Custom Install, Net8 client, Oracle Protocol Support, Sqlplus, Oracle ODBC Driver. I like to put this in it's own Oracle Home

WINDOWS REGISTRATION ENTRIES
If there are multiple Oracle Homes, how does the Windows app (Access or Excel, sqlplus, etc) know which to use? For Software after 8.0.4, it looks for the file oracle.key in it path. It's in the Oracle Home /bin directory. This file points to the correct windows registration entries. By changing the path, you change which windows registration entries. If you install pre 8.0.4 software, install them first.
Internet Explorer 5.5 must be installed prior - I don't know why!

TNSNAMES.ORA
After I install the Oracle client, I like to add a registration entry 'TNS_ADMIN' that points to the lan directory that has our TNSNAMES.ORA file
(like P:\APPS\ORAWIN95\NETWORK\ADMIN)

WINDOWS ODBC SETUP
Adding ODBC DSNs are a little different for win2000 versus win95 Win2000
start, settings, control panel, administrative tools, data sources (odbc) Win95
start, settings, control panel, odbc data sources (32 bit)

SETTING UP THE ODBC CONNECTION
The Data Source Name must match a entry in the ODBC application (MS/Access or EXCEL)
TNS Service name must match an entry in a TNSNAMES.ORA file. Use the highest level Oracle ODBC driver listed. I think the Microsoft supplied ODBC driver is based on Oracle 6, so we never use it.

ORACLE ISSUES
The odbc user will have the same privileges as a sqlplus user! We have a db profile that sets all roles to select only. We don't want ODBC users update the database.
Sometime an ODBC user is prompted for a userid and password. This must match the oracle database user name and password. You can test this test this with sqlplus at a dos command prompt
When the ODBC application does not prompt for a password. An Oracle User OSP$USERNAME must match the network id on user's PC. The ops$username database account must have the correct database privileges to view the tables.
The first time a ODBC user signs into the database, all table name are displayed to the user. This may take minutes to log in. There is a limit of 4000 table names displayed. Sometimes because of some other error, only one table is displayed. There is a suggested solution in METALINK Note 124117.1. This involves creating database special ALL_OBJECTS and ALL_SYNONYMS view for that user. I would not want to do this for every individual ODBC user but we could create a generic ODBC oracle database user so we could control what users see by this technique. I don't like the idea of a ODBC user seeing the names of every table even though they might not have select access on it.

PATH ISSUES
The biggest problem is getting the windows path variable correct. I don't want to set a path in an autoexec.bat file or Win2000 system env variable, so I execute Access (or whatever) this with a shortcut pointing to a .bat file:
set path=C:\oracle8iClient\8.1.6\bin;%PATH%  "C:\Program Files\MICROSOFT OFFICE\OFFICE\MSACCESS.EXE"

You will also need to do this for the ODBC Administrator Setup Panel set path=C:\oracle8iClient\8.1.6\bin;%PATH% C:\WINNT\SYSTEM32\ODBCAD32.exe

The Oracle utility 'Oracle Home Selector' sets the path too. See Metalink Note 66464.1.

Sqlplus and tnsping at a DOS prompt are very good at debugging connection problems.

Other Notes from Metalink or OTN
Note 11841.1, 1029911.6
ODBC 8.1.6.6.6 Read Me
Using the Oracle ODBC Drivers with Third Party Products by Rick Schultz, Oracle Corp

Hope this helps
Steve Gardiner
stevegardiner_at_csi.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gardiner, Steve
  INET: gardiner_at_Mail.Donaldson.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).