CODEWITHSUNDEEP
oracle-databaseunixodbc
wink
wink

Reputation: 466

Cannot connect to Oracle 19 with unixODBC

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("Connected\n");                                                      
    printf("Returned connection string was:\n\t%s\n", outstr);                  
    if (ret == SQL_SUCCESS_WITH_INFO) {                                         
      printf("Driver reported the following diagnostics\n");                    
      //extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);                 
    }                                                                           
    SQLDisconnect(dbc);               /* disconnect from driver */              
  } else {                                                                      
    fprintf(stderr, "Failed to connect\n");                                     
    //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/[email protected]/orclcdb as sysdba

SQL>

./sqlplus system/[email protected]/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)
    )
  )

Upvotes: 2

Views: 3285

Answers (2)

Leigh K
Leigh K

Reputation: 45

I had success with oracle 19c, with one minor change here - remove the "//" from ServerName i.e.

ServerName=192.168.178.32:1521/ORCLCDB

Upvotes: 0

wink
wink

Reputation: 466

I finally found the solution, although this is for 12.2.0.1 and not 19

# cat .odbc.ini
[oracledsn]                                                                     
Driver=oracle                                                                 
UID=system
PWD=oracle
DBQ=192.168.178.32:1521/ORCLCDB
ServerName=//192.168.178.32:1521/ORCLCDB

cat /usr/local/etc/odbcinst.ini
[oracle]
Description = foo
Driver = /mnt/libsqora.so.19.1
Driver Logging = 7
UsageCount=1
FileUsage=1

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

# LD_LIBRARY_PATH=. isql -v oracledsn system oracle
+---------------------------------------+
| Connected!                            |

I have no Oracle-specific environment variables set, but if it works if I take the value of ServerName verbatim for TWO_TASK.

Upvotes: 1

Related Questions