OracleContainer needs further configuration

[vanwobe]

The latest version of oracle database needs a slash between the port and the sid of the jdbcUrl: jdbc:oracle:thin@//[hostname]:[port]/[DB service name]

resources:
https://www.oracle.com/dk/database/technologies/appdev/xe/quickstart.html (under java connection)
https://docs.oracle.com/database/121/JJDBC/urls.htm#BEIDHCBA

Also the SID should be configurable (in the same way as username/password is)

[KyleAure]

@vanwobe I think there is some confusion here between service name and sid. When using jdbcURLs you should refer to a sid by using a colon:

jdbc:oracle:thin@//[hostname]:[port]:[sid]

and a service name using a slash

jdbc:oracle:thin@//[hostname]:[port]/[DB service name]

In this case, I believe testcontainers is only supporting XE versions of Oracle. Therefore, our current jdbc configuration is correct. XE is both the SID and Service Name. And there is no way to change that in the XE version.
See documentation here: https://github.com/oracle/docker-images/tree/master/OracleDatabase/SingleInstance#running-oracle-database-18c-express-edition-in-a-docker-container

[vanwobe]

I don't see how the information from your documentation link differs from the links I provided. There is also a slash in the connect string:
sqlplus sys/<your password>@//localhost:1521/XE as sysdba
sqlplus system/<your password>@//localhost:1521/XE
sqlplus pdbadmin/<your password>@//localhost:1521/XEPDB1

The last line shows an example to connect to a PDB (instead of CDB). In this case we want to change the default XE into XEPDB1.

The use of a ':' is only valid in previous versions like xe 11g:
https://docs.oracle.com/cd/E17781_01/appdev.112/e18805/getconn.htm#TDPJD138
jdbc:oracle:driver_type:[username/password]@//host_name:port_number:SID

[KyleAure]

Ahh okay so sqlplus connection strings are different than JDBC connection strings. The oracle JDBC driver will still allow you to connect using a URL with either a SID or Service name, but I think I understand where you are coming from with this. Let me see if I understand correctly:

So you would want something like a method:

    public OracleContainer withSeriviceName(String serviceName) {
        this.serviceName = serviceName;
        return self();
    }

That would allow you to add a serviceName. Since a service name is more specific than a SID we could then have logic to say if a serviceName is present return a JDBC URL that in the form:

jdbc:oracle:thin@//[hostname]:[port]/[DB service name]

otherwise, default back to using the SID (for XE containers this will always be XE)

jdbc:oracle:thin@//[hostname]:[port]:[sid]

[vanwobe]

Indeed that would solve it.

[kiview]

@KyleAure sounds reasonable.

Since you already provided another Oracle PR, do you want to take care of this? I will test the Oracle PRs next week (currently limited bandwidth and therefore can't build the Oracle images for testing).

[nicko]

I was going to open another ticket but this is still open and seems to be related to what I'd like to propose. We're using Oracle XE for test containers (with SpringBoot / jdbc URL for config) and have run into a couple of shortcomings (or so I think, happy to be corrected!)

• SID for XE can be either xe for the CDB (cluster level, non user DB) or xepdb1 for the PDB (pluggable database, the location for user data). When doing user-level stuff like running migrations or regular sql it should be done at PDB level I believe so this needs to be customisable or just hardcoded to xepdb1 for any of the more recent oracle versions.
• Having Username and Password hardcoded to system and oracle creates issues when trying to run Flyway migrations before your tests start. This is because Oracle conflates users and schemas and the default system schema contains a lot of DB internal tables which causes Flyway to fail.

Can I suggest allowing configuration of username and password the same way as the other JDBC configurations do it from the ConnectionURL the same way the PosgreSQLContainerProvider does it? As PostgreSQL also has a default container configured with a schema, username and password configured to 'test' I thought it might be useful to provide an OracleXE container build script that does the same.

Happy to implement myself, just want to make sure I'm not doing something silly as I'm relatively new to TestContainers and Oracle.

[s-jepsen]

Any news on this?