Generate XML/ SQL output with data types of target database

General Discussion
1

I need to convert the structure of PostgreSQL databases to Oracle. In PostgreSQL, I have a postgres database with data. In Oracle I have a blank database in which I want to write postgres database which in PostgreSQL.

In fact, I do not need the data, only the structure (relationships).

For this I use Liquibase. I get the changelog from PostgreSQL with the command:

  1. liquibase \    --driver=org.postgresql.Driver \    --classpath="C:\db_drivers\postgresql-9.3-1102.jdbc3.jar" \    --changeLogFile="./postgresql_changelog.xml" \    --url="jdbc:postgresql://localhost:5432/postgres" \    --username=schema_name_here \    --password=************** \    --logLevel=debug \    --defaultSchemaName=sep \    generateChangeLog

After this I try to create objects in the Oracle database:

  1. liquibase    --driver=oracle.jdbc.OracleDriver    --classpath="C:\db_drivers\ojdbc14.jar"    --changeLogFile="./postgresql_changelog.xml"    --url="jdbc:oracle:thin:@ip_here:orabeta"    --username=***    --password=***    update

Does not work:  ORA-00902

Here is a fragment of postgresql_changelog.xml:

  1. ... ...

I also generate a pure SQL- file:

  1. liquibase    --driver=oracle.jdbc.OracleDriver    --classpath="C:\db_drivers\ojdbc14.jar"    --changeLogFile="./postgresql_changelog.xml"    --url="jdbc:oracle:thin:@ip_here:orabeta"    --username=***    --password=***    updateSQL > update.sql

Here is a fragment of update.sql:

  1. ... CREATE TABLE SCHEMA_HERE.TABLE1A (total_pk INT8 NOT NULL, form_fk INT8, .....etc ); INSERT INTO SCHEMA_HERE.TABLE1A (ID, FORM_ID, ...etc) ...

I would like to generate the file, in which all data types correspond to the target database, ie that I want to create. I can write a simple parser that replace data types, but it is not the right solution - can be many database.

It possible to generate XML/ SQL output with data types of target database?

Or maybe there is an option that allow to generate output with "abstract" data types? Ie with the data types that are not in the real databases, for example, instead of INT8 - Integer, etc.

I would be very grateful for the information. Thanks to all.

2

Actually, it is necessary to manually correct the data types in the generated file. Data types are not converted automatically.

For example:

  1. ...
  3.    
  4.      
  5.                 
  6.          
  7.      
  8.      
  9.        
  10.          
  11.         
  12. ...

After that execute the command:

  1. liquibase 
  2.    --driver=oracle.jdbc.OracleDriver 
  3.    --classpath="C:\db_drivers\ojdbc14.jar" 
  4.    --changeLogFile="./postgresql_changelog.xml" 
  5.    --url="jdbc:oracle:thin:@ip_here:orabeta" 
  6.    --username=*** 
  7.    --password=***
  8.    update

The structure will be generated on the target database. Is similarly possible migrate the data (option --diffTypes = "data").

But, really, can be used "abstract" data types, as write in the documentation: Liquibase, Column tag

To help make scripts database-independent, the following “generic” data types will be converted to the correct database implementation: BOOLEAN CURRENCY UUID CLOB BLOB DATE DATETIME TIME BIGINT

Also, specifying a java.sql.Types.* type will be converted to the correct type as well. If needed, precision can be included. Here are some examples: java.sql.Types.TIMESTAMPjava.sql.Types.VARCHAR(255)

And possible to make the copy of structure without DDL (and data without DML).