Oracle XE 21c database and Eclipse

User_AW11T

I have a problem connecting Oracle XE 21c database to Eclipse.

During the installation I did everything that is in the video https://www.youtube.com/watch?v=DYleroLay5E and I have not encountered any problems, for fun the testtable table has 3 records.

At the Eclipse project I inserted between the libraries ojdbc11.jar and ucp11.jar (even if the latter would not be needed because there is a single connection).

In Main() method before creating the connection I insert:

Class.forName ("oracle.jdbc.driver.OracleDriver");

---

(Obviously my values are different and I use those in the video to make things easier) If I use:

String dbUrl = "jdbc:oracle:thin:@CSHAY-7420.oradev.oraclecorp.com:1521:XEPDB1";

String userName= "demouser";

String userPassword = "demouser";

java.sql.Connection connection = DriverManager.getConnection(dbUrl,userName,userPassword);

connection.setAutoCommit(false);

//...

I get:

java.sql.SQLException: No suitable driver found

It doesn't matter if I replace "XE" for "XEPDB1" or shorten "CSHAY-7420.oradev.oraclecorp.com" with "CSHAY-7420"

I always get that exception.

(I mentioned "CSHAY-7420" because using SQLPLUS, for me, this worked:

SQL> connect demouser/[email protected]:1521/XEPDB1;

and this not:

SQL> connect demouser/[email protected]:1521/XEPDB1;

)

---

At this point I thought of using the only string with userName and password in the form:

String dbUrl = "demouser/[email protected]:1521/XEPDB1";

java.sql.Connection connection = DriverManager.getConnection(dbUrl);

connection.setAutoCommit(false);

//...

Even using the variants I get the same:

java.sql.SQLException: No suitable driver found

---

So I wanted to change my approach:

oracle.jdbc.pool.OracleDataSource ds = new oracle.jdbc.pool.OracleDataSource();

ds.setDriverType("thin");

ds.setServerName("CSHAY-7420");// NO "CSHAY-7420.oradev.oraclecorp.com"

ds.setPortNumber(1521);

ds.setDatabaseName("XE"); // Oracle SID // NO XEPDB1

//ds.setUser("demouser"); // NO

//ds.setPassword("demouser"); // NO

ds.setUser("SYSTEM");

ds.setPassword("<my password>");

java.sql.Connection connection=ds.getConnection();

connection.setAutoCommit(false);

//...

this works until it finds it:

ResultSet resultSet = statement.executeQuery("SELECT * FROM TESTTABLE");

//...

where do i get:

java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

In theory from Eclipse I would create tables in the same schema as TESTTABLE, so what is the solution?

Best regards

L. Fernigrini

Actually, "CSHAY-7420.oradev.oraclecorp.com" is the computer name of Christian Shay, the autor of the video, you need to use the computer name where your XE instance is installed.

You somehow seem to be able to connect with the SYSTEM user name, and since TESTABLE is not a table in the system schema it is OK to receive the error message that you get.

You can simple test "SELECT * FROM dual" that is a table that is visible to all users.

You then need to create a user to host your tables (it is a bad practice to create objects in the SYSTEM Schema), change the connection string to use that user, and then create the tables you need.

User_AW11T

Good morning L. Fernigrini, first of all, thank you for your reply. I think I have explained badly and in a hurry. ^_^'

Obviously in my connection Eclipse – XE I used the name of my pc and not that of Christian Shay. If I add the suffix ".oradev.oraclecorp.com" to the name of the pc it fails.

My intention was to use a user other than SYS or SYSTEM for the reason you described.

As in the video, the table TESTTABLE was created by the user DemoUser and I expected that SYS or SYSTEM could "see" all the tables. Then it matters little that, in the real world, DemoUser and SYS of the video are the same person: Christian Shay. :-D

The query "SELECT * FROM dual" works, so does the query "select name from v$pdbs" of the video.

Patience! I will adapt using SYSTEM and hopefully someone finds a way to use another user.

Bye bye!

L. Fernigrini

Avoid creating objects on the SYSTEM schema.

Create a new user and use it to connect, create objects and query them.

Kmensah-Oracle

Hello

Are you still having issues with connecting to your XE database?

At any rate, the following suggestions may help or clarify

1) As you've found out and also indicated by L. Fernigrini, your connect string should not use the suffix in the video (oradev.oraclecorp.com), it was an example, using the Oracle domain name; rather use the name of your machine with your domain name or use localhome.

