Query as source in 12C

Query as source in 12C:

Today we will discuss on how a query can be used as source in ODI mapping. This time you will see a new option called Extract Options for source data store.  Remember a source data store can be the physical data store from the model or it can be a reusable mapping with output signature.

This seems to be more flexible now in terms of mappings. In 11g, for each query you have to create a temporary interface with specific source query, but in 12c you just have to create a generic output signature. Then the query will be mapping specific. Define the query with required fields and skip the unnecessary one. ODI will use its intelligence to select the mapped columns only. Isn’t it a good feature?

You can track the conversation with David in linkedin Oracle Data Integration group.

Okay here you go with screenshots.



Notice I have used a physical data store as source. However you can replace it with a reusable mapping having output signature. You can add as many columns you want into the output signature but make sure the selected fields of your query are present in the signature. You can refer link1 and link2 for reusable map.


You just double click on the value parameter of CUSTOM_TEMPLATE. Copy paste your query and save it. Since the default IKM would have been selected automatically, you can now execute the map.




Notice your query in the above screenshot.

Thanks for reading and ofcourse let me know if you see any issues or flaws.


About Bhabani 86 Articles
Bhabani has 12 plus years of experience in Data warehousing and Analytics projects that has span across multiple domains like Travel, Banking and Financial, Betting and Gaming Industries. Solution areas he focuses on designing the data warehouse and integrating it with cloud platforms like AWS or GCP. He is also a Elite level contributor at OTN forum more than 9 years. He loves to do experiment and POC on different integration tools and services. Some of his favorite skills are Redshift, Big Query, Python, Apache Airflow, Kafka, HDFS, Map Reduce ,HIVE, Habse, Sqoop, Drill, Impala.


  1. when i am creating Mapping in odi 12c Then i am getting these errors ..anyone knows about how 2 write a odisdk java code for create mapping pls tell me..

    Mapping odiInterface = new Mapping(“Mapping”+i, folder, context);
    //DataSet dataset = odiInterface.getDataSets().iterator().next();
    Dataset dataset = (Dataset) odiInterface.createComponent(“DATASET”,”DEFAULTDATASET”);
    SourceSet srcset = new SourceSet(“srcset0”, dataset);
    OdiDataStore odiDatastore1 = ((IOdiDataStoreFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiDataStore.class)).findByName(“”, null);
    DatastoreComponent dsc = (DatastoreComponent) dataSet.addSource(odiDatastore1, false);

    OdiDataStore targetDatastore = targetComponent.getDataStore();
    InteractiveInterfaceHelperWithActions helper = new InteractiveInterfaceHelperWithActions
    (odiInterface, odiInstance, entityManager);
    List allFlowSatge = ProjectOdiVariables.allFlowSatge;
    Map stageNameTypeMap = null;

    for(OdiFlowStages odiFlowStages : allFlowSatge) {
    if(odiFlowStages.getTargetStageName().equalsIgnoreCase(targetComponent.getName())) {
    stageNameTypeMap = odiFlowStages.getStageNameTypeMap();
    List<Pair> sourceTable = new ArrayList<Pair>();
    List dataStoreNames = getAllDataStoreName();
    for(OdiInterfaceComponent sourceComponent : ProjectOdiVariables.source) {
    if(stageNameTypeMap.containsKey(sourceComponent.getName())) {
    SourceDataStore sd = new SourceDataStore(dataset, false, sourceComponent.getName().toUpperCase(), 0, sourceComponent.getDataStore());
    List refTables = sourceComponent.getRefTables();
    int count = 0;
    if(null!=refTables) {
    int size = refTables.size();
    while(size > count) {
    String tableName = refTables.get(count);
    int lastIndexOf = tableName.lastIndexOf(“.”);
    tableName = tableName.substring(lastIndexOf+1);

  2. Hi Bhabani,

    I was trying to create temporary interface in oracle 12c.

    Created a new datastore in model. Considered that as target and did mapping. It didn’t work though.

    Can we create temporary interface in 12c. Please guide me through.

    • An interface is an object in ODI which consists of a set of rules that define the loading of a datastore or a temporary target structure from one or more source datastores.

  3. I have used a query in extract option in the source , then I am using expression , the code which i have written in the expression that is not getting picked up. please let me know what should i do.

  4. Dear Bhabani,
    Can we do the same in ODI 11g without using temporary Target??? For example, I am using source query as (select * from employees) this will be the source query and Target table is the structure of Emp table. And my requirement is to load the result of that query into the target table without using any table /join in the source level. Can we achieve this in ODI 11g?? Advance thanks.

  5. I just realize that this is an old threat but I am hoping you can still answer me.

    I have the following problem:
    I have a source datatable with an attribute that declares queries. I need to obtain the results of that queries (one for each register in the SRC datatable) and store them in a target datatable.

    Note: the queries may need to consult the attributes from other tables so they can work.

    I put an example just to clarify (or I hope):
    We have the following source table: SRC_APP and is composed by the following attributes: ID_APP, ID_TF, X1 and X2
    We also have the following parametric table: TF_PARAM and is composed by ID_TF, ALIAS and TF_QUERY
    And finally a target table: TRG_APP that has ID_APP, ID_TF, X1, X2 and TF_ALIAS (the name is variable)

    The attribute TF_PARAM.TF_QUERY has different queries that may need the attributes SRC_APP.X1 and SRC_APP.X2 to execute. I need to run them and store the result in the attribute TRG_APP.TF_ALIAS which is defined by the attribute (the value of it for each row) TF_PARAM.ALIAS, in other word the attribute TRG_APP.TF_ALIAS it defines in which attribute of the target table I need to store the result of the query.

    I hope you can help me.

    Thank you in advance.

  6. Hi,

    I have a complex SQL query to be used as the source and I am using ODI 12c. What do I need to do in order to make ODI use the SQL query as the source(table/datastore). I am building an environment which uses OGG between Source and Staging and ODI between Staging and Target.


  7. Hi Bhabhani,
    I need to create a mapping for which source is sql query and target is file. I created the reusable mapping with the output signature as file attributes. I updated the query in custom template in main mapping but I am not getting any IKM to select in drop down. I do have imported IKM Sql TO File Append and target integration type is Control Append in the main mapping.

  8. The problem I face with using this along with reusable mapping in 12c is , with how to set up execution db !?

    Now, I am pretty new to 12 c but in 11g we could simply change the execution db in overview!
    This I dnt see in 12 c reusable mapping. Also, even while using command odiRef.getUserExit(“query inside”), we could use dbLinks. Do we have to do the ssame here to change the execution db?

  9. Hi Bhabani,

    How to find the list of procedures which are using particular tables.
    example: if i have X(table name) table and it used in 10 ODI Procedures ,now i want to know the list of all 10 procedures.

    please help on how to find the procedures list based on table name.


  10. Hi Bhabani,

    How to find the list of procedures which are using particular tables.
    example: if i have X(table name) table and it used in 10 ODI Procedures ,now i want to know the list of all 10 procedures.

    please help on how to find the ODI procedures list based on table name.


Comments are closed.