Error convert oracle datatype in datatype java.sql (java.sql.bit)

[gp67git]

Environment

Schema Crawler verrsion 14.19.01 JDK: jdk1.8.0_112 O.S Windows 10 RDBMS : Oracle 11gR2 JDBC version: ojdbc7.jar

Issue

Sorry for my english !👎 SchemaCreawler return java.sql.bit for all columns that I have create with this datatype Oracle : Decimal Numeric Integer Smallint

Create this table:

CREATE TABLE "SVIL"."XGPI_DDL_ORACLE"
(
        COL00_DECIMAL DECIMAL ( 8, 3) NOT NULL
   ,    COL01_DECIMAL DECIMAL ( 7, 3) NULL
   ,    COL02_DECIMAL DECIMAL ( 9, 0) DEFAULT 45
   ,    COL10_CHAR CHAR ( 200 ) NOT NULL
   ,    COL11_CHAR CHAR ( 2000 ) NULL
   ,    COL12_CHAR CHAR ( 20 ) 
   ,    COL20_VARCHAR VARCHAR2 ( 200 ) NOT NULL
   ,    COL21_VARCHAR VARCHAR2 ( 4000 ) NULL
   ,    COL22_VARCHAR VARCHAR2 ( 40 ) 
   ,    COL30_INTEGER INTEGER NOT NULL
   ,    COL31_INTEGER INTEGER NULL
   ,    COL32_INTEGER INTEGER 
   ,    COL40_SMALLINT SMALLINT NOT NULL
   ,    COL41_SMALLINT SMALLINT NULL
   ,    COL42_SMALLINT SMALLINT
   ,    COL50_TIMESTAMP TIMESTAMP NOT NULL
   ,    COL51_TIMESTAMP TIMESTAMP NULL
   ,    COL52_TIMESTAMP TIMESTAMP 
   ,    COL60_DATE DATE NOT NULL
   ,    COL61_DATE DATE NULL
   ,    COL62_DATE DATE 
   ,    COL70_TIME TIME NOT NULL
   ,    COL71_TIME TIME NULL
   ,    COL72_TIME TIME 
   ,    COL81_NUMERIC NUMERIC ( 8, 3 ) NOT NULL
   ,    COL82_NUMERIC NUMERIC ( 7, 3 ) NULL
   ,    COL83_NUMERIC NUMERIC ( 9, 0 ) DEFAULT 45
   ,    COL90_FLOAT FLOAT NOT NULL
   ,    COL91_FLOAT FLOAT NULL
   ,    COL97_FLOAT FLOAT 
   , CONSTRAINT XGPI_DDL_MIGRATOR_PK PRIMARY KEY (COL00_DECIMAL)
)
;

After connet to database oracle with this code:

        String connectionUrl = jdbc:oracle:thin:@srvsvil:1521:orcl;
        DatabaseConnectionOptions dco = new DatabaseConnectionOptions(connectionUrl);
        this.connection = dco.getConnection(user, password);
        SchemaCrawlerOptions sco = new SchemaCrawlerOptions();
        sco.setSchemaInfoLevel("STANDARD");
        sco.setRoutineInclusionRule(new ExcludeAll());
        sco.setSchemaInclusionRule(new RegularExpressionInclusionRule("SVIL"));
        sco.setTableNamePattern("XGPI_DDL_ORACLE%");
        final DatabaseSpecificOverrideOptions dsoob = new DatabaseSpecificOverrideOptions();
        SchemaCrawler sc = new SchemaCrawler(this.getSchemaConn().getConnection(), dsoob);
        this.catalog = sc.crawl(sco);
        Schema schema = this.catalog.getSchemas().iterator().next();
        if (this.catalog != null) {
            Collection<Table> tablesList = this.catalog.getTables();
             Iterator<Table> i = tablesList.iterator();
            Table tbl;
             while (i.hasNext()) {
                    tbl = i.next();
                    String tblName = tbl.getName();
                   for (Column col : tbl.getColumns()) {
                        JavaSqlType sqlType = col.getColumnDataType().getJavaSqlType();
                   }
             }
        }

[sualeh]

Please use the following code to solve your issue.

https://gist.github.com/sualeh/97ae60a0d8f7a0a8d47bc1f42669421d

[gp67git]

