Connect to oracle 11 on a corporate network via oracle developer 17

Question

Sorry for this absolute oracle noob question:

I am in an environment that uses integrated / active directory authentication, for developer to access the oracle databases via an old version 9 PL/SQL Developer:

Clicking OK gets me into the dababase

I am trying to connect to the same database via Oracle SQL Developer 17 and the login screen is more complicated:

What do I enter here? I've searched far and wide but there are few resources for an oracle noob like me.

Answer 1

PL/SQL Developer can only connect to an Oracle database using what's known as a TNS Alias.

This is defined a file called "tnsnames.ora" that contains some text something a bit like this (shamelessly stolen from @Balazs's answer):

EDBDEV =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = edbdev.mycompany.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = EDBDEV)
    )
  )

This allows administrators to define database names and "hide" the IP address/service name details from end users. They say "connect to EDBDEV" and that's all you need to know.

The location of this file is defined by the variable "TNS_ADMIN". On a Linux system you'd find this by typing:

echo $TNS_ADMIN

On a Windows box you can find it by opening the command prompt and typing:

echo %TNS_ADMIN%

In SQL Developer, you can change the "Connection type" box to "TNS", and it will present you with a list of the TNS Aliases that it knows about - e.g. those that it found in the file:

%TNS_ADMIN%\tnsnames.ora

If for some reason you need to override this location, you can set the location of the TNSNames.ora file manually in the preferences:

Tools / Preferences / Database / Advanced

Answer 2

EDBDEV is a TNS alias.

If you search for a file called tnsnames.ora, that should contain an entry similar to below:

EDBDEV =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = edbdev.mycompany.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = EDBDEV)
    )
  )

Based on that, you can fill in the required connection details.

Answer 3

You are on the right track, but need to note a few items: First, since you are defining your "connection type" as "basic", SQL Dev will be using it's built-in jdbc thin client, and so will NOT be using tnsnames.ora. If you changed your connection type to "tns", you would get a different set of fields, including a drop-list that is populated by the entries in tnsnames. But stick to 'basic' connection and you don't have to worry about that.

Second, instead of setting SID=edbdev, use service name=edbdev.

Not part of the answer, but I find it astounding and shameful (on their part) that your colleagues have no interest in helping you at all. Especially for something like this. The gui interface make look a little different, but the fundamentals of connecting a client to the database are the same regardless of the client product. And so the information needed by the client is the same.