Accessing Oracle Tables from Vertica via ODBC (UnixODBC)

Moderator: NorbertKrupa

Post Reply
mrian
Newbie
Newbie
Posts: 8
Joined: Wed Jul 17, 2013 3:12 am

Accessing Oracle Tables from Vertica via ODBC (UnixODBC)

Post by mrian » 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.

mrian
Newbie
Newbie
Posts: 8
Joined: Wed Jul 17, 2013 3:12 am

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

Post by mrian » 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

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1806
Joined: Sat Jan 21, 2012 4:58 am
Contact:

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

Post by JimKnicely » Thu Sep 12, 2013 3:57 pm

Thanks for sharing!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “ODBC”