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

37 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. 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
  3. Nice one,
    You can get more information at
    http://ebiztechnics.blogspot.in

    ReplyDelete
  4. 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
  5. 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
  6. 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
  7. Hello, I read your blog occasionally, and I own a similar one, and I was just wondering if you get a lot of spam remarks? If so how do you stop it, any plugin or anything you can advise? I get so much lately it’s driving me insane, so any assistance is very much appreciated.
    Hadoop Training in Bangalore
    Hadoop Training in Chennai
    Big data training in tambaram
    Big data training in tambaram

    ReplyDelete
  8. This is ansuperior writing service point that doesn't always sink in within the context of the classroom. In the first superior writing service paragraph you either hook the reader's interest or lose it. Of course your teacher, who's getting paid to teach you how to write an good essay, 
    python training in chennai
    python training in chennai
    python training in Bangalore

    ReplyDelete
  9. You blog post is just completely quality and informative. Many new facts and information which I have not heard about before. Keep sharing more blog posts.
    java training in omr | oracle training in chennai

    java training in annanagar | java training in chennai

    ReplyDelete
  10. Whoa! I’m enjoying the template/theme of this website. It’s simple, yet effective. A lot of times it’s very hard to get that “perfect balance” between superb usability and visual appeal. I must say you’ve done a very good job with this.

    Oracle Training in Chennai | Best Oracle Training Institute in Chennai
    Web Design Training in Chennai
    Web Design Training in Chennai|Best Web Design Training in Chennai
    AngularJS Training in Chennai |Advanced SAS Training in Chennai | Best SAS Training in Chennai

    ReplyDelete
  11. This is most informative and also this post most user friendly and super navigation to all posts... Thank you so much for giving this information to me.. 
    Online DevOps Certification Course - Gangboard
    Best Devops Training institute in Chennai

    ReplyDelete
  12. A universal message I suppose, not giving up is the formula for success I think. Some things take longer than others to accomplish, so people must understand that they should have their eyes on the goal, and that should keep them motivated to see it out til the end.

    python training Course in chennai
    python training in Bangalore
    Python training institute in bangalore

    ReplyDelete
  13. It's interesting that many of the bloggers to helped clarify a few things for me as well as giving.Most of ideas can be nice content.The people to give them a good shake to get your point and across the command
    Data Science Course in Indira nagar
    Data Science Course in btm layout
    Python course in Kalyan nagar
    Data Science course in Indira nagar
    Data Science Course in Marathahalli
    Data Science Course in BTM Layout

    ReplyDelete
  14. Thanks for sharing great info with us.

    I wanted to write a little comment to support you and wish you a good continuation All the best for all your blogging efforts.Your good knowledge and kindness in playing with all the pieces were very useful.

    data science course in pune

    python training in pune

    ReplyDelete
  15. Thanks a lot for giving great kind of information. So useful and practical for me. Excellent blog and very informative, nice work keep updating. If you are looking for any R Programming related information, check our R Programming training in bangalore web page. Thanks a lot.

    ReplyDelete
  16. Really very informative and creative contents. These concept is a good way to enhance the knowledge.This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more .Nice blog,I understood the topic very clearly,And want to study more like this.
    Data Science Training In Chennai

    Data Science Online Training In Chennai

    Data Science Training In Bangalore

    Data Science Training In Hyderabad

    Data Science Training In Coimbatore

    Data Science Training

    Data Science Online Training


    ReplyDelete
  17. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it.data science course in Hyderabad

    ReplyDelete
  18. )Deep Learning Projects assist final year students with improving your applied Deep Learning skills rapidly while allowing you to investigate an intriguing point. Furthermore, you can include Deep Learning projects for final year into your portfolio, making it simpler to get a vocation, discover cool profession openings,
    Oracle apps Training in Bangalore

    ReplyDelete
  19. I surely acquiring more difficulties from each surprisingly more little bit of it
    data scientist training and placement

    ReplyDelete
  20. Truly quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue to post. Much obliged for sharing.cloud computing course in lucknow

    ReplyDelete
  21. Really an awesome blog and very useful information for many people. Keep sharing more blogs again soon. Thank you.
    Data Science Certification in Hyderabad

    ReplyDelete
  22. A home equity loan—also known as an equity loan, home equity installment loan, or second mortgage—is a type of consumer debt. real estate investing Home equity loans allow homeowners to borrow against the equity in their homes. The loan amount is based on the difference between the home’s current market value and the homeowner’s mortgage balance due. Home equity loans tend to be fixed-rate, while the typical alternative, home equity lines of credit (HELOCs), generally have variable rates. Home Equity Loans: What You Ought To Know

    ReplyDelete
  23. Good blog. keep up with writing skill. Looking forward for more blogs.
    SQL Classes in Pune

    ReplyDelete