CODEWITHSUNDEEP
oracle-databasehsqldbddl
Ayusman
Ayusman

Reputation: 8719

Oracle DDL export

I am trying to recreate an oracle database in an HSQL database. this is to enable better unit testing on the local developer systems.

What I need to know is is there anything tool/command I can use in the oracle server/client, that can provide me all the DDL commands for all the objects (tables, synonyms, views etc) in the oracle database.

I am planning to go through the created DDL commands and try to convert these commands into HSQL compatible commands.

Is it possible?

Any suggestions will be welcome.

system information:

Oracle DB: Oracle enterprise server 11g R2. 
HSQL DB: hsql 2.2.9

Upvotes: 4

Views: 3077

Answers (3)

Nikhil
Nikhil

Reputation: 590

One open source inactive project do exact same thing.

http://schemamule.sourceforge.net/index.html

Upvotes: 0

fredt
fredt

Reputation: 24372

There are quite a few products that can help.

OpenSource: http://www.liquibase.org/manual/formatted_sql_changelogs

Commercial with free trial: http://www.devart.com/dbforge/oracle/schemacompare/new-export-oracle-schema.html

If you use a tool that is generally designed for use with Oracle databases only, you may get quite a lot of non-standard Oracle DDL that needs converting. A cross platform tool is more likely to reduce the work.

When HSQLDB is used in the ORA compatibility mode, it can translate some Oracle types in the DDL to a similar SQL Standard type. So the types may not pose a problem.

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231711

You can use the DBMS_METADATA package along with the data dictionary to generate the DDL for your objects. For example, to generate the DDL for every table in a schema

declare
  l_ddl clob;
begin
  for t in (select * from user_tables)
  loop
    l_ddl := dbms_metadata.get_ddl( 'TABLE', t.table_name, USER );
    <<do something with l_ddl>>
  end loop;
end;

Are you sure that it makes sense to test with a completely different database than what you're really going to deploy to? Even if you translate the DDL to the closest analogue, it seems very likely that you'll get different results for some tests depending on the database you're connected to. Are you sure that you can't install Oracle (potentially Oracle XE if your concerns are primarily about licensing) on the developer's machines?

Upvotes: 2

Related Questions