Qns: What is SQL*LOADER
Ans: SQL*LOADER utility is used to transfer data from Flat file to Temporary tables.
For example we Used SQL*Loader mainly in Interface design to load data from flat file to Temporary table.
Qns: What is syntax of Control file?
Ans:
load data
INFILE
Append/Repalce/Insert into TABLE
fields terminated by ','
optionally enclosed by " "
TRAILING NULLCOLS
(
COLUMN1,
COLUMN2,
.
.
.
COLUMN….N
)
Qns : How will you give position in fixed Files.
Ans: Column Name POSITION(1:2)
Like this.
load data
INFILE
Append/Replace/Insert into TABLE
(
COLUMN1 POSITION(1:2),
COLUMN2 POSITION (3:6),
.
.
.
COLUMN….N
)
Qns :You have 100 records in flat file.if 99 records goes in temp table and One Record fails. Whats will happen to that Record file
Ans: That Record Goes into Bad File generated by SQL*Loader.
Qns: What is difference between Badfile, Logfile and Discard files.
Ans:
• Bad file: Record with error goes into Bad file.
• Log file: History of all records like number of records are loaded, number of record fails validations.
• Discard File: Records which fails in When Clause.
Qns: How will you Give Constant values to SQL*LOADER.
Ans : Column Name CONSTANT ‘AUD’
For Example:
load data
INFILE
Append/Repalce/Insert into TABLE
fields terminated by ','
optionally enclosed by " "
TRAILING NULLCOLS
(
COLUMN1 CONSTNAT AUD’,
COLUMN2,
.
.
.
COLUMN….N
)
Qns : Whats is syntax of executing SQL*LOADER.
Ans : Sqlldr userid = Control= Data=
Qns : SQL*Loader is auto commit or Not?
Ans: Yes Sql*loader is auto commit . because it end is give message Commit Point reached.
Qns : How will you register SQL*LOADER AS a Concurrent Programs.
Ans :
• Define executable by giving Execution Method as SQL*Loader.
• Define Concurrent Programs
• Attached Concurrent Program to the Request Group of appropriate Responsibility.
Qns: Where you placed Control file on server.
Ans: In Appropriate top bin Folder. For example if Control File is of Oracle Payable module then Control file should be placed in $AP_TOP/bin Folder.
Qns : In Which Mode You Placed Control Files on server.
Ans: ASCII mode
Qns: Whats the Common Error you faced during loading data from flat file to Temporary tables.
Ans: Error like.
• For insert Option your table should be empty.
• Started with “ but not ended with “
Qns : Can you Load data into Multiple tables with the help of SQL*Loaders.
Ans : Yes by giving condition in when clause.
Can one load variable and fix length data records?
Yes, look at the following control file examples. In the first we will load delimited data (variable length):
LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (data1, data2)
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"
If you need to load positional data (fixed length), look at the following control file example:
LOAD DATA
INFILE *
INTO TABLE load_positional_data (data1 POSITION(1:5), data2 POSITION(6:15) )
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
Can one skip header records load while loading?
Use the "SKIP n" keyword, where n = number of logical rows to skip. Look at this example:
LOAD DATA
INFILE *
INTO TABLE load_positional_data
SKIP 5(data1 POSITION(1:5), data2 POSITION(6:15))
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
Can one modify data as it loads into the database?
Data can be modified as it loads into the Oracle Database. Note that this only applies for the conventional load path and not for direct path loads.
LOAD DATA
INFILE *
INTO TABLE modified_data( rec_no "my_db_sequence.nextval", region CONSTANT '31', time_loaded "to_char(SYSDATE, 'HH24:MI')", data1 POSITION(1:5) ":data1/100", data2 POSITION(6:15) "upper(:data2)", data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')" )
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112
LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","(addr, city, state, zipcode, mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)", mailing_city "decode(:mailing_city, null, :city, :mailing_city)", mailing_state)
Can one load data into multiple tables at once?
Look at the following control file:
LOAD DATA
INFILE *
REPLACE
INTO TABLE emp WHEN empno != ' ' ( empno POSITION(1:4) INTEGER EXTERNAL, ename POSITION(6:15) CHAR, deptno POSITION(17:18) CHAR, mgr POSITION(20:23) INTEGER EXTERNAL )
INTO TABLE proj WHEN projno != ' ' ( projno POSITION(25:27) INTEGER EXTERNAL, empno POSITION(1:4) INTEGER EXTERNAL )
Can one selectively load only the records that one need?
Look at this example, (01) is the first character, (30:37) are characters 30 to 37:
LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_tableWHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'(region CONSTANT '31', service_key POSITION(01:11) INTEGER EXTERNAL, call_b_no POSITION(12:29) CHAR )
Can one skip certain columns while loading data?
One cannot use POSTION(x:y) with delimited data. Luckily, from Oracle 8i one can specify
FILLER columns. FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want. Look at this example:
LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ',' ( field1, field2 FILLER, field3 )
How does one load multi-line records?
One can create one logical record from multiple physical records using one of the following two clauses:
CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
CONTINUEIF - use if a condition indicates that multiple records should be treated as one.
Eg. by having a '#' character in column 1
How can get SQL*Loader to COMMIT only at the end of the load file?
One cannot, but by setting the ROWS= parameter to a large value, committing can be reduced.
Make sure you have big rollback segments ready when you use a high value for ROWS=.
Can one improve the performance of SQL*Loader?
A very simple but easily overlooked hint is not to have any indexes and/or constraints (primary key) on your load tables during the load process. This will significantly slow down load times even with ROWS= set to a high value.
Add the following option in the command line: DIRECT=TRUE. This will effectively bypass most of the RDBMS processing. However, there are cases when you can't use direct load. Refer to chapter 8 on Oracle server Utilities manual.
Turn off database logging by specifying the UNRECOVERABLE option. This option can only be used with direct data loads.
Run multiple load jobs concurrently.
What is the difference between the conventional and direct path loader?
The conventional path loader essentially loads the data by using standard INSERT statements. The direct path loader (DIRECT=TRUE) bypasses much of the logic involved with that, and loads directly into the Oracle data files. More information about the restrictions of direct path loading can be obtained from the Utilities Users Guide.
What is SQL*Loader and what is it used for?
SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.
20 Comments
Nice blog. Really helpful information about Oracle …. Please keep update some more…………
ReplyGreat Article, thank you for sharing this useful information!!
ReplyLinux Online Training India
Online devops Training India
Hadoop admin online Training India
thanks for sharing this post
ReplyDevOps Training in Chennai | DevOps Training Institute in Chennai
thanks for sharing this post
ReplyDevOps Training in Chennai | DevOps Training Institute in Chennai
nice..
Replyforeach loop in node js
ywy cable
javascript integer max value
adder and subtractor using op amp
"c program to find frequency of a word in a string"
on selling an article for rs 1020, a merchant loses 15%. for how much price should he sell the article to gain 12% on it ?
paramatrix interview questions
why you consider yourself suitable for the position applied for
Nice Post..
Replyiot internships
inplant training in chennai
internship for automobile engineering students
internship for mca students in chennai
internship for eee students
internship for aeronautical engineering students
inplant training report for civil engineering
internship for ece students in chennai with stipend
summer training for ece students after second year
python internship
Good..
Replyhow to hack flipkart
tp link wifi password hack
power bi developer resume
android secret codes and hacks pdf
slideshow html code for website
javascript max integer
tell me about yourself
given signs signify something and on that basis
kumaran systems interview pattern
bangalore traffic essay
great...
ReplyInplant Training in Chennai
Iot Internship
Internship in Chennai for CSE
Internship in Chennai
Python Internship in Chennai
Implant Training in Chennai
Android Training in Chennai
R Programming Training in Chennai
Python Internship
Internship in chennai for EEE
awesome blog.
ReplyIndustrial training for electronics and communication engineering students
Summer internship for ece students
Internship in bangalore for computer science students
Internships in bangalore for cse students 2019
Internship
Internship in kerala
Internship in chennai for eee with stipend
Internship in chandigarh for cse
Ethical hacking internship in chennai
Architecture firms in chennai for internship
nice blog.
ReplyAcceptance is to offer what a
lighted
A reduction of 20 in the price of salt
Power bi resumes
Qdxm:sfyn::uioz:?
If 10^0.3010 = 2, then find the value of log0.125 (125) ?
A dishonest dealer professes to sell his goods at cost price
but still gets 20% profit by using a false weight. what weight does he substitute for a kilogram?
Oops concepts in c# pdf
Resume for bca freshers
Attempt by security transparent method
'webmatrix.webdata.preapplicationstartcode.start()' to access security critical method 'system.web.webpages.razor.webpagerazorhost.addglobalimport(system.string)' failed.
Node js foreach loop
nice blog.
ReplyInternship for mba
Internships in chennai for cse students
Robotics training
Ccna certification in chennai
Industrial training for diploma ece students in hyderabad
Internship certificate for bba student
Internships in bangalore for ece
Internship
Inplant training report
Internship in coimbatore for eee
great blog.
ReplyComplaint letter to bank for deduction
Cisco aci interview questions
Type 2 coordination chart l&t
Mccb selection formula
Given signs signify something and on that basis assume the given statement
Adder and subtractor using op amp theory
Power bi resume for 3 years experience
Power bi resume for experience
Php developer resume for 2 year experience
Ayfy cable
awesome bloggers.....!!
Replypoland web hosting
russian federation web hosting
slovakia web hosting
spain web hosting
suriname
syria web hosting
united kingdom
united kingdom shared web hosting
zambia web hosting
inplant training in chennai
Great...
ReplyIntern Ship In Chennai
Inplant Training In Chennai
Internship For CSE Students
Online Internships
Coronavirus Update
Internship For MBA Students
iot internship
Bring your Organisation Brand into the Digital World, to know more contact us
Replywww.bluebase.in
https://www.facebook.com/bluebasesoftware/
https://www.linkedin.com/…/bluebase-software-services-pvt-…/
https://twitter.com/BluebaseL/
#applications #EnterpriseSolutions #CloudApplication #HostingServices #MobileAppDevelopment #Testing #QA #UIdesign #DigitalMarketing #SocialMediaOptimisation #SMO #SocialMediaMarketing #SMM #SearchEngineOptimisation #SEO #SearchEngineMarketing #SEM #WebsiteDevelopment #WebsiteDesigning #WebsiteRevamping #crm #erp #custombuildapplication #android #ios
Pretty article! I found some useful information in your blog....
Replyso here we provide,
We provide you with flexible services and complete hybrid network solutions. It can provide your organisation with exceptional data speeds, advanced external security protection, and high-resilience by leveraging the latest SD-WAN and networking technologies to monitor, manage and strengthening your organisation’s existing network devices.
https://www.quadsel.in/networking/>
https://twitter.com/quadsel/
https://www.linkedin.com/company/quadsel-systems-private-limited/
https://www.facebook.com/quadselsystems/
#quadsel #network #security #technologies #managedservices #Infrastructure #Networking #OnsiteResources #ServiceDeskSupport #StorageServices #WarrantyAMCServices #datacentersolutions #DataCenterBuild #EWaste #InfraConsolidation #DisasterRecovery #NetworkingServices #ImagingServices #MPS #Consulting #WANOptimisation #enduserservices
Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article..
ReplySalesforce Training in Chennai
Salesforce Online Training in Chennai
Salesforce Training in Bangalore
Salesforce Training in Hyderabad
Salesforce training in ameerpet
Salesforce Training in Pune
Salesforce Online Training
Salesforce Training
Awesome blog. It was very informative. I would like to appreciate you. Keep updated like this!
ReplySaleforce Training in Gurgaon
Saleforce Developer Training in Gurgaon
Salesforce lightning training in Gurgaon
Salesforce Einstein training in Gurgaon
Salesforce integration training in Gurgaon
Salesforce Marketing Cloud Training in Gurgaon
Nice collection. Please check out our website on Microsoft SQL Certification
ReplyYou've provided quite good information here about Automated Interview Scheduling. This is fantastic since it expands our knowledge and is also beneficial to us. Thank you for sharing this piece of writing.
ReplyPost a Comment