Put together JDBC URL for thin driver

An Oracle database server does not know the port the listener listens on, so we cannot retrieve it from the Oracle instance.

with BASE
     as (select sys_context('USERENV', 'DB_NAME') as DB_NAME
              , sys_context('USERENV', 'DB_UNIQUE_NAME') as DB_UNIQUE_NAME
              , sys_context('USERENV', 'DB_DOMAIN') as DB_DOMAIN
              , sys_context('USERENV', 'INSTANCE_NAME') as INSTANCE_NAME
              , sys_context('USERENV', 'ORACLE_HOME') as ORACLE_HOME
              , sys_context('USERENV', 'SERVER_HOST') as SERVER_HOST
              , sys_context('USERENV', 'SERVICE_NAME') as SERVICE_NAME
              , sys_context('USERENV', 'SESSION_USER') as SESSION_USER
           from dual)
select BASE.*
     , 'jdbc:oracle:thin:@' || SERVER_HOST || ':<port>:' || DB_NAME
          as JDBC_CONNECT_STRING_SID
     , 'jdbc:oracle:thin:@//' || SERVER_HOST || '<port>/' || SERVICE_NAME
          as JDBC_CONNECT_STRING_SERVICE
  from BASE;

You can look in the file

${ORACLEHOME}/Network/Admin/tnsnames.ora

of your local Oracle Home. If there is nothing in, you probably use LDAP. You check that in

${ORACLEHOME}/Network/Admin/sqlnet.ora

. There is DBMS_LDAP to access LDAP services from within PL/SQL. However, I do not know how to retrieve the data.