CODEWITHSUNDEEP
javasqljdbcoracle12cojdbc
corporateWhore
corporateWhore

Reputation: 635

Oracle 12c database connection using thin driver throws IO error

I'm following the JDBC Developer's Guide and trying to test the JDBC thin driver connection using a short java program.

import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
class JDBCVersion
{
public static void main (String args[]) throws SQLException
{
        OracleDataSource ods = new OracleDataSource();
        ods.setURL("jdbc:oracle:thin:hr/hr@localhost:1522:orcl");
        Connection conn = ods.getConnection();
        // Create Oracle DatabaseMetaData object
        DatabaseMetaData meta = conn.getMetaData();
        // gets driver info:
        System.out.println("JDBC driver version is " + meta.getDriverVersion());
}
} //<host>:<port>:<service>

I've tried every possible <host>:<port>:<service> combination but still get a java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection

I've successfully tested the OCI driver using another program included in the tutorial....but can't get this one to work. My application will be using the thin driver to connect to the database so my frustration level is....climbing.

Any help is appreciated.

Upvotes: 3

Views: 20359

Answers (4)

sayedali musavi Khoeini
sayedali musavi Khoeini

Reputation: 19

Ensure the password, username, and URL are correct. They might be the root of the problem.

Upvotes: 0

Jean de Lavarene
Jean de Lavarene

Reputation: 3763

When connecting to a PDB you should always use the PDB's service name in the connection string. It looks like your PDB's service is "pdborcl.global.XXXXXXXX.com" so that's what you need to use to connect the PDB directly.

Personally I find it easier to use the long URL format:

"jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=pdborcl.global.XXXXXXXX.com)))"

It makes it obvious that you're using a Service name instead of an SID.

The beauty of it is that you can also easily test your connection string with sqlplus:

sqlplus "hr/hr@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=pdborcl.global.XXXXXXXX.com)))"

If sqlplus works there is not reason why the JDBC Thin driver wouldn't.

Finally you can also connect the root database using a privilege user and then execute "ALTER SESSION SET CONTAINER=pdb" to switch to the PDB. Should you decide to do so you would have to modify your connection string to connect to the root container first. It should have its own service name.

Upvotes: 0

SubOptimal
SubOptimal

Reputation: 22973

Maybe following comments could explain why you need the sevice name instead of the SID in the URL.

  • the Oracle JDBC FAQ mention that SIDs will be cease to be supported in one of the next few releases of the database

  • the Oracle JDBC devolopers guide mention Always connect to a service. Never use instance_name or SID because these do not direct to known good instances and SID is deprecated

  • the Oracle 2 day + Java developer tutorial mention the syntax jdbc:oracle:driver_type:[username/password]@//host_name:port_number:SID which seems to be a mixture of SID and service name URL (following the other documents and your working example)

  • in contrast the javadoc for OracleDriver mention only the SID syntax

  • the Oracle FAQ wiki mention both syntax

.

jdbc:oracle:thin:[USER/PASSWORD]@[HOST][:PORT]:SID
jdbc:oracle:thin:[USER/PASSWORD]@//[HOST][:PORT]/SERVICE

Upvotes: 4

corporateWhore
corporateWhore

Reputation: 635

I'm able to connect to my container DB (containing my tables, packages, etc.) using the username/password.

Returns:

JDBC driver version is 12.1.0.2.0

Still can't connect to the tutorial "HR" PDB that comes with the oracle 12c install and which the JDBC tutorial uses.

Edit:

Got it to work using the following:

import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
class JDBCVersion
{
public static void main (String args[]) throws SQLException
{       
        OracleDataSource ods = new OracleDataSource();
        ods.setURL("jdbc:oracle:thin:@//localhost:1522/pdborcl.global.XXXXXXXX.com");
        ods.setUser("hr");
        ods.setPassword("hr");
        Connection conn = ods.getConnection();
        // Create Oracle DatabaseMetaData object
        DatabaseMetaData meta = conn.getMetaData();
        // gets driver info:
        System.out.println("JDBC driver version is " + meta.getDriverVersion());
}
}

Still don't understand why I need the full global name instead of the instance name.

Upvotes: 0

Related Questions