ORA-00942: table or view does not exist

System:


Oracle9i Enterprise Edition Release 9.2.0.6.0


Red Hat Linux 7.3 2.96-110


JChem v3.1.1 (upgrade from 3.0.13)





I'd like to reference user prous, and their topic "Error updating structures table"





I have a similar setup: I have a user JCHEM, in the oracle database,


which is the owner of all JChem objects, structure table and property table.





This user JCHEM does all maintenance on the tables he owns, in his schema, JCHEM....so I don't have the same permission problem on the sequence that user prous had.





I used jcman to regenerate the structure table, after installing jchem3.1.1, and when it finished, a window popped up with the message:





SQL Warning


SQL State:42000


Message ORA-00942: table or view does not exist


Vendor: 942





Some background:


Like user prous, I also have a java application which maintains the structure table. It's kicked off with a shell script on the Linux box. It updates the JCHEM.STRUCTURE table when we add a new structure to


an MDL database, external to JCHEM.





To test whether a JChem upgrade, and the subsequent regeneration works, I usually delete a few structures from the existing JCHEM.STRUCTURE table, start the script that runs the java, and those deleted structures are found in the MDL database and added back to the


JCHEM.STRUCTURE table.





Back to the error:


I checked after the upgrade, and noticed no missing tables...I deleted a few structures from JCHEM.STRUCTURE, and ran my script, to see if that might shed more light on this error message. As with user prous, the deleted structures were imported correctly into the JCHEM.STRUCTURE table, but I got another ORA-000942:





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





at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)


at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)


at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)


at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)


at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)


at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)


at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)


at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)


at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:854)


at chemaxon.jchem.db.UpdateHandler.storeUpdate(UpdateHandler.java:1795)


at chemaxon.jchem.db.UpdateHandler.saveUpdateAndGetUpdateCounter(UpdateHandler.java:1784)


at chemaxon.jchem.db.UpdateHandler.close(UpdateHandler.java:1753)


at chemaxon.jchem.db.FileToSQLHandler.importFile(FileToSQLHandler.java:317)


at chemaxon.jchem.db.Importer.importMols(Importer.java:423)


at com.ctiseattle.data.tools.impexp.structure.MolImporter.execute(Unknown Source)


at com.ctiseattle.data.tools.impexp.structure.MolImporter.main(Unknown Source)


import failed: ORA-00942: table or view does not exist





0 structure(s) imported





There were actually 4 structures imported back into the table.





What is happening here? I'm unable to locate any error. Any help would be appreciated!


Thanks!

Hi,





It seems that the log tables (introduced in JChem 3.1.1) are missing from your database.





Please read more on these tables at the following topic:





http://www.chemaxon.com/forum/ftopic879.html





So for JChem 3.1.1 there must be present a <table_name>_UL log table.





Please start the jcman GUI, it should automatically offer you to create these missing tables.


Please make sure you start the new (3.1.1) version of jcman, and you connect with the appropriate connection parameters.





Let me know if this helps.





Best regards,





Szilard

Thanks Szilard,





The jcman GUI did not automatically create the tables when I first ran it (it did not even notify me that my JCHEM.STRUCTURE table needed regenerating, but I know how to do that thru the menu).





This morning, I tried again - Help-About displayed 3.1.1 as the version, so I am using the correct jcman. I believe the connection parameters are correct, since the table did get regenerated properly.





Is there a way to manually generate the tables? I did not see anything in the jcman options, except for changing the number of update logs to keep...?





Best regards,


Julie

Hi,

Quote:

The jcman GUI did not automatically create the tables when I first ran it (it did not even notify me that my JCHEM.STRUCTURE table needed regenerating, but I know how to do that thru the menu).

This is very strange indeed that the GUI neither wanted to create tables or regenerate them.





Is it possible that that multiple JChem versions are present in the CLASSPATH at the same time ?





Please make sure there is no jchem.jar (or any chemaxon .jar file or directory with chemaxon class files) in your CLASSPATH.





Also it would be helpful if you could check in Oracle the following:


- what is the table version in the property table (e.g. JCHEMPROPERTIES) for the table "STRUCTURE" (look for "table.JCHEM.STRUCTURE.version").


- does the JCHEM.STRUCTURES_UL table exist ?

Quote:

Is there a way to manually generate the tables?

That wouldn't be too difficult, however this would just cover up solution for the problem. I suggest to get to the bottom of this to make sure everything is working well.





Best regards,





Szilard

