Several data loading methods for oracle11G

Several data loading methods for 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 SQL> spool/u01/app/oracle/test_data_loader/student.txt -- enable the SQL> select id | ', '| name |', '| age |', '| inner_date from student; -- export data ID |', '| NAME | ', '| AGE |', '| INNER_DATE--------------------------------------------------------------------------------1, zhangsan, 152-JAN-153, lisi,-JAN-, wangwu,-JAN-15SQL> spool off; -- disable SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit ProductionWith the Partitioning, OLAP, data Mining and Real Application Testing options [oracle @ localhost test_data_loader] $ cat student.txt -- you can view the exported Data record SQL> select id | ',' | name | ', '| age |', '| inner_date from student; ID |', '| NAME |', '| AGE | ', '| INNER_DATE--------------------------------------------------------------------------------1, zhangsan, 152-JAN-153, lisi,-JAN-, wangwu,-JAN-15SQL> spool off; write the configuration file: [oracle @ localhost test_data_loader] $ vi student. ctl [oracle @ localhost test_data_loader] $ cat student. ctloptions (skip = 4) -- indicates the first four rows of load data -- import data infile 'student.txt '-- import data through this file into table student -- import table insert -- execute the insert operation fields terminated ', '-- delimiter in the record (id char, -- note that although the table is of the number type, the char type name char, age char, inner_date date nullif (inner_date = "null") must be written ")) [oracle @ localhost test_data_loader] $ SQL> truncate table student; -- clears the Table because the insert operation table truncated is executed. SQL> select * from student; no rows selected: [oracle @ localhost test_data_loader] $ sqlldr hr/hr control = student. ctl log = student. logSQL * 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. logSQL * 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. ctlData File: student.txt Bad File: student. badDiscard File: none specified (Allow all discards) Number to load: ALLNumber to skip: 4 Errors allowed: 50 Bind array: 64 rows, maximum of 256000 bytesContinuation: none specifiedPath used: conventionalTable STUDENT, loaded from every logical record. insert option in effect for this table: INSERTColumn Name Position Len Term Encl Datatype certificate ---------- ----- ---- ----------------------- id first *, charactername next *, characterage next *, CHARACTERINNER_DATE NEXT *, DATE DD-MON-RRNULL 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 clses were failed.0 Rows not loaded because all fields were null. space allocated for bind array: 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: 0Run began on Fri Jan 23 23:11:08 2015Run ended on Fri Jan 23 23:11:08 2015 Elapsed time was: 00:00:00. 10CPU time was: 00:00:00. 01 [oracle @ localhost test_data_loader] $ sqlplus hr/hrSQL * 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 to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from student; id name age INNER_DATE ---------- -------------------- ---------- ------------ 1 zhangsan 21 23-JAN-152 lisi 22 23-JAN-153 wangwu 23 23-JAN-15SQL> 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 -- 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 asselect * from student; 2 3 4 5 6 7 8 9 10 Table created. SQL>! Lsstudent. dmp privileges> select * from stuent; RE_ID RE_NAME RE_AGE RE_INNER_DAT ---------- bytes ------------ ---------- 1 zhangsan 21 23-JAN-152 lisi 22 23-JAN-153 wangwu 23 23-JAN-15SQL>

External tables created based on text files.

SQL> spool student.txt ID | ',' | NAME | ',' | AGE | ',' | INNER_DATE--------------------------------------------------------------------------------1, zhangsan, 152-JAN-, lisi, 153-JAN-, wangwu,-JAN-15SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit ProductionWith the Partitioning, OLAP, data Mining and Real Application Testing options [oracle @ localhos T test_impdp_expdp] $ vi student.txt [oracle @ localhost test_impdp_expdp] $ sqlplus hr/hrSQL * 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 to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> 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 external4 (type oracle_loader -- External file mode 5 default directory test_impdp_expdp -- default directory 6 access parameters -- format 7 (records delimited by newline -- record delimiter 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-152 lisi 22 23-JAN-153 wangwu 23 23-JAN-15SQL>