Using SchemaCrawler with iData Data Cookbook

[dakuwan]

I am using SchemaCrawler to generate a JSON file to use with iData's Data Cookbook product. They indicated that I had to use a version 15 release in order for their system to be able to read the generated file. I downloaded a zip of 15.06.01. When I unzipped it and attempted to use the same command I used in the latest release I get back a Error: Could not find a suitable JDBC driver for database connection URL, jdbc:oracle:thin:@//... error. I installed the latest version via the scoop directions so I am guessing scoop did something downloading the zip did not do that allowed my connections to work? Could you please advise how I can properly install v15 or maybe any guidance on why it is not finding the included Oracle driver?

Thanks for any help you can give.

[sualeh]

@dakuwan - the Oracle JDBC driver was not redistributed with SchemaCrawler 15.x.x. You will need to obtain this driver from Oracle, and place the jar file in ".../schemacrawler-15.06.01-distribution/_schemacrawler/lib".

[sualeh]

Logs please, @dakuwan (Please note that I cannot support older versions of SchemaCrawler. I will take a look at the logs for you, and let you know if the problem is obvious. Otherwise, I will refer you to the consulting page.)

[dakuwan]

Understand and appreciate any help you are able to give. Most of the log is just repeating the database structure so I am pasting in the parts that are not those. My cmd prompt wouldn't scroll higher than what i pasted but hopefully the issue is in here somewhere.

{ lots of retrieving columns here }

Apr 06, 2022 4:16:19 PM schemacrawler.crawl.MetadataResultSet close
INFO: Processed 282 rows for
Apr 06, 2022 4:16:19 PM schemacrawler.crawl.SchemaCrawler crawlTables
INFO: Retrieving additional table information
Apr 06, 2022 4:16:19 PM schemacrawler.crawl.SchemaCrawler crawlTables
INFO: Total time taken for - 00:00:12.651 hours

• 5.5% - 00:00:00.691 -
• 17.5% - 00:00:02.216 -
• 52.4% - 00:00:06.628 -
• 0.6% - 00:00:00.080 -
• 24.0% - 00:00:03.036 -
• 0.0% - 00:00:00.000 -
• 0.0% - 00:00:00.000 -
• 0.0% - 00:00:00.000 -
• 0.0% - 00:00:00.000 -
• 0.0% - 00:00:00.000 -
• 0.0% - 00:00:00.000 -
• 0.0% - 00:00:00.000 -
• 0.0% - 00:00:00.000 -
• 0.0% - 00:00:00.000 -
• 0.0% - 00:00:00.000 -

Apr 06, 2022 4:16:19 PM schemacrawler.crawl.SchemaCrawler crawlRoutines
INFO: Crawling routines
Apr 06, 2022 4:16:19 PM schemacrawler.crawl.SchemaCrawler lambda$crawlRoutines$8
INFO: Retrieving procedure names
Apr 06, 2022 4:16:19 PM schemacrawler.crawl.RoutineRetriever retrieveProcedures
INFO: Retrieving procedures, using fast data dictionary retrieval
Apr 06, 2022 4:16:19 PM schemacrawler.utility.QueryUtility executeAgainstSchema
FINE: Executing PROCEDURES:
SELECT /*+ PARALLEL(AUTO) */
NULL AS PROCEDURE_CAT,
PROCEDURES.OWNER AS PROCEDURE_SCHEM,
PROCEDURES.OBJECT_NAME AS PROCEDURE_NAME,
PROCEDURES.OBJECT_NAME AS SPECIFIC_NAME,
CASE WHEN ARGUMENTS.OBJECT_ID IS NULL
THEN 1
ELSE 2
END AS PROCEDURE_TYPE,
NULL AS REMARKS
FROM
ALL_PROCEDURES PROCEDURES
LEFT OUTER JOIN ALL_ARGUMENTS ARGUMENTS
ON PROCEDURES.OBJECT_ID = ARGUMENTS.OBJECT_ID
AND ARGUMENTS.ARGUMENT_NAME IS NULL
AND ARGUMENTS.POSITION = 0
WHERE
PROCEDURES.OWNER NOT IN
('ANONYMOUS', 'APEX_PUBLIC_USER', 'APPQOSSYS', 'BI', 'CTXSYS', 'DBSNMP', 'DIP',
'EXFSYS', 'FLOWS_30000', 'FLOWS_FILES', 'GSMADMIN_INTERNAL', 'IX', 'LBACSYS',
'MDDATA', 'MDSYS', 'MGMT_VIEW', 'OE', 'OLAPSYS', 'ORACLE_OCM',
'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'OWBSYS', 'PM', 'SCOTT', 'SH',
'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WKPROXY', 'WKSYS', 'WK_TEST',
'WMSYS', 'XDB', 'XS$NULL', 'RDSADMIN')
AND NOT REGEXP_LIKE(PROCEDURES.OWNER, '^APEX_[0-9]{6}$')
AND NOT REGEXP_LIKE(PROCEDURES.OWNER, '^FLOWS_[0-9]{5,6}$')
AND REGEXP_LIKE(PROCEDURES.OWNER, 'XXXXXX')
AND PROCEDURES.OBJECT_TYPE = 'PROCEDURE'
ORDER BY
PROCEDURE_SCHEM,
PROCEDURE_NAME

