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

Question

when i was trying to read data from Oracle database using the following code i was getting exception

ResultSet res=stmt.executeQuery("select * from food");

But this table is actually exist in my database when i use this command directly in the command prompt its working fine.And also for one table among the tables in database this code is working fine ,but for other table names its not working properly.So someone please explain why this is happening.

java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193) at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:852) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275) at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1477) at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:392) at connecttooracle.ConnectOracle.main(ConnectOracle.java:67)

I am accessing data from food table by Toad . THen why am I getting this error in java ?

My full code is :

public class ConnectOracle { public static void main(String[] args) { String driver = "oracle.jdbc.driver.OracleDriver"; // String serverName = "10.11.201.84"; String portNumber = "1521"; String db = "XE"; String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + db; // connectOracle is the data // source name String user = "ORAP"; // username of oracle database String pwd = "ORAP"; // password of oracle database Connection con = null; ServerSocket serverSocket = null; Socket socket = null; DataInputStream dataInputStream = null; DataOutputStream dataOutputStream = null; try { Class.forName(driver);// for loading the jdbc driver System.out.println("JDBC Driver loaded"); con = DriverManager.getConnection(url, user, pwd);// for // establishing // connection // with database Statement stmt = (Statement) con.createStatement(); serverSocket = new ServerSocket(8888); System.out.println("Listening :8888"); while (true) { try { socket = serverSocket.accept(); System.out.println("Connection Created"); dataInputStream = new DataInputStream( socket.getInputStream()); dataOutputStream = new DataOutputStream( socket.getOutputStream()); System.out.println("ip: " + socket.getInetAddress()); // System.out.println("message: " + // dataInputStream.readUTF()); ResultSet res=stmt.executeQuery("select * from food"); while(res.next()){ System.out.println(res.getString(1)); } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } if (dataInputStream != null) { try { dataInputStream.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if (dataOutputStream != null) { try { dataOutputStream.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } }

if table is exist on your db, then check your db connection in your app — Mohammadreza Khatami
– Mohammadreza Khatami, Commented Apr 28, 2016 at 10:11
have you check your db connection info ?! maybe you have wrong info to connect or you have multiple connections, for example one connection to oracle and another connection to mysql ! — Mohammadreza Khatami
– Mohammadreza Khatami, Commented Apr 28, 2016 at 10:16

Y.E. · Accepted Answer · 2016-04-28 10:37:31Z

12

If your table is located under schema A:

select * from A.food

EDIT

If you can login via TOAD with user ORAP and execute the same query (select * from food) then you definitely have the table in ORAP schema. I see no reason for "select * from ORAP.food" to fail.

edited Apr 28, 2016 at 10:37

answered Apr 28, 2016 at 10:18

Y.E.

9328 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

osimer pothe Over a year ago

I have tried it . ResultSet res=stmt.executeQuery("select * from XE.food"); But I am getting the same error .

Y.E. Over a year ago

XE is your database name. use "ORAP.food"

osimer pothe Over a year ago

What are the problems ?

Y.E. Over a year ago

Your table is probably located under a different schema. You said you can login with TOAD, so log in and see your table's schema then change the query accordingly. or try this query on TOAD to see schema name if you have the privilage : select owner from all_tables where table_name='food'

osimer pothe Over a year ago

I have tried this query . select owner from all_tables where table_name='food' .But do not get anything .

|

Pran Kumar Sarkar · Accepted Answer · 2019-06-12 18:20:47Z

Some of the possible solutions are:

You can create connection in the database by the schema under which database is located.

For example: My original database has admin SYSTEM and then I created an user Toad and under this Toad schema I have created the table Food.

So I will create connection to the database by:

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "Toad", "PasswordOfToad");

Then the query ResultSet res = stmt.executeQuery("select * from Food"); will work fine.

Alternatively you could specify the table by SchemaName.DatabaseName, which in this case is Toad and Food respectively.

So the query would look like:

ResultSet res = stmt.executeQuery("select * from Toad.Food");

Aliuk · Accepted Answer · 2020-01-27 08:20:58Z

2

I had this error and the solution was to execute:

grant all on <schema>.<table> to <user>; grant all on <schema>.<sequence> to <user>;

answered Jan 27, 2020 at 8:20

Aliuk

1,4173 gold badges22 silver badges36 bronze badges

1 Comment

everis Over a year ago

Thank you. This was exactly my problem. I forgot that we have different DB user roles for app. So I was confused why my sql query works in dbeaver but not in java app.

ChrisMM · Accepted Answer · 2020-01-19 21:44:20Z

1

I had the same problem and something like this works for me:

ResultSet res=stmt.executeQuery("select * from \"food\"");

edited Jan 19, 2020 at 21:44

ChrisMM

10.1k19 gold badges41 silver badges60 bronze badges

answered Jan 19, 2020 at 21:00

Sandra Kuc

111 bronze badge

Comments

vssk · Accepted Answer · 2016-04-28 10:27:52Z

0

Read this. There are all reasons ans solutions.

answered Apr 28, 2016 at 10:27

vssk

4734 silver badges7 bronze badges

Comments

dhruti · Accepted Answer · 2016-04-28 10:46:34Z

0

Because of this CAUSE,u found this exception. You tried to execute a SQL statement that references a table or view that either does not exist, that you do not have access to, or that belongs to another schema and you didn't reference the table by the schema name.

answered Apr 28, 2016 at 10:46

dhruti

303 bronze badges

3 Comments

osimer pothe Over a year ago

I have checked all this but nothing happen .

dhruti Over a year ago

Can you please elaborate your problem so i can give the solution.

dhruti Over a year ago

use your scemaName.tableName in query

David · Accepted Answer · 2016-04-28 11:51:14Z

Follow these steps

First login to TOAD using same username and password. If it gives error then your credentials are wrong
If you are able to login successfully then try this query: SELECT table_name, owner, tablespace_name FROM all_tables;
You can see your table name is present or not using above query

user10314212 · Accepted Answer · 2019-01-03 10:24:34Z

I also faced the same issue, Resolution is:

Open the hibernate.cfg.xml file
Add your POJO class using

Also check, true update

Community · Accepted Answer · 2020-06-20 09:12:55Z

This will solve the problem:

Open the hibernate.cfg.xml file

Check the mapping properly

<mapping class="com.xyz.abc.java"/>

Also make sure below line:

<property name="hibernate.hbm2ddl.auto">update</property>

Aliuk · Accepted Answer · 2019-04-12 11:14:11Z

0

In my case, I solved the problem executing:

grant all on <schema>.<table> to <user>;

answered Apr 12, 2019 at 11:14

Aliuk

1,4173 gold badges22 silver badges36 bronze badges

Comments

Prakash BM · Accepted Answer · 2019-10-01 18:32:17Z

You should mention Schema.table For Example Schema name=xyz then the query would be:select * from xyz.food Try this, this will work

Yash · Accepted Answer · 2020-01-03 12:33:06Z

I have face the same problem but resolved using grant privilege,

when i run standalone application using the following query their is no error:

String sql = "select * from SCHEMA_NAME.TABLE_NAME"; Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql);

