ajbufort
asked on
Bizarre Oracle Metadata Problem When Using 'conn.getMetaData()' in Java
Hello,
I wrote a db-class auto-generator for my company that creates classes corresponding to tables in our Oracle database (we were on 9i, but recently moved to 9.2). The utility worked just fine for quite a while (in 9i), and then all of a sudden we experienced a corruption in the metadata it was seeing. We were still using 9i then, so the problem has nothing to do with a version change. Our dba swears that there were no changes during the period in which the metadata corruption began, and the corruption only seems to be visible when using the conn.getMetaData() method; making similar requests for the metadata in SQL*Plus, for example, yields the correct data, and the tables are showing up fine in TOAD.
The exact nature of the corruption involves the repetition of tables and fields. When I use the auto-generator, some tables have classes generated for them numerous times, because the metadata is telling the utility that certain tables exist numerous times. Also, it is interesting to note that the fields within the auto-generated classes get generated as many times as the classes themselves do. So, for example, if the metadata that the util received said that there were three TABLE_CUSTOMER tables, and that class got generated three times, the final file would have members that look like this:
protected String _OBJID = null;
protected String _ADDRESS = null;
protected String _ADDRESS_2 = null;
protected String _CITY = null;
protected String _OBJID = null;
protected String _ADDRESS = null;
protected String _ADDRESS_2 = null;
protected String _CITY = null;
protected String _OBJID = null;
protected String _ADDRESS = null;
protected String _ADDRESS_2 = null;
protected String _CITY = null;
As you can imagine, this results in a real mess.
When we finally moved to 9.2, we saw the same corruption, but on a smaller scale, generally speaking. Now, I am only seeing 6 tables repeat instead of most or all of them (100+).
Is there something our DBA needs to do (or not do)? What totally puzzles me is that this situation supposedly arose all of a sudden, with nothing changing on either the Java or Oracle ends. I can speak to the Java side of things and say that we didn't swicth JSDKs or JDBC drivers or anything like that. I find it hard to believe, however, that SOMETHING did not change on the Oracle side to provoke this. BUT... it IS only showing up when we retrieve the metadata from Java, making the theory that Java is somehow involved look tempting.
I am sure that with the severity of the symptoms we are experiencing that someone has run into and dealt with this before. I have a feeling the answer is subtle yet simple, but we have yet to discover it.
PLASE HELP!
Thanks In Advance,
Anthony
I wrote a db-class auto-generator for my company that creates classes corresponding to tables in our Oracle database (we were on 9i, but recently moved to 9.2). The utility worked just fine for quite a while (in 9i), and then all of a sudden we experienced a corruption in the metadata it was seeing. We were still using 9i then, so the problem has nothing to do with a version change. Our dba swears that there were no changes during the period in which the metadata corruption began, and the corruption only seems to be visible when using the conn.getMetaData() method; making similar requests for the metadata in SQL*Plus, for example, yields the correct data, and the tables are showing up fine in TOAD.
The exact nature of the corruption involves the repetition of tables and fields. When I use the auto-generator, some tables have classes generated for them numerous times, because the metadata is telling the utility that certain tables exist numerous times. Also, it is interesting to note that the fields within the auto-generated classes get generated as many times as the classes themselves do. So, for example, if the metadata that the util received said that there were three TABLE_CUSTOMER tables, and that class got generated three times, the final file would have members that look like this:
protected String _OBJID = null;
protected String _ADDRESS = null;
protected String _ADDRESS_2 = null;
protected String _CITY = null;
protected String _OBJID = null;
protected String _ADDRESS = null;
protected String _ADDRESS_2 = null;
protected String _CITY = null;
protected String _OBJID = null;
protected String _ADDRESS = null;
protected String _ADDRESS_2 = null;
protected String _CITY = null;
As you can imagine, this results in a real mess.
When we finally moved to 9.2, we saw the same corruption, but on a smaller scale, generally speaking. Now, I am only seeing 6 tables repeat instead of most or all of them (100+).
Is there something our DBA needs to do (or not do)? What totally puzzles me is that this situation supposedly arose all of a sudden, with nothing changing on either the Java or Oracle ends. I can speak to the Java side of things and say that we didn't swicth JSDKs or JDBC drivers or anything like that. I find it hard to believe, however, that SOMETHING did not change on the Oracle side to provoke this. BUT... it IS only showing up when we retrieve the metadata from Java, making the theory that Java is somehow involved look tempting.
I am sure that with the severity of the symptoms we are experiencing that someone has run into and dealt with this before. I have a feeling the answer is subtle yet simple, but we have yet to discover it.
PLASE HELP!
Thanks In Advance,
Anthony
Hello ajbufort,
I concurr with the former advice given, please post the version your JDK/JRE returns and try to determine if it changed.
Your problem looks alot like having to do with JDBC though SQL*Plus and TOAD (not relying on JDBC) give the correct data. There have been many changes on the JDBC side, it changed from version 2.0 (9i at its beginning) to version 3.0 (JDK 1.4). Did you ever try to download the newest JDBC-driver from Oracle? Might be that this'll help.
Anyway, deriving from your description my thinking goes to another direction, too. Would you do a kind of test for me? It is assuming that there might be a bug in your self-written software... The scheme made me think of it:
When you are writing of "3 times called, 3 times repeating data" I'd like you to modify the software so that every class you're using is created on scratch as it is needed (not being reused any more). No reuse, even the connection should be closed and re-opened (to perhaps prevent you from JDBC-bugs). You should even "null static variables", so that they will be reasigned for new usage. Testing-sytems do not necessarily mean to be systems with great performance :-). This way you might perhaps determine a not seen software-bug (which only occurs in some "new" configurations).
Hoping-not-to-sound-like-a -dumb-teac her :^)
jMelch
I concurr with the former advice given, please post the version your JDK/JRE returns and try to determine if it changed.
Your problem looks alot like having to do with JDBC though SQL*Plus and TOAD (not relying on JDBC) give the correct data. There have been many changes on the JDBC side, it changed from version 2.0 (9i at its beginning) to version 3.0 (JDK 1.4). Did you ever try to download the newest JDBC-driver from Oracle? Might be that this'll help.
Anyway, deriving from your description my thinking goes to another direction, too. Would you do a kind of test for me? It is assuming that there might be a bug in your self-written software... The scheme made me think of it:
When you are writing of "3 times called, 3 times repeating data" I'd like you to modify the software so that every class you're using is created on scratch as it is needed (not being reused any more). No reuse, even the connection should be closed and re-opened (to perhaps prevent you from JDBC-bugs). You should even "null static variables", so that they will be reasigned for new usage. Testing-sytems do not necessarily mean to be systems with great performance :-). This way you might perhaps determine a not seen software-bug (which only occurs in some "new" configurations).
Hoping-not-to-sound-like-a
jMelch
ASKER
Guys,
Thanks for your input. I know for a fact that my JDK did not change, as I am in full control of it. I am developing on a Windows NT system and my JDK is on my hard drive, not on a server where some admin could have fiddled with it. What kills me is that, with this selfsame JDK (1.3.1_02) and 9i, things were working just dandy for a long time. Then, without either changing, the auto-generator suddenly broke. If it worked under one set of conditions, and those conditions hadn't changed when it broke... well, you know what I was about to say. So obviously, something MUST have changed. And, speaking for my side, nothing did. Speaking for the DBA, however...? :)
-Tony
Thanks for your input. I know for a fact that my JDK did not change, as I am in full control of it. I am developing on a Windows NT system and my JDK is on my hard drive, not on a server where some admin could have fiddled with it. What kills me is that, with this selfsame JDK (1.3.1_02) and 9i, things were working just dandy for a long time. Then, without either changing, the auto-generator suddenly broke. If it worked under one set of conditions, and those conditions hadn't changed when it broke... well, you know what I was about to say. So obviously, something MUST have changed. And, speaking for my side, nothing did. Speaking for the DBA, however...? :)
-Tony
ASKER
SOLUTION FOUND:
For anyone who cares, here is the answer to my problem. It WAS an Oracle issue!
-------------------------- ---------- ---------- ---------
"Good news everyone, the problem with the metadata is resolved. The problem of retrieving multiple sets of metadata for a single table is resolved if we use the specfic name of the schema that the table is supposed to come from.
Explanation:
The underlying query used by the JDBC driver classes (both Oracle's and Sun's) look at the table ALL_TABLES. Therefore, if there are any grants from any other schemas to the connected user, on the same table, the select will see more than one entries in the ALL_TABLES.
eg. If account tnguyen has DEALERS locally in its schema, and if tnguyen also has a select grant to abufort.DEALERS, then a select to ALL_TABLES would come back with two entries for table DEALERS.
Therefore, since we cannot rely on the getTables() and getColumns() to default to using the ALL_TABLES.owner equal to the User used to instantiate the DatabaseMetaData object, we will need to be specific.
Why eval9i used to work was that there must not have been any cross table grants issued for any users (at least until the very end). When we went to 9.2, we established grants on certain tables as we went along. Hence the randomness of the testing results."
For anyone who cares, here is the answer to my problem. It WAS an Oracle issue!
--------------------------
"Good news everyone, the problem with the metadata is resolved. The problem of retrieving multiple sets of metadata for a single table is resolved if we use the specfic name of the schema that the table is supposed to come from.
Explanation:
The underlying query used by the JDBC driver classes (both Oracle's and Sun's) look at the table ALL_TABLES. Therefore, if there are any grants from any other schemas to the connected user, on the same table, the select will see more than one entries in the ALL_TABLES.
eg. If account tnguyen has DEALERS locally in its schema, and if tnguyen also has a select grant to abufort.DEALERS, then a select to ALL_TABLES would come back with two entries for table DEALERS.
Therefore, since we cannot rely on the getTables() and getColumns() to default to using the ALL_TABLES.owner equal to the User used to instantiate the DatabaseMetaData object, we will need to be specific.
Why eval9i used to work was that there must not have been any cross table grants issued for any users (at least until the very end). When we went to 9.2, we established grants on certain tables as we went along. Hence the randomness of the testing results."
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
- PAQ'd and pts refunded
Please leave any comments here within the
next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !
Nic;o)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
9.2 is a much better version it seems... it solved a few bugs we were experienceing with 9i, although none were with metadata...
good luck
randy