Skip to content

1.4 SQL * LOADER

Jump to bottom
thuantt0101 edited this page Dec 17, 2019 · 9 revisions

Using SQL Loader

References at

Step1 : khởi tạo scheme data

CREATE TABLE EMPS
(
    EMP_ID NUMBER,
    EMP_NAME VARCHAR(50),
    DEPT_ID NUMBER
);

Step 2: Khởi tạo data đầu vào emp_data.dat

id,name,dept_id
100,Steve,10
101,Bill,20
102,Larry,30
103,Elon,30
104,Watson,20
105,Satya,10

Step 3: Khởi tạo file control : load_emp_data.ctl

  • có thể sử dụng append thay cho TRUNCATE
OPTIONS(SKIP=1)
--LOAD=3 : JUST LOAD 3 FIRST RECORD
--ERRORS=5 : INDICATE THAT THE FIFTH ROW IS ERROR RECORD
LOAD DATA
INFILE 'D:\Databases\Oracle\docs\Sql Loader\emp_data.dat'
--TRUNCATE :TRUNCATE DATA BEFORE INSERT NEW DATA INTO TABLE
APPEND --PLUS NEW DATA TO OLD DATA
INTO TABLE EMPS
fields terminated by ","
(
	emp_id,
	emp_name,
	dept_id
)

Step 4: Sử dụng cmd để chạy import

  • Vào đúng thư mục chứa file control và sử dụng lệnh
sqlldir userid=admin/123456 control ='D:\Databases\Oracle\docs\Sql Loader\load_emp_data.ctl'

Step 5: truy vấn cơ sở dữ liệu để xem kết quả đầu ra

using sqlldr through network

  • command line : sqlldr hr/your_password@"//mymachine.mydomain:port/MYDB"
sqlldr userid=thuantt/123456@\"//192.168.39.181:1521/xe\" control='E:\Work\FT-LAB\Documents\2. Docodemo wifiEverywhere wifi\sbm_ftfwmonth.ctl'
  • cai dat

Installing Instant Client from the UNIX or Windows Zip Files

Download the RPM packages containing the Tools Instant Client package, and the OCI package from the OTN Instant Client page at http://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html. Both packages must be version 12.2.0.0.0 or higher, and the versions of both must be the same.

Create a new directory, for example, /home/instantclient12_2 on UNIX or c:\instantclient12_2 on Windows.

Unzip the two packages into the new directory. Install the OCI Package first.

Configure Instant Client. See Configuring Tools Instant Client Package

Clone this wiki locally