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 data
types that can be used to develop database independent applications.
ODBC is usually used when database independence or simultaneous access to different data sources is required.
What is the difference between ODBC and Net8?
ODBC is an industry standard way to connect to SQL databases like Oracle,
SQL Server and
DB2.
Net8 or SQL*Net is an Oracle proprietary standard connection protocol
that only works with Oracle databases. The fact that it is proprietary
usually means that it is slightly faster, better integrated with Oracle
and more feature rich.
Most ODBC to Oracle implementations require the ODBC software stack to run on top of the Oracle Net8 software stack.
Are there alternative products that can be used?
Yes, look at
Oracle Objects for OLE,
JDBC and
Net8.
What do I need to get ODBC to work?
You typically need the following:
- A workstation with TCP/IP connectivity
- An Oracle ODBC driver
- Oracle's SQL*Net or Instant Client software
Where can one get ODBC drivers for Oracle and Rdb?
Oracle's ODBC drivers can be downloaded free of charge from
Oracle Technet.
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.
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.
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.
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.
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.
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!
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!!!
the performance depends very much on the query application that you use
on the client side. Much of the query tools supply more options than
plain SQL. to perform these queries first all data is transported over
the network to your client. This you should avoid. When you create
queries that fit within regular SQL the query is processed on the server
and only the result is transported over the network. This is very fast.
But you have to always be aware of the kind of SQL that is made by your
front end tool.
Note: ODBC is a programming interface, not an implementation.
Performance depends on the implementation of the interface. There are
several types of ODBC drivers for Oracle. Here's a white paper with
benchmarks comparing OCI and ODBC performance:
http://www.datadirect.com/docs/public/whitepapers/wp-odbcvsoci.pdf
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.
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.
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.
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)
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
How does one get Oracle data into MS-Excel?
There are several ways to extract data from Oracle and load it into Microsoft Excel. Here are some:
SQL*Plus
The easiest method is to spool the data from
sqlplus to a
CSV formatted file and open it in MS-Excel. Here is an example:
set echo off pagesize 0 head off feed off veri off trimspool on
spool data.csv
select COL1 || ',' || COL2 || ',' || COL3 ....
spool off
The resulting spool file (data.csv in our exampel) can now be copied to a Windows machine and opened in Excel.
OO4O
Oracle Objects for OLE (OO4O) can be used to load data into Excel. For more details, see the
OO4O FAQ.
3rd Party Tools
- SQL*XL Lite
- a freeware plug-in used to run SQL statements directly from Excel.
SQL*XL (formerly Oraxcel) requires OO4O to be installed on your PC.
- Fastreader - from wisdomforce
- FlexTracer - 3rd party ODBC tracer
- IRI FACT (Fast Extract) - from IRI, The CoSort Company, which uses OCI and parallelism to produce CSV files
Excel will also directly read HTML-formatted tables, which can easily be
generated from sqlplus. For example, consider the following SQL script:
C:\>type htmlobjects.sql
set pages 0 lin 32767 feedback off verify off heading off trims on
alter session set nls_date_format = "RRRR/MM/DD HH24:MI";
select '<table>' from dual;
select '<tr><td>' ||
object_name ||
'</td><td>' ||
object_type ||
'</td><td>' ||
created ||
'</td></tr>'
from all_objects
where rownum < 10;
select '</table>' from dual;
Running this script from sqlplus gives you a table:
SQL> spool objects.html
SQL> start htmlobjects
<table>
<tr><td>I_ICOL1</td><td>INDEX</td><td>1996/05/10 10:25</td></tr>
<tr><td>_NEXT_OBJECT</td><td>NEXT OBJECT</td><td>1996/05/10 10:25</td></tr>
<tr><td>CDEF$</td><td>TABLE</td><td>1996/05/10 10:25</td></tr>
<tr><td>TAB$</td><td>TABLE</td><td>1996/05/10 10:25</td></tr>
<tr><td>IND$</td><td>TABLE</td><td>1996/05/10 10:25</td></tr>
<tr><td>C_FILE#_BLOCK#</td><td>CLUSTER</td><td>1996/05/10 10:25</td></tr>
<tr><td>CLU$</td><td>TABLE</td><td>1996/05/10 10:25</td></tr>
<tr><td>I_COBJ#</td><td>INDEX</td><td>1996/05/10 10:25</td></tr>
<tr><td>I_USER#</td><td>INDEX</td><td>1996/05/10 10:25</td></tr>
</table>
SQL> quit
