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

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?

oracleunit-testingintegration-testingddlh2


Solution 1:[1]

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);

Solution 2:[2]

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.

Solution 3:[3]

You should be able to use the DBMS_METADATA package to generate DDL for all the objects in your schema. There was a discussion a couple of days ago on How to Programmatically Generate DDL from an Oracle Database that would seem to be on point.

Solution 4:[4]

I've used DBeaver function of generating DDL.

Removing Oracle specific table statements, like:

SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL KEEP FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "data"

and removing ENABLE in column definition like

"ID" NUMBER(15,0) NOT NULL ENABLE,

and removing primary key additional attributes:

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT

it allowed to create tables in h2 version 1.4.196.

As an example, DBeaver 21.1.5 generated: enter image description here

Then removing unnecessary fields gave me:

CREATE TABLE "CARD_STATE" 
("ID" NUMBER(15,0) NOT NULL , 
"NAME" VARCHAR2(32) NOT NULL, 
CONSTRAINT "PK_CARD_STATE" PRIMARY KEY ("ID");

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Igor Mukhin
Solution 2 Gary Myers
Solution 3 Community
Solution 4 pl.square

Related Questions