ORA-01424 error when gathering schema details

[shah_akash]

Hi,

Below is the stack trace when i ran the following command. Any idea/help appreciated.

java -jar schemaSpy_1.7.0.jar -cp c:\temp\schemaspy\classes12.
jar -t orathin -host stado30 -port 1521 -db orcl -u testresults -p
welcome1 -s TESTRESULTS -o .

==================================
Using database properties:
    [schemaSpy_1.7.0.jar]/net/sourceforge/schemaspy/dbTypes/orathin.properties
Connected to Oracle - Oracle Database 10g Enterprise Edition Release 10.1.0.4.0
- Production
With the Partitioning, OLAP and Data Mining options

Gathering schema details
java.sql.SQLException: ORA-01424: missing or illegal character following the esc
ape character

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:124)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:304)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:271)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:622)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.
java:180)
        at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedS
tatement.java:542)
        at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleState
ment.java:914)
        at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPre
paredStatement.java:451)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme
nt.java:985)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePrep
aredStatement.java:2887)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePrepare
dStatement.java:2928)
        at oracle.jdbc.driver.OracleDatabaseMetaData.getColumns(OracleDatabaseMe
taData.java:275)
        at net.sourceforge.schemaspy.model.Table.initColumns(Table.java:122)
        at net.sourceforge.schemaspy.model.Table.<init>(Table.java:24)
        at net.sourceforge.schemaspy.model.Database$TableCreator.createImpl(Data
base.java:253)
        at net.sourceforge.schemaspy.model.Database$TableCreator.create(Database
.java:249)
        at net.sourceforge.schemaspy.model.Database.initTables(Database.java:91)

        at net.sourceforge.schemaspy.model.Database.<init>(Database.java:22)
        at net.sourceforge.schemaspy.SchemaSpy.<init>(SchemaSpy.java:13)
        at net.sourceforge.schemaspy.Main.main(Main.java:128)

Thx

[John Currier]

The version of SchemaSpy here: https://sourceforge.net/tracker/index.php?func=detail&aid=1325650&group_id=137197&atid=737987 should give you additional diagnostics and tell you what schema and table are being passed to the JDBC metadata services.

I'm not sure what escape character it's talking about.  I'm passing nothing but a % to the query.  It looks like quite a few Hibernate 3 users are getting the same exception with Oracle 10g...something to do with Oracle using $'s in its internal names.

[shah_akash]

Came back with the following

Gathering schema detailsFailed to collect column details for table 'BIN$/e3+Cng0
HBPgMFeMtgksMg==$0' in schema 'TESTRESULTS'

Seems like the flasback tables (feature they introduced in 10g) which have $ in the table names was causing this. Running 'purge recyclebin' got rid of all these flashback tables after which it works fine.

Thx

[davidlaurent]

new oracle "a la windows recycled bin"
execute :
PURGE RECYCLEBIN;

[John Currier]

Cool, I'm glad you figured it out.  That's an interesting 'table' name.