Oracle ODBC Connectivity FAQ
$Date: 29-Apr-2000 $
$Revision: 1.43 $
$Author: Frank Naudé $
Topics
Back to Oracle FAQ Index
What is ODBC and where does it come from?
ODBC (Open Database Connectivity) provides a way for client programs
(eg Visual Basic, Excel, Access, Q+E etc) to access a
wide range of databases or data sources.
ODBC is a standardized API, developed according to the specifications of the SQL Access Group,
that allows one to connect to SQL databases.
It defines a set of function calls, error codes and datatypes that can be
used to develop database independent applications.
ODBC is usually used when database independance or simultaneous access to
different data sources is required.
Back to top of file
Are there alternative products that can be used?
Yes, look at Oracle Objects for OLE (OO4O).
Back to top of file
Where can I get ODBC drivers for Oracle7/8 and Oracle Rdb?
The Oracle ODBC drivers can be downloaded, free of charge, from the
Oracle home page.
There is also a free, open source ODBC driver available from
http://www.easysoft.org/.
If you are still using Oracle V6, you can use the ODBC driver that
comes with Microsoft Access 1.1 or an equivalent driver from whatever source.
Since ODBC is a standard, there are alternative drivers available via other
vendors. The following vendors provide ODBC drivers that can run
from Windows and Unix:
Note: If you plan to use a 16-bit application with ODBC (even if it is running
on Windows95 or Windows NT), you will need to use a 16-bit Windows 3.1
ODBC driver.
Back to top of file
What do I need to get ODBC to work?
A working Oracle ODBC stack looks like this:
+--------------------+ Visual Basic, Excel, Access,
| ODBC Application | Oracle Power Objects, etc.
+--------------------+
|
+--------------------+
| Driver Manager | This is ODBC.DLL
+--------------------+
|
+--------------------+ This is the Oracle ODBC Driver
| ODBC Driver | (SQORA7x.DLL or SQO32_7x.DLL) or
+--------------------+ driver for a different data source/ vendor
|
+--------------------+ Oracle's SQL*Net (only for remote database)
| Database Transport | or other database transport
+--------------------+
|
+--------------------+ TCP/IP or
| Network Transport | other protocol driver
+--------------------+ (optional - only for remote connections)
|
+--------------------+ Your Oracle database or
| Data Source | other data source you connect to
+--------------------+
Back to top of file
Do I need SQL*Net to connect to Oracle via ODBC?
For Oracle's ODBC drivers, YES.
Some multi-tier ODBC drivers (eg. Openlink and Visionware) do not require
SQL*Net on the client as one has to install an 'ODBC server component'
on the server. The ODBC drivers then use the underlying network protocol
(eg. TCP/IP) without using SQL*Net.
Back to top of file
How do I create a Data Source?
A data source is a logical name for a data repository or database. To define a
data source, open the ODBC manager by double clicking on the ODBC icon in the
Control Panel.
Select the "add" button, then select the ODBC Driver for the database you want
to connect to. The data source definition screen will appear. Define the data
source name and other information as it pertains to your configuration.
Back to top of file
What ODBC Conformance Level is supported by Oracle?
ODBC provides 4 conformance levels depending upon how much of the ODBC
specification is implemented in the driver. The levels are:
- CORE API
- LEVEL 1
- LEVEL 2
- LEVEL 3 - latest spec.
The Oracle7 ODBC driver supports ODBC Version 2.5 and 3.0 Level 2 only.
Oracle does not support Level 3 ODBC, but Level 1 is all that is necessary to
do standard operations. If you develop applications that will run on different
databases, you might want to limit yourself to level 1 ODBC calls.
Back to top of file
Should I give ODBC to my end-users?
It all depends... for performance reasons I would not allow end-users to access
an OLTP (On-line Transaction Processing) system via ODBC. Rather, setup a Data
Warehouse or Data Mart and let users enter their "queries from hell" against
that database.
Back to top of file
How secure is ODBC?
Any ODBC sniffer will be able to trace everything from an ODBC
perspective. This includes data, usernames, passwords etc.
However, if you are using an ODBC driver that provides encryption, you
can increase your level of security.
Since any front-end tool can effectively connect to and modify your databases,
you need to enforce security at the server level.
On the other hand, if you use TCP/IP, ODBC security should be the least of
your concerns!!!
Back to top of file
How fast is ODBC?
According to Oracle, their ODBC driver, on average, runs about 3% slower
than native Oracle access.
My experience, however, is that ODBC can be extremely slow!!!
Back to top of file
How can I test if ODBC is setup correctly?
Execute the ODBCTST.EXE program that comes with your ODBC driver to
ensure that all your connections are properly configured and that you can
connect to your data source.
Back to top of file
How can I trace ODBC calls?
The Microsoft ODBC Administrator (My Computer/ Control Panel/ ODBC) provides a simple ODBC call trace that logs ODBC
calls to a file. To use this facility, click the Options button on the initial Data
Sources form. Check the box labeled "Trace ODBC Calls" and change the default log
filename (SQL.LOG) if desired. The underlying ODBC calls the front-end
application makes to communicate with the ODBC Driver will be logged to
this file.
Specialized ODBC trace programs, such as
Microsoft's ODBC Spy (included in the ODBC SDK 2.0),
ODBC Inspector and
ODBC Agent
can be used to capture detailed ODBC call information and the
return code for each call.
Back to top of file
How do I tell which driver version I have installed?
Run the ODBC administrator from the desktop (ODBCADM.EXE or ODBCAD32.EXE):
- Click the "Drivers" button
- Choose the desired ODBC driver
- Click the "About" button
Version information will be displayed for the selected driver.
Back to top of file
What is SQLPASSTHROUGH?
Use the ODBC SQLPASSTHROUGH option when you need to pass your SQL statement
to the ODBC data source directly. No massaging or local processing is done on
the statement, it is passed to the database server AS IS.
This improves performance, but the resulting dynaset is not updatable.
Example:
db.ExecuteSQL("BEGIN procedurename(param1,param2,param3); END;", SQLPASSTHROUGH)
Back to top of file
How does one attach an Oracle table in MS-Access?
Create a linked table under the table tab in Access. Right click;
select link table. A dialog box opens, at the bottom, change the "files
of type" box to ODBC Databases. This will open the Data Source dialog box.
Select your data source, file or machine (note the type you created earlier).
You will now be prompted to login to the Oracle database.
One can also link a table programmatically. Open a new MODULE in Access,
add this code to it and RUN (F5):
Option Compare Database
Option Explicit
Function AttachTable() As Variant
On Error GoTo AttachTable_Err
Dim db As Database
Dim tdef As TableDef
Dim strConnect As String
Set db = CurrentDb()
strConnect = "ODBC;DSN=oraweb;DBQ=qit-uq-cbiw_oraweb;DATABASE="
' NOTE: DSN is your ODBC Data Source Name; DBQ is your TNSNAMES.ORA entry name
Set tdef = db.CreateTableDef("MY_ACCESS_TABLENAME")
' tdef.Attributes DB_ATTACHEDODBC
tdef.Connect = strConnect
tdef.SourceTableName = "MY_ORACLE_TABLENAME"
db.TableDefs.Append tdef
AttachTable_Exit:
Exit Function
AttachTable_Err:
MsgBox "Error: " & Str(Err) & " - " & Error$ & " occured in global module."
Resume AttachTable_Exit
End Function
Back to top of file
How does one get Oracle data into MS-Excel?
There are several ways to extract Oracle data from Microsoft Excel.
Look at these methods:
Back to top of file
Where can I get more info about ODBC in general?
Back to top of file