Solved

Extended error info - oracle JDBC driver

Posted on 2004-10-08
13
520 Views
Last Modified: 2008-01-09
Hi,

I am writing a simple Servlet that is being hosted on Apache Tomcat. I am using JDBC with the official Oracle Thin JDBC client driver to connect to an oracle 9i or greater database. Most of the time I use the standard JDBC classes, but the Oracle driver does provide some of its own extension classes (such as 'OracleResultSet').

Anyway, all i want to know is how to get the extended error information that oracle can return about SQL problems. For example, before this application, i was using perl with the DBI module. Whenever I got an SQL error, I could dump the entire SQL with the location of the problem highlighted with <*>. (this was all done automatically). So far with JDBC, I have only been able to get a simple 1-line error message such as "ORA-XXXX: Inserted data is too large for field". Is there a way, using standard JDBC or the oracle extensions, to get extended error information such as what I described above??

Thanks,
Robert.
0
Comment
Question by:headexplodes
  • 7
  • 6
13 Comments
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12256903
Hi,

What other error information would you expect in your example? This is the same level of error information made available through SQLPlus...

SQL> create table test( a varchar2(1) );

Table created.

SQL> insert into test(a) values( 'fail' );
insert into test(a) values( 'fail' )
                            *
ERROR at line 1:
ORA-01401: inserted value too large for column


Cheers,
C.
0
 

Author Comment

by:headexplodes
ID: 12256913
thanks for your reply.

ok bad example :p. If there's a problem such as a missing table, with DBI i recieved a large dump of the SQL with a <*> right where the error occured. This is the kind of extended error i'm after. I dont know exactly what stage in the process the <*> was added, but i know tools like Golden (for windows) are able to tell you exactly where the error occured. Any extra info such as even just the position in the sql script would be a start.

0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12256988
Hi,

If the exception is caused by a call to a procedure/function, then the ORA errors returned will indicate the line that caused the error, e.g.

SQL> create table test( a varchar2(1) );

Table created.

SQL> create procedure test_proc
  2  as
  3  begin
  4  insert into test(a) values( 'fail' );
  5  end;
  6  /

Procedure created.

SQL> call test_proc();
call test_proc()
     *
ERROR at line 1:
ORA-01401: inserted value too large for column
ORA-06512: at "CONORC.TEST_PROC", line 4


the standard JDBC exception will display similar ORA error trace. Its always better to avoid downcasting to Oracle specific classes if possible - the less Oracle specific code you have means the more portable your application is...

Cheers,
C.
0
 

Author Comment

