[unixODBC-support] how to connect to oracle xe?

Ken Resander kresander at yahoo.com
Mon Jan 4 08:56:55 GMT 2010


Hi Gian,

I was on Ubuntu 8.10 (still am) when I installed the Debian package:

 oracle-xe.10.2.0.1-1.0i386.deb

I just followed the instructions for installing. There were no problems doing that, but it did not work until I discovered Oracle had changed the directory structure.

I did not install the Oracle Client because I was going to use my own ODBC API code for accessing local and remote servers. I don't know what the Oracle Client is for, but other clients I have seen (for MySQL, SQL Server, SQL Anywhere, Mimer SQL) just offer a GUI for SQL defining, searching, inserting and deleting records/rows in a database as an alternative to the commandline interface. These clients are designed for their respective backends and I don't remember having any difficulties using them two or three years ago on Windows.

Below is a small part of the directory structure and files produced by the oracle-xe.10.2.0.1-1.0i386.deb install. The .../server/ subdirectory is not mentioned in any of the documentation I managed to find, but is clearly present. Check if there is a similar omission for the Oracle client.

./
usr/
usr/lib/
usr/lib/oracle/
usr/lib/oracle/xe/
usr/lib/oracle/xe/app/
usr/lib/oracle/xe/app/oracle/
usr/lib/oracle/xe/app/oracle/product/
usr/lib/oracle/xe/app/oracle/product/10.2.0/
usr/lib/oracle/xe/app/oracle/product/10.2.0/server/
usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/
usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh
usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.csh
usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/nls_lang.sh
usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/createdb.sh
usr/lib/oracle/xe/app/oracle/product/10.2.0/server/ctx/lib/xywsr.so
usr/lib/oracle/xe/app/oracle/product/10.2.0/server/ctx/lib/xmlsr.so
usr/lib/oracle/xe/app/oracle/product/10.2.0/server/ctx/lib/xmllib.so
usr/lib/oracle/xe/app/oracle/product/10.2.0/server/ctx/lib/xlssr.so
usr/lib/oracle/xe/app/oracle/product/10.2.0/server/ctx/lib/wpmsr.so
usr/lib/oracle/xe/app/oracle/product/10.2.0/server/ctx/lib/wpmap.so
usr/lib/oracle/xe/app/oracle/product/10.2.0/server/ctx/lib/wp6sr.so
usr/lib/oracle/xe/app/oracle/product/10.2.0/server/ctx/lib/wosr.so


Do you have the option of using another database? If so, consider using Mimer SQL because their documentation and email support is excellent. MySQL is reasonable too. Oracle and DB2 may have powerful features, but do not provide enough documentation for new users.

Regards
Ken



--- On Mon, 4/1/10, GianLuca Sarto <glsarto at tiscali.it> wrote:

From: GianLuca Sarto <glsarto at tiscali.it>
Subject: Re: [unixODBC-support] how to connect to oracle xe?
To: "Support for the unixODBC project" <unixodbc-support at mailman.unixodbc.org>
Date: Monday, 4 January, 2010, 5:08 AM




  
GianLuca Sarto wrote:

  
Ken Resander wrote:
  
    
      
        
          Hi GianLuca,

          

I managed to get Oracle XE working with unixODBC about 8 months ago. It
was not easy. The Oracle driver documentation is very poor and
incomplete.

 

A few days ago I posted on this mailing giving all information that
helped me get Oracle XE up and working. Before continuing your Google
search try the ini files below, if you have not already done so:    

          

/etc/odbcinst.ini

[OracleExpressdrv]

Driver =
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libsqora.so.10.1

Description = Oracle 10g express driver

          

/etc/odbc.ini

[TestDBDSN]

Application Attributes = T

Attributes = W

BatchAutocommitMode = IfAllSuccessful

CloseCursor = F

DisableDPM = F

DisableMTS = T

Driver = OracleExpressdrv

DSN = TestDBDSN

EXECSchemaOpt =

EXECSyntax = T

Failover = T

FailoverDelay = 10

FailoverRetryCount = 10

FetchBufferSize = 64000

ForceWCHAR = F

Lobs = T

Longs = T

MetadataIdDefault = F

QueryTimeout = T

ResultSets = T

# ServerName is TNS_alias_from_tnsnames.ora

ServerName = XE

SQLGetData extensions = F

Translation DLL =

Translation Option = 0

UserID = hr

          

          

You may also need to put this or parts of this in your startup file:

          

ODBCINI=/etc/odbc.ini;export ODBCINI

ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0;export
ORACLE_HOME

TNS_ADMIN=$ORACLE_HOME/server/network/admin;export TNS_ADMIN

