/* SQL*Loader is used to move the data from external file to the database. The file must be either with fixed length or delimited format. */
/* SQL*Loader is invoked with sqlldr command and has following parameters.
USERID – username and password of a database user.
CONTROL – name of the control file that is used to describe the external file, define how to interpret and load it to the table.
LOG – name of the logfile where the whole activity of the load process is recorded.
BAD – name of the file where rejected rows are inserted. The rejection cause might be either the wrong data type or constraint violation.
DISCARD – name of the file where the rows that do not meet any loading criteria are inserted.
DATA – name of the flat file that contains the actual data.
SKIP – specifies how many rows from the beginning of the file should be skipped and not loaded.
LOAD – specifies how many rows should be loaded, where the default values is – all.
ERRORS – specifies maximum number of errors allowed occur (default is 50). */
$ vi /tmp/list_of_employees.dat
1.jonathan.watson.mg_team.3000.jwatson.1970
2;nikola;thomson;mg_team;4000;nthomson;1975
3;carl;max;vp_team;6000;cmax;1967
4;william;dalton;vp_team;6200;wdalton;1969
5;leonardo;crick;director;8000;lcrick;1965
CREATE TABLE sqlldr_test_01
(id NUMBER, name VARCHAR2(10), surname VARCHAR2(10), team VARCHAR2(10), salary NUMBER, username VARCHAR2(10), year NUMBER);
$ vi /tmp/sqlldr_controlfile01.ctl
load data
infile '/tmp/list_of_employees.dat'
into table sqlldr_test_01
fields terminated by ';' optionally enclosed by '"'
(id, name, surname, team, salary, username, year)
$ sqlldr system/asdf0930k control=/tmp/sqlldr_controlfile01.ctl log=/tmp/sqlldr_log01.log bad=/tmp/sqlldr_bad.log
$ more /tmp/sqlldr_log01.log
SELECT * FROM sqlldr_test_01;
실습: Load a data without providing a flat file
CREATE TABLE sqlldr_test_02
AS
SELECT *
FROM sqlldr_test_01
WHERE 1=2;
$ vi /tmp/sqlldr_control02.ctl
load data
infile *
into table sqlldr_test_02
fields terminated by ';' optionally enclosed by '"'
(id, name, surname, team, salary, username, year)
begindata
2;marx;karl;mg_team;4000;kmarx;1975
3;carl;max;vp_team;6000;cmax;1967
4;william;dalton;vp_team;6200;wdalton;1969
5;leonardo;crick;director;8000;lcrick;1965
$ sqlldr system/asdf0930k control=/tmp/sqlldr_control02.ctl log=/tmp/sqlldr_log02.log
SELECT count(1) FROM sqlldr_test_02;
실습: Load a data to the non-empty table
$ vi /tmp/sqlldr_control03.ctl
load data
infile *
append
into table sqlldr_test_02
fields terminated by ';' optionally enclosed by '"'
(id, name, surname, team, salary, username, year)
begindata
2;marx;karl;mg_team;4000;kmarx;1975
3;carl;max;vp_team;6000;cmax;1967
4;william;dalton;vp_team;6200;wdalton;1969
5;leonardo;crick;director;8000;lcrick;1965
$ sqlldr system/asdf0930k control=/tmp/sqlldr_control03.ctl log=/tmp/sqlldr_control02.log
실습: Load data with fixed format
$ vi /tmp/list_of_employees02.dat
2Nikola Thomson MG_TEAM 4000nthomson 1975
3Carl Max VP_TEAM 6000cmax 1967
4William Dalton VP_TEAM 6200wdalton 1969
5Leonardo Crick DIRECTOR 8000lcrick 1965
$ vi /tmp/sqlldr_control04.ctl
load data
infile '/tmp/list_of_employees02.dat'
append
into table sqlldr_test_02
fields terminated by ';'
(id position(1:1), name position(2:10), surname position(11:18), team position(19:27), salary position(28:31), username position(32:40), year position(41:44))
$ sqlldr system/asdf0930k control=/tmp/sqlldr_control04.ctl log=/tmp/sqlldr_control04.log
실습: Load data to multiple tables
$ vi /tmp/sqlldr_control05.ctl
load data
infile '/tmp/list_of_employees02.dat'
truncate
into table sqlldr_test_01
(id position(1:1), name position(2:10), surname position(11:18), team position(19:27), salary position(28:31), username position(32:40), year position(41:44))
into table sqlldr_test_02
(id position(1:1), name position(2:10), surname position(11:18), team position(19:27), salary position(28:31), username position(32:40), year position(41:44))
$ sqlldr system/asdf0930k control=/tmp/sqlldr_control05.ctl log=/tmp/sqlldr_logfile05.ctl
SELECT count(1) FROM sqlldr_test_01;
SELECT count(1) FROM sqlldr_test_02;
실습: Using WHERE clause with SQL*Loader
$ vi /tmp/sqlldr_control06.ctl
load data
infile '/tmp/list_of_employees02.dat'
truncate
into table sqlldr_test_01
WHEN (team = 'MG_TEAM')
(id position(1:1), name position(2:10), surname position(11:18), team position(19:27), salary position(28:31), username position(32:40), year position(41:44))
$ sqlldr system/asdf0930k control=/tmp/sqlldr_control06.ctl log=/tmp/sqlldr_logfile06.log
SELECT * FROM sqlldr_test_01;
실습: Loading LOB data
$ vi /tmp/sqlldr_exadata.dat
Oracle Exadata provides the highest-performing and most-available infrastructure for running Oracle Database, whether it’s in the Oracle Cloud, on premises, or a combination of both. Oracle Exadata architecture features a scale-out design with industry-standart servers and intelligent storage, including state-of-the-art flash technology and a high-speed InfiniBand internal fabric. This architecture provides 100 percent Oracle Database portability on premises to Oracle Public Cloud and back
$ sqlplus system/asdf0930k
CREATE TABLE tbl_loader_lob (id NUMBER, product VARCHAR2(10), text CLOB);
$ vi /tmp/sqlldr_control07.ctl
load data
infile *
truncate
into table tbl_loader_lob
fields terminated by ','
(id CHAR(1), product CHAR(10), clob_fname FILLER CHAR(500), text LOBFILE(clob_fname) terminated by EOF)
begindata
1,Exadata,/tmp/sqlldr_exadata.dat
EOF
$ sqlldr system/asdf0930k control=/tmp/sqlldr_control06.ctl
SELECT * FROM tbl_loader_lob;
난이도: ★★☆☆☆
문제 1: SQL*Loader를 사용하여 같은 구조를 가진 테이블을 생성하고 데이터를 load하여라. (15분)
$ sqlplus system/asdf0930k
CREATE TABLE sqlldr_table_01 (id NUMBER);
$ vi /tmp/controlfile_01.ctl
load data
infile *
into table sqlldr_table_01
fields terminated by ','
(id)
begindata
1
2
3
$ sqlldr system/asdf0930k control=/tmp/control_01.ctl
난이도: ★★☆☆☆
문제 2: SQL*Loader를 사용하여 flat file 없이 행을 append하여라. (12분)
$ sqlplus system/asdf0930k
CREATE TABLE sqlldr_table_01 (id NUMBER);
$ vi /tmp/controlfile_02.ctl
load data
infile *
append
into table sqlldr_table_01
fields terminated by ','
(id)
begindata
1
2
3
$ sqlldr system/asdf0930k control=/tmp/control_01.ctl
난이도: ★★☆☆☆
문제 3: SQL*Loader를 사용하여 ID<3인 행만 읽어들여라. (10분)
$ sqlplus system/asdf0930k
CREATE TABLE sqlldr_table_01 (id NUMBER);
$ vi /tmp/controlfile_03.ctl
load data
infile *
truncate
into table sqlldr_table_01
WHEN id <>'3'
fields terminated by ','
(id)
begindata
1
2
3
$ sqlldr system/asdf0930k control=/tmp/control_01.ctl
난이도: ★★★★☆
문제 4: 100단어 정도로 구성된 텍스트 파일을 생성하고, CLOB 컬럼 타입으로 테이블을 생성하라. (15분)
$ vi /tmp/textfile01.dat
Cristiano Ronaldo dos Santos Aveiro GOIH ComM (Portuguese pronunciation: [k?i?'tj?nu ??'naldu];
born 5 February 1985) is a Portuguese professional footballer who plays as a forward for and captains both Saudi Professional League club Al Nassr and the Portugal national team.
Widely regarded as one of the greatest players of all time, Ronaldo has won five Ballon dOr awards[note 3] and four European Golden Shoes, the most by a European player.
He has won 32 trophies in his career, including seven league titles, five UEFA Champions Leagues, the UEFA European Championship and the UEFA Nations League.
Ronaldo holds the records for most appearances (183), goals (140), and assists (42) in the Champions League, goals in the European Championship (14), men's international goals (122), and men's international appearances (198).
He is one of the few players to have made over 1,100 professional career appearances, and has scored over 800 official senior career goals for club and country.
$ sqlplus system/asdf0930k
CREATE TABLE text_loader (id NUMBER, text CLOB);
$ vi /tmp/sqlldr_control.ctl
load data
infile *
truncate
into table text_loader
fields terminated by ','
(id CHAR(1), clob_directory FILLER CHAR(100), text LOBFILE(clob_directory) terminated by EOF)
begindata
1,/tmp/textfile01.dat
EOF
$ sqlldr system/asdf0930k control=/tmp/sqlldr_control.ctl
