The Vertica Database Forums

Posted: **Wed Sep 11, 2013 8:27 am**

Guys,

Have you had any luck with accessing oracle tables from within Vertica using UnixODBC?

I installed the Vertica odbc_loader_package (https://github.com/vertica/Vertica-Exte ... er_package) but currently encountering connectivity issues, which I believe is a problem with how I setup my odbc.ini

Setup:

1. Oracle Database (orcl) in host machine (Windows)
2. Vertica Database inside vmware (CentOS)
- Oracle database client installed and is able to access the database(orcl) in #1

Oracle Database Details:

Code: Select all

Username: utlzapp_ctl
Password: oracle
Listener Port: 1521
ServiceName/SID/DBName: orcl
Hostname: myservername
Database Version: 11g (11.2.0.3)

I've installed Oracle 11g Client in my Vertica Server and I am able to access the database (orcl) using it.

# odbc.ini

Code: Select all

[OracleDSN]
Description = Oracle 11g Driver
Driver = /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
ServerName = orcl
UserName = utlzapp_ctl
Password = oracle
Port = 1521
HostName = myservername

#odbcinst.ini

Code: Select all

[OracleDSN]
Description = Oracle example database
Driver = /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1

In my testing, I believe ServerName parameter is referenced as the "oracle service name". Right now I'm having issues with parameters for user and password.

Code: Select all

# vsql

dbadmin=> COPY dumptbl WITH SOURCE ODBCSource() PARSER ODBCLoader(connect='DSN=OracleDSN', query='SELECT * FROM dumptbl;');
ERROR 0:  Error calling setup() in User Function UDParser at [ODBCLoader.cpp:213], error code: 0, message: ODBC Error: SQLDriverConnect() failed with error code 28000, native code 1017 [[unixODBC][Oracle][ODBC][Ora]ORA-01017: invalid username/password; logon denied
]

Thanks.

Posted: **Wed Sep 11, 2013 8:55 am**

Found the working odbc.ini setup

Code: Select all

[OracleDSN]
Description = Oracle 11g Driver
Driver = /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
ServerName = orcl
UserID = utlzapp_ctl
Password = oracle
Port = 1521

Posted: **Thu Sep 12, 2013 3:57 pm**

Thanks for sharing!

The Vertica Database Forums

Accessing Oracle Tables from Vertica via ODBC (UnixODBC)

Accessing Oracle Tables from Vertica via ODBC (UnixODBC)

Re: Accessing Oracle Tables from Vertica via ODBC (UnixODBC)

Re: Accessing Oracle Tables from Vertica via ODBC (UnixODBC)