by:headexplodes
ID: 12257027
so would both those ORA-xxx errors be chained together as JDBC SQLExceptions? Also, would the same thing happen if the error was just in an SQL script that you'd sent? Unfortunatley its friday arvo now so I wont be able to test it out until monday. Thanks again!
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12257070
not sure about the chained errors... generally I catch and log the entire exception (use Log4J) and this is part of what comes out... also, regarding anonymous SQL, it seems to report the correct line (just tried a simple anonomyous piece of SQL through a Java tool (DBVis) and it did report the correct line in the ORA error...

Cheers,
C.
0
 

Author Comment

by:headexplodes
ID: 12258833
Ok, here's a specific example i managed to replicate on a similar server configuration.

If i throw a simple SQL with a table name that doesnt exist i recieve an exception:

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

Unfortunatley, had this been a large SQL i would have absolutley no idea which table or view was causing the problem, as it doesnt seem to state the name of the missing table or the location of the error. I checked for chained exceptions and i dont think there were any. Is there a way to find out where in the SQL the error occured in this case?

Thanks a heap! (if i had more points available i'd give u more!)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Expert Comment

by:cjjclifford
ID: 12296212
Using your example, no, there is no way (that I know of at least) to do this... the best you can get when there is a lot of SQL is down to the line of the code that the problematic statement is on (see my example above). This is one of the (many) things about Oracle errors I dislike - they are generally static, i.e. not listing the bad table name, or column name, etc...
0
 

Author Comment

by:headexplodes
ID: 12296282
The example you have above:

---
SQL> call test_proc();
call test_proc()
     *
ERROR at line 1:
ORA-01401: inserted value too large for column
ORA-06512: at "CONORC.TEST_PROC", line 4
---

... is using a stored procedure. I'm not doing anything to do with stored procedures, and i'm not even sure if i can put on in the database just to test it. Can i get a similar error (the one stating the line) if I had submitted a large SQL script? I dont have access to sqlplus at the moment so i cant try it. If you could give me an example of a simple SQL that returns an ORA-wateva error stating the line that'd be great! Then i can possibly replicate it (once i know that the error should be available) and then maybe find out how to extract it from JDBC.

Thanks for u help!

0
 
LVL 11

Accepted Solution

by:
cjjclifford earned 500 total points
ID: 12296389
SQL> create table test(a varchar2(1) );  

Table created.

SQL> begin
  2  insert into test(a) values( '1' );
  3  insert into test(b) values( '2' );
  4  end;            
  5  /
SQL> begin
  2  insert into test(a) values( '1' );
  3  insert into test(a) values( '2' );
  4  insert into test(a) values( 'fail' );
  5  end;
  6  /
begin
*
ERROR at line 1:
ORA-01401: inserted value too large for column
ORA-06512: at line 4

I've used SQLPlus, but you could use JDBC to fire the same SQL:

PreparedStatement pstmt = conn.prepareStatement(
"BEGIN \n" +  // Newline to force multiple lines...
"insert into test(a) values( '1' );\n" +
"insert into test(a) values( '2' );\n" +
"insert into test(a) values( 'fail' );\n" +
"END;" );

// etc...
0
 

Author Comment

by:headexplodes
ID: 12296395
will try now.. thanks.
0
 

Author Comment

by:headexplodes
ID: 12296517
I tried ur PLSQL example and I did recieve a second error, just like urs, that states the line that it failed on. However, when i converted that into just one SQL statement (only the 'fail' line), and split it over multiple lines of SQL, i didnt recieve any extra error telling me which line died. I'm starting to think its just not possible. Just so u know i'm not mad, here are the types of errors I was able to get thru Perl with DBI and DBD::Oracle:

select * from NONEXIST_TABLE

SQL ERROR: ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 14 in 'select * from <*>NONEXIST_TABLE')

I'd love to know how the DBD driver was able to fogure out where the error was, but I think the info just isnt is available in JDBC :(

Thanks muchly for ur help. Unless anyone comes up with a magical solution soon i'll finalize this thingeo and u'll get all mi points!
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12296623
sorry I wasn't more help... btw, splitting a single SQL statement over multiple lines is still a single "line" of code...
 
The DBD error looks to me like the DBD driver is parsing the statement itself, and attempting to extract the error, above and beyond the actual ORA error... unfortunately the JDBC driver doesn't do this, as far as I know...

you may have some luck using the Oracle specific code, but I still don't believe the exact information you are looking for is available:

// code put together using 9201 Oracle JDBC JavaDoc
import oracle.jdbc.driver.OracleSQLException;

...
..

catch( SQLException sqle ) {
    if( sqle instanceof OracleSQLException ) {
        Object[] params = ((OracleSQLException)sqle).getParameters();
        for(int i = 0; i != params.length; i++ ) {
            // just display for now, the error code, and further details might be avaible...
            System.out.println( "Parameter " + i + " :  " + params[i] );
        }
    }
}

Cheers,
C
0
 

Author Comment

by:headexplodes
ID: 12305228
Oh well. I mite try asking around at oracle somewhere.  I still believe DBD::Oracle had a way of finding out from the database where the errror occured, since it was always able to pick up even tables that I didnt have permission to view. there must be a way! (probably not, but i'll keep looking).

 Thanks for all ur help!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now