Results for tag "oracle"

2 Articles

Hibernate uses wrong schema during schema validation

Recently I struggled upon the same problem, this guy described. Our Oracle database instance contains multiple schematics with almost the same structure. Every developer has it’s own schema for unit and integration tests. On application startup the Hibernate schema validator calls the DatabaseMetaData.getTables() for every linked entity. The method returns the first table which could be found in any schema. The returned tables are by default ordered by schema name. Side node: I would expect that the home schema of the current user would be prefered. This leads to situation that sometimes the validation fails: a user has already migrated his own schema (schema name app_user_unittest) but the schema for the build server (schema name app_build_unittest) still has the old schema version.

Overwriting DatabaseMetaData.getTables() method is not possible as it resides in the Oracle JDBC driver. Instead, you can use the environment variable hibernate.default_schema which points to to prefered schema. Depending on your development environment, the variable could be set during application startup by the application itself or by a system property through your application server.

oracleDiff – a schema differential tool for Oracle

On friday last week I got a requirement from a co-worker. He asked me for a simple Java based application which compares two different revisions of Oracle database schemas. After some discussion about the sense of this tool, we made a quick requirement and workflow analysis.

  1. User extracts target database schema information with oracleDiff. Only column names and types are required. There is no need for exporting Stored Procedures, Functions, References and so on. Schema information should be stored as XML.
  2. User exports full table data with oracleDiff. The extracted data should be stored as binary or flat file – XML export for large data is too memory intensive.
  3. User does a manual upgrade of the changes with help of Oracle Enterprise Manager Console or other 3rd party tools.
  4. User imports exported data on upgraded database, oracleDiff asks for remapping of columns and informs about column type changes.

Yesterday I started the project and stumbled over two Oracle specific facts:

  1. DESC $TABLENAME does not work in PreparedStatements. You will receive ORA-00900 from Oracle JDBC driver. For retrieving table information you have to use SELECT * FROM user_tab_columns WHERE table_name = ?.
  2. As a long time MySQL programmer I used SHOW TABLES for retrieving all tables of a schema. This will also not work. You have to to use SELECT table_name FROM user_tables.

The current development status extracts the database schema information and compares two schemas. I hope that I will finish the tool within the next two weeks.