CODEWITHSUNDEEP
javaoracle-databasedocker
Cesar Justo
Cesar Justo

Reputation: 813

I can't visualize a table in an oracle base that is in a container from java

Good afternoon,
I am having a rather strange problem. I cannot visualize from Java a table that is in an Oracle database, mounted with a Docker container. From SQL Developer I can connect and everything normal, I visualize my table, but when doing it from java I get an error that the table is not found:

Main Class:

package BaseDeDatos;

import java.sql.ResultSet;
import java.sql.SQLException;

public class Conexion {

    public static void main(String[] args) {
        MyConexion conexion=new MyConexion();
        ResultSet resultado;
        String cedula,nombre, apellido;

        resultado=conexion.getQuery("Select * from Cliente");

        try {
            while(resultado.next()){
                cedula = resultado.getString("cedula");
                nombre = resultado.getString("nombre");
             //   apellido = resultado.getString("apellido");

                System.out.println("Nombre: "+cedula+"\nRut: "+nombre);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

My configuration to access the database

package BaseDeDatos;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MyConexion {

    private String user = "cesar";
    private String password = "xxxxxxx123";

    static String url = "jdbc:oracle:thin:@//10.164.7.203:1521/ORCLPDB1.localdomain";
    private Connection conn = null;

    public MyConexion() {
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            conn = (Connection) DriverManager.getConnection(url, user, password);

            if (conn != null) {
                System.out.println("Conexion a base de datos " + url + " . . . Ok");
            }
        }
        catch (SQLException ex) {
            System.out.println("Hubo un problema al intentar conecarse a la base de datos" + url);
        }
        catch (ClassNotFoundException ex) {
            System.out.println("Error... " + ex);
        }
    }

    /**
     * Consultas a la Base de Datos.
     * 
     * @param _query
     * @return
     */
    public ResultSet getQuery(String _query) {
        Statement state = null;
        ResultSet resultado = null;
        try {
            state = (Statement) conn.createStatement();
            resultado = state.executeQuery(_query);
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        return resultado;
    }
}

When I try to connect from java:

enter image description here

Performing the query in SQL Developer:

enter image description here

Details of my correct connection with SQL Developer:

enter image description here

When running:

select owner, table_name from all_tables where table_name = 'CLIENTE';
select * from dba_tab_privs where table_name = 'CLIENTE' and privilege = 'SELECT';

enter image description here

What could it be? I do not understand

Upvotes: 0

Views: 491

Answers (2)

Abra
Abra

Reputation: 20913

Oracle errors can sometimes be misleading. You also get this Oracle error code...

ORA-00942: table or view does not exist

when the table does exist but the user does not have permission to access the table.
In other words user cesar does not have access to database table CLIENTE.

By the way, according to what you posted, it appears there are two CLIENTE tables. One in the SYS schema and one in the SYSTEM schema. This Stack Exchange Q&A describes the difference between the schemas and also advises not to create user tables in these schemas. Hence I would recommend creating table CLIENTE in cesar schema. Then you won't get ORA-00942 error. If you can't do that then you need to grant access to table CLIENTE to user cesar like so

GRANT SELECT ON CLIENTE TO cesar

Note that you need to execute the above statement as the owner of the table, e.g. first connect to the database as SYSTEM and then execute the above statement. Remember that user cesar then needs to access the table as SYSTEM.CLIENTE.

select * from SYSTEM.CLIENTE

Upvotes: 2

Julian
Julian

Reputation: 4085

So kfinity's comment should have been pointed you to what really happened. But here is the explanation what I suspect.

You connected to the database as SYSDBA when you created the CLIENTE table. As a result this table is own by the system user. Given this with your SYSDBA connection you are able to see the table content.

The Java application does not connect as SYSDBA but rather as an ordinary user caesar. As a result all your selects running from Java will be executed against caesar schema where of course the table CILENTE does not exist.

To fix this just recreate the table and populate it with data into caesar schema.

Hope it helps.

Upvotes: 0

Related Questions