Scribd
Upload
6 views4 pages

SQL Loader

The document outlines the SQL*Loader process for importing data from flat files into staging or temporary tables, including the creation of control files (.ctl) and handling of error tracking through bad, discard, and log files. It details various modes of operation (INSERT, APPEND, TRUNCATE, REPLACE) and provides examples of data loading commands with specific conditions and column manipulations. Additionally, it discusses options for file handling and data positioning within the SQL*Loader program.

Uploaded by

contact4venky
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
6 views4 pages

SQL Loader

The document outlines the SQL*Loader process for importing data from flat files into staging or temporary tables, including the creation of control files (.ctl) and handling of error tracking through bad, discard, and log files. It details various modes of operation (INSERT, APPEND, TRUNCATE, REPLACE) and provides examples of data loading commands with specific conditions and column manipulations. Additionally, it discusses options for file handling and data positioning within the SQL*Loader program.

Uploaded by

contact4venky
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

SQL * LOADER

--FLAT FILE IN (.CSV). CREATE STAGING OR TEMP TABLE WITH 2 EXTRA COL’S FOR TRACKING OF ERROR
INFORMATION. NEXT CODE HAS TO WRITE IN (.CTL).

SQL * LOADER PROCESS FOR MOVING THE DATA.

STAGING OR TEMP TABLE.

INTERFACE TABLE

BASE TABLE

--BAD FILE (CAPTURE THE ERROR INFORMATION. WILL RECORD THE DATA TYPE ERRORS)

--DISCARD FILE (CAPTURE THE ERROR INFORMATION. I.E., CONDITION BASED ERRORS)

--LOG FILE (TRACKING OF HOW MANY RECORDS INSERTED, REJECTED, SKIPPED ETC)

--WHAT ARE THE MODES IN SQL * LOADER (INSERT, APPEND, TRUNCATE, REPLACE)

--DATA INSIDE PROGRAM

LOAD DATA

INFILE *

INSERT/APPEND/TRUNCATE/REPLACE INTO TABLE TABLENAME

FIELDS TERMINATED BY ‘,’

OPTIONALLY ENCLOSED BY ‘”’

TRAILING NULLCOLS

(COL1, COL2, COL3)

BEGINDATA

(A, B, C)

--CONDITIONS FOR COLUMN NAMES

LOAD DATA

INFILE *

TRUNCATE INTO TABLE TABLENAME

FIELDS TERMINATED BY ‘,’

TRAILING NULLCOLS
(ENAME “INITCAP (:ENAME)”,

JDATE SYSDATE,

DEPTNO CONSTANT “10”,

SAL,

COMM “(:SAL-1000)”,

LOCATION “DECODE (:LOCATION, ’H’, ’HYDERABAD’, ’B’, ’BANGLORE’, ’C’, ‘INDIA’)”

BEGINDATA

RAJ,10000,H

RAG,11000,B

RAM,12000,C

--PATH IN PROGRAM

LOAD DATA

INFILE ‘PATH’

TRUNCATE INTO TABLENAME

FIELDS TERMINATED BY ‘,’

OPTIONALLY ENCLOSED BY ‘”’

(COL1, COL2, COL3)

--PARAMETER IN PROGRAM

LOAD DATA

INFILE ‘&1’

TRUNCATE INTO TABLENAME

FIELDS TERMINATED BY ‘,’

OPTIONALLY ENCLOSED BY ‘”’

(COL1, COL2, COL3)

--OPTIONS IN PROGRAM

OPTIONS (SKIP=2, LOAD=2)

[SKIP IS PRIOR TO LOAD]

--POSITIONS INSIDE PROGRAM

LOAD DATA
INFILE *

INSERT/APPEND/TRUNCATE/REPLACE INTO TABLE TABLENAME

FIELDS TERMINATED BY ‘,’

OPTIONALLY ENCLOSED BY ‘”’

(COL1 POSITION (1:5), COL2 POSITION (7:11), COL3 POSITION (13:17))

BEGINDATA

A1234 B1234 C1234

D E F

--MULTI TABLES WITH POSITION INSIDE PROGRAM

LOAD DATA

INFILE *

INSERT/APPEND/TRUNCATE/REPLACE INTO TABLE TABLENAME1

FIELDS TERMINATED BY ‘,’

OPTIONALLY ENCLOSED BY ‘”’

(COL1 POSITION (1:5), COL2 POSITION (7:11))

INTO TABLE TABLENAME2

(COL3 POSITION (13:17), COL4 POSITION(19:23))

BEGINDATA

A1234,B1234,C1234,D1234

D ,E ,F ,G

--WHEN CONDITION INSIDE PROGRAM

LOAD DATA

INFILE *

INSERT/APPEND/TRUNCATE/REPLACE INTO TABLE TABLENAME1

FIELDS TERMINATED BY ‘,’

OPTIONALLY ENCLOSED BY ‘”’

(COL1, COL2)

INTO TABLE TABLENAME2

WHEN COL2=’X'
(COL3, COL4)

BEGINDATA

A1234,B1234,C1234,D1234

D ,E ,F ,G

You might also like