Oracle DBA Quest: SQLLOADER IN ORACLE

============================================================================================================================
EMP.CTL
============================================================================================================================

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 23 05:58:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn scott/tiger;
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE

SQL> create table load (id number(5),ename varchar2(20),salary number(5),job_id
varchar2(20),dept_no number(5));

Table created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
LOAD TABLE
SALGRADE TABLE

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\tinku>cd\

C:\>

C:\>sqlldr userid=scott/tiger control='c:\sqlloader\emp.ctl'

SQL*Loader: Release 9.2.0.1.0 - Production on Fri Jan 23 06:06:42 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 10

C:\>

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 23 05:58:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn scott/tiger;
Connected.
SQL>
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
LOAD TABLE
SALGRADE TABLE

SQL> select * from load;

ID ENAME SALARY JOB_ID DEPT_NO
---------- -------------------- ---------- -------------------- ----------
100 raj 5000 clerk 40
101 ram 6000 prog 50
102 arun 9000 it_admin 60
103 vishal 10000 hr 30
104 ashish 9000 admin 20
105 sujeet 10000 clerk 40
106 ombikash 9000 prog 50
107 amitab 6000 it_admin 60
108 mukesh 5000 hr 30
109 sundeep 7000 admin 20

10 rows selected.

SQL>

============================================================================================================================
EMPDATE.CTL
============================================================================================================================

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 23 05:58:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn scott/tiger;
Connected.

SQL> create table loader (id number(4),ename varchar2(20),salary number(6),job_i
d varchar2(15),dept_no number(4),hire_date date);

Table created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
LOAD TABLE
LOADER TABLE

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\tinku>cd\

C:\>

C:\>sqlldr userid=scott/tiger control='c:\sqlloader\empdate.ctl'

SQL*Loader: Release 9.2.0.1.0 - Production on Mon May 3 23:30:18 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 9
Commit point reached - logical record count 10

C:\>

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 23 05:58:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn scott/tiger;
Connected.

SQL> select * from loader;

ID ENAME SALARY JOB_ID DEPT_NO HIRE_DATE
---------- -------------------- ---------- --------------- ---------- ---------
100 raj 5000 clerk 40 01-MAY-00
101 ram 6000 prog 50 03-JUN-09
102 arun 9000 it_admin 60 20-NOV-10
103 vishal 10000 hr 30 30-JUL-99
104 ashish 9000 admin 20 12-SEP-98
105 sujeet 10000 clerk 40 03-FEB-08
106 ombikash 9000 prog 50 05-JUL-01
107 amitab 6000 it_admin 60 27-MAY-00
108 mukesh 5000 hr 30 20-MAY-96
109 sundeep 7000 admin 20 15-JUL-00

10 rows selected.

===========================================================================================================================
EMPFULLLINE.CTL
==========================================================================================================================

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 23 05:58:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn scott/tiger;
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
LOAD TABLE
LOADER TABLE
LOADER1 TABLE

SQL> create table loader1 (id number(4),ename varchar2(20),salary number(6),job
id varchar2(15),dept_no number(4),hire_date date,entire_line varchar2(50));

Table created.

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\tinku>cd\

C:\>sqlldr userid=scott/tiger control='c:\sqlloader\empFULLLINE.ctl'

SQL*Loader: Release 9.2.0.1.0 - Production on Mon May 3 23:30:18 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 9
Commit point reached - logical record count 10

C:\>

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 23 05:58:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn scott/tiger;
Connected.

SQL> select * from loader1;

ID ENAME SALARY JOB_ID DEPT_NO HIRE_DATE
---------- -------------------- ---------- --------------- ---------- ---------
ENTIRE_LINE
--------------------------------------------------
100 raj 5000 clerk 40 01-MAY-00
100raj5000clerk1/5/2000

101 ram 6000 prog 50 03-JUN-09
101ram6000prog3/6/2009

102 arun 9000 it_admin 60 20-NOV-10
102arun9000it_admin20/11/2010

ID ENAME SALARY JOB_ID DEPT_NO HIRE_DATE
---------- -------------------- ---------- --------------- ---------- ---------
ENTIRE_LINE
--------------------------------------------------
103 vishal 10000 hr 30 30-JUL-99
103vishal10000hr30/7/1999

104 ashish 9000 admin 20 12-SEP-98
104ashish9000admin12/9/1998

105 sujeet 10000 clerk 40 03-FEB-08
105sujeet10000clerk3/2/2008

ID ENAME SALARY JOB_ID DEPT_NO HIRE_DATE
---------- -------------------- ---------- --------------- ---------- ---------
ENTIRE_LINE
--------------------------------------------------
106 ombikash 9000 prog 50 05-JUL-01
106ombikash9000prog5/7/2001

