How to Connect to a Database through SQL Developer in MS Window

Prerequisites:

• Oracle SQL Developer installed on the machine. If not installed already then download it from Oracle website and install. Note that SQL Developer requires Java JDK already installed on the machine.
• The database should be up and running

Step 1: Setting TNS_ADMIN environment variable

TNS Admin (TNS_ADMIN) environment variable should be set to the directory where tnsname.ora file resides i.e C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN in our case
To setup this variable:

1. Open the My Computer properties (Start > My Computer - Right Click and choose properties), Advance System Settings, Environment Variables. 
2. Then create a new system variable with variable name as TNS_ADMIN and value as C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN. Note that this the oracle home might be different in your case depending upon how you have installed the Oracle Database Application.

Step 2: Updating tnsnames.ora and listener.ora files

Both the tnsnames.ora and listener.ora should have the entry for SID or Database Name to establish connection with database.

PeopleSoft HRMS Online Training

Lets say the SID for our database is HRDEMO, this is how the tnsnames.ora and listener.ora located under \NETWORK\ADMIN will be updated (highlighted in red):

tnsnames.ora

# tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

LISTENER_ORCL =

  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

    (CONNECT_DATA =

      (SID = CLRExtProc)

      (PRESENTATION = RO)

    )

  )

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl.0.2.15)

    )

  )

HRDEMO =

(DESCRIPTION =

  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID = HRDEMO)

    )

)

listener.ora

# listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")

    )

    (SID_DESC =

      (SID_NAME = HRDEMO)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

    ) 

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = C:\app\Administrator\product\11.2.0\dbhome_1

DIRECT_HANDOFF_TTC_LISTENER=OFF

Step 3: Restart the TNS Listener (OracleOraDb11g_home1TNSListener)

Open the Windows Services utility from Start > Run > services.msc and start the TNS Listener if its not started already. If already started, then stop and start back again.

Step 3: Establishing Connection through Oracle SQL Developer

1. Start the database HRDEMO, to know how to start the database visit this article Connecting and Starting the Database through sqlplus Command
2. Launch the SQL Developer by executing the binary from <SQL Developer Home>\sqldeveloper.ext.
3. Open the new connection window by clicking on + sign on top left as shown below:
4. Enter the Connection Name HRDEMO, User Name - SYSADM, Password - SYSADM. Select the option 'Save Password', Role - Default, Connection Type - TNS. Finally select the Network Alias - HRDEMO. Note that SYSADM is the Access ID that we created while completing the database setup.
   
5. Click on Test Connection, if the connection was established then the status on bottom left of this window will show as 'Success', if not then error message will be thrown. Click on Save to save the connection.
6. If the connection was successful then click on Connect which will establish connection with the database HRDEMO and open the SQL Editor to write/execute SQL queries on this database.

Want to learn PeopleSoft technical module thoroughly? I have several videos with total duration of over 50 hours.

Following is the link to the YouTube videos Technical
Click here to see course contents

Click here to know how it works

However, if you want to save money by purchasing whole module instead of in parts then visit this page to get more details PeopleSoft Functional and technical online training