How to export a ddl script from an Oracle 10 schema to create tables and constraints in H2-database?

Question

We would like to use H2 in-memory database for automated testing of our web-applications. We use Oracle 10 for our production and development environments.

So the idea is to duplicate the table structure in H2 test-database as it is in our Oracle dev-database.

It there an easy way to extract DDLs from an Oracle 10 schema (tables and constraints) so they could be executed against H2 database?

Answer 1

I'd have to ask what are you 'proving' if your test environment is using a different database engine that the actual implementation. For example H2 has a DATE datatype that is just a DATE. In Oracle the DATE datatype stores a time as well.

If you do decide to go this route, then rather than trying to convert Oracle DDL syntax to H2 you'd be better off designing the data structures in a modelling tool and using that as your 'source of truth'. The tool should be capable of exporting / creating DDL in both Oracle and H2 formats. Most tools should support Oracle, though H2 might be a little trickier.

Answer 2

This script helped me:

create or replace function mymetadata return sys.ku$_ddls is
  md_handle number;
  tr_handle number;
  dl_handle number;

  result_array sys.ku$_ddls;
begin

  md_handle := dbms_metadata.open('TABLE');

  tr_handle := dbms_metadata.add_transform(md_handle, 'MODIFY');
  dbms_metadata.set_remap_param(tr_handle, 'REMAP_SCHEMA', 'MLIS_DEV', null);

  dl_handle := dbms_metadata.add_transform(md_handle, 'DDL');
  dbms_metadata.set_transform_param(dl_handle, 'SEGMENT_ATTRIBUTES', false);
  dbms_metadata.set_transform_param(dl_handle, 'STORAGE', false);
  dbms_metadata.set_transform_param(dl_handle, 'TABLESPACE', false);
  dbms_metadata.set_transform_param(dl_handle, 'REF_CONSTRAINTS', false);
  dbms_metadata.set_transform_param(dl_handle, 'SQLTERMINATOR', true);
  dbms_metadata.set_transform_param(dl_handle, 'CONSTRAINTS_AS_ALTER', true);

  LOOP
      result_array := dbms_metadata.fetch_ddl(md_handle);
      EXIT WHEN result_array IS NULL;

      FOR i IN result_array.FIRST..result_array.LAST LOOP
          dbms_output.put_line(result_array(i).ddltext);
      END LOOP;
  END LOOP;    

  dbms_metadata.close(md_handle);


  md_handle := dbms_metadata.open('REF_CONSTRAINT');

  tr_handle := dbms_metadata.add_transform(md_handle, 'MODIFY');
  dbms_metadata.set_remap_param(tr_handle, 'REMAP_SCHEMA', 'MLIS_DEV', null);

  dl_handle := dbms_metadata.add_transform(md_handle, 'DDL');
  dbms_metadata.set_transform_param(dl_handle, 'SQLTERMINATOR', true);

  LOOP
      result_array := dbms_metadata.fetch_ddl(md_handle);
      EXIT WHEN result_array IS NULL;

      FOR i IN result_array.FIRST..result_array.LAST LOOP
          dbms_output.put_line(result_array(i).ddltext);
      END LOOP;
  END LOOP;    

  dbms_metadata.close(md_handle);

  return result_array;
end;
/


select ddltext from table(mymetadata);