search

schemacrawler / SchemaCrawler

Free database schema discovery and comprehension tool
http://www.schemacrawler.com/
Other
1.62k stars 200 forks source link

schemacrawler crashes on Oracle - java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator #343

Closed everflux closed 4 years ago

everflux commented 4 years ago

Bug Report

Issue

Please explain the issue briefly

Debug log: debug.log.gz

Environment

sualeh commented 4 years ago

@everflux - Internally, SchemaCrawler uses REGEXP_LIKE, and I am not sure if that is supported on Oracle 9i. Thomas, please rename (or preferably move out) lib/schemacrawler-oracle-16.9.2.jar, and try again. Once you move this jar out of the CLASSPATH (that is, the lib folder), you will find that SchemaCrawler will not support --server=oracle --host=10.23.15.212 --port=1521 --urlx=oracle.jdbc.timezoneAsRegion=false --database=WASS. Instead, you will need to use something similar to --url=jdbc:oracle:thin:@//10.23.15.212:1521/WASS. (You may need need to modify the URL based on the Oracle 9i JDBC driver documentation.)

everflux commented 4 years ago

Thanks for the swift reply! When I remove the plugin from the classpath, schemacrawler itself is unhappy:

Jul 11, 2020 9:43:07 PM schemacrawler.tools.commandline.utility.CommandLineLoggingUtility logFullStackTrace
SEVERE: Cannot connect to database
java.lang.RuntimeException: Cannot connect to database
    at schemacrawler.tools.commandline.command.ConnectCommand.run(ConnectCommand.java:127)
    at schemacrawler.tools.commandline.SchemaCrawlerCommandLine.executeCommandLine(SchemaCrawlerCommandLine.java:141)
    at schemacrawler.tools.commandline.SchemaCrawlerCommandLine.execute(SchemaCrawlerCommandLine.java:72)
    at schemacrawler.Main.main(Main.java:85)
Caused by: schemacrawler.schemacrawler.SchemaCrawlerException: SchemaCrawler database plugin should be on the CLASSPATH for <jdbc:oracle:thin:wass/passwd@//10.23.15.212:1521/WASS;oracle.jdbc.timezoneAsRegion=false>
    at schemacrawler.tools.databaseconnector.UnknownDatabaseConnector.newDatabaseConnectionSource(UnknownDatabaseConnector.java:85)
    at schemacrawler.tools.commandline.command.ConnectCommand.createDataSource(ConnectCommand.java:180)
    at schemacrawler.tools.commandline.command.ConnectCommand.run(ConnectCommand.java:119)
    ... 3 more

While I did not check the source, I assume that the oracle type is recognized and a plugin is assumed to be available. (When using a wrong type like foo schemacrawler shows a different error message.)

I checked: REGEXP_LIKE is not support in Oracle 9, only in Oracle 10. There is a polyfill available, though:

CREATE OR REPLACE FUNCTION regexp_like (source_char     IN VARCHAR2
                                      , pattern         IN VARCHAR2
                                      , match_parameter IN VARCHAR2 DEFAULT NULL)
RETURN INTEGER
AS
/*************************************************************************
 * Program  : regexp_like
 * Version  : 1.0
 * Author   : Philip Moore
 * Date     : 20-JUN-2009 Anno Domini
 * Purpopse : This provides a pseudo "REGEXP_LIKE" operator for Oracle 9iR2
 * Warnings : Do NOT compile in an Oracle 10GR2 database (or above)!
 *************************************************************************/
  -- Variables
  l_return INTEGER;
BEGIN
   IF owa_pattern.match (line  => source_char
                       , pat   => pattern
                       , flags => match_parameter) THEN
      l_return := 1;
   ELSE
      l_return := 0;
   END IF;

   RETURN l_return;
END regexp_like;
/

I added it using the same schema I am targeting with schemacrawler, but it seems that the function was not available, since the original error persisted.

sualeh commented 4 years ago

@everflux Thomas, yes, the oracle plugin is provided dynamically by the jar you removed. I missed a step - please add SC_WITHOUT_DATABASE_PLUGIN = true either as an environmental setting or as a Java system property (-DSC_WITHOUT_DATABASE_PLUGIN=true). Then rename (or preferably move out) lib/schemacrawler-oracle-16.9.2.jar, and try again with the connection URL (--url).

everflux commented 4 years ago

Thanks again for taking care even on exotic issues.

I tried using the SC_WITHOUT_DATABASE_PLUGIN approach and set the additional driver property required for my timezone issue by editing the schemacrawler.sh and adding a system property.

This indeed helped - but lead to multiple other exceptions (SQL command not properly ended). I attach the debug logging, but my hopes are low that there is an easy way to fix this.

debug.log.gz

sualeh commented 4 years ago

@everflux Thomas, I am sorry. Please stick with me - I think you are close. For some strange reason, SchemaCrawler seems to be picking up the DB2 plugin. Since you are not using any plugins, please remove the following jars:

lib/schemacrawler-db2-16.9.2.jar
lib/schemacrawler-hsqldb-16.9.2.jar
lib/schemacrawler-mysql-16.9.2.jar
lib/schemacrawler-postgresql-16.9.2.jar
lib/schemacrawler-sqlite-16.9.2.jar
lib/schemacrawler-sqlserver-16.9.2.jar

and then try again. I wish I had an Oracle 9i database so I could help you better.

sualeh commented 4 years ago

@everflux - Thomas - any luck with getting this to work?

everflux commented 4 years ago

Yay it works! This is what I had to do:

This nearly worked except

Error: Could not retrieve indexes for table SYS._ALL_INSTANTIATION_DDL: Invalid argument(s) in call

This was solved using an appropriate --grep-columns="MYDB\\..*" to exclude the SYS tables.

Thank you! (I don't know if this issue can be closed as it might be useful to improve the faq? Otherwise I don't consider it a schemacrawler issue anyway to not support ancient dbms.)

sualeh commented 4 years ago

@everflux - I am so glad it worked. Sorry for all the run-around to get to this point. Let me think through if I want to have a way to disable the plugins, and improve the documentation. I will leave this issue open for a bit, while I chew on ways to make things better.

sualeh commented 4 years ago

@everflux - Thomas, I have hopefully made this process of disabling plugins easier for you in the future. In the next version of SchemaCrawler, here is what you will need to do:

everflux commented 4 years ago

Thanks a lot!