ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Direct load by interface with no work tables (C$, I$)

Hi friends…

I got this video from Craig Stewart that shows us how to direct load data from source to target with no work (C$ and I$) table use.

http://s3.amazonaws.com/Ora/ODI-Simple_SELECT_and_INSERT-interface.swf

Simple, fast and very clever!

Thank you for share your expertize Craig!

14 Comments

Leave a reply →

Jyoti
August 13, 2015 at 8:43 AM

IKM SQL to SQL append not present in 11.1.1.7. need help.

Reply
khaja mohammed
May 20, 2015 at 10:24 AM

Hi,

I am trying to direct load in my ODI interface without work tables (C$ and I$). I am using ODI version 11.1.1.7.

I am not able to find out Staging area : MYSQL:MYSQL Source
and
Not able to find out IKM SQL to SQL append

Is it not possible in ODI version 11.1.1.7 or should I need to import any IKM or any drivers to achive this requirement.

Please help me on this.

Reply
raj
June 15, 2014 at 7:12 AM

Hi ,

I have a requirement wherein I need to avoid C$ and I$. But in my case, I have fetch/extract data from many tables and I don’t want to create staging in Source db as my source application is a web commerce portal and the portal team does not recommend to create staging area on an OLTP db as it might have a un-nesessary IO. The interface will run on a daily basis and it will extract around 200 thousand records.

Do you know if there is any other way of bypassing I$ and C$.

Reply
- Cezar Santos
  June 16, 2014 at 2:59 PM
  
  Hi,
  
  it’s a simple KM customization where you alter the “target” in the LKM to be the “real” target and, for IKM kill all steps.
  
  BUT, it’ll work only if you have every single data transformation, filters or joins mapped to be executed at source.
  
  Let me know if works and thank you for visit us!
  
  Reply
  - raj
    June 17, 2014 at 2:10 AM
    
    Hi.
    
    Sorry, I didn’t understand what you mean by customizing the KLM to a “real” target. Can you pls give an example.
    
    Since my source area has tables from different data servers, I don’t think we can bypass C$table. Correct? Please do let me know.
    
    Reply
  - Prabakar M
    March 3, 2017 at 1:58 PM
    
    Hi can any one help me.. I have to load data into target table from flat life. I will removed all steps in km and hard coded load data with insert statement.. But my file data as to converted into timestamp any1 can help
    
    Reply
Shaik
March 27, 2014 at 8:13 AM

Hi ,

I have tried with SQL Server (source database) to Oracle (target database) and Oracle (source database) to Oracle (target database) but in flow tab we should select LKM also or else while saving the interface it is showing error pop up box.

Could you please suggest me to avoid the C$ table or how to get bulk data from source database instead of 30 records every time.

ODI installed on Oracle 10g.

Thanks and Regards,
Shaik.

Reply
- Cezar Santos
  June 16, 2014 at 3:06 PM
  
  Hi,
  
  the amount of data that is fetched from some connection is controlled in the correspondent Data Server (Topology). You can change it over there.
  
  About the Bulk SQL, if you have distinct databases instances as source and target unless it possible to build some kind of “bridge” (DBLink, External Table, etc) it’s necessary go thru some text files to get a fast integration. But if a bridge is possible, then is just indicate it at ODI and use the right LKM.
  
  Make sense?
  
  Thank you for visit us!
  
  Reply
Leo
April 6, 2011 at 9:38 AM

Raul can you be more specific on how to modify “generate ctl ” step.

Thanks

Reply
raul alvarenga
February 11, 2011 at 10:16 PM

hi, i was use another method.
working with sql loader integrated feature, (file to oracle), i modified the lkm specific “generate ctl ” step , i change odi variable to load data directly to target table rather than temp c$ table.

Reply
- Bandaru_rk
  October 10, 2013 at 3:05 AM
  
  Hi Raul,
  
  Can you please explain specifically how to modify “generate ctl ”. It help me so much.
  
  Thanks
  
  Reply
  - kdevendr
    October 10, 2013 at 4:56 PM
    
    what kind of changes are you looking for ?
    
    Reply
Craig
December 4, 2010 at 4:12 AM

Yes indeed, this has always been available, it is just one of those “you gotta know it to use it!” things.
I hope you found this useful.

Reply
Enguerran
December 3, 2010 at 7:04 AM

Is it working only in ODI 11g or is it possible in 10g also ?

Reply

Leave a Reply Cancel reply

Recent Posts ODI Expert and why there is no new posts… Passing Values to ODI Variables in Packages/Scenarios New Author Arriving… ODI Certification – Approved How to Change ODI Language (Idiom) Recent Comments Dylan on Killing session in ODI 11g Cezar Santos on CDC CONSISTENT geeta on CDC CONSISTENT Cezar Santos on Internal ID monish on Internal ID Archives February 2016 April 2015 March 2015 February 2014 November 2013 September 2013 December 2011 November 2011 September 2011 August 2011 June 2011 March 2011 February 2011 January 2011 December 2010 November 2010 October 2010 September 2010 August 2010 July 2010 June 2010 April 2010 March 2010 January 2010 December 2009 November 2009 October 2009 September 2009 August 2009 Categories Administration Architecture Common Errors Drivers How to Java Codes Knowledge Modules Logic ODI ODI Bug Others Reports SDK SQL Technology Tips and Tricks Uncategorized Meta Log in Entries RSS Comments RSS WordPress.org

