sqlloader II with date

30 Jun

sqlloader II, adalah kelanjutan sqlloader I , dengan menggunakan type data DATE

##########################################################################
# sqlloader II with date
##########################################################################
1. login sqlplus di oracle server I [orcl1]
[oracle@orcl1 ~]$ sqlplus scott/tiger@orcl1

2. periksa struktur
SQL> desc emp;
Name Null? Type
—————————————– ——– —————————-
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

3. membuat script sql untuk export ke text file
SQL> host
[oracle@orcl1 ~]$ vi emp_txt.sql
set linesize 120;
set feedback off;
set HEADING off;
set pagesize 0;
SELECT
EMPNO || ‘,’ || ENAME || ‘,’ || JOB || ‘,’ || MGR || ‘,’ || to_char(HIREDATE,’DD-MON-YYYY’) || ‘,’ || SAL || ‘,’ || COMM || ‘,’ || DEPTNO
FROM EMP;
[oracle@orcl1 ~]$ exit

4. data di table dept ditambahkan
SQL> SET LIN 150
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

SQL> insert into emp values (9001,’SEAG’,’PRG’,7839,TO_DATE(‘2009/06/29′,’YYYY/MM/DD’),1000,50,41);
SQL> insert into emp values (9002,’WARI’,’PRG’,7839,TO_DATE(‘2009/06/29′,’YYYY/MM/DD’),1000,50,41);
SQL> commit;

SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
9001 SEAG PRG 7839 29-JUN-09 1000 50 41
9002 WARI PRG 7839 29-JUN-09 1000 50 41
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> exit

5. lakukan export ke text file
[oracle@orcl1 ~]$ cat emp_txt.sql | sqlplus -s scott/tiger@orcl1 > emp_1.txt
[oracle@orcl1 ~]$ cat emp_1.txt
9001,SEAG,PRG,7839,29-JUN-2009,1000,50,41
9002,WARI,PRG,7839,29-JUN-2009,1000,50,41
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,02-APR-1981,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-1981,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-1981,2850,,30
7782,CLARK,MANAGER,7839,09-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,08-SEP-1981,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-1987,1100,,20
7900,JAMES,CLERK,7698,03-DEC-1981,950,,30
7902,FORD,ANALYST,7566,03-DEC-1981,3000,,20
7934,MILLER,CLERK,7782,23-JAN-1982,1300,,10

6. untuk melakukan sqlloader ke server ke 2 [orcl2], perlu dibuat file controlnya
[oracle@orcl1 ~]$ vi emp.ctl
load data
INFILE emp_1.txt
APPEND
into table emp
fields terminated by “,”
TRAILING NULLCOLS
(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)

7. periksa keadaan tabel sebelum di sqlloader
[oracle@orcl1 ~]$ sqlplus scott/tiger@orcl2
SQL> SET LIN 150
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> exit

8. menjalankan sqlloader
[oracle@orcl1 ~]$ sqlldr userid=scott/tiger@orcl2 control=emp.ctl log=emp.log

9. periksa keadaan tabel sesudah di sqlloader
[oracle@orcl1 ~]$ sqlplus scott/tiger@orcl2
SQL> SET LIN 150
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
9001 SEAG PRG 7839 29-JUN-09 1000 50 41
9002 WARI PRG 7839 29-JUN-09 1000 50 41
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> exit

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: