Tuesday 16 February 2016

GENERATE THE DISCARD FILE EXAMPLE

AS SALAMO ALAIKUM WA RAHMATULLAH

TABLE STRUCTURE
================


create table XXX_EMP_STG
(
    EMPNO NUMBER(4) PRIMARY KEY,
    ENAME VARCHAR2(20) NOT NULL,
    JOB VARCHAR(20) NOT NULL,
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(8,3),
    COMM NUMBER(8,3),
    DEPTNO NUMBER(2)
)


==============
CONTROL FILE 
=============


OPTIONS (SKIP=1)
LOAD DATA
INFILE 'D:\ORACLEDEMO\SQLLOADER\emp.csv'
BADFILE 'D:\ORACLEDEMO\SQLLOADER\EMP_BAD.bad'
DISCARDFILE 'D:\ORACLEDEMO\SQLLOADER\EMP_DISCARD.dis'
INSERT INTO TABLE XXX_EMP_STG
WHEN COMM != 'NULL'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)



=======
CSV FILE
=======


EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,17-Dec-1980,800,,20
7499,ALLEN,SALESMAN,7698,20-Feb-1981,1600,300,30
7521,WARD,SALESMAN,7698,22-Feb-1981,1250,500,30
7566,JONES,MANAGER,7839,2-Apr-1981,2975,,20
7654,MARTIN,SALESMAN,7698,28-Sep-1981,1250,1400,30
7698,BLAKE,MANAGER,7839,1-May-1981,2850,,30
7782,CLARK,MANAGER,7839,9-Jun-1981,2450,,10
7788,SCOTT,ANALYST,7566,19-Apr-1987,3000,,20
7839,KING,PRESIDENT,,17-Nov-1981,5000,,10
7844,TURNER,SALESMAN,7698,8-Sep-1981,1500,0,30
7876,ADAMS,CLERK,7788,23-May-1987,1100,,20
7900,JAMES,CLERK,7698,3-Dec-1981,950,,30
7902,FORD,ANALYST,7566,3-Dec-1981,3000,,20
7934,MILLER,CLERK,7782,23-Jan-1982,1300,,10


===================
DISCARD FILE GENERATED BECAUSE THE CONDITION MET INTO THE CONTROL FILE
=====================

7369,SMITH,CLERK,7902,17-Dec-1980,800,,20
7566,JONES,MANAGER,7839,2-Apr-1981,2975,,20
7698,BLAKE,MANAGER,7839,1-May-1981,2850,,30
7782,CLARK,MANAGER,7839,9-Jun-1981,2450,,10
7788,SCOTT,ANALYST,7566,19-Apr-1987,3000,,20
7839,KING,PRESIDENT,,17-Nov-1981,5000,,10
7876,ADAMS,CLERK,7788,23-May-1987,1100,,20
7900,JAMES,CLERK,7698,3-Dec-1981,950,,30
7902,FORD,ANALYST,7566,3-Dec-1981,3000,,20
7934,MILLER,CLERK,7782,23-Jan-1982,1300,,10


----
MA ASALAAM
PASSION FOR ORACLE

No comments:

Post a Comment