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

Question

I am using Oracle

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 25 20:17:54 2020

Java

java -version
java version "1.8.0_241"
Java(TM) SE Runtime Environment (build 1.8.0_241-b07)
Java HotSpot(TM) 64-Bit Server VM (build 25.241-b07, mixed mode)

jar files

ojdbc14.jar

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

I have created a table in my oracle dbms

SQL> create Table employees ( name varchar(20));
Table created.

I inserted few records

SQL> insert into employees (name) values ('Navjot');

1 row created.

SQL> insert into employees (name) values ('jagjot');


SQL> commit;

Commit complete.

I trying to fetch these records from my java class But it says no such table exist.

BaseDAO.java

import java.sql.*;

    class BaseDAO
    {
        public Connection getConnection()
        {
            String driver="oracle.jdbc.driver.OracleDriver";
            String url="jdbc:oracle:thin:@localhost:1521/xe";
            String user="system";
            String password="system";
            Connection conn=null;
            try
            {
                Class.forName(driver);
                conn=DriverManager.getConnection(url,user,password);
                System.out.println("Connected to oracle database successfully");
            }
            catch(Exception e)
            {
                System.out.println(e);
            }
            return conn;
        }//End of getConnection method
    }//End of BaseDAO class

RetriveAllEmployees.java

import java.sql.*;
public class RetriveAllEmployees extends BaseDAO
{
    public void retrive()
    {
        try
        {
            Connection conn=getConnection();
            Statement stmt=conn.createStatement();
            ResultSet rs=stmt.executeQuery("select * from EMPLOYEES");
            while(rs.next())
            {
                String employeeName=rs.getString("name");
                System.out.println(employeeName);
            }
            
        }
        catch(Exception e)
        {
            System.out.println(e);
        }
    }
    public static void main(String args[])
    {
        RetriveAllEmployees retriveAllEmployees=new RetriveAllEmployees();
        retriveAllEmployees.retrive();
    }
}

When I tried to execute this code it says table does not exist

   E:\aman\java\jdbc\jdbc 1 doc examples\003 connection oracle sql>java -classpath .;C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib\ojdbc14.jar; RetriveAllEmployees
Connected to oracle database successfully
java.sql.SQLException: ORA-00942: table or view does not exist

When I login to oracle through command line

Enter user-name: system
Enter password:system

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> conn sys as sysdba;
Enter password:none
Connected.

It's impossible to establish connection to the DB. Check your connection properties (above all JDBC url) — Angelo Immediata
I don't understand your comment. '"It's impossible to establish connection to the DB. " @AngeloImmediata. As you can see when I complile my cod it says connected to database successfuly. BaseDAO — Aman
In the previous version of the question (before the edit) the error message was: The Network Adapter could not establish connection that's why i wrote that comment — Angelo Immediata

pifor pifor · Accepted Answer · 2020-06-25T15:38:50

You need to check who is the owner of the table: the owner of the table is the user having run CREATE TABLE.

You can access a table without owner prefix only if you connect as table owner (or if a synonym has been created): if the table has been created with SYSTEM user, you need to connect as SYSTEM user - you can with another user but to access the table you need to use SYSTEM.EMPLOYEES and to have SELECT privilege on SYSTEM.EMPLOYEES (which you cannot have by default unless you connect with DBA role or with SYS AS SYSDBA).

ORA-00942 means either table does not exist or you don't have any privileges to access the table.

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

1 Answers