107 amitab 6000 it_admin 60 27-MAY-00
107amitab6000it_admin27/5/2000

108 mukesh 5000 hr 30 20-MAY-96
108mukesh5000hr20/5/1996

ID ENAME SALARY JOB_ID DEPT_NO HIRE_DATE
---------- -------------------- ---------- --------------- ---------- ---------
ENTIRE_LINE
--------------------------------------------------
109 sundeep 7000 admin 20 15-JUL-00
109sundeep7000admin15/7/2000

10 rows selected.

============================================================================================
EMPDATECASE.CTL
============================================================================================

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 23 05:58:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn scott/tiger;
Connected.

SQL> create table loader (id number(4),ename varchar2(20),salary number(6),job_i
d varchar2(15),dept_no number(4),hire_date date);

Table created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
LOAD TABLE
LOADER TABLE

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\tinku>cd\

C:\>sqlldr userid=scott/tiger control='c:\sqlloader\empdatecase.ctl'

SQL*Loader: Release 9.2.0.1.0 - Production on Mon May 3 23:30:18 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 9
Commit point reached - logical record count 10

C:\>

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 23 05:58:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn scott/tiger;
Connected.

SQL> select * from loader;

ID ENAME SALARY JOB_ID DEPT_NO HIRE_DATE
---------- -------------------- ---------- --------------- ---------- ---------
100 raj 5000 clerk 40 01-MAY-00
101 ram 6000 prog 50 03-JUN-09
102 arun 9000 it_admin 60 01-MAY-10
103 vishal 10000 hr 30 30-JUL-99
104 ashish 9000 admin 20 12-SEP-98
105 sujeet 10000 clerk 40 03-FEB-08
106 ombikash 9000 prog 50 05-JUL-01
107 amitab 6000 it_admin 60 27-MAY-00
108 mukesh 5000 hr 30 01-MAY-10
109 sundeep 7000 admin 20 15-JUL-00

10 rows selected.

============================================================================================
EMPFILLER.CTL
============================================================================================

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 23 05:58:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn scott/tiger;
Connected.

SQL> create table load (id number(5),ename varchar2(20),salary number(5),job_id
varchar2(20),dept_no number(5));

Table created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
LOAD TABLE

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\tinku>cd\

C:\>sqlldr userid=scott/tiger control='c:\sqlloader\empFILLER.ctl'

SQL*Loader: Release 9.2.0.1.0 - Production on Tue May 4 00:51:38 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 10

C:\>

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 23 05:58:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn scott/tiger;
Connected.

SQL> SELECT * FROM LOAD;

ID ENAME SALARY JOB_ID DEPT_NO
---------- -------------------- ---------- -------------------- ----------
100 5000 40
101 6000 50
102 9000 60
103 10000 30
104 9000 20
105 10000 40
106 9000 50
107 6000 60
108 5000 30
109 7000 20

10 rows selected.

============================================================================================
EMP1.CTL [ EXCEL FILE ] SAVE EXCEL FILE FROM FILENAME.CSV
============================================================================================

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 23 05:58:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn scott/tiger;
Connected.

SQL> create table load (id number(5),ename varchar2(20),salary number(5),job_id
2 varchar2(20),dept_no number(5));

Table created.

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\tinku>cd\

C:\>sqlldr userid=scott/tiger control='c:\sqlloader\emp1.ctl'

SQL*Loader: Release 9.2.0.1.0 - Production on Tue May 4 01:13:22 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 10

C:\>

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 23 05:58:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn scott/tiger;
Connected.

SQL> SELECT * FROM LOAD;

ID ENAME SALARY JOB_ID DEPT_NO
---------- -------------------- ---------- -------------------- ----------
100 raj 5000 clerk 40
101 ram 6000 prog 50
102 arun pr 9000 it_admin 60
103 vishal pr 10000 hr 30
104 ashish pr 9000 admin 20
105 sujeet pr 10000 clerk 40
106 ombikash pr 9000 prog 50
107 amitab pr 6000 it_admin 60
108 mukesh pr 5000 hr 30
109 sundeep pr 7000 admin 20

10 rows selected.

============================================================================================= EMPFIXED.CTL
============================================================================================

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 23 05:58:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn scott/tiger;
Connected.

SQL> create table load (id number(5),ename varchar2(20),salary number(5),job_id
varchar2(20),dept_no number(5));

Table created.

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\tinku>cd\

C:\>sqlldr userid=scott/tiger control='c:\sqlloader\empfixed.ctl'

SQL*Loader: Release 9.2.0.1.0 - Production on Tue May 4 00:51:38 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 10

C:\>

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 23 05:58:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn scott/tiger;
Connected.

SQL> SELECT * FROM LOAD;

