how to use oracle tnsnames.ora?

Hello,

please tell us your Oracle server version and JDBC driver version used for data source in IDE.

DB: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

oracle driver: ver. 12.2.0.1 [latest]

and which OS you are running?

Ubuntu 18.04.4 LTS

comment: oracle DB is NOT installed on this system at all. I just have tnsnames.ora here. Hundreds of DBs lives elsewhere, I just need to connect to them reasonably quickly. If it's not possible from this window, but it is somehow possible from shell, this would be even better for me.

Please attach DataGrip log, it can be found under 'Help -> Compress logs and show in..' menu.

It's intellij idea, not datagrip. I'm not sure if I can share logs I'm not sure they not contain something private. So I read them for you, and stuff like usernames etc. ... I had to change them; but I did it using ideas search replace, so it was replaced on all lines equally.

tnsnames.ora contains these lines:

IHadToChangeThis2=
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=abcdef.ab.abcde)(PORT=1521))(CONNECT_DATA=(UR=A)(SERVICE_NAME=IHadToChangeThis2)(SERVER=DEDICATED)))

tnsadmin field was set to dir containing tnsnames.ora

tnsname to IHadToChangeThis2

user was set to: IHadToChangeThis

valid password was provided.

in idea.log this can be found:

2020-02-25 15:22:45,319 [11924719]   INFO - .DatabaseConnectionEstablisher - Connecting as: IHadToChangeThis
2020-02-25 15:22:45,319 [11924719]   INFO - .DatabaseConnectionEstablisher - Connecting to: jdbc:oracle:thin:@IHadToChangeThis2
2020-02-25 15:22:45,589 [11924989]   WARN - .DatabaseConnectionEstablisher - Connecting to: jdbc:oracle:thin:@IHadToChangeThis2
2020-02-25 15:22:45,590 [11924990]   WARN - .DatabaseConnectionEstablisher - oracle.net.ns.NetException: could not resolve the connect identifier  "IHadToChangeThis2"
2020-02-25 15:22:45,591 [11924991]   WARN - ialects.oracle.OraErrorHandler - IO Error: could not resolve the connect identifier  "IHadToChangeThis2"
java.sql.SQLException: IO Error: could not resolve the connect identifier  "IHadToChangeThis2"
   at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:774)
   at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:688)
   at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:39)
   at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:691)
   at com.intellij.database.remote.jdbc.helpers.JdbcHelperImpl.connect(JdbcHelperImpl.java:590)
   at com.intellij.database.remote.jdbc.impl.RemoteDriverImpl.connect(RemoteDriverImpl.java:37)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.base/java.lang.reflect.Method.invoke(Method.java:566)
   at java.rmi/sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:359)
   at java.rmi/sun.rmi.transport.Transport$1.run(Transport.java:200)
   at java.rmi/sun.rmi.transport.Transport$1.run(Transport.java:197)
   at java.base/java.security.AccessController.doPrivileged(Native Method)
   at java.rmi/sun.rmi.transport.Transport.serviceCall(Transport.java:196)
   at java.rmi/sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:562)
   at java.rmi/sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:796)
   at java.rmi/sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$0(TCPTransport.java:677)
   at java.base/java.security.AccessController.doPrivileged(Native Method)
   at java.rmi/sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:676)
   at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
   at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
   at java.base/java.lang.Thread.run(Thread.java:834)
   at java.rmi/sun.rmi.transport.StreamRemoteCall.exceptionReceivedFromServer(StreamRemoteCall.java:303)
   at java.rmi/sun.rmi.transport.StreamRemoteCall.executeCall(StreamRemoteCall.java:279)
   at java.rmi/sun.rmi.server.UnicastRef.invoke(UnicastRef.java:161)
   at java.rmi/java.rmi.server.RemoteObjectInvocationHandler.invokeRemoteMethod(RemoteObjectInvocationHandler.java:209)
   at java.rmi/java.rmi.server.RemoteObjectInvocationHandler.invoke(RemoteObjectInvocationHandler.java:161)
   at com.sun.proxy.$Proxy251.connect(Unknown Source)
   at com.intellij.database.dataSource.DatabaseConnectionEstablisher.connect(DatabaseConnectionEstablisher.java:231)
   at com.intellij.database.dataSource.DatabaseConnectionEstablisher.tryConnect(DatabaseConnectionEstablisher.java:172)
   at com.intellij.database.dataSource.DatabaseConnectionEstablisher.lambda$establishConnection$1(DatabaseConnectionEstablisher.java:104)
   at com.intellij.database.dataSource.AsyncUtil.lambda$null$5(AsyncUtil.java:131)
   at com.intellij.openapi.progress.impl.CoreProgressManager.executeProcessUnderProgress(CoreProgressManager.java:538)
   at com.intellij.openapi.progress.impl.ProgressManagerImpl.executeProcessUnderProgress(ProgressManagerImpl.java:59)
   at com.intellij.database.dataSource.AsyncUtil.underProgress(AsyncUtil.java:190)
   at com.intellij.database.dataSource.AsyncUtil.lambda$null$1(AsyncUtil.java:59)
   at com.intellij.database.dataSource.AsyncUtil.lambda$getNonEdtExecutor$17(AsyncUtil.java:285)
   at com.intellij.database.dataSource.AsyncUtil.lambda$asyncFriendlyExecutor$2(AsyncUtil.java:57)
   at com.intellij.database.dataSource.AsyncUtil.lambda$thenComposeAsync$6(AsyncUtil.java:129)
   at com.intellij.database.dataSource.AsyncUtil.lambda$processWhenComplete$10(AsyncUtil.java:162)
   at com.intellij.database.dataSource.AsyncUtil.lambda$handleWhenComplete$11(AsyncUtil.java:170)
   at java.base/java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:859)
   at java.base/java.util.concurrent.CompletableFuture$UniWhenComplete.tryFire(CompletableFuture.java:837)
   at java.base/java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:506)
   at java.base/java.util.concurrent.CompletableFuture.complete(CompletableFuture.java:2073)
   at com.intellij.database.dataSource.AsyncUtil.lambda$processWhenComplete$10(AsyncUtil.java:162)
   at com.intellij.database.dataSource.AsyncUtil.lambda$handleWhenComplete$11(AsyncUtil.java:170)
   at java.base/java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:859)
   at java.base/java.util.concurrent.CompletableFuture.uniWhenCompleteStage(CompletableFuture.java:883)
   at java.base/java.util.concurrent.CompletableFuture.whenComplete(CompletableFuture.java:2251)
   at java.base/java.util.concurrent.CompletableFuture.whenComplete(CompletableFuture.java:143)
   at com.intellij.database.dataSource.AsyncUtil.handleWhenComplete(AsyncUtil.java:168)
   at com.intellij.database.dataSource.AsyncUtil.processWhenComplete(AsyncUtil.java:160)
   at com.intellij.database.dataSource.AsyncUtil.lambda$null$5(AsyncUtil.java:131)
   at com.intellij.openapi.progress.impl.CoreProgressManager.registerIndicatorAndRun(CoreProgressManager.java:585)
   at com.intellij.openapi.progress.impl.CoreProgressManager.executeProcessUnderProgress(CoreProgressManager.java:531)
   at com.intellij.openapi.progress.impl.ProgressManagerImpl.executeProcessUnderProgress(ProgressManagerImpl.java:59)
   at com.intellij.database.dataSource.AsyncUtil.underProgress(AsyncUtil.java:190)
   at com.intellij.database.dataSource.AsyncUtil.lambda$null$1(AsyncUtil.java:59)
   at com.intellij.openapi.application.impl.ApplicationImpl$1.run(ApplicationImpl.java:238)
   at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
   at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
   at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
   at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
   at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.lang.RuntimeException: oracle.net.ns.NetException: could not resolve the connect identifier  "IHadToChangeThis2"
   at oracle.net.resolver.NameResolver.resolveName(NameResolver.java:179)
   at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:489)
   at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:660)
   at oracle.net.ns.NSProtocol.connect(NSProtocol.java:286)
   at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1438)
   at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:518)
   at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:688)
   at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:39)
   at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:691)
   at com.intellij.database.remote.jdbc.helpers.JdbcHelperImpl.connect(JdbcHelperImpl.java:590)
   at com.intellij.database.remote.jdbc.impl.RemoteDriverImpl.connect(RemoteDriverImpl.java:37)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.base/java.lang.reflect.Method.invoke(Method.java:566)
   at java.rmi/sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:359)
   at java.rmi/sun.rmi.transport.Transport$1.run(Transport.java:200)
   at java.rmi/sun.rmi.transport.Transport$1.run(Transport.java:197)
   at java.base/java.security.AccessController.doPrivileged(Native Method)
   at java.rmi/sun.rmi.transport.Transport.serviceCall(Transport.java:196)
   at java.rmi/sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:562)
   at java.rmi/sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:796)
   at java.rmi/sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$0(TCPTransport.java:677)
   at java.base/java.security.AccessController.doPrivileged(Native Method)
   at java.rmi/sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:676)
   ... 3 more

