1

I want to run unit tests by generating all tables in HSQLDB, present in my oracle database.

For that I want to export all DDL create table statements from oracle tables.

I tried export database, but along with create table sql I am getting lot other SQLs like,

" PARTITION BY RANGE ("CREATION_DATE") " etc.

How do I export all oracle tables(schema) to HSQLDB? is there any better way?

Anantha Krishnan
  • 3,068
  • 3
  • 27
  • 37
  • that's probably because your tables are partitioned - that is part of the ddl dyntax. it's ok. – haki Jun 27 '13 at 16:49
  • write some regular expressions to filter out the Oracle specific stuff that are not needed by HSQLDB. – fredt Jun 27 '13 at 16:58

2 Answers2

4

You can use the DBMS_METADATA.GET_DDL() function to get the table definition, and modify what is included with the SET_TRANSFORM_PARAM() options, specifically in this case the PARTITIONING parameter.

There are lots of examples for you to search for, but here's one that shows the DDL being simplified with similar transformations.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
0

It's some work, but you can implement your own tool to create the DDL.

All you need is stored in the Oracle database catalogue.

To create just tables (without index and constraints) you need these 2 tables:

  • USER_TAB_COLUMNS
  • USER_TABLES

You will find a detailed documentation of these tablese here: Oracle Database Reference

Other usefull Oracle tables are

  • USER_CONSTRAINTS
  • USER_INDEXES
Olaf H
  • 496
  • 2
  • 9