0

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:

enter image description here

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: enter image description here

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

5
  • 1
    SID should be EDBDEV, Connection Name = "Anything you want...", Username/Password as for previous connection. Hostname - whatever that is (or IP). Ask the sysadmin if it's running on 1521.
    – Vérace
    Dec 14, 2017 at 13:54
  • @Vérace so I have all the info (hostname and port) and I enter the database name for SID, but I get this : "Status : Failure -Test failed: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor "
    – Greg Bala
    Dec 14, 2017 at 14:48
  • Is there nobody you can ask about this? I mean, surely your colleagues must access this database also? It's a dev machine I take it? What do the other devs do? Can you access the host machine with sqlplus? What about a shell account? What happens when you run lnsrctl (or similar - can't remember exact name - been a while since I've been on Oracle!)
    – Vérace
    Dec 14, 2017 at 15:42
  • @Vérace no, no one is interested in using sql developer, they are happy with pl/sql developer they have. So no, i am on my own :)
    – Greg Bala
    Dec 14, 2017 at 17:25
  • How does the EDBEV option appear on the dropdown in your PL/SQL Developer? It must be obtaining that list from somewhere? You find out where developers add new machines/servers/SIDs to that list and then you look at the config for those - there has to be something for EDBEV.
    – Vérace
    Dec 14, 2017 at 17:54

3 Answers 3

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
1
  • your explanation assumes the use of TNS_ADMIN, which is not required and really not used all that often. Without it, tns will look first in the current directory, then in $ORACLE_HOME/network/admin. And with SQL Dev having its own jdbc thin client, it's quite possible to not have the oracle client (and thus, no $ORACLE_HOME) at all.
    – EdStevens
    Dec 15, 2017 at 21:38
1

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.

2
  • I've tried looking for this file but no cigar - no such file exists. Any other place this code be defined?
    – Greg Bala
    Dec 14, 2017 at 17:28
  • Maybe on someone else's machine?
    – Vérace
    Dec 14, 2017 at 17:54
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.