ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

How to create a Temp Table in ODI Interface (Yellow Interface)

  • Define the Interface Name , optimization context and select the proper schema for staging Area different from the Target

  • In the Diagram Tab, Drag your source in to the Source side
  • In the Target Data store Click on the Untitled area and you can see in the properties area Name tab , Context and Temporary Data store Location
  • Type in the Target Temp table name you wish to create in the Name tab
  • Leave the context as it is unless you want specifically to run only for one context
  • Select the Work schema for Temporary Data store Location unless you wish to create in the Main Database or Schema.
  • To create copy of the entire table as the source, Right click and select Add to target.
  • For particular selected columns, Right click particular columns and select Add to Target and only those particular columns will be added to the target.
  • Modify the Data type for the target Database and also the column name ( if required ) . Verify the Column name and the Execution area i.e ( Source , Staging area or Target )
  • In the Flow tab, Select the appropriate IKM and put the Flow Control to be No and Create Targ Table to be Yes , so that ODI can create the table before loading in Data or Work schema as defined in step 6
  • Run the Interface and Check for Errors .If all fine you should see the table created in the Schema selected (in step 1) with data loaded. Go to the required schema check for the table and data .
  • when you create Temp table using ODI ,  you won’t see the option of Data to see the data directly in the Interface
  • Temp Interface are marked by a Yellow Color Icon unlike Blue

  • To use this Temporary table, just drag and drop the interface as the Source for other Interfaces. You don’t need to do the Selective Reverse of the temporary data store.

14 Comments

  1. When we create and add yellow interface to a blue interface and running blue interface will automatically run yellow interface ? or we need to run yellow interface first ?

  2. hi hope you are fine.

    kindly can you tell us that while upgrading odi 12c to 11 g we need to update temporary interface manually?

    • Hi,

      it should convert automatically, but I already got some cases where was necessary a manual intervention!

  3. Hi
    I have one error in yello/temp interface how can i solve it

    ERROR:ODI-17501 TEMPORARY TABLE HAS NO COLUMN

    THANKS
    KRISHNA

  4. Pingback: Yellow Interface (Temporary Interface) in ODI11g | DW TeamDW Team

  5. Can the temporary interface be used in an ODI procedure.

    I want to execute a procedure which references several different databases. But i do not want to create permananet db links for this. I am aware that we can use ODI interface to create DB links and Synonyms on the fly. SO i was wondering if this can be an option. If yes, tehn how?

    • Hi Priya,

      If you wish to create DBLink on the fly, the ODI procedure is a good option for that.
      I believe you’ll create it and drop at end of process, right? The databases will be accessed thru interfaces?

  6. Hi cezar.

    I have 3 questions:

    1. what is the time life of temp table?
    2. can i use this temp table to refresh a ODI variable? (i tried but i get a error that say: the table or view doesn’t exist)
    3 can i create a dinamic temp tables? (something like you posted about the odi parallel interfaces)

    Thanks

    • The life time of the temp table is till someone drop it manually or through script .
      Yes you can use this temp table to refresh , since even this is temp table the table is created in the database . This temp table is not the same as Oracle Global Temporary Table that is why you have table and data retained even after the session is disconnected.
      Yes you can create dynamic tables .

  7. After drag the yellow interface in diagram, there is not the precision of the columns. For example:
    1) run the interface (it creates the temp table with char(4))
    2) drag the temporary interface in a diagram and columns indicates only CHAR and not CHAR(4) so i have error massages when i save the new interface.
    (ODI 10g)

  8. Hello,

    I’m very interested in this solution. Sorry for asking a junior question – after running the interface, which module to go to check for the tables under WORK_SCHEMA? Is it in MODEL tab of DESIGNER or TOPOLOGY Manager? I can’t find it anywhere.

    thanks
    Jeffrey

    • Hi Jeffery,

      You won’t find the table under these tabs. You need to check directly the database for the tables created using this option.

Leave a Reply

Required fields are marked *.