Help needed with ODBC Oracle 12C, iODBC to Excel connectivity on MAC

Edwin · May 29, 2020, 5:41pm

Hello,I am new at this, I need to connect Excel 2016 to an Oracle database with ODBC.
I use iODBC with the following configuration:

Driver: /usr/local/instantclient_12_2/libsqora.dylib.12.1
Setup File:/Library/ODBC/odbc.ini

Setup File:

[ODBC Data Sources]
INTEGRA_ODBC = ORACLE

[INTEGRA_ODBC]
Driver = /usr/local/instantclient_12_2/libsqora.dylib.12.1
Server = 192.9…
Port = 1521

Error:

Captura de pantalla 2020-05-29 a la(s) 12.28.20

Location of program files:

Driver: /usr/local/instantclient_12_2/libsqora.dylib.12.1
Libreria Oracle: /usr/local/instantclient_12_2/libclntsh.dylib.12.1 /usr/local/instantclient_12_2/libclntsh.dylib

hwilliams · May 29, 2020, 10:19pm

Looks like the Libreria Oracle ODBC Driver you have installed is badly linked or installed resulting in the Library not loaded: @rpath/libclntsh.dylib.12.1
error. It can possibly be worked around by placing all the Oracle instantclient files in the /usr/local/lib directory where the rpath is set to.

Note also OpenLink provide there own Oracle 12 ODBC Driver for macOS, which just works out of the box, as detailed in this post which show connection to Excel on macOS.

Edwin · June 11, 2020, 6:06pm

Thanks for your reply. I copied all the Oracle files into /usr/local/lib, but a blank window appears without any error and doesn’t work and Openlink is paid. Openlink there is no way to test

hwilliams · June 11, 2020, 9:30pm

You need to speak with Libreria Oracle to determine whether it support the macOS iODBC Driver Manager.

How did you configure the DSN did you edit the odbc.ini file manually or does it have a loadable Setup Dialog for the driver ?

When you click the Test DSN button the driver should load a login dialog if implemented, which given you indicate a blank window appears it would appear it does not. It maybe that driver does not implement the GUI dialogs for use with GUI apps and has to be manually configured and connect manually to application with connect string params passed as a string and not via a GUI login dialog.

If you want a working Oracle ODBC Driver that support iODBC on macOS then our drivers does, but is a licensed driver as that is our line of business …

Edwin · June 24, 2020, 4:22pm

How many machines is the driver for?

How much does the driver cost?

hwilliams · June 24, 2020, 5:02pm

An ODBC Driver license is required per machine, with the following currently available offers for Oracle 12.x

Edwin · June 24, 2020, 6:20pm

Now use a free trial with a free license file, but license not found

Edwin · June 24, 2020, 7:17pm

This driver support libreoffice?

hwilliams · June 24, 2020, 10:36pm

Yes, LibreOffice supports iODBC and thus works with our ODBC drivers.

Have you resolved the license not found error , if so please refer to the following document on OpenLink License Manager Usage on macOS ?

Edwin · June 25, 2020, 5:41pm

No, the same error continues. license not found.

hwilliams · June 26, 2020, 12:37pm

Please provide details of steps you have performed to install the driver:

Name of the OpenLink Oracle 12 ODBC Driver dmg file
Version and build date of the OpenLink Oracle 12 ODBC Driver as reported in the iODBC Administrator
Where the license file (ora12_lt.lic) has been placed, which should be in the /Library/Applications Support/OpenLink/Licenses folder as indicated in the OpenLink License Manager Usage on macOS document.

Edwin · June 26, 2020, 1:38pm

mxl8o12z.dmg
08.00.200114, Wed Feb 12 05:12:55 2020
The downloaded file is ora10_lt.lic. It was placed in /Library/Applications Support/OpenLink/Licenses.
In the installation the file was chosen ora10_lt.lic and the ora12_lt.lic file was generated in /Library/Applications Support/OpenLink/Licenses

hwilliams · June 28, 2020, 12:06am

Ah, there was a problem with the evaluation download service which was incorrectly generating an “ora10_lt.lic” file which is not valid for an Oracle 12 Lite driver. This issue has been fixed, thus if you attempt to obtain another evaluation license for Oracle 12 you should not get the correct “ora12_lt.lic” file for use …

Edwin · June 29, 2020, 4:19pm

The ora12_lit.lic file was downloaded, but I get the same error. license not found.

hwilliams · June 29, 2020, 6:08pm

Please provide the output of running the command:

openssl asn1parse  -in ora12_lt.lic -i -dump -inform der

which displays the details of the license file being used …

Edwin · June 29, 2020, 7:37pm

MacBook-Air-de-Administrador:~ administrador$ openssl asn1parse -in ora12_lt.lic -i -dump -inform der
ora12_lt.lic: No such file or directory

140735974097864:error:02001002:system library:fopen:No such file or directory:/BuildRoot/Library/Caches/com.apple.xbs/Sources/libressl/libressl-22.50.2/libressl/crypto/bio/bss_file.c:255:fopen(‘ora12_lt.lic’, ‘r’)
140735974097864:error:20074002:BIO routines:FILE_CTRL:system lib:/BuildRoot/Library/Caches/com.apple.xbs/Sources/libressl/libressl-22.50.2/libressl/crypto/bio/bss_file.c:257:

hwilliams · June 29, 2020, 9:53pm

Are you specifying the full path and name of the license file in the command, unless the license file is in the actual directory the command is being run from ? ie if you have copied the license file to /Library/Applications Support/OpenLink/Licenses folder then the command would be:

openssl asn1parse  -in /Library/Applications\ Support/OpenLink/Licenses/ora12_lt.lic -i -dump -inform der

