آشنایی با SQL*Loader همراه با چند مثال

قصد داریم اطلاعات زیر را در جدولی از بانک درج کنیم:

1,vahid,usefzadeh,oracle database

2,reza,hosseini,sqlserver database

3,nima,alavi,mysql database

برای درج این اطلاعات، روشهای مختلفی وجود دارد که یکی از این روشها، استفاده از ابزار SQL*Loader می باشد. در این متن به بررسی این ابزار خواهیم پرداخت. شکل زیر، ساختار کلی SQL*Loader را نشان می دهد:

قبل از بررسی چگونگی استفاده از SQL*Loader، نیاز است تا با دو اصطلاح تخصصی این ابزار آشنا باشیم(در ادامه متن، با دیگر کامپوننتهای این ابزار آشنا خواهیم شد):

دیتافایل(Input Data files): در محیط SQL*Loader، به فایل متنی حاوی اطلاعات اصلی( اطلاعاتی که قصد داریم آنها را به بانک منتقل کنیم)، دیتافایل می گویند. برای مثال، mydatafile1.txt یک دیتافایل می باشد:

[oracle@myhost ~]$ vi /home/oracle/mydatafile1.txt

1,vahid,usefzadeh,oracle database

2,reza,hosseini,sqlserver database

3,nima,alavi,mysql database

کنترل فایل(Loader Control file): این فایل هم برخلاف کنترل فایل دیتابیس(که باینری است)، یک فایل متنی می باشد و اطلاعات کنترلی نظیر مسیر دیتافایل(infile)، نام جدولی که قرار است اطلاعات در آن ثبت شود(into table)، کاراکتر جداکننده هر فیلد(fields terminated by)، اسامی ستونهای جدول و … را شامل می شود.

برای مثال، mycontrolfile.ctl یک کنترل فایل می باشد:

[oracle@myhost ~]$ vi /home/oracle/mycontrolfile.ctl

load data

infile ‘/home/oracle/mydatafile1.txt’

into table mytbl

fields terminated by “,”

( id, name, last_name, comment )

برای اجرای عملیات SQL*Loader، باید از دستور sqlldr استفاده کرد. این دستور، پارامترهای متعددی دارد که تعدادی از آنها اختیاری و تعدادی دیگر، اجباری می باشند. در زیر، اسامی تعدادی از این پارامترها را مشاهده می کنید:

[oracle@myhost ~]$ sqlldr -help

SQL*Loader: Release 18.0.0.0.0 – Production on Tue Azar 27 17:10:47 1397

Version 18.3.0.0.0

userid — ORACLE username/password

control — control file name

log — log file name

bad — bad file name

data — data file name

discard — discard file name

parfile — parameter file: name of file that contains parameter specifications

در ادامه با ارائه چند مثال، با پارامترهای مهم دستور sqlldr و کنترل فایل آشنا خواهیم شد.

مثال 1: به عنوان اولین مثال، اطلاعات دیتافایل mydatafile1.txt را در جدولی به نام mytbl درج می کنیم:

[oracle@myhost ~]$ vi /home/oracle/mydatafile1.txt

1,vahid,usefzadeh,oracle database

2,reza,hosseini,sqlserver database

3,nima,alavi,mysql database

برای درج اطلاعات mydatafile1.txt در بانک، ابتدا باید کنترل فایلی را ایجاد کنیم. ساختار کنترل فایل را قبلا توضیح دادیم:

infile: با کمک این پارامتر، مسیر دیتافایل را مشخص می کنیم که در مثال ما، مسیر به صورت زیر می باشد:

infile ‘/home/oracle/mydatafile1.txt’

into table: نام جدول را همراه با این عبارت مشخص می کنیم:

into table mytbl

fields terminated by: چه کاراکتری نقش جداکننده را ایفا می کند؟ در مثال ما، کاراکتر , این نقش را دارد:

fields terminated by “,”

در نهایت هم اسامی ستونهای جدول mytbl را مشخص می کنیم:

( id, name, last_name, comment_ )

TRAILING NULLCOLS: در صورتی که برای فیلد اخر، مقداری در نظر گرفته نشود، این عبارت مانع از رخ دادن خطای زیر خواهد شد و برای آن فیلد، null درنظر گرفته خواهد شد.

Record 1: Rejected – Error on table MN, column S.

Column not found before end of logical record (use TRAILING NULLCOLS)

پس ساختار کنترل فایل در این مثال، به شکل زیر می باشد:

[oracle@myhost ~]$ cat mycontrolfile.ctl

load data

