Oracle 11g Data Loading Methods

Oracle 11g Data Loading Methods

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.

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

Total logical records skipped: 4
Total logical records read: 4
Total logical records rejected: 1
Total logical records discarded: 0

Run began on Fri Jan 23 23:11:08 2015
Run ended on Fri Jan 23 23:11:08 2015

Elapsed time was: 00:00:00. 10
CPU time was: 00:00:00. 01
[Oracle @ localhost test_data_loader] $ sqlplus hr/hr

SQL * Plus: Release 11.2.0.1.0 Production on Fri Jan 23 23:13:14 2015

Copyright (c) 1982,200 9, Oracle. All rights reserved.

Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from student;

Id name age INNER_DATE
----------------------------------------------------
1 zhangsan 21 23-JAN-15
2 lisi 22 23-JAN-15
3 wangwu 23 23-JAN-15

SQL> exit

 

External table:
The data in the External table is not loaded into the database. The database only stores the definition information of the External table. The actual data is stored in the operating system's flat file,
However, you can access the data contained in a flat file in the operating system using the select statement in a database, just like accessing a normal table.
External tables are read-only.
You can use SQL, PL/SQL, and JAVA to access external tables.
There are two types of external tables: External tables generated by the Data Pump engine,

SQL> create table student_re (re_id, re_name, re_age, re_inner_date)
Organization external
(
Type oracle_datapump -- the External table generation method is Data Pump
Default directory test_impdp_expdp -- default path
Location ('student. dmp ') -- generate a flat data file of the External table in the operating system by path and file name
)
Parallel -- parallel
As
Select * from student; 2 3 4 5 6 7 8 9 10

Table created.

SQL>! Ls
Student. dmp STUENT_17109.log
SQL> select * from stuent;

RE_ID RE_NAME RE_AGE RE_INNER_DAT
----------------------------------------------------
1 zhangsan 21 23-JAN-15
2 lisi 22 23-JAN-15
3 wangwu 23 23-JAN-15

SQL>

External tables created based on text files.
SQL> spool student.txt

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

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_impdp_expdp] $ vi student.txt
[Oracle @ localhost test_impdp_expdp] $ sqlplus hr/hr

SQL * Plus: Release 11.2.0.1.0 Production on Sat Jan 24 00:05:18 2015

Copyright (c) 1982,200 9, Oracle. All rights reserved.

Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table student_re_2 -- the column below the name of this external table is the table creation statement of the External table, and the table above through the data pump is created through the as select from tab.
2 (id number, name varchar2 (20), age number (10), inner_date date)
3 organization external
4 (type oracle_loader -- External file Mode
5 default directory test_impdp_expdp -- default directory
6 access parameters-format used to access external files
7 (
Records delimited by newline -- Record Separator
Fields terminated by ',' -- field separator
8) location ('student.txt ') -- location
); 9 10 11

Table created.

SQL> select * from student_re_2;

Id name age INNER_DATE
----------------------------------------------------
1 zhangsan 21 23-JAN-15
2 lisi 22 23-JAN-15
3 wangwu 23 23-JAN-15

SQL>