Odbc ошибка подключения oracle

Arrhhh!! I RAN INTO THIS AGAIN!!!

Just install ToadForOracle in C: or any directory without parenthesis in the path.

In my case its because I was on a x64 PC and still using the old Oracle 9i with the 32bit drivers!

I am using SQL Reporting Services with an Oracle Database. The problem is the brackets in the path to Visual Studio (BIDS). Oracle doesn’t like apps that start in a path with brackets:

RDBMS 10g XE problem with parenthesis in path

So I made a BAT file to open Visual Studio with Progra~2 as the short path name for «Program Files (x86)».

Here is the contents of the BAT file:

rem Progra~2 is short path name for "Program Files (x86)" and works around an Oracle client bug that doesn't like the ()'s in the path
start /B "C:Progra~2Microsoft Visual Studio 9.0Common7IDE" "C:Progra~2Microsoft Visual Studio 9.0Common7IDEdevenv.exe"

I name this BAT file StartBIDS.BAT and put it in the directory:

«C:Program FilesMicrosoft SQL ServerStart BIDS.bat»

Then I make a short cut to the BAT file on my Desktop and also my Start Menu and change the ShortCuts icon. This allows me to open TOAD, Visual Studio, BIDS and etc apps that I use to work with Oracle.

Update:

Alternatively make a Junction:

mklink /J "C:Program Files (x86)" "C:Program Files x86"

Then remove the brackets in the shortcut:

I’m trying to connect to an Oracle 19 database using unixODBC 2.3.7.
I’m using the Oracle Developer Days VirtualBox VM.

When trying to troubleshoot via isql I always get this error:

# isql -v -3 oracledsn

[HY000][unixODBC][Oracle][ODBC][Ora]ORA-12545: Connect failed because target host or object does not exist
[ISQL]ERROR: Could not SQLConnect

Sadly I can’t reproduce it now, but when last trying it I always had

[IM004][unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed
[ISQL]ERROR: Could not SQLConnect

This is the same error message I get from my (proprietary) C++ code, so I’m pretty sure the problem is just the connection settings.

I have checked https://www.connectionstrings.com/oracle/ but none of the combinations work.

Here are all my config settings:

# odbcinst -j                                                                                                                                            -130-

unixODBC 2.3.7
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /home/florian/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

# cat /usr/local/etc/odbcinst.ini

[oraclex]
Description     = Oracle ODBC driver for Oracle 19
Driver          = /mnt/libsqora.so.19.1
Setup           =
FileUsage       =
CPTimeout       =
CPReuse         =
Driver Logging  = 7

[ODBC]
Trace      = Yes
TraceFile  = /tmp/odbc.log
ForceTrace = Yes
Pooling    = No
DEBUG      = 1

# cat ~/.odbc.ini 

[oracledsn]
Driver = oraclex
Server = 10.1.6.19
Port = 1521
Uid = system
Pwd = oracle
Database = orclcdb/orcl/"<empty>"

ORA-12545: Connect failed because target host or object does not exist

I also tried ServerName

ServerName = //10.1.6.19/orclcdb or //10.1.6.19/orcl
[28000][unixODBC][Oracle][ODBC][Ora]ORA-01017: invalid username/password; logon denied

Here’s the /tmp/odbc.log:

[ODBC][18832][1564563250.044774][__handles.c][460]
                Exit:[SQL_SUCCESS]
                        Environment = 0x1fd7780
[ODBC][18832][1564563250.044854][SQLSetEnvAttr.c][189]
                Entry:
                        Environment = 0x1fd7780
                        Attribute = SQL_ATTR_ODBC_VERSION
                        Value = 0x3
                        StrLen = 0
[ODBC][18832][1564563250.044886][SQLSetEnvAttr.c][381]
                Exit:[SQL_SUCCESS]
[ODBC][18832][1564563250.044914][SQLAllocHandle.c][377]
                Entry:
                        Handle Type = 2
                        Input Handle = 0x1fd7780
[ODBC][18832][1564563250.044944][SQLAllocHandle.c][493]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x1fd8090
[ODBC][18832][1564563250.044983][SQLConnect.c][3721]
                Entry:
                        Connection = 0x1fd8090
                        Server Name = [oracledsn][length = 9 (SQL_NTS)]
                        User Name = [NULL]
                        Authentication = [NULL]
                UNICODE Using encoding ASCII 'ANSI_X3.4-1968' and UNICODE 'UCS-2LE'

                DIAG [HY000] [Oracle][ODBC][Ora]ORA-12545: Connect failed because target host or object does not exist


[ODBC][18832][1564563250.082037][SQLConnect.c][4244]
                Exit:[SQL_ERROR]
[ODBC][18832][1564563250.082070][SQLGetDiagRec.c][677]
                Entry:
                        Connection = 0x1fd8090
                        Rec Number = 1
                        SQLState = 0x7fffeabcac60
                        Native = 0x7fffeabcac5c
                        Message Text = 0x7fffeabcac70
                        Buffer Length = 500
                        Text Len Ptr = 0x7fffeabcac5a
[ODBC][18832][1564563250.082092][SQLGetDiagRec.c][726]
                Exit:[SQL_SUCCESS]
                        SQLState = HY000
                        Native = 0x7fffeabcac5c -> 12545
                        Message Text = [[unixODBC][Oracle][ODBC][Ora]ORA-12545: Connect failed because target host or object does not exist

I’ve also been trying out this C code from https://www.easysoft.com/developer/languages/c/odbc_tutorial.html#connect_full — basically the same result.

# cat test2.c
#   compile with gcc -I ../include test2.c -o test2 -lodbc

#include <stdio.h>                                                              
#include <sql.h>                                                                
#include <sqlext.h>                                                             

/*                                                                              
 * see Retrieving ODBC Diagnostics                                              
 * for a definition of extract_error().                                         
 */                                                                             
static void extract_error(                                                      
    char *fn,                                                                   
    SQLHANDLE handle,                                                           
    SQLSMALLINT type);                                                          

main() {                                                                        
  SQLHENV env;                                                                  
  SQLHDBC dbc;                                                                  
  SQLHSTMT stmt;                                                                
  SQLRETURN ret; /* ODBC API return status */                                   
  SQLCHAR outstr[1024];                                                         
  SQLSMALLINT outstrlen;                                                        

  /* Allocate an environment handle */                                          
  SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);                        
  /* We want ODBC 3 support */                                                  
  SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);          
  /* Allocate a connection handle */                                            
  SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);                                    
  /* Connect to the DSN mydsn */                                                
  ret = SQLDriverConnect(dbc, NULL, "DSN=oracledsn;", SQL_NTS,                  
                         outstr, sizeof(outstr), &outstrlen,                    
                         SQL_DRIVER_COMPLETE);                                  
  if (SQL_SUCCEEDED(ret)) {                                                     
    printf("Connectedn");                                                      
    printf("Returned connection string was:nt%sn", outstr);                  
    if (ret == SQL_SUCCESS_WITH_INFO) {                                         
      printf("Driver reported the following diagnosticsn");                    
      //extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);                 
    }                                                                           
    SQLDisconnect(dbc);               /* disconnect from driver */              
  } else {                                                                      
    fprintf(stderr, "Failed to connectn");                                     
    //extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);                   
  }                                                                             
  /* free up allocated handles */                                               
  SQLFreeHandle(SQL_HANDLE_DBC, dbc);                                           
  SQLFreeHandle(SQL_HANDLE_ENV, env);                                           
}