What is the IDEA version?

IntelliJ IDEA 2019.3.3 (Ultimate Edition)
Build #IU-193.6494.35, built on February 11, 2020
Licensed to Home Credit International a.s.

Runtime version: 11.0.5+10-b520.38 amd64
VM: OpenJDK 64-Bit Server VM by JetBrains s.r.o
Linux 4.15.0-76-generic
GC: ParNew, ConcurrentMarkSweep
Memory: 2988M
Cores: 12
Registry: ide.tooltip.initialDelay=905
Non-Bundled Plugins: BashSupport, DeltaTabs, Lombook Plugin, Rearranger, String Manipulation, claims.bold.intellij.avro, com.dmarcotte.handlebars, com.dubreuia, com.khmelyuk.multirun, io.codearte.props2yaml, net.seesharpsoft.intellij.plugins.csv, org.asciidoctor.intellij.asciidoc, org.sonarlint.idea, training

We need a line like:

2020-01-15 08:54:56,103 [ 70631] INFO - ution.rmi.RemoteProcessSupport - "C:\Program Files\JetBrains\DataGrip 2019.2.5\jbr\bin\java" -Djava.net.preferIPv4Stack=true -Djava.rmi.server.hostname=127.0.0.1 -Duser.timezone=UTC -Dfile.encoding=windows-1252 -classpath "C:\Program Files\JetBrains\DataGrip 2019.2.5\lib\util.jar;C:\Program Files\JetBrains\DataGrip 2019.2.5\lib\trove4j.jar;C:\Program Files\JetBrains\DataGrip 2019.2.5\lib\groovy-all-2.4.17.jar;C:\Program Files\JetBrains\DataGrip 2019.2.5\plugins\DatabaseTools\lib\jdbc-console.jar;C:\Program Files\JetBrains\DataGrip 2019.2.5\plugins\DatabaseTools\lib\dekaf-single-2.0.0.390.jar" com.intellij.database.remote.RemoteJdbcServer oracle.jdbc.OracleDriver