infile ‘/home/oracle/mydatafile1.txt’

into table mytbl

fields terminated by “,”

( id, name, last_name,comment_)

برای درج اطلاعات در دیتابیس، باید جدولی با ساختار مورد نظر ایجاد کنیم پس جدول mytbl را با دستور زیر ایجاد می کنیم:

SQL> create table usef.mytbl( id number, name varchar2(100), last_name varchar2(100), comment_ varchar2(100));

Table created.

بعد از تنظیم کنترل فایل و ایجاد جدول mytbl، صرفا کافیست دستور sqlldr را به همراه پارامتر control که مسیر کنترل فایل را مشخص می کند، اجرا کنیم:

[oracle@myhost ~]$ sqlldr control=/home/oracle/mycontrolfile.ctl

Username:usef/a

SQL*Loader: Release 18.0.0.0.0 – Production on Wed Azar 28 09:44:24 1397

Version 18.3.0.0.0

Path used: Conventional

Commit point reached – logical record count 3

Table MYTBL:

3 Rows successfully loaded.

Check the log file:

mycontrolfile.log

for more information about the load.

با اجرای این دستور، اطلاعات در جدول mytbl ثبت خواهند شد:

SQL> select * from usef.mytbl;

همچنین بعد از اجرای دستور sqlldr، در مسیر کنترل فایل، فایلی برای لاگ هم ایجاد خواهد شد:

[oracle@myhost ~]$ ls -l mycontrolfile*

-rw-r–r– 1 oracle oinstall 128 Dec 19 09:32 mycontrolfile.ctl

-rw-r–r– 1 oracle oinstall 1725 Dec 19 09:44 mycontrolfile.log

محتویات این فایل log را در قسمت زیر می بینید:

[oracle@myhost ~]$ less /home/oracle/mycontrolfile.log

SQL*Loader: Release 18.0.0.0.0 – Production on Wed Azar 28 09:44:24 1397

Version 18.3.0.0.0

Control File: /home/oracle/mycontrolfile.ctl

Data File: /home/oracle/mydatafile1.txt

Bad File: /home/oracle/mydatafile1.bad

Discard File: none specified

(Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Bind array: 250 rows, maximum of 1048576 bytes

Continuation: none specified

Path used: Conventional

Table MYTBL, 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

LAST_NAME NEXT * , CHARACTER

COMMENT_ NEXT * , CHARACTER

Table MYTBL:

3 Rows successfully loaded.

0 Rows 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 array: 258000 bytes(250 rows)

Read buffer bytes: 1048576

Total logical records skipped: 0

Total logical records read: 3

Total logical records rejected: 0

Total logical records discarded: 0

Run began on Wed Azar 28 09:44:24 1397

Run ended on Wed Azar 28 09:44:27 1397

Elapsed time was: 00:00:02.95

CPU time was: 00:00:00.03

برای تغییر دادن نام و مسیر فایل لاگ، می توان از پارامتر log در دستور sqlldr استفاده کرد:

sqlldr control=/home/oracle/mycontrolfile.ctl log=/18c/mylog.txt

در اولین مثال، همه رکوردها را به جدول mytbl منتقل کردیم حال اگر بخواهیم رکوردهای مشخصی را برای انتقال به بانک انتخاب کنیم، می توانیم از عبارت when، بعد از تعیین نام جدول استفاده کنیم:

[oracle@myhost ~]$ vi mycontrolfile.ctl

load data

infile ‘/home/oracle/mydatafile1.txt’

into table mytbl

when last_name =’usefzadeh’

fields terminated by “,”

( id, name, last_name,comment_)

[oracle@myhost ~]$ sqlldr control=/home/oracle/mycontrolfile.ctl

Username:usef/a

Path used: Conventional

Commit point reached – logical record count 3

Table MYTBL:

1 Row successfully loaded.

با کمک عبارت when، صرفا یک رکورد در جدول mytbl درج شده است. برای تعیین رکوردهایی که به دلیل وجود شرط ‘when last_name =’usefzadeh در جدول mytbl ثبت نشده اند، می توان از پارامتر discard استفاده کرد:

[oracle@myhost ~]$ sqlldr control=/home/oracle/mycontrolfile.ctl discard=/18c/dis.txt

[oracle@myhost ~]$ less /18c/dis.txt

2,reza,hosseini,sqlserver database

3,nima,alavi,mysql database

پارامتر APPEND، TRUNCATE و REPLACE در کنترل فایل

مثال دوم: در مثال قبل، سه رکورد را در جدول mytbl درج کردیم حال قصد اضافه کردن دو رکورد دیگر را هم داریم که به اشتباه کاراکتری هم به سطر سوم فایل اضافه شده است:

[oracle@myhost ~]$ vi /home/oracle/mydatafile2.txt

4,ramzon,rezai,oracle developer

5,akbar,abedi,c##

بعد از تغییر نام دیتافایل در کنترل فایل mycontrolfile.ctl، دستور sqlldr را مجددا اجرا می کنیم:

[oracle@myhost ~]$ sqlldr control=/home/oracle/mycontrolfile.ctl

Username:usef

Password:

SQL*Loader: Release 18.0.0.0.0 – Production on Wed Azar 28 10:27:41 1397

Version 18.3.0.0.0

Path used: Conventional

SQL*Loader-601: For INSERT option, table must be empty. Error on table MYTBL

اجرای دستور با خطا متوقف شد و در پیام هم می بینیم که اجرای دستور به خالی بودن جدول منوط شده است. برای جلوگیری از رخ دادن این خطا، و اضافه کردن اطلاعات به جدول mytbl، کافیست پارامتر APPEND را به کنترل فایل اضافه کنیم:

[oracle@myhost ~]$ vi mycontrolfile.ctl

load data

infile ‘/home/oracle/mydatafile2.txt’

append

into table mytbl

fields terminated by “,”

( id, name, last_name, comment_ )

بعد از اضافه کردن پارامتر APPEND، دستور را اجرا می کنیم:

[oracle@myhost ~]$ sqlldr control=/home/oracle/mycontrolfile.ctl

Username:usef

Password:

SQL*Loader: Release 18.0.0.0.0 – Production on Wed Azar 28 10:32:59 1397

Version 18.3.0.0.0

Path used: Conventional

Commit point reached – logical record count 3

Table MYTBL:

2 Rows successfully loaded.

Check the log file:

mycontrolfile.log

for more information about the load.

ثبت دو رکورد با موفقیت انجام شد و در همین حال، کاراکتر s به عنوان یک رکورد تلقی شد که درج آن، با خطا مواجه شده است. برای مشاهده رکوردهای ثبت نشده، می توان به فایلی که همنام با دیتافایل ایجاد می شود و پسوند bad. دارد، رجوع کرد:

[oracle@myhost ~]$ ls -l mydatafile2*

-rw-r–r– 1 oracle oinstall 2 Dec 19 10:33 mydatafile2.bad

-rw-r–r– 1 oracle oinstall 52 Dec 19 10:23 mydatafile2.txt

[oracle@myhost ~]$ less mydatafile2.bad

البته در موارد دیگری چون، عدم رعایت unique constraint هم رکورد یا رکوردهای مورد نظر، در این فایل ثبت خواهند شد! مثال زیر را ببینید:

SQL> alter table MYTBL add constraint prim_key primary key (ID);

TABLE altered.

[oracle@myhost ~]$ sqlldr control=/home/oracle/mycontrolfile.ctl

Username:usef/a

SQL*Loader: Release 18.0.0.0.0 – Production on Wed Azar 28 10:51:39 1397

Version 18.3.0.0.0

Path used: Conventional

Commit point reached – logical record count 3

Table MYTBL:

0 Rows successfully loaded.

Check the log file:

mycontrolfile.log

for more information about the load.

[oracle@myhost ~]$ less mydatafile2.bad

4,ramzon,rezai,oracle developer

5,akbar,abedi,c##

نکته 1: تعداد خطاها را می توان با کمک پارامتر errors محدود کرد(مقدار پیش فرض این پارامتر، برابر با 50 می باشد.)

نکته 2: در صورتی که بخواهیم اطلاعات قبلی جدول، قبل از درج، truncate شود، می توانیم از پارامتر TRUNCATE به جای APPEND استفاده کنیم و استفاده از پارامتر REPLACE سبب خواهد شد تا از عملیات delete به جای truncate استفاده شود.

درج اطلاعات دیتافایل در کنترل فایل(عدم استفاده از دیتافایل)

مثال سوم: با کمک عبارت begindata، می توان اطلاعات دیتافایل را به کنترل فایل اضافه کرد و به صورت کلی، از دیتافایل صرف نظر کرد. برای این کار، به جای ذکر مسیر دیتافایل در کنار عبارت infile، از کاراکتر * استفاده می کنیم:

load data

infile *

append

into table mytbl

fields terminated by “,”

( id, name, last_name, comment_ )

begindata

6,ahmad,mottaghi,oracle database

7,nader,dastneshan,oracle database

با اجرای دستور sqlldr، خواهیم دید که درج به سادگی انجام خواهد شد:

[oracle@myhost ~]$ sqlldr control=/home/oracle/mycontrolfile.ctl log=/18c/mylog.txt

Username:usef/a

SQL*Loader: Release 18.0.0.0.0 – Production on Wed Azar 28 12:12:52 1397

Version 18.3.0.0.0

Path used: Conventional

Commit point reached – logical record count 2

Table MYTBL:

2 Rows successfully loaded.

Check the log file:

/18c/mylog.txt

for more information about the load.

تعیین فرمت تاریخ

مثال چهارم: برای درج اطلاعاتی که حاوی تاریخ هستند، باید ملاحظاتی را در نظر گرفت(البته اگر مقادیر به فرمت DD-MON-YYYY نباشند!) برای مثال، ثبت اطلاعات زیر، به شکل قبل؛ با خطا همراه خواهد بود:

[oracle@myhost ~]$ vi /home/oracle/mydata01.txt

1,reza,ahmadi,1397/01/05

2,ali,rahmani,1397/05/08

3,mehrdad,teimori,1395/04/12

[oracle@myhost ~]$ sqlldr control=/home/oracle/mycontrolfile.ctl

Username:usef/a

SQL*Loader: Release 18.0.0.0.0 – Production on Sat Dey 1 14:00:44 1397

Version 18.3.0.0.0

Path used: Conventional

Commit point reached – logical record count 3

Table MYTBL2:

0 Rows successfully loaded.

Check the log file:

mycontrolfile.log

for more information about the load.

همانطور که می بینید، عملا اطلاعاتی در جدول mytbl2 درج نشده است. برای ثبت تاریخ، باید فرمت تاریخ را در کنترل فایل مشخص کرد:

[oracle@myhost ~]$ vi /home/oracle/mycontrolfile.ctl

load data

infile ‘/home/oracle/mydata01.txt’

append

into table mytbl2

fields terminated by “,”

( id, name, last_name, emp_date date ‘YYYY/MM/DD’)

مجددا دستور sqlldr را اجرا می کنیم:

[oracle@myhost ~]$ sqlldr control=/home/oracle/mycontrolfile.ctl

Username:usef/a

SQL*Loader: Release 18.0.0.0.0 – Production on Sat Dey 1 14:13:37 1397

Version 18.3.0.0.0

Path used: Conventional

Commit point reached – logical record count 3

Table MYTBL2:

3 Rows successfully loaded.

Check the log file:

mycontrolfile.log

for more information about the load.

ثبت اطلاعات بدون خطا انجام شد:

SQL> select count(*) from usef.mytbl2;

1 reza ahmadi 1397/01/05

2 ali rahmani 1397/05/08

3 mehrdad teimori 1395/04/12

تعیین جداکننده ها بر اساس ستون

مثال پنجم: جداکننده ها را می توان در سطح ستون تعیین کرد برای مثال، فرض کنید که قصد داریم اطلاعات زیر را در جدول mytbl3 درج کنیم:

[oracle@myhost ~]$ vi /home/oracle/mydata01.txt

1-reza,@ahmadi_1397/01/05

2-ali@rahmani_1397/05/08

3-mehrdad@teimori_1395/04/12

روشن است که انتخاب کاما به عنوان جداکننده، کارساز نخواهد بود و باید برای هر فیلد، جداکننده ای را در نظر بگیریم، این کار در کنترل فایل mycont.ctl انجام شده است:

[oracle@myhost ~]$ vi /home/oracle/mycont.ctl

load data

infile ‘/home/oracle/mydata01.txt’

append

into table mytbl3

fields terminated by “,”

( id terminated by “-” , name terminated by “@”, last_name terminated by “_”, emp_date date ‘YYYY/MM/DD’)

دستور sqlldr را اجرا می کنیم:

[oracle@myhost ~]$ sqlldr control=/home/oracle/mycont.ctl

Username:usef/a

SQL*Loader: Release 18.0.0.0.0 – Production on Sat Dey 1 14:38:20 1397

Version 18.3.0.0.0

Path used: Conventional

Commit point reached – logical record count 3

Table MYTBL3:

3 Rows successfully loaded.

Check the log file:

mycont.log

for more information about the load.

همچنین اگر در متن دیتا، به صورت کلی خبری از جداکننده نباشد، ولی ستونها طول ثابتی داشته باشند، می توان از روش زیر استفاده کرد:

–datafile

[oracle@myhost ~]$ vi /home/oracle/mydata01.txt

1rezaahmadi1397/01/05

2alirahmani1397/05/08

3mehrteimor1395/04/12

–controlfile

[oracle@myhost ~]$ vi /home/oracle/mycont.ctl

load data

infile ‘/home/oracle/mydata01.txt’

append

into table mytbl2

fields terminated by “,”

( id position(1:1), name position(2:4), last_name position(5:11), emp_date position(12:22) date ‘YYYY/MM/DD’)

دستور sqlldr را اجرا می کنیم:

[oracle@myhost ~]$ sqlldr control=/home/oracle/mycont.ctl

Username:usef/a

SQL*Loader: Release 18.0.0.0.0 – Production on Sat Dey 1 15:07:26 1397

Version 18.3.0.0.0

Path used: Conventional

Commit point reached – logical record count 3

Table MYTBL2:

3 Rows successfully loaded.

Check the log file:

mycont.log

for more information about the load.

انتقال دیتا به چند جدول و خواندن دیتا از چند دیتافایل

مثال ششم: در زمان اجرای دستور sqlldr، می توان کنترل فایل را طوری تغییر داد که درج به طور همزمان در چند جدول انجام شود:

[oracle@myhost ~]$ vi /home/oracle/mycont.ctl

load data

infile ‘/home/oracle/mydata01.txt’

append

into table table1

fields terminated by “,”

( id position(1:1), name position(2:4), last_name position(5:11), emp_date position(12:22) date ‘YYYY/MM/DD’)

into table table2

fields terminated by “,”

( id position(1:1), name position(2:4), last_name position(5:11), emp_date position(12:22) date ‘YYYY/MM/DD’)

–sqlldr

[oracle@myhost ~]$ sqlldr control=/home/oracle/mycont.ctl

Path used: Conventional

Commit point reached – logical record count 3

Table TABLE1:

3 Rows successfully loaded.

Table TABLE2:

3 Rows successfully loaded.

همچنین برای خواندن اطلاعات از دو دیتافایل و ثبت آن در یک یا چند جدول، می توان کنترل فایل را به شکل زیر تنظیم کرد:

[oracle@myhost ~]$ vi /home/oracle/mycont.ctl

load data

infile ‘/home/oracle/mydata01.txt’

infile ‘/home/oracle/mydata02.txt’

append

into table table1

fields terminated by “,”

( id position(1:1), name position(2:4), last_name position(5:11), emp_date position(12:22) date ‘YYYY/MM/DD’)

into table table2

fields terminated by “,”

( id position(1:1), name position(2:4), last_name position(5:11), emp_date position(12:22) date ‘YYYY/MM/DD’)

–sqlldr

[oracle@myhost ~]$ sqlldr control=/home/oracle/mycont.ctl

Username:usef/a

SQL*Loader: Release 18.0.0.0.0 – Production on Sat Dey 1 16:15:05 1397

Version 18.3.0.0.0

Path used: Conventional

Commit point reached – logical record count 13

Commit point reached – logical record count 16

Table TABLE1:

16 Rows successfully loaded.

Table TABLE2:

16 Rows successfully loaded.

انتقال به صورت Direct یا Conventional

مثال هفتم: انتقال اطلاعات از طریق sql*Loader به دو روش Direct و Conventional قابل انجام است که به صورت پیش فرض، از روش Conventional استفاده خواهد شد.

توجه!: برای مطالعه در زمینه روشهای insert، مطلب Direct path vs Conventional insert را مطالعه بفرمایید.

در مثال زیر، عملیات انتقال را به روش Direct، انجام خواهیم داد:

[oracle@myhost ~]$ sqlldr control=/home/oracle/mycontrolfile.ctl direct=true

Username:usef/a

SQL*Loader: Release 18.0.0.0.0 – Production on Sat Dey 1 17:19:01 1397

Version 18.3.0.0.0

Path used: Direct

Load completed – logical record count 3.

Table MYTBL:

3 Rows successfully loaded.

Check the log file:

mycontrolfile.log

for more information about the load.

نکته: در زمان استفاده از Direct Path insert، می توان از پارامتر parallel هم استفاده کرد:

[oracle@myhost ~]$ sqlldr control=/home/oracle/mycontrolfile.ctl direct=true parallel=true

وحید یوسف زاده

ارائه خدمات مشاوره ، پشتیبانی و نصب و راه اندازی پایگاه داده اوراکل در سراسر کشور...................... تلفن: 09128110897 ایمیل:vahidusefzadeh@gmail.com

وحید یوسف زاده

دیدگاهتان را بنویسید لغو پاسخ