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 pest 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 the author

Bhabani(http://dwteam.in) - Currently Bhabani is working as Sr Development Engineer at Harman International. He has good expertise on Oracle, Oracle Data Integrator, Pervasive Data Integrator, MSBI, Talend and Java. He is also contributing in ODI-OTN forum for last 5 years. He is from India. If you want to reach him then please visit contact us page. If you have any doubts or concerns on the above article, please put your question here. Dw Team will try to respond it as soon as possible. Also dont forget to provide your comments / suggestions / feedback for further improvement. Thanks for your time.

Similar Posts


how 2 change source set and sourceDataStore in odi 12c sdk code

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

anyone knows how 2 create Mapping in odi 12c using odi sdk 12c api

Hi Bhabani,

do you have any idea about how 2 write a odi sdk java code for creating mapping in odi 12c pls tell me..

Hi Ram,
You can refer this post ( http://dwteam.in/create-project-using-odi12c-groovy-script ) to get idea on creating mapping. Yes its groovy script. I will replicate this in java and will try to post tonight. Reference http://docs.oracle.com/middleware/1212/odi/ODIJA/overview-summary.html

Hi Bhabani,
Thanks for your information.

Now I want 2 create a mapping in odi through Javacode using eclipse..

Hi Bhabani,

I am looking for your reply on the java code for creating mapping in ODI 12c


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.


we can’t create interface in odi 12c, Mapping is introduced in odi 12c instead of interface please check below link

What is exact definiton of interface

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.

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.

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.

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.

Leave a reply


Are you a human? *