Recent Jobs ODI Expert and why there is no new posts… Passing Values to ODI Variables in Packages/Scenarios New Author Arriving… ODI Certification – Approved How to Change ODI Language (Idiom) Information about the ODI Certification Exam Finally!!!! The Oracle Data Integrator 11g Certified Implementation Exam!!!!!! Interface with Union, Minus Operator using ODI SDK Export and Importing Scenarios based on Project (or)Folder Creating Interface for Single Source and Target Creating Permanent Interface based on Model Level Automatically add Interfaces/Scenarios to new ODIPackage using ODI SDK Creating Package using SDK Handling the “ORDER BY” clause in an interface Creating Temporary Interface using ODI SDK Creating ODI Procedure using SDK ODI SDK Setup and Config in Eclipse Things to be pride of…. CDC CONSISTENT Ordered SQL Killing session in ODI 11g 11g Oracle Data Integrator – Part 8/11g – Table Partitioning supported Jython Scripts fails to recognize the module during scenario run in ODI 11g Scenarios, a lot in the first delivery makes maintenance very simple! Direct load by interface with no work tables (C$, I$) 11g Oracle Data Integrator – Part 7/11g – SUNOPSIS MEMORY ENGINE 11g Oracle Data Integrator – Part 6/11g – Union , Minus, Intersect Jython’s Beginner Guide for ODI Send SQL by Email Security Module – Main concepts and use Drivers Location in ODI 11g 11g Oracle Data Integrator – Standalone Agent but, and about the Windows Service??? 11g Oracle Data Integrator – Part 5/11g – odiConsole, the new Metada Navigator Using one of ODI’s lesser-known secrets: the Common Format Designer (CFD) – by Craig Stewart 11g Oracle Data Integrator – Part 4/11g – Upgrading from 10g 11g Oracle Data Integrator – Upgrading from 10g – Part 4/11g First year of many…. Thank you Friends! 11g Oracle Data Integrator – Part 3/11g – Graphical User Interface Datatypes – Creating what is missing for any technology IS_NUMBER (and IS_CHAR) at Oracle? A workaround… Calling OdiSqlUnload using ODI Procedure with no hardcoded password SNP_EXP_TXT – A “huge” table problem Internal ID SCD Type 2 11g Oracle Data Integrator – Part 2/11g – Creating Repositories 11g Oracle Data Integrator – Creating Repositories – Part 2/11g Regenerate ODI Objects using OdiGenerateAllScen Generate Column_name Header for OdiSqlUnload Error Records Log Get the Error Message of any Failed ODI objects inside Package SCD TYPE 3 Best practice of ODI 11g Oracle Data Integrator – Part 1/11g – First Screen Automated ODI Export Import Scenarios LKM OdiSqlUnload(File) to Oracle ( SQLLDR ) Jython OS bug in Windows 7 Writing SQL counts using Jython Getting one or several unknown files from a directory Interface Parallel Execution – A new solution Displaying SQL results in Operator Flow Control and Static Control Export DWR to EWR [ Common Master Rep ] Scenario Report Asynchronous -Parallel Execution Dropping $ Tables KM FOR UNION BASED ON IKM SQL CNTRL APPEND Installing ODI in Windows 7 Querying in ODI Changed Data Capture ( CDC ) Agent Manual entry in ODI Driver List MINUS IN ODI Variable Detail Query Interface Mapping Query Topology Connection Detail Versioning Get File Length and Header in Operator Find the Value of the Variable in Operator ? Invisible Join Shortcuts Keys Export – Import DWR to EWR having same Master Rep Categorize Scenarios Alignment of objects in Package Duplicate Multiple Objects in Package Smart Search Export – Import DWR to DWR having same Master Repository Procedures (or KM) Steps – Source and Target tab’s Subscribers – An example of how to use Why Creating a separate schema in ODI? How to show Business Messages on Operator and, too, debug variables… Topology – Data Server – Which user should be used to connect? How to use PL/SQL procedures and functions in ODI Context, Logical And Physical Schema – How does it work? Repository Architecture – Work Repository, Development or Execution? When and why uses each one. Removing Special character using Jython script Faster and Easiest way to design Interfaces AUTOMIZE ODI REPOSITORY EXPORTS Informix Connection SQL Server connection Repository Architecture – Two or more Masters – Part 3 Report of list of KMs in each Interface Connecting openLDAP How to call a Web Service that start an ODI Scenario and waits its complete execution Processing multiple Interface through single Package Multiple Files – Single Interface Generate Multiple Scenarios Sequence How to get a constant or pseudo-column from source mapping Lock Windows Layout Hide Unused Technologies Tab at the Top oracle.jdbc.V8Compatible Oracle to Flat file Scheduling using ODI Scheduler Exception – getobjectname schema How to create a Temp Table in ODI Interface (Yellow Interface) Printing report Step by step procedure to Read Excel (*.xls) Importing Knowledge Modules Creating Master and Work Repository Installation Repository Architecture – Just one Master – Part 2 How to refresh ODI variables from file – Part 2 – Getting all lines, once at time How to refresh ODI variables from file – Part 1 – Just one value Repository Architecture – Just one Master – Part 1 Welcome to ODI Experts, this is Cezar Santos.