Re: win2k: odbc to oracle-db

Hi Alex

I don't know about Oracle Net Manager. I usually just edit tnsnames.ora directly.

ODBC needs the Oracle client software present to work. First step is to setup a connection in the client to the database, that you can verify with SQL*Plus (which runs on the Windows box. Telnetting to the server and running sqlplus locally does NOT verify the connection settings at the Windows box (yes I know, but you'd be surprised how often this happens)). The connect string contains username, password and alias, for example scott/tiger_at_v920 - scott is the username, tiger is the password and V920 is the alias. When you run SQL*Plus, you can specify these separately in the boxes provided, or the whole string in the above format in the username box.

The next step is to link the ODBC data source name (DSN) that you want to use to the database alias. Your application will use a specific name, but if you don't have an application, using the same DSN as alias helps keep things a bit simpler (you would just use "V920" everywhere without worrying whether you should be using a DSN or alias. Helpful if you're using all sorts of different stuff, some of which wants DSN, some alias, some your mother's maiden name and so on).

This is performed in the ODBC Administrator, which on my Win2k box is at Start - Settings - Control Panel - Administrative Tools - Data sources (ODBC). Click Add, select the Oracle ODBC driver, Finish and the Oracle ODBC setup screen will appear. The data source name is the name defined by the application, and the TNS Service Name is the alias (V920), and some possibilities should be visible if you open the combobox. Test Connection attempts an ODBC connect using the specified settings, if that says OK then you're ready to go.

Other terms:

TNS Service Name / alias / database alias - the entry in tnsnames.ora that contains the connection details for the database. Documented in Chapter 6 of the 10g Oracle Database Net Services Reference Guide.

Oracle Client
- the low level networking stuff and other clutter that helps applications connect to databases. Implements the low level SQL*Net protocol, which runs on top of TCP/IP.

OCI Client
- No such thing. OCI is the Oracle Call Interface, a C API (application programming interface) that applications use to communicate with Oracle via the Oracle Client libraries. Stray into the OCI programmers manual at your peril, make sure you have a good supply of string and breadcrumbs. Going by the name, it sounds like 10g_win32_client contains everything you need to connect applications to Oracle.

Universal Installer
- a horrendous waste of space written in Java that uses about 500MB of RAM just to unpack and install a bunch of files. Last time I wrote an installer it had peak memory usage of about 20K so $DEITY alone knows what it's doing.

Listener
- runs on the database machine and listens for incoming connections. The port it listens on is specified in tnsnames.ora. Others can explain how this all works if you really want to know, or see Chapter 7 of the Net Ref manual. The listener needs to be running on the database machine if you want to connect from another computer.

TNS
- Transparent Network Substrate, a foundation technology that is built into Oracle Net providing a single, common interface to all industry-standard protocols.

Dave.

Alexander Haid wrote:
> Hi all.
>
> I want to create an ODBC connection from my win2k PC to an Oracle DB
> (version 9.2.0.1.0) on another server (Linux, Suse) in our network. I
> realised that it is not easy...
>
> I'm lost in OCI Client? Oracle Client? Universal Insatller? Listener?
> TNS???
> Ahhhhhh!!!! Please help!!
>
> I'd like to discribe what I did untill now: I downloaded the 340MB
> package named 10g_win32_client form the Oracle Homepage. I installed
> it in the 'Administrator'-mode. Then the ODBC-Driver got installed
> too.
> Based on that ODBC-Driver I thought to arrange an DSN in win2k
> ODBC-Manager. But I need an TNS for doing that. According to the
> Oracle-Help-file I need to create an TNS with the 'Oracle Net
> Manager'. As soon as I've understood that it is the server name of the
> oracle mashine in the format: server_name.domain. But the Net Manager
> doesn't accapt that name as proper?!
>
> Can anyone tell me whether I am on the right way? Do I have to install
> an OCI Client? Or is it included in the package I installed?
>
> Thx, alex
Received on Thu Jul 15 2004 - 05:53:28 CDT