Several Methods for loading Oracle11g data

Data Loading: SQL * LOADER External table Import and Export SQL * LOADER: SQL * LOADER is an Oracle tool that can load data from external data files to databases.

Data Loading: SQL * LOADER External table import/export SQL * LOADER: SQL * LOADER is an Oracle tool that can load data from external data files to databases.

Data Loading:

SQL * LOADER:

SQL * LOADER is an Oracle tool that can load data from external data files to databases.

The command for running SQL * LOADER is sqlldr.

Two Sqlldr usage methods:

1. Use only one control file, which contains data

2. Use a control file (as a template) and a data file

Generally, the second method is used. data files can be CSV files, txt files, or separated by other delimiters.

Note: The operation type can be one of the following values:

1) insert -- this is the default mode. The table is required to be empty when data loading starts.

2) append -- Add a new record to the table

3) replace -- delete the old record (using the delete from table statement) and replace it with the newly loaded record

4) truncate -- delete the old record (with the truncate table statement) and replace it with the newly loaded record

Use spool to create a data file: -- The sample code of the help document can be queried.

SQL> spool/u01/app/oracle/test_data_loader/student.txt -- enable spool to export data files
SQL> select id | ',' | name | ',' | age | ',' | inner_date from student; -- export data

ID | ',' | NAME | ',' | AGE | ',' | INNER_DATE
--------------------------------------------------------------------------------
1, zhangsan, 21, 23-JAN-15
2, lisi, 22, 23-JAN-15
3, wangwu, 23-JAN-15

SQL> spool off; -- disable
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[Oracle @ localhost test_data_loader] $ cat student.txt -- you can view the exported data records.
SQL> select id | ',' | name | ',' | age | ',' | inner_date from student;

ID | ',' | NAME | ',' | AGE | ',' | INNER_DATE
--------------------------------------------------------------------------------
1, zhangsan, 21, 23-JAN-15
2, lisi, 22, 23-JAN-15
3, wangwu, 23-JAN-15

SQL> spool off;

Write the configuration file:
[Oracle @ localhost test_data_loader] $ vi student. ctl
[Oracle @ localhost test_data_loader] $ cat student. ctl
Options (skip = 4) -- indicates the first four rows
Load data -- import data
Infile 'student.txt '-- import data through this file
Into table student -- imported table
Insert -- the insert operation is executed.
Fields terminated by ',' -- delimiter in the record
(
Id char, -- note that although the table is of the number type, the char type must be written.
Name char,
Age char,
Inner_date date nullif (inner_date = "null "))
[Oracle @ localhost test_data_loader] $

Since it is an insert operation, so:
SQL> truncate table student; -- clears the table because the insert operation is performed.

Table truncated.

SQL> select * from student;

No rows selected

Execute the sqlldr operation:
[Oracle @ localhost test_data_loader] $ sqlldr hr/hr control = student. ctl log = student. log

SQL * Loader: Release 11.2.0.1.0-Production on Fri Jan 23 23:11:08 2015

Copyright (c) 1982,200 9, Oracle and/or its affiliates. All rights reserved.

Commit point reached-logical record count 4
[Oracle @ localhost test_data_loader] $ cat student. log

SQL * Loader: Release 11.2.0.1.0-Production on Fri Jan 23 23:11:08 2015

Copyright (c) 1982,200 9, Oracle and/or its affiliates. All rights reserved.

Control File: student. ctl
Data File: student.txt
Bad File: student. bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 4
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table STUDENT, loaded from every logical record.
Insert option in effect for this table: INSERT

Column Name Position Len Term Encl Datatype
--------------------------------------------------------------------------
Id first *, CHARACTER
Name next *, CHARACTER
Age next *, CHARACTER
INNER_DATE NEXT *, DATE DD-MON-RR
NULL if INNER_DATE = 0X6e756c6c (character 'null ')

Record 4: Rejected-Error on table STUDENT, column ID.
Column not found before end of logical record (use trailing nullcols)

Table STUDENT:
3 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 Arrays: 66048 bytes (64 rows)
Read buffer bytes: 1048576