Recently encountered in the project CLOB type large object imported into the database (Oracle), usually only copy on the line. But when they got clob on the line, asked a few of my colleagues have had no way to ask the leader and the background cattle they tell me to use pl / sql and toad import, but the import problem, and later to Huawei R & D to a SQL * loader script, looked quite simple, the results of research and development to guide into the local deployment but it’s not the finalized version, and then download the latest version of the problem to get. But in the process think we should know SQL * loader usage, to view some of the information, and simple operation records to everyone, I hope to give some help novice.
First of all, I is we have data objects and instances show, so I chose the scott user emp source table data, pilot out emp.csv file
Exported a variety of ways in which pl / sql and toad better, based on the former exported. (I recently learned, ha ha)
Can not upload pictures, so I can only be explained. (Pl / sql query result column top, an export query results in the drop-down box, select the csv file you can save the file as csv format a)
With import file, we need to do now is to write a configuration file, I find a lot of examples in this regard are similar, I find a better give you a reference, we want to play guiding role. Paste (later in this article)
I first the scott user to build a emp_bak table.
create table scott.emp_bak as
select * from scott.emp t
where 1 = 2
View the table structure
create table EMP_BAK is
EMPNO NUMBER (4) not null,
ENAME VARCHAR2 (10),
JOB VARCHAR2 (9)
MGR NUMBER (4),
SAL NUMBER (7,2)
COMM NUMBER (7,2),
DEPTNO NUMBER (2)
c. write control file
append into table “EMP_BAK”
fields terminated by ‘,’ optionally enclosed by ‘”‘
HIREDATE date’yyyy-mm-dd ‘,
The the above content into a emp.ctl file save. (Date’yyyy-mm-dd ‘this place due to the situation of data, my data is 1980-12-17, so I used this, can not be directly imported, we first consider function to achieve)
d. csv file and ctl files are in d :/ initdata directory, so I dos operate
1.C :/ Documents and Settings / jcttest> d:
2.D :/> cd initdata
3.D :/ initdata> dir
e In fact, we have to develop view the log a good habit, we want to view the log (the log is generally based on import file name is the same, but the extension is not the same, the extension is generally for the log, so I emp log) reads as follows (when successful, of course, are not the same for different error, ha ha)
SQL * Loader: Release 10.1.0.2.0 – Production on Sat Jan 23 09:21:28 2010
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Control File: emp.ctl
Data File: emp.csv of
Bad File: emp.bad of
Discard File: emp.dsc
(Allow 10 Discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table “EMP_BAK”, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
EMPNO FIRST *, O (“) CHARACTER
ENAME NEXT *, O (“) CHARACTER
JOB NEXT *, O (“) CHARACTER
MGR NEXT *, O (“) CHARACTER
HIREDATE NEXT *, O (“) DATE yyyy-mm-dd
The SAL Next * O (“) CHARACTER
COMM NEXT *, O (“) CHARACTER
DEPTNO NEXT *, O (“) CHARACTER
Record 1: Rejected – Error on table “EMP_BAK”, column EMPNO.
ORA-01722: invalid number
The Table “EMP_BAK”:
14 Rows successfully loaded.
1 Row 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.
Space allocated for bind array: 132096 bytes (64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 15
Total logical records rejected: 1
Total logical records discarded: 0
Run began on Sat Jan 23 09:21:28 2010
Run ended on Sat Jan 23 09:21:31 2010
Elapsed time was: 00:00:02.51
CPU time was: 00:00:00.04