After removing all duplicates, I found 2 different lines you asked for in yesterday log, but maybe it's just different parameter order.

INFO - ution.rmi.RemoteProcessSupport - /opt/idea/idea-IU-191.7479.19/jbr/bin/java -Djava.net.preferIPv4Stack=true -Djava.rmi.server.hostname=127.0.0.1 -Duser.timezone=UTC -Dfile.encoding=UTF-8 -classpath /opt/idea/idea/lib/util.jar:/opt/idea/idea/lib/trove4j.jar:/opt/idea/idea-IU-191.7479.19/lib/groovy-all-2.4.17.jar:/opt/idea/idea/plugins/DatabaseTools/lib/jdbc-console.jar:/opt/idea/idea/plugins/DatabaseTools/lib/dekaf-single-2.0.0.390.jar:/home/mmucha/.IntelliJIdea2019.3/config/jdbc-drivers/Oracle/12.2.0.1/ojdbc8.jar:/home/mmucha/.IntelliJIdea2019.3/config/jdbc-drivers/Oracle/12.2.0.1/xdb6.jar:/home/mmucha/.IntelliJIdea2019.3/config/jdbc-drivers/Oracle/12.2.0.1/orai18n.jar:/home/mmucha/.IntelliJIdea2019.3/config/jdbc-drivers/Oracle/12.2.0.1/xmlparserv2.jar com.intellij.database.remote.RemoteJdbcServer oracle.jdbc.OracleDriver
INFO - ution.rmi.RemoteProcessSupport - /opt/idea/idea-IU-191.7479.19/jbr/bin/java -Doracle.net.tns_admin=/home/mmucha -Djava.net.preferIPv4Stack=true -Djava.rmi.server.hostname=127.0.0.1 -Duser.timezone=UTC -Dfile.encoding=UTF-8 -classpath /opt/idea/idea/lib/util.jar:/opt/idea/idea/lib/trove4j.jar:/opt/idea/idea-IU-191.7479.19/lib/groovy-all-2.4.17.jar:/opt/idea/idea/plugins/DatabaseTools/lib/jdbc-console.jar:/opt/idea/idea/plugins/DatabaseTools/lib/dekaf-single-2.0.0.390.jar:/home/mmucha/.IntelliJIdea2019.3/config/jdbc-drivers/Oracle/12.2.0.1/ojdbc8.jar:/home/mmucha/.IntelliJIdea2019.3/config/jdbc-drivers/Oracle/12.2.0.1/xdb6.jar:/home/mmucha/.IntelliJIdea2019.3/config/jdbc-drivers/Oracle/12.2.0.1/orai18n.jar:/home/mmucha/.IntelliJIdea2019.3/config/jdbc-drivers/Oracle/12.2.0.1/xmlparserv2.jar com.intellij.database.remote.RemoteJdbcServer oracle.jdbc.OracleDriver

