How to configure remote connection to Oracle in PL/SQL?

2 years ago
Olivia Parker
2 minutes

To connect to a remote Oracle database, the following configuration steps need to be carried out:

  1. Ensure that the listener is running and listening on the correct port on the Oracle database server. You can use the following command to check the status of the listener:
  2. Check the listener status.
  3. Edit the listener.ora file on the Oracle database server, which is located in the network/admin directory of Oracle. Add a new listener entry to listen for remote connection requests. For example:
  4. SID_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = )
    (PROGRAM = extproc)
    )
    )

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    )

  5. should be replaced with the installation directory of Oracle, is the hostname or IP address of the database server, and is the port number for listening.
  6. Restart the listener on the Oracle database server to apply the configuration changes.
  7. Stop the listener
    Start the listener
  8. On a remote client, configure Oracle client environment variables, including ORACLE_HOME and PATH. Set ORACLE_HOME to the installation directory of Oracle and add PATH to the ORACLE_HOME/bin directory.
  9. On a remote client, establish a new network connection descriptor (Net Service Name) to specify the connection information for the remote Oracle database. This can be done using the Oracle Net Configuration Assistant (NetCA) tool or by manually editing the tnsnames.ora file.
  10. In the NetCA tool, select the “Add Database” option and provide the connection information for the remote Oracle database.
  11. In the tnsnames.ora file, add a new connection descriptor. For example:

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

    Replace with the service name of the remote Oracle database, with the hostname or IP address of the database server, and with the listening port number.

  12. Test the connection using the created network connection descriptor on the remote client. Example:
  13. Access the service using SQLPlus with the specified username and password.
  14. Here, and are the username and password for the remote Oracle database, and is the name of the network connection descriptor created.

After completing the above configuration steps, you will be able to access and operate the Oracle database using a remote connection string.

bannerAds