As another point reference I can connect to said Oracle Dev VM with sqlplus from another machine like this:

./sqlplus sys/oracle@10.1.6.19/orclcdb as sysdba

SQL>

./sqlplus system/oracle@10.1.6.19/orclcdb

SQL>

On the VM I have a file /u01/app/oracle/product/version/db_1/network/admin/tnsnames.ora but I am not completely sure if I also need one on the client?

ORCLCDB=localhost:1521/orclcdb
ORCL=
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

After bashing my head for a good while, I could make the ODBC 32 bits run. It was the VC ++ 2013 Redistributable.

My install is version 12.2.0.1.0. I can’t believe how stupidly hard it is to get the simple ODBC. You must uncompress the Instant CLient plus ODBC in the same folder and then manually install VCRedist to get this running. GEEEZ.

UPDATE:
Adding a few extra details on my exact steps:

Download Oracle Instant client from the download portal:

Make sure you download the version that you need: X64 or X32. NOTE: DO NOT MIX THE x64 and X32 folders!!!

After you choose the x64 or x86 version, you will be presented with a huge list of packages. You want to make sure you scroll down to the proper version, in my case, it was 12.2.0.1.0.

You must download 2 packages:

• Basic Package
• ODBC Package

If you are installing x32 and x64, you must then download 4 packages:

• Basic Package x32
• ODBC Package x32
• Basic Package x64
• ODBC Package x64

Then, download and install VCredist 2010 and VCredist 2013. Here is a good resource to download them, for x32 you must install x32 bits VCRedist.

Then, you must uncompress the Basic and ODBC package in the same folder. DO NOT MIX x32 and x64, you must create separate folders if you are going to install both bit parity versions.

Then, open CMD as administrator, navigate to your uncompressed folder and run odbc_install.exe.

Then go to ODBC Datasources (Start menu, type ODBC) and add a new DSN, you will be presented with the Oracle ODBC screen.
Here is a tutorial to create the DSN:

That’s it! the key part here is the VCRedist, if you don’t have it, you will have the error stated by the Original Poster.