Oracle Database Discussions

Oracle doesn't provide an ODBC driver that runs on a Solaris
client. You can use the Oracle ODBC driver from a Windows
client to access a Solaris database, however. Microsoft
designed ODBC as a Windows-client-centric protocol where drivers
rely on infrastructure like the ODBC Driver Manager which
Microsoft provides.

There are a variety of third parties that do provide ODBC
drivers which may run on Solaris. I'd start with Merant, but
there are probably others. I have no personal experience with
them, however.

There really isn't a programming guide for the Oracle ODBC
driver. There are a variety of books on ODBC, however, which
should show you how to use any ODBC driver. Microsoft publishes
a canonical two-volume paper reference "The ODBC Programmer's
Reference" which documents the ODBC API in pretty excruciating
detail.

Justin

thanks, it is very very helpful

Cant i use the oracle odbc driver in my client program which runs on
solaris.?

If not, is there any way by which a solaris client can be connected with
oracle?

Since you've resurrected a 5 year old thread, I should note that Oracle now provides a Linux ODBC driver.

If your client runs on a Solaris machine, there is no Oracle ODBC driver that could be used to connect to the database. There are companies (i.e. DataDirect) that do produce and sell such drivers, however, and they are generally very good.

You could also use one of the other APIs that Oracle does support on Solaris. OCI, OCCI, and JDBC would all be excellent alternatives to ODBC, which tends to be a Windows-centric API.

Justin

Visigenic Software published the ODBC for UNIX SDK and Programmer's Reference in 1994 (for ODBC 2.0). There was only one ODBC function call in UNIX that differed from the Windows version. SQLDriverConnect (pg. 257) takes an HWND argument. For UNIX, it's a widget handle and for Windows platforms, it's a window handle. Visigenic Software was acquired by Borland.

For some scripting and programming languages, JDBC is not a viable alternative even though the API has evolved beyond ODBC.

That's an excellent correction. I didn't intend to imply that ODBC was inappropriate for Unix environments. ODBC, however, never got the sort of Unix traction that it got on Windows where just about every Windows app that deals with data can use and ODBC data source. The ODBC API was also developed by Microsoft and there are various aspects of various APIs that are designed to work well with SQL Server and that tend to be a pain for non-SQL Server data sources to implement.

There are certainly situations where ODBC on Unix is quite useful (I use the Merant driver daily in the project I'm currently on). Most of the ODBC support out there, however, is focused on the Windows platform.

Justin

Hi Justin.
I'm stuck with a situation.
We are accessing to a Cobol system via relativity software (http://www.liant.com/products/relativity/) which gives access to Cobol via ODBC.
Our application is PHP based and runs on the Oracle HTTP Server form OAS 10.1.2.0.2 installed in a Red Hat 4 AS. We are having problems with the ODBC connections.
Reading this thread I see that Oracle nos provides a Linux ODBC Driver.
Can you give some more details on this? where we can find it? is just another download? Do we have to recompile PHP?
Well, thanks in advance for your help.

I don't know of a way to download the Linux ODBC driver separately, though it should be part of the 10.2 Oracle client for linux. You shouldn't need to recompile your PHP, depending on how the current code creates the connection. Assuming you're using unixODBC as your driver manager and a DSN, changing the driver shouldn't necessitate recompiling your code.

Justin

Seems Oracle Instant Client support applications to use OCI, JDBC, ODBC,
and it has a downloadable version for Solaris, so this provide a way
for Solaris applications to use ODBC APIs to connect to Oracle server.

http://www.oracle.com/technology/tech/oci/instantclient/index.html

Your application can use either the Datadirect(old name as Merant) driver manager
wilt license, or the Unix ODBC driver manager(public domain),
to connect to the ODBC driver libsqora.so.10.x provided in the Instant Client pkg.

Have you actually set this up?

I'm looking at the Instant Client page and there is a version of the Instant Client base package for Solaris. However, the ODBC supplement for the Instant Client is (or at least appears to be) a Windows-only product. That's where the comment "Additional libraries for enabling ODBC applications with Instant Client (Not all platforms)" on the page you linked to comes from.

Justin

The basic + odbc zip files provided the needed libraries for Solaris sparc zip,
but it is missing for the x86 odbc zip.

It works with Oracle 9.2.0.3, but now when we patched it to 9.2.0.5
the TNS listener return the following errors...

Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=A)(CID=(PROGRAM=ODBCtest)(HOST=n440sa41)(USER=root)))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))