But when i run through a web-based application, As each user has different roles with permissions. If user doesn't have particular SELECT permission then it leads to the ERROR.

For TABLES if the USER_ROLE does not have an select permission. Then leads to the following error as ORA-00942: Table or view does not exist:

GRANT (Transact-SQL) - ADD SELECT privilege for tables:

GRANT SELECT ON SCHEMA_NAME.TABLE_NAME TO "USER_ROLE"; GRANT SELECT, INSERT, UPDATE ON SCHEMA_NAME.TABLE_NAME TO "USER_ROLE"; GRANT SELECT, DELETE ON SCHEMA_NAME.TABLE_NAME TO "USER_ROLE";

Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: Table or view does not exist at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193) at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:873) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289) at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1491) at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)

For Stored procedure if the USER_ROLE does not have an execute permission. Then leads to the following error as Internal Error: Inconsistent catalog view

ADD EXECUTE privilege for Stored Procedure/Functions:

GRANT DEBUG ON SCHEMA_NAME.PROCEDURE_NAME TO "USER_ROLE"; GRANT EXECUTE ON SCHEMA_NAME.PROCEDURE_NAME TO "USER_ROLE";

java.sql.SQLException: Internal Error: Inconsistent catalog view at oracle.sql.StructDescriptor.initMetaData1_9_0(StructDescriptor.java:1420)

Vikram · Accepted Answer · 2022-07-29 16:50:32Z

Just add schema in @Table annotation on Entity class.

@Table(name = "tableName", schema = "schemaName")

Smart Coder · Accepted Answer · 2022-12-12 22:20:02Z

Check the entity class fields and actual table columns matches in number and name. This was the case in my case. Also in other environment, I had to rerun the liquibase script in uppercase table name. Somehow the same issue didn't happen in another environment regards to the case of table name.

Collectives™ on Stack Overflow

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

14 Answers 14

11 Comments

Comments

1 Comment

Comments

Comments

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

14 Answers 14

11 Comments

Comments

1 Comment

Comments

Comments

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Related