ID ENAME SALARY JOB_ID DEPT_NO
---------- -------------------- ---------- -------------------- ----------
101 ram 3000 prog 30
102 arun 9000 it_admin 40
103 vishal 8000 hr 50
104 ashish 7000 admin 60
105 sujeet 6000 clerk 20
106 ombikash 5000 prog 30
107 amitab 4000 it_admin 40
108 mukesh 8000 hr 50
109 sundeep 9000 admin 60

9 rows selected.

============================================================================================== EMPFUNCTION.CTL
============================================================================================

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 23 05:58:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn scott/tiger;
Connected.

SQL> create table loader (id number(4),ename varchar2(20),salary number(6),job_i
d varchar2(15),dept_no number(4),hire_date date);

Table created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
LOAD TABLE
LOADER TABLE

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\tinku>cd\

C:\>

C:\>sqlldr userid=scott/tiger control='c:\sqlloader\empFUNCTION.CTL'

SQL*Loader: Release 9.2.0.1.0 - Production on Tue May 4 01:32:24 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 9
Commit point reached - logical record count 10

C:\>

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 23 05:58:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn scott/tiger;
Connected.

SQL> SELECT * FROM LOADER;

ID ENAME SALARY JOB_ID DEPT_NO HIRE_DATE
---------- -------------------- ---------- --------------- ---------- ---------
100 RAJ 5000 CLERK 40 01-MAY-00
101 RAM 6000 PROG 50 03-JUN-09
102 ARUN 9000 IT_ADMIN 60 20-NOV-10
103 VISHAL 10000 HR 30 30-JUL-99
104 ASHISH 9000 ADMIN 20 12-SEP-98
105 SUJEET 10000 CLERK 40 03-FEB-08
106 OMBIKASH 9000 PROG 50 05-JUL-01
107 AMITAB 6000 IT_ADMIN 60 27-MAY-00
108 MUKESH 5000 HR 30 20-MAY-96
109 SUNDEEP 7000 ADMIN 20 15-JUL-00

10 rows selected.

=============================================================================================== EMPQUOTED.ETL
============================================================================================

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 23 05:58:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn scott/tiger;
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE

SQL> create table load (id number(5),ename varchar2(20),salary number(5),job_id
varchar2(20),dept_no number(5));

Table created.

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\tinku>cd\

C:\>

C:\>sqlldr userid=scott/tiger control='c:\sqlloader\empQUOTED.CTL'

SQL*Loader: Release 9.2.0.1.0 - Production on Tue May 4 01:38:35 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 10

C:\>

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 23 05:58:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn scott/tiger;
Connected.

SQL> SELECT * FROM LOAD;

ID ENAME SALARY JOB_ID DEPT_NO
---------- -------------------- ---------- -------------------- ----------
100 raj 5000 clerk 40
101 ram 6000 prog 50
102 arun 9000 it_admin 60
103 vishal 10000 hr 30
105 sujeet"pr" 10000 clerk 40
106 ombikash 9000 prog 50
107 amitab 6000 it_admin 60
108 "mukesh" 5000 hr 30
109 sundeep 7000 admin 20

9 rows selected.

============================================================================================
EMPSPACE.CTL
============================================================================================

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 23 05:58:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn scott/tiger;
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE

SQL> create table load (id number(5),ename varchar2(20),salary number(5),job_id
varchar2(20),dept_no number(5));

Table created.

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\tinku>cd\

C:\>

C:\>sqlldr userid=scott/tiger control='c:\sqlloader\empSPACE.CTL'

SQL*Loader: Release 9.2.0.1.0 - Production on Tue May 4 01:43:54 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 10

C:\>

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 23 05:58:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn scott/tiger;
Connected.

SQL> SELECT * FROM LOAD;

ID ENAME SALARY JOB_ID DEPT_NO
---------- -------------------- ---------- -------------------- ----------
100 raj 5000 clerk 40
101 ram 6000 prog 50
102 arun 9000 it_admin 60
103 vishal 10000 hr 30
105 sujeet"pr" 10000 clerk 40
106 ombikash 9000 prog 50
107 amitab 6000 it_admin 60
108 "mukesh" 5000 hr 30
109 sundeep 7000 admin 20
100 raj 5000 clerk 40
101 ram 6000 prog 50

ID ENAME SALARY JOB_ID DEPT_NO
---------- -------------------- ---------- -------------------- ----------
102 arun 9000 it_admin 60
103 vishal 10000 hr 30
104 ashish 9000 admin 20
105 sujeet 10000 clerk 40
106 ombikash 9000 prog 50
107 amitab 6000 it_admin 60
108 mukesh 5000 hr 30
109 sundeep 7000 admin 20

19 rows selected.

SQL>

Oracle DBA Quest

Tuesday, 31 December 2013

SQLLOADER IN ORACLE

No comments:

Post a Comment