1 Reply Latest reply on Jan 11, 2019 10:56 PM by Adrian_Popescu-Oracle

    [ODI 12c] Using SQL as source (Reusable Mappings) and insert into table from different database




      I have problem with buil mapping.


      I have two database:


      1. Source - I have user: PA and I can use select like:


      I can't create any objects like view, I can only use sql query. I give simple example code but in real it has procedure into query, but main problem is to use SQL query as source.

      I create physical/logical scheme like: E_SRC


      2. Target - I have table: APP_STATUS_TRG with columns: INV_ID, APP_STATUS

      Physical/logical scheme: E_TRG


      Physical/Logicam scheme working fine.

      Now I want create mapping.


      1. I created Reusable Mapping with output signature with two atributes: INV_ID APP_STATUS - like Yellow Interfaces in Odi 11g.

      2. Next I want create mappings - how can I do this?

      I have idea like:


      Mappings has Dataset with my Reusable Mapping (I set Extract Options in physical layer I paste my sql query) and target table: APP_STATUS_TRG - but I can't set logical schemas for dataset or can I?

      I want in physical layer in mapping: E_SRC_UNIT with my reusable mapping and E_TRG_UNIT with target table.


      How can I do this or maybe someone have another solution?

        • 1. Re: [ODI 12c] Using SQL as source (Reusable Mappings) and insert into table from different database



          It is not clear why have you used reusable mappings.

          In mappings you add datastores (tables / views).

          Datastores belongs to models.

          Models have setup logical schemas, mapped to a physical schemas in a specific context.

          Physical schemas belongs to a Data servers, describing the access to the databases.


          Now, you can transfer data from one database to another. They may be also on different technologies: Oracle, MS Sql Server, MySql, Teradata, etc.

          Since the source is a Sql query, then you need to use "Extract option".

          So, you setup a model for source DB and reverse engineer a table.

          You drag that table in the mapping.

          You use  "Extract option" and setup your SQL.

          Instead of source table, ODI will use the Sql query to extract data.

          The SQL query will be executed on the same logical schema setup in the model.

          But prefix each table / function / view with its schema name.

          Data sets are not needed also.

          By default, when you create a mapping, there is a checkbox to create a data set also - you may uncheck it.

          It is used more for backwards compatibility.