Hm, `-Doracle.net.tns_admin=/home/mmucha` means that we've passed everything to JDBC driver and now it's turn.
Have you been able to use this tnsnames.ora with native oracle tools?
Is tns name camel-cased like in your example? Or does it contain non-alphabetic symbols?
Could you try minimizing your tnsnmaes.ora file to only this entry, try simple alias for it?

If I rewrite manually data from this tnsnames.ora into intellj idea dialog, I can log in.

If I use this file with free sqldeveloper (by putting it into directory sqldeveloper/tns/) I am able to create new connections.

some examplary line looks like this. I cannot grant all entries looks like this(>25K LOC), but most do. I never saw any non-ascii symbol. I never saw lower case letter. Casing kept, letters transformed to A, numbers to 1.

# 18.10.2019 11:17
AAAAAA.AA.AAAAAA=
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=AAAAA1-AA.AAA.AAA)(PORT=1521))(CONNECT_DATA=(UR=A)(SERVICE_NAME=AAAAAA.AAAAA.AA)(SERVER=DEDICATED)))

# 14.06.2018 11:38
AAA1AA.AA.AAAA=
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=AAAAAA1.AA.AAAA)(PORT=1521))(CONNECT_DATA=(UR=A)(SERVICE_NAME=AAAAA1AA.AA.AAA)(SERVER=DEDICATED)))

If it is problem, is it possible to create DS from command line anyhow? If it would be possible to contact idea from shell to create DS it would be the best. Like openning files from shell. If oracle driver does not cooperate, would it be able to just call idea somehow with all information? Definitely easy to do on my side ...

It is said here https://github.com/utPLSQL/utPLSQL-cli/issues/88 that dots in the aliases names do not work in JDBC.
As for command line data source creation - no, that is not possible.
You can paste strings like

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=AAAAAA1.AA.AAAA)(PORT=1521))(CONNECT_DATA=(UR=A)(SERVICE_NAME=AAAAA1AA.AA.AAA)(SERVER=DEDICATED)))

in the url

1. how is utPLSQL related?

