The following example shows loading records into table EMPLOYEE from CSV file EMPLOYEE.dat without having to create a control file.
SQL> create table EMPLOYEE (id integer primary key, name varchar2(10));
Table created.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
[oracle@rac1 admin]$ more EMPLOYEE.dat
1,Adam
2,Ben
3,Colin
4,Dean
5,Evan
6,Frank
7,Greg
8,Hank
9,Ian
10,Jack
[oracle@rac1 admin]$ sqlldr test/test TABLE=EMPLOYEE
SQL*Loader: Release 12.1.0.1.0 - Production on Fri Jun 28 11:58:11 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: EMPLOYEE
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
SQL*Loader-816: error creating temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for file EMPLOYEE.dat
ORA-01031: insufficient privileges
SQL*Loader-579: switching to direct path for the load
SQL*Loader-583: ignoring trim setting with direct path, using value of LDRTRIM
SQL*Loader-584: ignoring DEGREE_OF_PARALLELISM setting with direct path, using value of NONE
Express Mode Load, Table: EMPLOYEE
Path used: Direct
Load completed - logical record count 10.
Table EMPLOYEE:
10 Rows successfully loaded.
Check the log file:
EMPLOYEE.log
for more information about the load.
[oracle@rac1 admin]$ ls EMPLOYEE*
EMPLOYEE.dat EMPLOYEE.log
[oracle@rac1 admin]$ more EMPLOYEE.log
SQL*Loader: Release 12.1.0.1.0 - Production on Fri Jun 28 11:58:11 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: EMPLOYEE
Data File: EMPLOYEE.dat
Bad File: EMPLOYEE_%p.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: External Table
Table EMPLOYEE, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
-------------------------- ---------- ----- ---- ---- ---------
ID FIRST * , CHARACTER
NAME NEXT * , CHARACTER
Generated control file for possible reuse:
OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM)
LOAD DATA
INFILE 'EMPLOYEE'
APPEND
INTO TABLE EMPLOYEE
FIELDS TERMINATED BY ","
(
ID,
NAME
)
End of generated control file for possible reuse.
SQL*Loader-816: error creating temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for file EMPLOYEE.dat
ORA-01031: insufficient privileges
----------------------------------------------------------------
SQL*Loader-579: switching to direct path for the load
SQL*Loader-583: ignoring trim setting with direct path, using value of LDRTRIM
SQL*Loader-584: ignoring DEGREE_OF_PARALLELISM setting with direct path, using value of NONE
----------------------------------------------------------------
Express Mode Load, Table: EMPLOYEE
Data File: EMPLOYEE.dat
Bad File: EMPLOYEE.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct
Table EMPLOYEE, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
--------------------- ---------- ----- ---- ---- ----------------
ID FIRST * , CHARACTER
NAME NEXT * , CHARACTER
Generated control file for possible reuse:
OPTIONS(DIRECT=TRUE)
LOAD DATA
INFILE 'EMPLOYEE'
APPEND
INTO TABLE EMPLOYEE
FIELDS TERMINATED BY ","
(
ID,
NAME
)
End of generated control file for possible reuse.
The following index(es) on table EMPLOYEE were processed:
index TEST.SYS_C009860 loaded successfully with 10 keys
Table EMPLOYEE:
10 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 10
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 1
Total stream buffers loaded by SQL*Loader load thread: 0
Run began on Fri Jun 28 11:58:11 2013
Run ended on Fri Jun 28 11:58:12 2013
Elapsed time was: 00:00:01.27
CPU time was: 00:00:00.02
[oracle@rac1 admin]$ sqlplus test/test
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 28 12:05:49 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Fri Jun 28 2013 11:58:11 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL> select * from employee;
ID NAME
---------- ----------
1 Adam
2 Ben
3 Colin
4 Dean
5 Evan
6 Frank
7 Greg
8 Hank
9 Ian
10 Jack
10 rows selected.
No comments:
Post a Comment