VERSION INFORMATION:
TNS for Solaris: Version 10.2.0.2.0 - Production
TCP/IP NT Protocol Adapter for Solaris: Version 10.2.0.2.0 - Production
Time: 09-NOV-2006 14:20:53
Tracing not turned on.
Tns error struct:
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0

Ok, it works now, turn out to be our listener setup problem.
In 9.2.0.3 the static setup works fine, but in 9.2.0.5 we need to
config the listener registration from init.ora to register dynamically.
Our config was carried from way back on Oracle 7 era.

This confirm ODBC connection from Solaris sparc, any others had
experience from Linux ? Our application will port to SUSE Linux soon.

Hello,

Can any one explain How to connect the Oracle Database using the ODBC Drivers ( Oracle Vender provided). When I am trying to install ODBC Drivers on linux. its asking me for the DriverManager PATH.

Where we will get this driverManager ? How to install this ?

I have downloaded the Drivermanager from the UnixODBC but I am unable to install this on linux....

my requirement is, I want to connect the Oracle databse using ODBC Drivers( from oracle only no third party). How to do this ?

SRIDHAR.

I have done an application using ODBC that will run on Solaris. My DBMS is Oracle 9.2.0.4.

Now I know, reading this posts that ODBC is too Microsoft directed... My questions is which steps should I take to have a working development environment to make that application run? I need the development headers and the library against which I have to link.

Thanks, any help would be appreciated!

Hello
I made my environment to work [ Linux + ODBC to Cobol (relativity) ] using the UnixODBC drivers from http://www.unixodbc.org/

The latest version is quite easy to deploy and use (comes with a Xwindow admin tool). All the doc you need is there.

Thanks daniel_co!

Could you give a brief summary of the steps I should take? I have to convince the server administrator that the installation would be harmless and painless.

The following Oracle ODBC installation applies to CentOS Linux.
It should work on any Linux or Unix variant that Oracle supports.

I use unixODBC. Read http://www.unixodbc.org/odbcinst.html to learn how it replaces
the Microsoft MDAC utilites. Unfortunately, Oracle doesn't supply a set-up library to install via GUI on Unix.

STEPS

1) Get an ODBC Driver Manager: unixODBC (unixODBC.org)
WARNING: Many Web pages refer to the iODBC Driver Manager, which I don't recommend!
Download the Driver Manager from unixODBC.org, and compile it.
OR
Use binary RPMs. CentOS has good RPMs (try your distro.):
a) unixODBC.i386 (libodbc.so driver manager library) - required
b) unixODBC-devel.i386 (C header files to compile new programs) - optional

2) Get Oracle Instant Client w/ ODBC Support.
WARNING: Get Basic version 10.2.0.2; version 10.2.0.1 and lesser are very buggy!
NOTE: The ODBC supplement may still be 10.2.0.1; that's okay.
Search for "Oracle Instant Client"; and go to the download page for your OS.
Get at least the "basic" & "odbc supplement" ZIP files. "sqlplus" is recommended.
Install them as directed; they are all binary files.

3) The "odbc supplement" has a shell script with the odbc.ini file format in it.
Create an Oracle odbc.ini entry based on it. You must change the following log-on fields.
The username/password can be blank, and provided at connect time.
NOTE: Every string is case-sensitive.
ServerName = [ A tnsnames.ora name ]
UserID = username
Password = password for user
Driver = {the full path to libsqora.so, the Oracle ODBC driver}

4) unixODBC has a command line DSN installer, "odbcinst". Use it to install your Oracle
odbc.ini globally. Only root can do this.
NOTE: Read http://www.unixodbc.org/odbcinst.html
[root]% odbcinst -l -i -s -f oracle_odbc.ini

5) View the DSN in the global odbc.ini file. For the CentOS unixODBC RPM, it is /etc/odbc.ini

6) IMPORTANT: Set TNS_ADMIN to the full directory path where tnsnames.ora is located.
Do not include the filename "tnsnames.ora".
ODBC calls plain OCI, and needs the TNS_ADMIN environment var.
ORACLE_HOME can be unset; it does not matter.

7) IMPORTANT: Make sure all the Oracle shared libaries are in your path.
Find the directory where libsqora.so* is located; that's the dir.

8) TEST: unixODBC has a SQL/ODBC terminal, like sql-plus, called ISQL. Run it on your DSN.
% isql -v {DSN} {username} {passwd}

We followed your guide exactly as you suggest.

We also succeeded to connect to database.

When we've tried to execute SELECT command- we received an error:
[ISQL]ERROR: Could not SQLAllocStmt

