oracle learning _Sql loader to use

Jun 19 posted in Oracle (RSS), 85 views.

rn
Yesterday looked oracle data import method to import data in oracle in the following main methods:
rn
1, directly insert ...... values () statement;
rn
example:
rn
insert into book (no, name, type) values ('2006001 ',' basketball park ',' magazine ')
rn

rn
2, use select query sentence;
rn
example:
rn
insert into book_1 select * from book2 where book1 = 'condition'
rn

rn
3, use PL / SQL procedure;
rn
example:
rn
SQL> edit input_example.sql
rn
----------- input prompt
rn
accept p_no prompt 'please input book no'
rn
accept p_name prompt 'please input book name'
rn
accept p_type prompt 'please input the type of book'
rn
------------ evaluation
rn
declare
rn
v_no book.no% type: = & p_no;
rn
v_name book.name% type: = & p_name;
rn
v_type book.type% type: = & P_type;
rn
------------- insert
rn
begin
rn
insert book (no, name, type)
rn
values (v_no, v_name, v_type);
rn
commit work;
rn
end;
rn
/
rn

rn
4, insert records using Oracle Loader
rn
The focus here to talk about the use of Oracle Loader, very often we will use the Oracle Loader to enter information into the work. As we in the factory or other man-made machines into the data file we will use Oracle usually Oracle Loader.
rn
I say again, oracle loader is also a tool for automatic import.
rn
I, for example, others now to 1000 our library books, and e-mail has given us a list.
rn
List is a *. txt, reads as follows:
rn

rn
# bookname, bookno, bookqty, booktype, intime, price
rn
# Title, ISBN, the number of books, book type, by adding time, price
rn
C language programming, 001,13, text books, 20060521,20.45
rn
oracle learning and improving, 002,15, extra-curricular books, 20060522,45
rn
...........
rn
...........
rn
...........
rn

rn
If this list of a dozen, we can manually insert is 1000 but if we manually on the slow, and prone to error, then we can use oracle Loader.
rn
Second, sql loader examples
rn
c: \ sqlldr userid = bookadm1/sysadm control = input.ctl log = input.log bad = input.bad errors = 30 skip = 1 direct = true
rn

rn
oracle loader is sqlldr command, followed by the parameters of the import control of specific methods, the parameters as follows:
rn
userid = bookadm1/sysadm - username = 'bookadm', password = 'sysadm'
rn
control = input.ctl - control file name input.ctl
rn
log = input.log - log file name input.log
rn
bad = input.bad - when input error to take down error record
rn
errors = 30 - permit error rows
rn
skip = 1 - skip rows
rn
direct = true - use direct directory
rn

rn
Input.ctl which is the control input data file format.
rn
- This is the control file
rn
load data ---- keywords that this is a controlled document.
rn
infile 'd: \ book \ 200605.txt' --- to import the file location and file name
rn
append (insert, replace) into table book - the table to the BOOK additional (append) (insert an empty table (insert), the replacement of a table (replace the contents of the original table will be deleted))
rn
fields terminated by ',' - between the two field separator
rn
(Bookname char,
rn
bookno number,
rn
bookqty number,
rn
booktype char,
rn
intime date 'yyyymmdd'
rn
price number (6,2)
rn
) - Corresponds to the definition of out of order

rn rn rn