Sql*Loader
1.What Is Sql*Loader ?
Sqlloader Is An Oracle Server Tool Used To Load Data From Operating System Files Into Oracle Server data Tables.
2.What Are Files In Sqlloader And Define Them?
Sql Loader Is Having Five Types Of Files
Those Are Following
1.Flat File
2. Control File
3.Bad File
4.Dis Card File
5.Log File
1.Flat File:-
Flat File Is File Which Is Given By Client And Is Having Data Which Is Going To Be Load Into Data Bases.
The File Format May Be .Dat,.Txt And Csv(Comma Separated View)
2.Control File
Control File Is A File It Is Main File Of Sql loader.It has Necessary Information On Format Of Data And Sql Statements
3.Bad File
Bad File Contain Records Those Are Not Transfer Data To Database During Loading Data .Due To Problem of Data Type Mismatch And Declaration (Syntax Errors). Rejects By Control File.
4.Discard File
Discard file Having Records Those Are Not Satiesfed By Where Condition In Select Stament.
5.Log File
Log File Contain Summary Information Of Controle File
How Many Records Stored In Data Bases And How Many Records Rejects And .Etc.
3.CONTROL FILE SYNTAX
LOAD DATA
INFILE
INSERT OR APPEND OR REPLACE OR TRUNCATE INTO TABLE
FIELDS TEMINATED BY ‘’
()
4.SQL LOADER OVERVIEW
5.SQL LOADER DATA TYPES
O F THE DATA TYPE.
6. WHAT ARE SQL FUNCTIONS USED IN CONTROLE FILE?
Syntax
LOAD DATA
INFILE *
APPEND INTO TABLE XXX
(
“LAST “ POSITION(1:7) CHAR “UPPER(:\”LAST\”),
FIRST POSTION (8:15) CHAR “UPPER(:FIRST)” )
BEGINDATA
Sharan Gowda
Raghu Ram
Here “LAST” is sqlloader’s keyword That is Way to Mention In Double Codes
In Declaration FIRST Is Keyword Of Sql Loader
• SQL LOADER functions must be enclosed in double quotations mark
• Colomn name and name of the column in sql string must match exactly.
Some of the sql functions as follows
Field1 position (1:9) DECIMAL EXTERNAL(8) “FIELD1/100”
FILED2 CHAR ACTER (10) “NVL(LTRIM(:FILED),’UNKONWN’)”
FIELD 3 CHAR TERMINATED BY “,”
“SUBSTAR(:FIELD3,1,10);
FILED4 “TO_CHAR(:FIELD,’$09999.99’)”
And
LTRIM ,CONSTANT ,RTRIM ,TODATE()
7.HOW TO HANDLE NULL COLUMNS?
NULL INDICATES ANY THING VALUE.
We can handle null columns as follows
A). GIVING DATA FORMAT
3| |5 AS (3,NULL,5)
|2|3 AS (NULL,2,3)
| | 3 AS (NULL,NULL,7)
B) BY TRALING NULLCOLS
AFTER FIELDS TERMINATED BY LINE you mention as “TRALING NULLCOLS”
IN YOUR CONTROL FILE
C)NULLIF
LOADDATA
INFILE *
(
FILED1
FILED2
DEPT_NAME POSITION (05:15) CHAR NULLIF DEPT_NAME =BLANKS
FILED3
)
8.)HOW TO EXECUTE CONTROL FILE
$SQLLDR SCOTT/TIGER CONTROL =here sqlldr is optional
9) HOW TO INVOKE SQL*LOADER FILE
10.) WHAT CONVENTIONAL AND DIRECTPATH
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
DIRECT PATH
LOAD DATA
INFILE 'month.dat'
INTO TABLE register
(tx_type POSITION(1:10),
acct POSITION(13:17),
amt POSITION(20:24) ":amt/100"
)
CONVENTIONAL PATH
LOAD DAT A
INFILE*
INSERT INTO TABLE EMP(
EMPNO,ENAME
)
BEGINDATA
1200,SHARAN
1300,RAGHU
11.) HOW TO SKIP RECORDS FROM CONTROL FILE
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
12).HOW TO INSERT MULTIPLE TABLE USING SINGLE CONTROLE FILE ?
13)HOW TO COMMIT IN CONTROLE FILE ?
NO, 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=.
14) CON YOU CHANGE CONTROLE,LOG ,DISCARD FILE NAMES ?
yes
15)HOW TO INSERT INTO SINGLE TABLE FROM multiple datafiles
FILES?
Syntax
Load data
Infile “datafile1”,”datafile2”
Insert into table < tablename>
fields teminated by ‘’
()
16) HOW TO CHANGE CONTROLE,LOG ,DISCARD FILE NAMES ?
17) HOW TO CHANGE THE MODE .BY DEFAULT WHICH MODE IS APPLICABLE?
18).How To Mention Default Values In Controle File?
We Can SET default values in control file by two ways
1.defaultif clause
2.sql funtions
columjn to null
No comments:
Post a Comment