We are working on SLES10- 64 bit environment.

If anyone experienced similar situation- please help.

BR,
Aleksandar

We followed your guide exactly as you suggest.

We also succeeded to connect to database.

When we've tried to execute SELECT command- we received an error:
[ISQL]ERROR: Could not SQLAllocStmt

We are working on SLES10- 64 bit environment.

If anyone experienced similar situation- please help.

BR,
Aleksandar

Platform: RHEL x86_64
Oracle Instant Client Basic and ODBC version 10.2.0.3
unixODBC "isql" client connects to our remote Oracle 10g server, however, attempting a simple "SELECT * FROM testable" fails with this error message:
"[ISQL]ERROR: Could not SQLAllocStmt"

We subsequently installed the full Oracle 10g client on the same platform, attempted the same test SELECT statement with the sqlplus client, this was successful.

sqlplus functions OK, ODBC fails.

Does anybody have a functioning Oracle ODBC running on any x86_64 Linux distribution?

U did not fill in odbc.ini correctly

it must be smth like this:

[XE2]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle 10g ODBC driver
DSN = XE2
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName =
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID =

No they filled it out correctly. Also not working here, on x86_64 linux.

Oracle 10.2 ODBC Driver is supported only on following platforms

o Windows XP
o Windows Server 2003
o Windows 2000
o Windows NT X86
o Windows 98
o Red Hat Linux 3.0 (x86-32bit)
o Solaris 5.8 (Sparc 32-bit and 64-bit)

Metalink Note on the ODBC Support

https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=396635.1

Hi, Did anyone get this working to connect Oracle10g, using InstantClient+ODBC Driver, on Linux X86 64bit?

thanks
S

Below is my test environment...

$ uname -a
Linux tsys64 2.6.16.21-0.8-smp #1 SMP Mon Jul 3 18:25:39 UTC 2006 x86_64 x86_64 x86_64 GNU/Linux
$ cat /etc/SuSE-release
SUSE Linux Enterprise Server 10 (x86_64)
VERSION = 10
$
$ # Unzip Instant Client
$
$ unzip instantclient-basic-linux-x86-64-10.2.0.3-20070103.zip
$ unzip instantclient-odbc-linux-x86-64-10.2.0.3-20070103.zip
$ cd instantclient_10_2
$ export LD_LIBRARY_PATH=$PWD:$LD_LIBRARY_PATH
$ cd ..
$
$ # Build unixODBC
$
$ wget http://www.unixodbc.org/unixODBC-2.2.12.tar.gz
$ tar zxf unixODBC-2.2.12.tar.gz
$ cd unixODBC-2.2.12
$ CFLAGS="-O2 -mtune=nocona" ./configure prefix=$PWD/install disable-gui
$ make -j 2 && make install
$ cd install/bin
$ export PATH=$PWD:$PATH
$ cd ../lib
$ export LD_LIBRARY_PATH=$PWD:$LD_LIBRARY_PATH
$ cd ../etc
$
$ # Create odbcinst.ini
$
$ sed 's/^X//' << 'SHAR_EOF' > 'odbcinst.ini' &&
X[Oracle10gODBCDriver]
XDescription     = Oracle 10g Linux ODBC driver
XDriver = /home/test/pkg/instantclient_10_2/libsqora.so.10.1
XSetup         =
XFileUsage     =
XCPTimeout     =
XCPReuse       =
SHAR_EOF
  : || $echo 'restore of' 'odbcinst.ini' 'failed'
$
$ # Create odbc.ini
$
$ sed 's/^X//' << 'SHAR_EOF' > 'odbc.ini' &&
X[demodb]
XServerName = ORCL
XApplication Attributes = T
XAttributes = W
XBatchAutocommitMode = IfAllSuccessful
XCloseCursor = F
XDisableDPM = F
XDisableMTS = T
XDriver = Oracle10gODBCDriver
XEXECSchemaOpt =
XEXECSyntax = T
XFailover = T
XFailoverDelay = 10
XFailoverRetryCount = 10
XFetchBufferSize = 64000
XForceWCHAR = F
XLobs = T
XLongs = T
XMetadataIdDefault = F
XQueryTimeout = T
XResultSets = T
XSQLGetData extensions = F
XTranslation DLL =
XTranslation Option = 0
SHAR_EOF
  : || $echo 'restore of' 'odbc.ini' 'failed'

