Forum Stats

  • 3,815,605 Users
  • 2,259,059 Discussions
  • 7,893,185 Comments

Discussions

How do I use Oracle ODBC driver

3031285
3031285 Member Posts: 3
edited Sep 17, 2015 12:00PM in ODBC

I am a non-I.T. power user.  We have a database on an Oracle 11G server.  I interface with it using an ODBC link to MS Access 2010 on windows.  Now I'd like to work with CLOB fields, which cause errors in my queries.  Some searches show that Microsoft's ODBC driver does not support these, but Oracle's does.  I.T. says they can help me install the driver if I can find it.  This is where my bafflement begins.

Oracle ODBC Drivers

is a page on the Oracle site.  It has links to a page of drivers:

Oracle ODBC Drivers Download

But this only has 9.x and 10.x versions.

Some googling has not helped things further.  How do I work with CLOB fields in MS Access?

Best Answer

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Sep 14, 2015 8:36PM Answer ✓

    I'm confused.  Are you using odbc to acces Access, or to access Oracle?  Make up your mind and clarify.

    The drivers for oracle sit ON TOP OF the standard oracle client.  You get the drivers by installing the oracle client software.  For sure the 'run time' client will get it.  The 'instant' client may .. I really don't know about that.

    One caveat.  Up through Oracle 11, the client did not install the ODBC drivers by default.  You have to select to do a custom install when you launch the client installation.  If you install the 12c client (which you should) then the odbc drivers will install by default.

    Another big complexity will be dealing with 32-bit vs. 64-bit.  The oracle client and the odbc driver have to be the same architecture as the front-end that is using them.  That could well be a 32-bit app, even if your desktop OS is 64-bit.

    I'm not sure I'd trust your I.T. guys to be able to install it if they don't even know where to get it.

Answers

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Sep 14, 2015 8:36PM Answer ✓

    I'm confused.  Are you using odbc to acces Access, or to access Oracle?  Make up your mind and clarify.

    The drivers for oracle sit ON TOP OF the standard oracle client.  You get the drivers by installing the oracle client software.  For sure the 'run time' client will get it.  The 'instant' client may .. I really don't know about that.

    One caveat.  Up through Oracle 11, the client did not install the ODBC drivers by default.  You have to select to do a custom install when you launch the client installation.  If you install the 12c client (which you should) then the odbc drivers will install by default.

    Another big complexity will be dealing with 32-bit vs. 64-bit.  The oracle client and the odbc driver have to be the same architecture as the front-end that is using them.  That could well be a 32-bit app, even if your desktop OS is 64-bit.

    I'm not sure I'd trust your I.T. guys to be able to install it if they don't even know where to get it.

  • 3031285
    3031285 Member Posts: 3
    edited Sep 16, 2015 10:58AM

    Aha! So the ODBC driver is embedded in the client starting at the 11 versions.  We did a custom install and one of the options was to use the Oracle ODBC driver.  The ODBC login was the Oracle-looking one instead of the Microsoft one.  We weren't able to set up a successful ODBC connection after that, though.

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Sep 16, 2015 11:31AM
    3031285 wrote:
    
    Aha! So the ODBC driver is embedded in the client starting at the 11 versions.  
    

    No.  The ODBC libraries have always come with the oracle client software distribution.

    Up through 11.2, the installer did not select them for installation by default.

    Starting with 12.1 the installer selects them for installation by default - at least on a Windows installation.

    We did a custom install and one of the options was to use the Oracle ODBC driver.  The ODBC login was the Oracle-looking one instead of the Microsoft one. 
    

    I presume by that statement that you mean that when you tried to create a DSN, using the odbc admin applet, that there were two oracle related drivers listed to choose from - one from Microsoft and one from Oracle.  That's not a 'login', that is simply choosing which driver to associate with a given ODBC DSN.

    We weren't able to set up a successful ODBC connection after that, though.
    

    That statement provides no actionable information, so I'm afraid I can't help with it.

    3031285
  • 3031285
    3031285 Member Posts: 3
    edited Sep 17, 2015 11:37AM

    "Up through 11.2, the installer did not select them for installation by default" - Oh, interesting!

    "That's not a 'login', that is simply choosing which driver to associate with a given ODBC DSN" - The login part happened when using the connection in MS Access.  It asks for username, password, Server and the screenlet that asks for it is visually different based on the driver being used.

    We found out why the install didn't work; the client install had done something to our TNSnames file, so we replaced it with one from another computer and then things worked right.  Queries with CLOB fields were readable and came in as memos then.  This link gave us some clues:

    https://tensix.com/2012/06/setting-up-an-oracle-odbc-driver-and-data-source/

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Sep 17, 2015 12:00PM
    3031285 wrote:
    
    - The login part happened when using the connection in MS Access.  It asks for username, password, Server and the screenlet that asks for it is visually different based on the driver being used.
    
    

    Q:  And what do we learn from that?

    A:  The "screenlet" (your term) comes from the odbc driver and its libraries, not from a common windows library.

    Note:  That's not the only thing that's different between the MS supplied driver and the Oracle supplied driver.  I'd trust the Oracle-supplied driver until proven differently in a specific use-case.

This discussion has been closed.