Skip to Main Content

Oracle Database Express Edition (XE)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle XE 21c database and Eclipse

User_AW11TMay 11 2022

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/demouser@CSHAY-7420:1521/XEPDB1;
and this not:
SQL> connect demouser/demouser@CSHAY-7420.oradev.oraclecorp.com:1521/XEPDB1;
)
---

At this point I thought of using the only string with userName and password in the form:
String dbUrl = "demouser/demouser@CSHAY-7420.oradev.oraclecorp.com: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

Comments

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"?

1 - 5

Post Details

Added on May 11 2022
5 comments
1,625 views