Apr 06, 2022 4:16:20 PM schemacrawler.crawl.RoutineRetriever retrieveProceduresFromDataDictionary
INFO: Processed 0 procedures
Apr 06, 2022 4:16:20 PM schemacrawler.crawl.MetadataResultSet close
INFO: Processed 0 rows for
Apr 06, 2022 4:16:20 PM schemacrawler.crawl.SchemaCrawler lambda$crawlRoutines$8
INFO: Retrieving function names
Apr 06, 2022 4:16:20 PM schemacrawler.crawl.RoutineRetriever retrieveFunctions
INFO: Retrieving functions, using fast data dictionary retrieval
Apr 06, 2022 4:16:20 PM schemacrawler.utility.QueryUtility executeAgainstSchema
FINE: Executing FUNCTIONS:
SELECT /*+ PARALLEL(AUTO) */
NULL AS FUNCTION_CAT,
FUNCTIONS.OWNER AS FUNCTION_SCHEM,
FUNCTIONS.OBJECT_NAME AS FUNCTION_NAME,
FUNCTIONS.OBJECT_NAME AS SPECIFIC_NAME,
CASE WHEN ARGUMENTS.OBJECT_ID IS NULL
THEN 0
ELSE 1
END AS FUNCTION_TYPE,
NULL AS REMARKS
FROM
ALL_PROCEDURES FUNCTIONS
LEFT OUTER JOIN ALL_ARGUMENTS ARGUMENTS
ON FUNCTIONS.OBJECT_ID = ARGUMENTS.OBJECT_ID
AND ARGUMENTS.ARGUMENT_NAME IS NULL
AND ARGUMENTS.POSITION = 0
WHERE
FUNCTIONS.OWNER NOT IN
('ANONYMOUS', 'APEX_PUBLIC_USER', 'APPQOSSYS', 'BI', 'CTXSYS', 'DBSNMP', 'DIP',
'EXFSYS', 'FLOWS_30000', 'FLOWS_FILES', 'GSMADMIN_INTERNAL', 'IX', 'LBACSYS',
'MDDATA', 'MDSYS', 'MGMT_VIEW', 'OE', 'OLAPSYS', 'ORACLE_OCM',
'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'OWBSYS', 'PM', 'SCOTT', 'SH',
'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WKPROXY', 'WKSYS', 'WK_TEST',
'WMSYS', 'XDB', 'XS$NULL', 'RDSADMIN')
AND NOT REGEXP_LIKE(FUNCTIONS.OWNER, '^APEX_[0-9]{6}$')
AND NOT REGEXP_LIKE(FUNCTIONS.OWNER, '^FLOWS_[0-9]{5,6}$')
AND REGEXP_LIKE(FUNCTIONS.OWNER, 'XXXXXX')
AND FUNCTIONS.OBJECT_TYPE = 'FUNCTION'
ORDER BY
FUNCTION_SCHEM,
FUNCTION_NAME

Apr 06, 2022 4:16:21 PM schemacrawler.crawl.RoutineRetriever retrieveFunctionsFromDataDictionary
INFO: Processed 0 functions
Apr 06, 2022 4:16:21 PM schemacrawler.crawl.MetadataResultSet close
INFO: Processed 0 rows for
Apr 06, 2022 4:16:21 PM schemacrawler.crawl.SchemaCrawler crawlRoutines
INFO: Retrieved 0 routines
Apr 06, 2022 4:16:21 PM schemacrawler.crawl.SchemaCrawler crawlSynonyms
INFO: Not retrieving synonyms, since this was not requested
Apr 06, 2022 4:16:21 PM schemacrawler.crawl.SchemaCrawler crawlSequences
INFO: Not retrieving sequences, since this was not requested
Apr 06, 2022 4:16:21 PM schemacrawler.filter.ReducerFactory$BaseReducer reduce

{ Lots of include statements with tables names here }

