Date sent: Sat, 27 Apr 2002 01:05:32 -0800 > ------------------------------ > > From: "Gardiner, Steve" <[EMAIL PROTECTED]> > Date: Fri, 26 Apr 2002 13:07:15 -0500 > Subject: Oracle ODBC On Windows95 (Don't ask) :) > > ORACLE ODBC BRAIN DUMP from [EMAIL PROTECTED] > > 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 > [EMAIL PROTECTED] > > > > > > > ------------------------------ >
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