DB_URL="jdbc:oracle:thin:@//localhost:1521/XEPDB1"

2) Each database schema SYS, SYSTEM, Demouser or else only see objects they own. Use SYSTEM to create Demouser (or another user) then connect with Demouser to create and query TESTTABLE.

Please visit our JDBC guide at https://www.oracle.com/database/technologies/getting-started-using-jdbc-onpremise.html; it points to a code sample.

Hope this helps, Kuassi

User_AW11T

Good evening,

Kuassi, I followed the link and I added:

e.printStackTrace();

1) and I get:

java.sql.SQLException: ORA-65096: invalid common user or role name

ORA-06512: a line 1

 

      at com.oracle.database.jdbc/oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:630)

      at com.oracle.database.jdbc/oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:564)

      at com.oracle.database.jdbc/oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1231)

      at com.oracle.database.jdbc/oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:772)

      at com.oracle.database.jdbc/oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:299)

      at com.oracle.database.jdbc/oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:512)

      at com.oracle.database.jdbc/oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:123)

      at com.oracle.database.jdbc/oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1200)

      at com.oracle.database.jdbc/oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1820)

      at com.oracle.database.jdbc/oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1472)

      at com.oracle.database.jdbc/oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:2055)

      at com.oracle.database.jdbc/oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:394)

      at com.oracle.database.ucp/oracle.ucp.jdbc.proxy.oracle$1ucp$1jdbc$1proxy$1oracle$1StatementProxy$2oracle$1jdbc$1internal$1OracleStatement$$$Proxy.executeQuery(Unknown Source)

      at com.example.CreateUser.main(CreateUser.java:99)

Caused by: Error : 65096, Position : 0, Sql = BEGIN EXECUTE IMMEDIATE ('CREATE USER newDBUser IDENTIFIED BY newDBPassword DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS'); EXECUTE IMMEDIATE ('GRANT CREATE SESSION, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE MATERIALIZED VIEW TO newDBUser'); END;, OriginalSql = BEGIN EXECUTE IMMEDIATE ('CREATE USER newDBUser IDENTIFIED BY newDBPassword DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS'); EXECUTE IMMEDIATE ('GRANT CREATE SESSION, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE MATERIALIZED VIEW TO newDBUser'); END;, Error Msg = ORA-65096: invalid common user or role name

ORA-06512: a line 1

 

      at com.oracle.database.jdbc/oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:637)

      ... 13 more

2) I wanted to try to separate the instructions into 2 parts to execute only the first, if ok then I execute only the second.

BEGIN EXECUTE IMMEDIATE ('CREATE USER newDBUser IDENTIFIED BY newDBPassword DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS'); END;

BEGIN EXECUTE IMMEDIATE ('GRANT CREATE SESSION, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE MATERIALIZED VIEW TO newDBUser'); END;

The first line confirms the same exception. Yet the text in the quotes has the same keywords used on SQLPlus.

3) I tried that "pure" text and got the same result:

CREATE USER newDBUser IDENTIFIED BY newDBPassword DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS

java.sql.SQLException: ORA-65096: invalid common user or role name 

      [...]

Caused by: Error : 65096, Position : 12, Sql = CREATE USER newDBUser1 IDENTIFIED BY newDBUser1 DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS, OriginalSql = CREATE USER newDBUser1 IDENTIFIED BY newDBUser1 DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS, Error Msg = ORA-65096: invalid common user or role name

 

      at com.oracle.database.jdbc/oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:637)

      ... 13 more

So the only way was to look for this famous ORA-65096 and from here:

https://stackoverflow.com/questions/33330968/error-ora-65096-invalid-common-user-or-role-name-in-oracle

4) thanks to that "Position: 12" I changed the username by adding the prefix c## and tried the 2 "pure" SQL texts ... everything ok! Console:

New Database user c##newDBUser created

5) To use the original createUserSQL + the prefix c## for the username you must use

statement.execute(createUserSQL);

instead of

statement.executeQuery(createUserSQL);

because it doesn't recognize it as a Query.

 

*Conclusions*

For completeness of information: with the new user I can create/read a new table with Eclipse.

On SQLPlus to connect as c##newDBUser is used

SQL> connect c##newDBUser/<passwordDBUser>@localhost:1521/XE;

Attention: XE not XEPDB1.

The question remains: is it correct to use a "container user" aka "common user" instead of a "pluggable user" aka "local user"?