0

Tasked with, and failing to, connect Java code to an Oracle 11.2 DB, that has TNS listeners disabled. The code is expected to be run on the same machine as the DB, so my understand is that the connection is possible

SQLPlus can connect locally, when given the correct user and password.

I won't add all of the code I've tried, but it looks largely like this (for now): How to connect JDBC to tns oracle. At this point, I'm just guessing at connection strings, with no luck.

While, I'm not certain it's pertinent, here is the tnsnames.ora structure.

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA = ...
ORACLR_CONNECTION_DATA = ...

[EDIT] Forgot to mention that there is existing .NET code that is able to make the connection. Not a .NET developer, but here is what I think is the relevant code.

Dim oConn As New OracleConnection

oConn.ConnectionString = String.Format("User id={0};Password={1};", "***", "***")
oConn.Open()
oConn.Close()
Community
  • 1
  • 1
P. Deters
  • 815
  • 2
  • 9
  • 18

2 Answers2

1

When listener is down, you can still connect to database via shared memory(IPC). But it option is NOT supported by thin (pure Java) JDBC drivers.

So you options are:

  • Start listener on localhost 127.0.0.1 only. So it will not be accessible from outside world

  • Use JDBC OCI Driver, but this one is not easy to setup.

    XE=
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(Key = IPCKEY))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
  )
  (CONNECT_DATA = (SID = XE))
)
ibre5041
  • 4,903
  • 1
  • 20
  • 35
  • Listener is not an option, going to try the OCI route. The best documentation I can find is here: https://docs.oracle.com/cd/B28359_01/java.111/b31224/instclnt.htm#CHDIHGEF. Can you suggest any other resources that might help? – P. Deters May 17 '16 at 16:37
  • I do not understand why listener is not an option, but shared memory is not used widely these days. It is something from *ancient times*. – ibre5041 May 18 '16 at 08:17
  • I don't own the DB and the team that does is resistant to change for security reasons. The thing that gets me, is that sqlplus can connect just fine, and I assume (maybe incorrectly) that it's written in Java. – P. Deters May 18 '16 at 15:16
  • sqlplus is written in C, and uses OCI driver. That *security reasons* are nonsense. if you want to forbid access to database then use firewall. – ibre5041 May 18 '16 at 15:19
  • PS: that database run on windows? then it is completely illogical. – ibre5041 May 18 '16 at 15:20
  • Please elaborate. If I can make a case for the listener, I will. That is, what does Windows add to the argument for firewall only, over disabled listeners. – P. Deters May 18 '16 at 15:53
0

A tns connection, by definition, is a network connection which requires a listener. Doesn't matter that the client and db are on the same machine, it is still using tns (network) protocols.

EdStevens
  • 3,708
  • 2
  • 10
  • 18