Can't connect to oracle database outside container with jdbc-CodePudding

I created a container for an Oracle Express database following

What doesn't work

I fail to connect using IntelliJ, and therefore the underlying JDBC library. I use the following options:

For Password, I used root again, the JDBC URL is as follows: jdbc:oracle:thin:@localhost:1521:XE

When I click on Test connection, IntelliJ tries to connect for about a minute, before showing the following error

CodePudding user response：

I did a test on my MacOS

# fire up the database. Hint, use gvenzl images instead. Much faster! 
docker run -d -e ORACLE_PWD="root" --name testdb -p 5500:5500  -p 8081:8080 -p 1521:1521 container-registry.oracle.com/database/express:21.3.0-xe

# I have sqlplus installed locally on my MacOS
echo 'select dummy from dual;' | sqlplus -S system/"root"@localhost/XE

D
-
X

echo 'select dummy from dual;' | sqlplus -S system/"root"@localhost:XE
ERROR:
ORA-12545: Connect failed because target host or object does not exist


SP2-0306: Invalid option.

# so, how is JDBC behaving taking the connect string as argument 
java -cp .:./ojdbc8-19.6.0.0.jar OracleJDBC "jdbc:oracle:thin:@localhost:1521:XE"
X

java -cp .:./ojdbc8-19.6.0.0.jar OracleJDBC "jdbc:oracle:thin:@localhost:XE"
java.sql.SQLRecoverableException: IO Error: Invalid number format for port number

java -cp .:./ojdbc8-19.6.0.0.jar OracleJDBC "jdbc:oracle:thin:@localhost/XE"
X

Note. Port is not needed, defaults to 1521

cat OracleJDBC.java
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;

public class OracleJDBC {
    public static void main(String[] argv) {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            System.out.println("Where is your Oracle JDBC Driver?");
            e.printStackTrace();
            return;
        }
        Connection connection = null;
        String query = "select dummy from dual";
        try {
            connection = DriverManager.getConnection(argv[0], "system","root");
            Statement stmt = connection.createStatement();
            ResultSet rows = stmt.executeQuery(query);
            while (rows.next()) {
                System.out.println(rows.getString("dummy"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

I cannot explain why you get the error. Can it be the JDBC version you are using? I have just proven, your connection should work. That said, you are not supposed to connect using the SID construct (:SID) anymore. You will hit the root-container and not where you are supposed to store your data - in a pluggable database. The express-edition comes with the default pluggable database "XEPDB1".

echo 'select name from v$pdbs;' | sqlplus -S system/"root"@localhost/XE

NAME
------------------------------
PDB$SEED
XEPDB1

This should be your connect string:

echo 'select dummy from dual;' | sqlplus -S system/"root"@localhost/XEPDB1

D
-
X

From here you create your app schema and user so you no longer will use the power-user 'system'.

Best of luck!

CodePudding user response：

I worked perfectly well when I used the same configuration, but with this image instead of the official one.

Thanks to Bjarte Brandt for pointing me to this image.