Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Migrate oracle ddl (of tables) to postgresql ddl(or tables)

  1. #1
    Join Date
    Feb 2013
    Posts
    530

    Default Migrate oracle ddl (of tables) to postgresql ddl(or tables)

    Hi Forum,

    How to migrate ddl of oracle to postgresql ?

    I'm using tableinput of oracle with the following query

    SELECT DBMS_METADATA.get_ddl ('TABLE', table_name)
    FROM user_tables



    Is it possible to covert oracle ddl to postgresql ddl ? What are the steps need to take ?

    Thank you.

    Sadakar
    BI developer

  2. #2
    Join Date
    Feb 2013
    Posts
    530

    Default

    Using Tools -> Wizard -> Copy Tables

    Need to give source and target database connections :-)

    Thank you Pentaho :-)



    Quote Originally Posted by sadakar View Post
    Hi Forum,

    How to migrate ddl of oracle to postgresql ?

    I'm using tableinput of oracle with the following query

    SELECT DBMS_METADATA.get_ddl ('TABLE', table_name)
    FROM user_tables



    Is it possible to covert oracle ddl to postgresql ddl ? What are the steps need to take ?

    Thank you.

    Sadakar
    BI developer

  3. #3
    Join Date
    Feb 2013
    Posts
    530

    Default

    Dear experts
    What about views, sequences migration ?

    Can anybody give hints on the same ?




    Quote Originally Posted by sadakar View Post
    Using Tools -> Wizard -> Copy Tables

    Need to give source and target database connections :-)

    Thank you Pentaho :-)

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Looks like you found Maslow's hammer.
    Why don't you use a tool made specifically for database schema migration?
    Since schema migration typically is a one time job, I wouldn't hesitate to buy a specialized tool.

    You might find something useful here: Converting from other Databases to PostgreSQL
    So long, and thanks for all the fish.

  5. #5

    Default

    I agree with marabu, it's better to use a specific tool.
    I performed some db migrations (not so big, less the 100 tables) with SQL Power Architect (revres eng. of the source and sql forward to the target).

  6. #6
    Join Date
    Feb 2011
    Posts
    152

    Default

    There is a way to make PDI do it, but it is somewhat tricky to set up. Basically you have to have a transformation the gets a list of all tables you want to migrate from Oracle, and then have a second transformation that is executed once for every row of the output from the first.

    In the second transformation, do a query similar to this in a table input step tied to the Oracle database using table name and schema name as parameters passed in:


    Code:
    select
      
    /* I forget why I had to alias the columns like this */ table_name as "table_name", column_name as "column_name", data_type as "data_type", data_precision as "data_precision", data_scale as "data_scale", data_length as "data_length", nullable as "nullable", column_id as "column_id" from all_tab_columns
    /* the ${} variables are the parameters passed in from the job that runs both transformations */ where upper(table_name) = '${source_table}' and upper(owner) = '${source_schema}' order by column_id


    Then write a javascript step to read / parse this metadata information and dynimically build the create table SQL statement, pass that built SQL to an execute SQL step tied to the destination database (PSQL) , and you're done.

    This only gets the create table statements, not view definitions or sequences, indexes, etc. You can expand on this idea to get those other objects though.
    Last edited by dnrickner; 09-20-2014 at 02:41 PM. Reason: formatting

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.