But, something is wrong. I can get it almost working... but it keeps segfaulting around memory-related issues. Below are several examples using isql (I had to patch isql to use ODBC 3.0-compliant SQLAllocHandle instead of SQLAlloc(Env|Connect|Stmt|...))

SQL> select count(*) from user_tables;

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 47847904429776 (LWP 6824)]
0x00002b84753d155e in wordcopyfwd_aligned () from /lib64/libc.so.6
(gdb) bt
#0  0x00002b84753d155e in wordcopyfwd_aligned () from /lib64/libc.so.6
#1  0x00002b84753cf9cf in memmove () from /lib64/libc.so.6
#2  0x00002b847585fae3 in bccSQLPrepareLckd () from /home/test/pkg/instantclient_10_2/libsqora.so.10.1
#3  0x00002b847585f70b in SQLPrepareW () from /home/test/pkg/instantclient_10_2/libsqora.so.10.1
#4  0x00002b8474fe2f36 in SQLPrepare () from /home/test/pkg/unixODBC-2.2.12/install/lib/libodbc.so.1
#5  0x00000000004025b4 in main (argc=<value optimized out>, argv=<value optimized out>) at isql.c:319

SQL> select * from user_tables;

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 47257070618320 (LWP 6844)]
0x00002afae4cbf40b in malloc_consolidate () from /lib64/libc.so.6
(gdb) bt
#0  0x00002afae4cbf40b in malloc_consolidate () from /lib64/libc.so.6
#1  0x00002afae4cc12f9 in intmalloc () from /lib64/libc.so.6
#2  0x00002afae4cc27ff in calloc () from /lib64/libc.so.6
#3  0x00002afae5144ab8 in pMEMAlloc () from /home/test/pkg/instantclient_10_2/libsqora.so.10.1
#4  0x00002afae5157bd1 in bccSQLPrepareLckd () from /home/test/pkg/instantclient_10_2/libsqora.so.10.1
#5  0x00002afae515770b in SQLPrepareW () from /home/test/pkg/instantclient_10_2/libsqora.so.10.1
#6  0x00002afae48daf36 in SQLPrepare () from /home/test/pkg/unixODBC-2.2.12/install/lib/libodbc.so.1
#7  0x00000000004025b4 in main (argc=<value optimized out>, argv=<value optimized out>) at isql.c:319

Everyone else see the same thing?

Many memory issues are caused by invalid null-termination of strings. And, as the call stack shows that SQLPrepare is calling SQLPrepareW, I figured there may be something up with handling null-terminated strings between the standard and UNICODE versions of the calls. As such, I've just tried strlen(strSQL) instead of SQL_NTS in SQLPrepare and SQLExecDirect and it seems to work fine. So, this looks like a bug in the 64-bit Linux driver. Nevertheless, you now have a workaround.

If someone could pass this bug onto Oracle, that would be nice.

could you post your patch? I would be interested in seeing it.

Thank you

Hi guys,

Gone through numerous websites to find out information on ODBC on UNIX platforms and finally found this thread. Hope you experts out there can give me some pointers.

I have an AIX machine and have Oracle 8 client installed onto it. I'm not the DBA and the guys maintaining the machine are IBM fellas so they are not very familiar with Oracle configurations.

Question is:
1) Is my assumption correct that once Oracle 8 client is installed into the AIX machine, there should be some kind of odbc drivers installed together some where in $ORACLE_HOME/lib or something?
2) If yes, based on the forums, i notice that i need to create the DSNs myself by editing the odbc.ini file manually. The ones i find on the net are mostly data direct or wire protocol drivers which if i understand correctly, different oracle specific drivers requires different attributes. Can someone point me to the original Oracle DSN attributes that i should set?

The connection should be pretty simple as the TNSNames.ora has already been set on that server. Assuming i have the TNS set as follow:

OPS7RO =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.209.40.182)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ops7)
)
)

Can someone point me to the correct direction of setting up my DSN manually in the ODBC.INI file? I came up with something like below, please correct me:

[MyTestDSN]
driver=[can someone tell me the name of Oracle driver??]
Description=Oracle8
ServerName=**Is this the TNS server name? = OPS7RO <--? **
LogonID= testuser
Password=testuser

Are the above attribute sufficient? I need to know the Oracle Driver name as well and what are the default paths normally that stores the driver.

Apologies if my questions appear to be coming from a newB, as i have no experience in setting up Oracle in UNIX before. Trying to get MicroStrategy to connect to it and the drivers that came with it couldnt support the Oracle version. Sigh.

Any help is much appreciated!!! Thank you thank you