Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle OCI tnsnames.ora

uklein
Contributor

Hi, 

we installed the Exasol Community Edition (.OVA), downloaded and installed the instantclient-basic-linux.x64-12.1.0.2.zip driver, and created a connection OCI_P as 

(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <serivice name>)
)
)

When running the select statement

select * from
(
import from ORA at OCI_P
statement 'select ''Connection works'' from dual'
);

we get the following error: 

SQL-Fehler [ETL-4]: Oracle tool failed with error code '12154' and message 'ORA-12154: TNS:could not resolve the connect identifier specified' (Session: 1743146455125262336)

When running the same query with the same connection from DBEAVER, this works as expected. 

We tried to add the tnsnames.ora file to the instantclient-basic-linux.x64-12.1.0.2.zip but now we cannot load this zip, as the checksum fails. 

 

Any help is greatly appreciated. 

6 REPLIES 6

mwellbro
Xpert

Hi @uklein ,

just off the top of my head: could you try to omit the "(SERVER = DEDICATED)" portion and add ADDRESS_LIST ? So:

(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = 1521)))
(CONNECT_DATA =(SERVICE_NAME = <serivice name>))
)

Shouldn´t technically be much of a difference, but perhaps just give it a shot.

exa-Uwe
Moderator
Moderator

Hi uklein,

just to clarify: You say "When running the same query with the same connection from DBEAVER, this works as expected." Does that mean you connect to the same Exasol database with a different SQL Client than dBeaver (which one?) and you run the same SELECT with that client and get the error ORA-12154?

Best regards

Uwe

uklein
Contributor

This has not made a difference, unfortunately. 

exa-Uwe
Moderator
Moderator

I have just tried that myself: Downloaded instantclient-basic-linux.x64-12.1.0.2.0.zip from Oracle, uploaded it to EXAoperation:

exaUwe_0-1662450414130.png

Then connected to my Exasol 7.1.8 database with DbVisualizer and running these statements

create or replace connection ora_conn to
'(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.105)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )'
user 'SYSTEM' identified by 'oracle';

select * from
(
import from ORA at ora_conn
statement 'select ''Connection works'' from dual'
);

This returns 'Connection works' - no tnsnames.ora needed at the Exasol database for that.

exaUwe_1-1662450906342.png

 

Best regards

Uwe

uklein
Contributor

After replacing the server name with the server ip in the create connection statement it worked. Thanks for the help, @exa-Uwe and @mwellbro . 

mwellbro
Xpert

Great that it worked @uklein ! 
I´d guess that the resolution "hostname<->ip" hinges on what you´ve configured as DNS in ExaOperation under "Network", so if you´d want to use the Oracle host name it might pay off to have a look at that.

Cheers,
Malte