Several data loading methods for oracle11G and oracle11g

Several data loading methods for oracle11G and oracle11g
Data Loading

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. A control file (as a template) and a data file are generally used in the second method. 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 data files:-- Query the sample code of the help document 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 a flat file of the operating system. However, it can be stored in the database, like accessing a normal table, you can use the select statement to access the data contained in a flat file in the operating system. 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>