how to create a temp table in ODI 12C

1
votes

I would like to know if it's possible to create a temp table inside a mapping in ODI 12C.

Any suggestions would greatly be appreciated.

1
oracleoracle-data-integrator
You should use proper tags, and tell us a little more about what you intend to do with your "temp" table... What do you need it for? (refer to How to Ask for quicker and more accurate answers). – J. Chomel
Each row from a source table should create multiple rows in a temp table and be transformed and moved to a target table. – Arman Sanaee
Your question is still very vague. Any tutorial you've tried? Maybe you should dig into RDBMS Table Transformation? – J. Chomel

1 Answers

0
votes

You can go to Oracle Data Integrator

  1. in your project go to knowledge modules
  2. go to "Loading(LKM)"
  3. right click on "LKM SQL to SQL"
  4. select duplicate selection and set new name
  5. go to "tasks" tab in "Execution Unit Main" add new task

  6. you can go to the target command and write the below code

    create global temporary table <%=odiRef.getTable( "L" , "TARG_NAME" , "A" )%>_gtbl (t1 number,t2 varchar2(100 char))

This code creates a temp table in target oracle database, the name of the table would be "_gtbl". For example, if you have an "ABC" table then your target temp table will be "ABC_gtbl".

  1. create new mapping and drag and drop both source and target tables from models and set LKM knowledge Module to it.

Notice: Be careful, if you have the same schema for both tables (source and target) in a database LKM will not appear for you and you should use IKM instead of LKM.