Apr 06, 2022 4:16:21 PM schemacrawler.tools.executable.SchemaCrawlerExecutable execute
INFO: Executing command using <schemacrawler.tools.executable.CommandDaisyChain>
Apr 06, 2022 4:16:21 PM schemacrawler.utility.PropertiesUtility loadProperties
INFO: Loading properties from </schemacrawler.colormap.properties>
Apr 06, 2022 4:16:21 PM schemacrawler.tools.iosource.ClasspathInputResource openNewInputReader
INFO: Opened input reader to classpath resource, /schemacrawler.colormap.properties
Apr 06, 2022 4:16:21 PM schemacrawler.tools.iosource.InputReader close
INFO: Closing input reader
Apr 06, 2022 4:16:21 PM schemacrawler.tools.iosource.FileInputResource
FINE: Cannot read file, C:\schemacrawler-15.06.01-distribution_schemacrawler\schemacrawler.colormap.properties
java.io.IOException: Cannot read file, C:\schemacrawler-15.06.01-distribution_schemacrawler\schemacrawler.colormap.properties
at schemacrawler.tools.iosource.FileInputResource.(FileInputResource.java:68)
at schemacrawler.tools.iosource.FileInputResource.(FileInputResource.java:58)
at schemacrawler.tools.text.utility.DatabaseObjectColorMap.initialize(DatabaseObjectColorMap.java:81)
at schemacrawler.tools.text.base.BaseFormatter.(BaseFormatter.java:79)
at schemacrawler.tools.text.base.BaseTabularFormatter.(BaseTabularFormatter.java:64)
at schemacrawler.tools.text.schema.SchemaTextFormatter.(SchemaTextFormatter.java:127)
at schemacrawler.tools.text.schema.SchemaTextRenderer.getSchemaTraversalHandler(SchemaTextRenderer.java:165)
at schemacrawler.tools.text.schema.SchemaTextRenderer.execute(SchemaTextRenderer.java:87)
at schemacrawler.tools.executable.BaseCommandChain.executeChain(BaseCommandChain.java:127)
at schemacrawler.tools.executable.CommandDaisyChain.execute(CommandDaisyChain.java:113)
at schemacrawler.tools.executable.SchemaCrawlerExecutable.execute(SchemaCrawlerExecutable.java:133)
at schemacrawler.tools.commandline.SchemaCrawlerCommandLine.execute(SchemaCrawlerCommandLine.java:134)
at schemacrawler.Main.main(Main.java:92)

Apr 06, 2022 4:16:21 PM schemacrawler.tools.text.utility.DatabaseObjectColorMap initialize
CONFIG: Could not load color map from file
Apr 06, 2022 4:16:21 PM schemacrawler.tools.iosource.ConsoleOutputResource openNewOutputWriter
INFO: Opened output writer to console

{Schema stuff was here}

Apr 06, 2022 4:16:22 PM schemacrawler.tools.iosource.OutputWriter close
INFO: Not closing output writer, since output is to an externally provided writer

[sualeh]

Please try with:
.\schemacrawler.cmd -port=15210 -host=xxx.xxx.xxx.xxx -server=oracle -database=xxx.xxx.xxx.xxx -schemas=xxx -user=xxx -password=xxx -infolevel=maximum -command=schema -outputformat=json -outputfile=test.json

Notice that there are no dashes in "-infolevel", "-outputformat" and "-outputfile".

For help, run:
.\schemacrawler.cmd -help -c schema

Let me know if that works for you.

[dakuwan]

So I tried that and the window just sat there for a long time until finally it came back with Error: Cannot open output writer: C:\test.json

[sualeh]

@dakuwan - this seems like a solvable problem (both the time issue and the generation of the file). However, I will not be able to support older versions of SchemaCrawler on this forum.

[sualeh]

@jseaman-idata - I am thinking that it may be possible to use a (Thymeleaf/ Velocity/ Mustache/ Python) template to get SchemaCrawler to generate iData Data Cookbook CSV directly. This way, users like @dakuwan are not tied to a particular version of SchemaCrawler. I am thinking of a technique similar to the one I described in How to Generate Mermaid Diagrams for Your Database. Any thoughts?

[jseaman-idata]

Hi Sualeh. Sorry I missed this earlier, but we have a process that uses the SchemaCrawler API to generate an importable file. It works great. I love the API. Not every client pays for that version. But here's a quick little bit I wrote a while ago:

Step By Step Instructions
SchemaCrawler can be run from any system cleared to directly access your database through an open port. Depending on your local network infrastructure, this may be a limited set of machines, and you may not have rights to do this. Please contact your IT department before starting, as they may want to manage this access or even do the schema output for you.
Windows Instructions
Download and install Java JRE 1.8. (If you don’t already have it)
Download and unzip SchemaCrawler (it will create files and folders)
Copy the JDBC driver for your database to the C:<your directory path>schemacrawler-14.16.03-distribution_schemacrawler\lib\ directory
Open a Windows Command prompt (type cmd or cmd.exe from the Windows Search box or type one of those into the Windows Run dialogue -- press Win+R to open the Windows Run Dialogue).
Navigate to the folder where you expanded SchemaCrawler: cd “C:<your directory path>\schemacrawler-14.16.03-distribution_schemacrawler”
Type schemacrawler.cmd -url= -driver= -user= -password= -command=schema -infolevel=maximum -outputformat=json -outputfile=MySchemaOutput.json -schemas= -tables=

Linux Instructions
Download and install Java JRE 1.8. (If you don’t already have it)
Download and unzip SchemaCrawler (it will create files and folders)
Copy the JDBC driver for your database to the //schemacrawler-14.16.03-distribution/_schemacrawler/lib/ directory
From the command line navigate to the //schemacrawler-14.16.03-distribution/_schemacrawler directory
Type .schemacrawler.sh -url= -driver= -user= -password= -command=schema -infolevel=maximum -outputformat=json -outputfile=MySchemaOutput.json -schemas= -tables=