Sorry but I have a error with https://gist.github.com/sualeh/97ae60a0d8f7a0a8d47bc1f42669421d

This code: final Catalog catalog = SchemaCrawlerUtility.getCatalog(connection, sco);

return a Null Exception:

Exception in thread "main" schemacrawler.schemacrawler.SchemaCrawlerException: Exception retrieving table information: Cannot use null results at schemacrawler.crawl.SchemaCrawler.crawlTables(SchemaCrawler.java:753) at schemacrawler.crawl.SchemaCrawler.crawl(SchemaCrawler.java:811) at schemacrawler.utility.SchemaCrawlerUtility.getCatalog(SchemaCrawlerUtility.java:75) at sample.TestDataType.main(TestDataType.java:44) Caused by: java.lang.NullPointerException: Cannot use null results at java.util.Objects.requireNonNull(Objects.java:228) at schemacrawler.crawl.MetadataResultSet.(MetadataResultSet.java:100) at schemacrawler.crawl.MetadataResultSet.(MetadataResultSet.java:90) at schemacrawler.crawl.TableColumnRetriever.retrieveColumnsFromDataDictionary(TableColumnRetriever.java:268) at schemacrawler.crawl.TableColumnRetriever.retrieveColumns(TableColumnRetriever.java:102) at schemacrawler.crawl.SchemaCrawler.lambda$crawlTables$18(SchemaCrawler.java:602) at sf.util.StopWatch.time(StopWatch.java:185) at schemacrawler.crawl.SchemaCrawler.crawlTables(SchemaCrawler.java:599)

but I resolved with final DatabaseSpecificOverrideOptions dsoob = new DatabaseSpecificOverrideOptions(); final SchemaCrawler sc = new SchemaCrawler(connection, dsoob); final Catalog catalog = sc.crawl(sco);

but in this case there are the error in java sql datatype SVIL.XGPI_DDL_ORACLE o--> COL00_DECIMAL -data-type- BIT -7 bit o--> COL01_DECIMAL -data-type- BIT -7 bit o--> COL02_DECIMAL -data-type- BIT -7 bit o--> COL10_CHAR -data-type- CHAR 1 character o--> COL11_CHAR -data-type- CHAR 1 character o--> COL12_CHAR -data-type- CHAR 1 character o--> COL20_VARCHAR -data-type- VARCHAR 12 character o--> COL21_VARCHAR -data-type- VARCHAR 12 character o--> COL22_VARCHAR -data-type- VARCHAR 12 character o--> COL30_INTEGER -data-type- BIT -7 bit o--> COL31_INTEGER -data-type- BIT -7 bit o--> COL32_INTEGER -data-type- BIT -7 bit o--> COL40_SMALLINT -data-type- BIT -7 bit o--> COL41_SMALLINT -data-type- BIT -7 bit o--> COL42_SMALLINT -data-type- BIT -7 bit o--> COL50_TIMESTAMP -data-type- TIMESTAMP 93 temporal o--> COL51_TIMESTAMP -data-type- TIMESTAMP 93 temporal o--> COL52_TIMESTAMP -data-type- TIMESTAMP 93 temporal o--> COL60_DATE -data-type- TIME 92 temporal o--> COL61_DATE -data-type- TIME 92 temporal o--> COL62_DATE -data-type- TIME 92 temporal o--> COL81_NUMERIC -data-type- BIT -7 bit o--> COL82_NUMERIC -data-type- BIT -7 bit o--> COL83_NUMERIC -data-type- BIT -7 bit o--> COL90_FLOAT -data-type- FLOAT 6 real o--> COL91_FLOAT -data-type- FLOAT 6 real o--> COL97_FLOAT -data-type- FLOAT 6 real BUILD SUCCESSFUL (total time: 13 seconds)

[sualeh]

@gpnick67 - the issue is that SchemaCrawler works around an Oracle JDBC driver bug. SchemaCrawler only works with Oracle 12c and higher. The code I gave you uses the SchemaCrawler workaround, but does not work with Oracle 11g. The code you wrote does not use the SchemaCrawler workaround, so the Oracle JDBC driver bug is present.

The solution for you is to upgrade to a newer Oracle version if possible. Or, you can create a custom SchemaCrawler plugin for Oracle 11g to fix the bug.

[sualeh]

Or, you can use an older version of SchemaCrawler.