Getting table from SQL failure

Question

I have many queries which I am executing in my JAVA application. I am getting exceptions because of failures in SQL queries. I want to show the table name in the alert message on my Web Application after getting exception from SQL query. Is there any way to get the table name for which I got the exception. Thanks in advance.

And I asking about any kind of SQLException that comes because of some table. I want to get the table name just below to System.out.println("exception1:::" + e); line.

Code:

        try {
                      System.out.println("DB     File : "+fileName);
                  fileData = readFile    (fileName);
                  String[] staticProperties = fileData.toString().split("\n");

              ipAddress = staticProperties[Constants.IP];
              port = staticProperties[Constants.PORT];
              datasource_name = staticProperties[Constants.DATASOURCE];

              Hashtable<String, String> env = new Hashtable<String, String>();
              env.put(Context.INITIAL_CONTEXT_FACTORY, Constants.JNDI_FACTORY);
              env.put(Context.PROVIDER_URL, "t3://" + ipAddress + ":"+port);
              datasource = (DataSource) new InitialContext(env).lookup(datasource_name);

              if (datasource != null) {

                    connection = datasource.getConnection();
                    statement = connection.createStatement();

                    rset = statement.executeQuery(query);
                    ResultSetMetaData  rsetMetaData = rset.getMetaData();

                    while (rset.next()) {
                        dataFromDB = new ArrayList<String>();
                           for (int i = 1; i <= rsetMetaData.getColumnCount(); i++) {

                                  dataFromDB.add(rset.getString(i));
                           }
                           inputDataFromDB.put(rset.getRow(), dataFromDB);
                    }
              } 
       } catch (SQLException e) {
              System.out.println("exception1:::" + e);
              throw new SQLException(e);
       } catch (Exception e) {
              System.out.println("exception2:::" + e);
       } finally {


              if (rset != null) {
                    try {
                           rset.close();

                    } catch (Exception e) {
                           System.out.println("exception:::" + e);
                    }
              }
              if (statement != null) {
                    try {
                           statement.close();

                    } catch (Exception e) {
                           System.out.println("exception:::" + e);
                    }
              }
              if (connection != null) {
                    try {
                           connection.close();

                    } catch (Exception e) {
                           System.out.println("exception:::" + e);
                    }
              }

       }

Answer 1

I understand you're wanting to extract he table name from an sql error message, and NOT how to get the sql to run. However, there are very few instances, if any you will be able to do that (at least without extreme effort and access to system tables - and maybe not even then). The basic issue you face being that Oracle issues error messages at the statement not the table level. Consider the following: (these all were run in SQL Developer, similar messages occur is SQL Plus, I suggest you try running the same through Java and see those results)

create table table_that_exists ( te_id integer, description varchar2(200) ); 
/*
Table TABLE_THAT_EXISTS created.
*/

insert into able_that_exists( id, description) values (1, 'test1') ;
/*
Error starting at line : 6 in command -
insert into able_that_exists( id, description) values (1, 'test1') 
Error at Command Line : 6 Column : 13
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

The lines "Error starting ... Error Report" are added by my development environment. 
I'm not all that familier with Java or what ever you useing to connect. But I would 
guee hat all you'll ge back is:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"

*/

insert into table_that_exists( id, description) values (1, 'test1') ;
/*
Error starting at line : 18 in command -
insert into table_that_exists( id, description) values (1, 'test1') 
Error at Command Line : 18 Column : 32
Error report -
SQL Error: ORA-00904: "ID": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:


Again I would guess all you'll get back in Jave is
SQL Error: ORA-00904: "ID": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*/

insert into table_that_exists( te_id, description) values (1, 'test1') ;
/*
1 row inserted.

No message returned to Java (?)
*/
select * from table_that_exists where te_id = 2; 
/* 
   results in an 'empty' result' set. IE the values returned for td_id, description is NULL, NULL
   but no error generated

No message returned to Java (?)  
*/

-- finally:
select * 
  from table_that_exists         te
  join table_tha_does_not_exist  tne
       on te.te_id = tne.te_id ;

/*
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
Error at Line: 44 Column: 8


Even my 'nice friendly' development environment doesn't tell me which table. 
NOTE: You will get the same message if all tables do exist but you have not been granted access.
*/