LD_LIBRARY_PATH=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib;export
LD_LIBRARY_PATH

          

          

The main problem that stopped me connecting was that Oracle had changed
the install directory structure (undocumented?). I discovered by trial
and error that ORACLE_HOME has to be:

          

ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server

          

not

          

ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0

          

which is shown in dozens of pages on the Internet going back many years.

          

Hope this works for you.

          

Ken

          

P.S. I stopped using the GUI tools a long time ago. It is a lot easier
just to type in the ini sections as text files. Don't remember which
one(s), but some GUI tool(s) are buggy and cannot generate complete and
correct text files.

          

    

          

--- On Sat, 2/1/10, GianLuca Sarto <glsarto at tiscali.it>
wrote:

          

From: GianLuca Sarto <glsarto at tiscali.it>

Subject: Re: [unixODBC-support] how to connect to oracle xe?

To: "Support for the unixODBC project"
            <unixodbc-support at mailman.unixodbc.org>

Date: Saturday, 2 January, 2010, 6:35 PM

            

            
            
Nick Gorham wrote:
            GianLuca
Sarto wrote: 

              Nick Gorham wrote: 

                GianLuca Sarto wrote: 

                  Nick Gorham wrote: 

                    GianLuca Sarto wrote: 

                      Nick, 

                        

thanks for your kind reply. 

                        

I am not sure I fully understood the Oracle ReadME, but I managed to
install the driver. 

                        

Well, almost... 

                        

ODBCConfig crashes when I try to configure the driver: 

gian at hp-gls:/usr/lib/oracle/instantclient_10_2$ sudo ODBCConfig 

ODBCConfig: libltdl/ltdl.c:1178: try_dlopen: Assertion `filename
&& *filename' failed. 

Aborted 

                        

                      
I would avoid ODBCConfig and just edit the ini files. You haven't got a
setup lib 

                      

                    
How should I edit my ini files? are there any required entries I should
add? 

                  
Should be documented with the driver. 

                  

                
it should, but it is not. 

                

I'm asking what are the UnixOdbc requirements in the ini files. 

                

If I'm OT or on the wrong list please forgive me. 

              
It fine asking, I just don't know the full answer :-) 

              

All unixODBC needs is a [DSN] section in the odbc.ini file that
contains a 

              

Driver = NameofDriver 

              

That refers to a [NameofDriver] section on odbcinst.ini that contains a
              

              

Driver = /path/to/the/driver/libdriver.so 

              

All the other entries are up to the driver. Thats why it needs info
from the driver. I would expect there wil be a sample ini setup
somewhere in the docs, it would be a mistake assuming that everyone can
use (or wants to use) the GUI tools. 

              

            
it's fine for me to use the command line, but reading unixODBC docs I
found:

            NOTE: odbc.ini and
odbcinst.ini are
considered to be ODBC System Information. All access (read or write) to
this System Information should occur via the Driver Manager or the ODBCINST
share library. Do not access these INI files directly!

            

            That's why I tried ODBCConfig.

            

I 'll go on googling...

            

thanks for your help,

-Gian

            

            

             
            

-----Inline Attachment Follows-----

            

            _______________________________________________

unixODBC-support mailing list

            unixODBC-support at mailman.unixodbc.org

            http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support

            
          
          
        
      
    
    

     New
Email addresses available on Yahoo!  

Get the Email name you've always wanted on the new @ymail and
@rocketmail.

Hurry before someone else does!
    
_______________________________________________
unixODBC-support mailing list
unixODBC-support at mailman.unixodbc.org
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
  
  
Ken,

  

thanks for your message.

  

I do not have the /server subfolder, did you install the XE client, or
the instant one?

  

I'm totally confused!

  

-Gian

  
_______________________________________________
unixODBC-support mailing list
unixODBC-support at mailman.unixodbc.org
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
  

Let's get back to square one.



Because this works:



gian at hp-gls:~$ ./sqlplus user/pwd@//192.168.1.126:1521/XE



So, where do I put the server address, 192.168.1.126:1521/XE into the
odbc.ini or odbcinst.ini?



I guess that missing this info , I never be able to connect...



-G

 

-----Inline Attachment Follows-----

_______________________________________________
unixODBC-support mailing list
unixODBC-support at mailman.unixodbc.org
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support



      Get your preferred Email name!
Now you can @ymail.com and @rocketmail.com. 
http://mail.promotions.yahoo.com/newdomains/aa/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.unixodbc.org/pipermail/unixodbc-support/attachments/20100104/a8d52846/attachment.html>


More information about the unixODBC-support mailing list