search

gvenzl / oci-oracle-xe

Build scripts for Oracle Database XE container/docker images
Apache License 2.0
257 stars 77 forks source link

Add "SID_LIST_LISTENER" section to "listener.ora" to enable jdbc-URLs with ":SID" (legacy "ojdbc6-11.2.0.4.jar" requirement) #221

Closed TomislavMatas-DBSystel closed 10 months ago

TomislavMatas-DBSystel commented 11 months ago

When using legacy "ojdbc6-11.2.0.4.jar", a JDBC URL with SID "XE" works, e.g.:

jdbc:oracle:thin:\@localhost:1521:XE"

Unfortunately, a JDBC URL with SID "XEPDB1" does not work, e.g.:

jdbc:oracle:thin:\@localhost:1521:XEPDB1"

When connection using the SID "XEPDB1", the below error is reported:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

It also can be reproduced with Oracle SQL Developer using "XEPDB1" as "SID" instead as "Service-Name".

Most likely, that's because the "ojdbc6-11.2.0.4.jar" (and potentially other) legacy client library doe's not support the service notation with ":port/servicename", like e.g.:

jdbc:oracle:thin:\@localhost:1521/XEPDB1"

A possible solution would be to add a "SID_LIST_LISTENER" section to the image's "listener.ora" file, see examples at -->< https://docs.oracle.com/cd/B13789_01/network.101/b10776/listener.htm >, something like e.g.:

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=xepdb1.acme.com)
      (ORACLE_HOME=/opt/oracle/homes/OraDBHome21cXE)
      (SID_NAME=XEPDB1))
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=/opt/oracle/homes/OraDBHome21cXE)
      (PROGRAM=extproc)))

This should enable the SID "XEPDB1". It would be consistent with the entries found in "tnsnames.ora" also.

gvenzl commented 10 months ago

Hi @TomislavMatas-DBSystel,

Thanks for using these images!

May I ask what issue triggered this post?

Service names and the / notation were introduced back in Oracle Database 9i, an 11g R2 driver is capable of parsing that (see the Oracle 11g R2 JDBC Developer's Guide example using .../orcl)

Unfortunately, the SID_LIST_LISTENER won't solve the issue as the GLOBAL_DBNAME remains XE. But, as said, any client dating back to 9i should be able to connect to the service name XEPDB1.

TomislavMatas-DBSystel commented 10 months ago

Hi Gerald, thank you very much for your reply, really appreciate support. I've posted this request because I need to establish a JDBC database connection with PowerBuilder 2021 That particular PowerBuilder version supports JDK 1.6 only and in turn, legacy "ojdbc6*.jar" only.

Reading through your tips, I was able to solve the issue without modification to your images: Using a more recent "ojdbc6-12.1.0.1.jar" and the below URLs

jdbc:oracle:thin:@localhost:11521:xe jdbc:oracle:thin:@localhost:11521/xe jdbc:oracle:thin:@localhost:11521/xepdb1

works just fine for me :-)

Just for future reference: When using the legacy URL with the SID format for the plug in database "xepdb1" like jdbc:oracle:thin:@localhost:11521:xepdb1 the below error comes up:

"SQLSTATE = 66000 Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor"

But as said, just not using the legacy SID format ":xepdb1" any more, but the new service format "/xepdb1" allows to connect successfully.

Thanks again.

Kind regards, Tomi