Tuesday, March 5, 2013

SQL Loader Control File

SQL Loader is a Oracle provided utility to load the data or records from any external systems.
It can load .csv(Comma seperated values), Pipe delimited values.

In Oracle apps, there's a executable type called "SQL* Loader"
This kind of concurrent programs search for control file in $APPL_TOP/1.0.0/bin

Sample control file

OPTIONS (ERRORS=30000)
OPTIONS(SKIP=1)
   LOAD DATA
   INFILE '%1' --to get the file name as parameter from the concurrent program
   APPEND -- it can also be INSERT to insert into fresh tables
   INTO TABLE APPS.SERIAL_TABLE
   FIELDS TERMINATED BY ','  --Pipe delimited value (, or |)
   OPTIONALLY ENCLOSED BY '"'
   TRAILING NULLCOLS
  (
        SERIAL_NUMBER,
        MAX_COUNT,
        EXPIRY_DATE         "TO_DATE(TRIM(:EXPIRY_DATE),'MM/DD/YYYY')",
        TEMP_NAME         "REPLACE(:TEMPLATE_NAME,'|',',')",
        TEM_ID,
        EXT_DATE FILLER , -- FILLER option can be used above SQL loader Version 8i.
        --EXT_DATE          "TO_DATE(TRIM(:EXTRACT_DATE), 'MM/DD/YYYY HH24:MI')",
        --EXT_DATE         "TO_DATE(TRIM(:EXTRACT_DATE), 'MM/DD/YYYY HH:MI:SS PM')",
        BATCH_ID "trim(trailing ' ' from LTRIM(RTRIM(:BATCH_ID)))",
        STATUS CONSTANT 'NEW',
        CREATION_DATE SYSDATE,
        LAST_UPDATE_DATE SYSDATE,
        CREATED_BY     "-1",
        LAST_UPDATED_BY "-1"
  )


To Run the Control file
sqlldr apps/apps_pwd control=control_file_name.ctl

11 comments:

  1. Hi
    I read this post two times.
    I like it so much, please try to keep posting.
    Let me introduce other material that may be good for our community.

    Hadoop training

    ReplyDelete
  2. Hey very nice blog!!
    Hi there,I enjoy reading through your article post, I wanted to write a little comment to support you and wish you a good continuationAll the best for all your blogging efforts.
    Appreciate the recommendation! Let me try it out.
    Keep working ,great job!
    Microsoft dynamics training

    ReplyDelete
  3. Valuable information and excellent design you got here! I would like to thank you for sharing your thoughts and time into the stuff you post!!
    Latest jobs

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. I am Sridevi Koduru, Senior Oracle Apps Trainer at Oracleappstechnical.com With 8 Yrs Exp on Oracle Apps and 13 Yrs IT Exp Providing Online Training on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Apps SCM, Oracle Apps HRMS, Oracle Financial for Indian Localization, SQL, PL/SQL and D2K. I have Provided Training for 500+ Professionals Most of them are Working in Real Time now.

    Contact for (One to One Personal Online Training) on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Apps SCM, Oracle Apps HRMS, Oracle Financial for Indian Localization, SQL, PL/SQL and D2K at training@oracleappstechnical.com | sridevikoduru@oracleappstechnical.com | +91-9581017828 | http://www.oracleappstechnical.com

    Linkedin profile - http://in.linkedin.com/pub/sridevi-koduru/8b/76a/9b8/

    ReplyDelete
  6. Its impressive to know something about your note on Oracle apps Course. Please do share your articles like this your articles for our awareness. Mostly we do also provide Online Training on Cub training oracle apps course.

    ReplyDelete
  7. Nice one,
    You can get more information at
    http://ebiztechnics.blogspot.in

    ReplyDelete
  8. Hi friends, This is Murali from Chennai. I am a technology freak. Your technical information is really useful for me. Keep update your blog.
    Regards..
    Oracle Training Institutes in Chennai

    ReplyDelete
  9. There are lots of information about latest technology and how to get trained in them, like Big Data Training in Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies(Big Data Training). By the way you are running a great blog. Thanks for sharing this. cloud computing training chennai

    ReplyDelete
  10. www.oracleonlinetraining.com is providing Oracle APPS Functional and Technical

    training contact us:+91-9533456356, by real time experts in USA,UK,Canada,

    Australia.Oracle apps finance online training,oracle Apps SCM online

    training,Oracle apps technical online training,oracle Apps 11g online

    training,Oracle apps manufacturing training,Oracle Apps Supply Chain Management training

    full course details please visit our website http://oracleonlinetraining.com/



    ReplyDelete
  11. I read full blog its so important blog to learn about SQL Loader file.............Visit our best website for Oracle R12 financials online training

    ReplyDelete