Sql Loader

Sql*Loader

                                   

1.What Is  Sql*Loader  ?

Sqlloader Is An Oracle Server Tool Useed To Load Data  From Operating System Files Into Oracle Serverdata 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 <DATA  FILE  PATH>

INSERT OR APPEND OR  REPLACE OR TRUNCATE  INTO  TABLE <TABLE  NAME>

FIELDS TEMINATED BY ‘<SEPARATOR>’

(<LIST OF ALL ATTRIBUTEE NAMES TO LOAD >)

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   CHARACTER (10) “NVL(LTRIM(:FILED),’UNKNOWN’)”

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 =<control file.ctl

here  sqlldr is optional

9) HOW TO INVOKE  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 ‘<separator>’

(<list of all attributee names to load >)

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