Edwin · June 30, 2020, 1:36pm

MacBook-Air-de-Administrador:~ administrador$ openssl asn1parse -in /Library/Application\ Support/OpenLink/Licenses/ora12_lt.lic -i -dump -inform der

Error: offset too large

hwilliams · June 30, 2020, 3:24pm

Please provide a copy of the /Library/Application\ Support/OpenLink/Licenses/ora12_lt.lic file for review as it should return something like the following:

$ openssl asn1parse -in /Library/Application\ Support/OpenLink/Licenses/ora12_lt.lic -i -dump -inform der
    0:d=0  hl=4 l= 690 cons: SEQUENCE          
    4:d=1  hl=4 l= 378 cons:  SEQUENCE          
    8:d=2  hl=2 l=   2 prim:   INTEGER           :0101
   12:d=2  hl=2 l=   8 prim:   PRINTABLESTRING   :ora12_lt
   22:d=2  hl=4 l= 360 cons:   SEQUENCE          
   26:d=3  hl=2 l=  87 cons:    SEQUENCE          
   28:d=4  hl=2 l=  12 prim:     PRINTABLESTRING   :RegisteredTo
   42:d=4  hl=2 l=  71 prim:     PRINTABLESTRING   :http://my.openlinksw.com/dataspace/person/hwilliams@openlinksw.com#this
  115:d=3  hl=2 l=  44 cons:    SEQUENCE          
  117:d=4  hl=2 l=  12 prim:     PRINTABLESTRING   :SerialNumber
  131:d=4  hl=2 l=  28 prim:     PRINTABLESTRING   :aoyA6loOwJzST9gPJsudnqUaAhM=
  161:d=3  hl=2 l=  18 cons:    SEQUENCE          
  163:d=4  hl=2 l=  13 prim:     PRINTABLESTRING   :NumberOfUsers
  178:d=4  hl=2 l=   1 prim:     PRINTABLESTRING   :0
  181:d=3  hl=2 l=  24 cons:    SEQUENCE          
  183:d=4  hl=2 l=  19 prim:     PRINTABLESTRING   :NumberOfConnections
  204:d=4  hl=2 l=   1 prim:     PRINTABLESTRING   :0
  207:d=3  hl=2 l=  17 cons:    SEQUENCE          
  209:d=4  hl=2 l=  12 prim:     PRINTABLESTRING   :NumberOfCPUS
  223:d=4  hl=2 l=   1 prim:     PRINTABLESTRING   :0
  226:d=3  hl=2 l=  42 cons:    SEQUENCE          
  228:d=4  hl=2 l=  10 prim:     PRINTABLESTRING   :ExpireDate
  240:d=4  hl=2 l=  28 prim:     PRINTABLESTRING   :Fri Jul 31 00:00:00 2020 GMT
  270:d=3  hl=2 l=  20 cons:    SEQUENCE          
  272:d=4  hl=2 l=   8 prim:     PRINTABLESTRING   :Platform
  282:d=4  hl=2 l=   8 prim:     PRINTABLESTRING   :Mac OS X
  292:d=3  hl=2 l=  11 cons:    SEQUENCE          
  294:d=4  hl=2 l=   6 prim:     PRINTABLESTRING   :WSType
  302:d=4  hl=2 l=   1 prim:     PRINTABLESTRING   :3
  305:d=3  hl=2 l=  17 cons:    SEQUENCE          
  307:d=4  hl=2 l=  12 prim:     PRINTABLESTRING   :Availability
  321:d=4  hl=2 l=   1 prim:     PRINTABLESTRING   :1
  324:d=3  hl=2 l=  14 cons:    SEQUENCE          
  326:d=4  hl=2 l=   7 prim:     PRINTABLESTRING   :Release
  335:d=4  hl=2 l=   3 prim:     PRINTABLESTRING   :8.0
  340:d=3  hl=2 l=  44 cons:    SEQUENCE          
  342:d=4  hl=2 l=   8 prim:     PRINTABLESTRING   :UniqueID
  352:d=4  hl=2 l=  32 prim:     PRINTABLESTRING   :8C1791A3C5D654AEE92F74D620675BD5
  386:d=1  hl=4 l= 304 cons:  cont [ 2 ]        
  390:d=2  hl=2 l=  24 prim:   INTEGER           :301602016613114F70656E4C696E6B20536F667477617265
  416:d=2  hl=2 l=  16 prim:   INTEGER           :27D9156BE7E5249939918CFEB4A292A7
  434:d=2  hl=4 l= 256 prim:   INTEGER           :1913DC37B81BAF3B239B5BA642FCF8A1323986E2DAD795A628DA18533B2D3E76E085C27DCDA3D926373EA260B57881B7418D49B258E7C23C6FBAB97FBCAA56EFBD0749CA3ED4AE2541B39770FDDDCFE5942E8A2CA03A9B5962FF3EA069DF0A207F75CF1124D7635D8F8309F44364987825213B0BEA2582558C60292573315BE7002560D7B130C3504E3CB92486C2FCA43B84A20E331D4B85508E82365B152BE3F09D70A90FC4141AA090A1CCDAA2D57A04D2F1F904CFFF554FA0E605C63A672430F95118D338B0A9AB931B0D7F74CDF197A559C4FE210236C18984C7C5EF14519E736C0D4018DE8CDBB8F952E22DC6F0E5F5696513BE188864800E2B966474D2

and can only assume it has somehow been corrupted in your case …

Edwin · June 30, 2020, 6:11pm

Copy the downloaded file to /Library/Application\ Support/OpenLink/Licenses/ and it worked, This file was not registered when installing only copied. How to configure with a TNS Names file?