2. where on that page is stated, that dots in the aliases does not work in jdbc? But OK, I did similar research on "dots in alias" research, and found this, which is more relevant. https://stackoverflow.com/questions/23010218/using-tns-alias-with-ojdbc-12-1 

But notice two things:

a) generated exception explicitly says, that there are dots in alias which are not allowed

b) in comment under accepted answer bradley claims, that this bug was fixed by oracle in 12.1.0.2, while I use newer 12.2.0.1.

thus from these two things: I'm using newer driver + I'm not getting exception about dots, but about:

oracle.net.ns.NetException: could not resolve the connect identifier  "IHadToChangeThis2"

I'd think it's not the case.

3. but OK, let give it a try. I changed alias `some.alias.we.use` to `some_alias_we_use` as both links mentions as a workaround. The result is:

oracle.net.ns.NetException: could not resolve the connect identifier "some_alias_we_use"

so I don't think this is anyhow related to dots.

and one more testing with aliases:

If I use longer alias corresponding to regex [A-Z_], I got this:

2020-02-27 08:48:31,557 [ 203360] INFO - .DatabaseConnectionEstablisher - Connecting as: AAAAAAAAA 
2020-02-27 08:48:31,557 [ 203360] INFO - .DatabaseConnectionEstablisher - Connecting to: jdbc:oracle:thin:@AA11A1AA_AA_AAAA 
2020-02-27 08:48:31,848 [ 203651] WARN - .DatabaseConnectionEstablisher - Connecting to: jdbc:oracle:thin:@AA11A1AA_AA_AAAA 
2020-02-27 08:48:31,848 [ 203651] WARN - .DatabaseConnectionEstablisher - oracle.net.ns.NetException: could not resolve the connect identifier "AA11A1AA_AA_AAAA" 
2020-02-27 08:48:31,848 [ 203651] WARN - ialects.oracle.OraErrorHandler - IO Error: could not resolve the connect identifier "AA11A1AA_AA_AAAA" 
java.sql.SQLException: IO Error: could not resolve the connect identifier "AA11A1AA_AA_AAAA"

however if I rename that alias to just A, like literally one uppercase char A, I got this:

2020-02-27 09:00:47,346 [ 939149] INFO - .DatabaseConnectionEstablisher - Connecting as: AAAAAAAAA 
2020-02-27 09:00:47,346 [ 939149] INFO - .DatabaseConnectionEstablisher - Connecting to: jdbc:oracle:thin:@A 
2020-02-27 09:02:57,786 [1069589] WARN - .DatabaseConnectionEstablisher - Connecting to: jdbc:oracle:thin:@A 
2020-02-27 09:02:57,787 [1069590] WARN - .DatabaseConnectionEstablisher - Connection timed out, socket connect lapse 130214 ms. /213.212.81.71 1521 0 1 true 

I even tried copy-pasting strings as you mentioned, so I put connection type "URL only" and place there a string — no, does not work.

BUT if I take that connection string and human parse it, selecting host, service name, port and user into "service name" type of connection dialog — it works immediately.

I don't know, but I think there is something wrong. Well I definitely can write bash script to parse that connection string and use xdotool to type it out for me, but I'm not sure if that's the correct way to use intellij dialogs ;) (but thinking about it, I will do that, that's pretty trivial and fast as well)

Looks like tns name handling a bit broken in thin driver, so  we may some day implement alias substitution on our side.
So `_`'s seems to be invalid characters too.
If you are not afraid of using command line tools, you can always generate directly to .idea/dataSources.xml ;)

PS. This page https://geek-tips.github.io/articles/485122/index.html insists that

jdbc:oracle:thin:@(DESCRIPTION=...

works. Maybe you've copy-pasted multiline  definition in url? I don't know what'll happen with line breaks.
also it is strange, that you have the timeout (in @A attempt)

I will retry that connection string later, maybe it was multiline, cannot grant that.

also `_` removal won't fix that, because I already stated, that even single letter A as alias does not work, and I don't think we can go with char removal any further ...

datasource.xml is watched for changes or do I have to shutdown, modify and start upon every change?

Yes, you need to stop IDE before modifications