Well, once or twice before, I have started up jcman after an upgrade of the Jchem base software, and I was not prompted to regenerate the STRUCTURE table. I used the menu, and everything worked fine. I do not recall anything different or unusual happening in those particular upgrades before calling jcman.





Our classpath is simply C:\Oracle9i\jdbc\lib\ojdbc14.jar, where I am running the jcman from. Whatever workstation I am on, it points only to an ojdbc14.jar.





In the JCHEMPROPERTIES table itself, I find:





table.JCHEM.STRUCTURE.version 24





The JCHEM.STRUCTURES_UL table does not exist.





Thanks,


Julie

Hi,

Quote:

The JCHEM.STRUCTURES_UL table does not exist.

Sorry, I misspelled it, I intended to ask about "JCHEM.STRUCTURE_UL" (same as the original table but with an _UL at the end), but I assume this doesn't exist, right ?





To tell the truth, this problem really puzzles me.





Could you temporarily replace your jcman.bat with the attached one and send me the console output that is generated during the GUI startup, so I can see some startup options ?





(please rename the file, so it won't end with .txt - it was required for the forum engine)





Best regards,





Szilard

Hi,





Yes, I understood that was a typo - the table STRUCTURE_UL does not exist either.





I ran the jcman.bat that you sent, and noticed the JCHEMCLASSPATH did have the jchem.jar in it while the CLASSPATH did not...here is the output on the console:


JCHEMHOME:


system CLASSPATH: C:\Oracle9i\jdbc\lib\ojdbc14.jar


JCHEMCLASSPATH:


javaopts: -client -Xmx200m -classpath "..\lib\jchem.jar;C:\Oracle9i\jdbc\lib\ojd


bc14.jar"





After that, once I login, I get the same "java.sql.SQLException: ORA-00942: table or view does not exist"


dump that I sent you before.





Hope this helps,


Julie

Julie,

Quote:

After that, once I login, I get the same "java.sql.SQLException: ORA-00942: table or view does not exist" dump that I sent you before.

The stack trace probably not exactly the same, since the previous one was thrown by JChem running inside your application, this one from jcman. Also there is no import here yet either - as I understand it's right after you press OK in the connection dialog, right ?





Could you send this (full) stack trace too ?





Thanks,





Szilard

Yes it happens right after hitting OK. Sorry I didn't include it before - was trying not to clutter the thread:





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





at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)


at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)


at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)


at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)


at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:


830)


at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.jav


a:2391)


at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme


nt.java:2672)


at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:


572)


at chemaxon.jchem.db.TableInfo.containsHashColumn(TableInfo.java:1139)


at chemaxon.jchem.gui.Connector.addHashCodeColumnIfNeeded(Connector.java


:254)


at chemaxon.jchem.gui.Connector.go(Connector.java:126)


at chemaxon.jchem.gui.JChemFrame.connect(JChemFrame.java:561)


at chemaxon.jchem.JChemManager.init(JChemManager.java:89)


at chemaxon.jchem.JChemManager.main(JChemManager.java:106)

Hi,





I think I have figured out what can be wrong.





You have a reference in your property table to a non-existing JChem table, which was possibly deleted by hand.


At the startup JChem tries to determine the status of this table, but it doesn't exist. The resulted exception prevents the upgrade process from going on, that's why jcman doesn't offer regeneration or creating of log tables.





If I'm right the stack trace will be something like this:





(I've just received your answer at this point, so won't repeat the stack trace, which was the same for me)





Please remove the references from your property table to non-existing JChem tables, and jcman should work fine at startup, and will create the log table(s) for you.


Let me know if this helps.





Please only delete JChem tables or other DB objects by jcman (or via our API) in the future, so the property table will remain consistent.





I will try and make the code less sensitive for such problems, so this very important upgrade process can complete for the other tables regardless.





Best regards,





Szilard

Thank you Szilard!!





There was a view we created in the JCHEM schema that joined to the STRUCTURE table....we only created it in the test environment (where I am working now), and never went forward with it. Every time I started the jcman, it would complain about that view - we ignored it, as that view was obsolete (but not yet deleted). I finally just deleted that view, and eliminated the complaints from jcman. I had not known that its join with the STRUCTURE table had caused entries to be placed in the JCHEMPROPERTIES table.





Removing those entries in JCHEMPROPERTIES worked like a charm! The update tables were automatically generated by jcman. And our java script cleanly updates the STRUCTURE table without any exceptions.





I appreciate your help and your staying late to fix this for me!





Best regards,


Julie