Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> fwd: Oracle/ODBC

fwd: Oracle/ODBC

From: Eric D. Pierce <PierceED_at_csus.edu>
Date: Mon, 29 Apr 2002 16:58:22 -0800
Message-ID: <F001.00452BF1.20020429165822@fatcity.com>


Date sent: Sat, 27 Apr 2002 01:05:32 -0800
> ------------------------------
>
> From: "Gardiner, Steve" <gardiner_at_Mail.Donaldson.com>
> Date: Fri, 26 Apr 2002 13:07:15 -0500
> Subject: 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: Eric D. Pierce
  INET: PierceED_at_csus.edu

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).
Received on Mon Apr 29 2002 - 19:58:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US