Geek Answers Handbook

Export Oracle DDL

I am trying to recreate the oracle database in the HSQL database. this allows better testing of modules in local development systems.

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

I plan to go through the generated DDL commands and try to convert these commands to HSQL compatible commands.

Is it possible?

Any suggestions would be welcome.

system information:

Oracle DB: Oracle enterprise server 11g R2. HSQL DB: hsql 2.2.9
+4
source share
3 answers

You can use the DBMS_METADATA package along with a data dictionary to generate DDL for your objects. For example, to generate DDL for each 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 it makes sense to test a completely different database than what you really intend to deploy to? Even if you translate DDL to the closest analogue, it seems very likely that you will get different results for some tests depending on the database you are connected to. Are you sure you cannot install Oracle (perhaps Oracle XE, if your licensing problems) on the developer's machines?

+1
source

There are many products that can help.

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

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

If you use a tool that is usually intended to be used only with Oracle databases, you can get quite a lot of non-standard Oracle DDL that needs to be converted. A tool with a transverse platform is likely to reduce work.

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

+1
source

One inactive open source project does the same.

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

0
source

More articles:


All Articles