جداول inline خارجی (Inline External Tables) دردیتابیس اوراکل ۱۸C
دیتابیس اوراکل نسخه ۱۸C امکان دسترسی به flat files (بطور مثال فایل CSV) را با استفاده از Inline External Tables مشخص شده در دستور select فراهم می نماید.
این ویژگی یک گام فراتر از امکان override external table parameters from a query که در نسخه دوم دیتابیس اوراکل ۱۲C (12.2) معرفی شده است، می باشد.-Setup
-Inline External Tables
-پشتیبانی PL/SQL
-مباحث امنیتی
-سایر موارد

جداول inline خارجی (Inline External Tables) دردیتابیس اوراکل ۱۸C

Setup
برای اعلان یک جدول inline خارجی (Inline External Tables) ما به تعدادی دیتا بر روی Flat Files داریم. کوئری ذیل ۴ فایل CSV که هرکدام ۱۰۰۰ ردیف دارند را جمع می کند.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

SET MARKUP CSV ON QUOTE ON
SET TRIMSPOOL ON LINESIZE 1000 FEEDBACK OFF PAGESIZE 0

SPOOL /tmp/gbr1.txt
SELECT ‘GBR’,
object_id,
owner,
object_name
FROM dba_objects
WHERE object_id <= 2000
AND rownum <= 1000;
SPOOL OFF

SPOOL /tmp/gbr2.txt
SELECT ‘GBR’,
object_id,
owner,
object_name
FROM dba_objects
WHERE object_id BETWEEN 2000 AND 3999
AND rownum <= 1000;
SPOOL OFF

SPOOL /tmp/ire1.txt
SELECT ‘IRE’,
object_id,
owner,
object_name
FROM dba_objects
WHERE object_id <= 2000
AND rownum <= 1000;
SPOOL OFF

SPOOL /tmp/ire2.txt
SELECT ‘IRE’,
object_id,
owner,
object_name
FROM dba_objects
WHERE object_id BETWEEN 2000 AND 3999
AND rownum <= 1000; SPOOL OFF SET MARKUP CSV OFF SET TRIMSPOOL ON LINESIZE 1000 FEEDBACK OFF PAGESIZE 14 برای دسترسی به این فایلها بایستی ۲ دایرکتوری ایجاد کنیم، در این وضعیت هر دو به یک دایرکتوری اشاره میکنند، با این حال امکان دیدن Syntax را فراهم می کند. CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; CREATE OR REPLACE DIRECTORY tmp_dir1 AS ‘/tmp/’; GRANT READ, WRITE ON DIRECTORY tmp_dir1 TO test; CREATE OR REPLACE DIRECTORY tmp_dir2 AS ‘/tmp/’; GRANT READ, WRITE ON DIRECTORY tmp_dir2 TO test; CONN test/test@pdb1 جداول درون خطی خارجی ((Inline External Table Inline External Table این امکان را فراهم می کند که تعریف جدول خارجی (External Table) به همراه External Clause در بندFrom دستور SQL قرار گیرد، بنابراین بطور مشخص نیازی به ایجاد جدول خارجی نمی باشد. SELECT country_code, COUNT(*) AS amount FROM EXTERNAL ( ( country_code VARCHAR2(3), object_id NUMBER, owner VARCHAR2(128), object_name VARCHAR2(128) ) TYPE oracle_loader DEFAULT DIRECTORY tmp_dir1 ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE BADFILE tmp_dir1 LOGFILE tmp_dir1:’inline_ext_tab_%a_%p.log’ DISCARDFILE tmp_dir1 FIELDS CSV WITH EMBEDDED TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ MISSING FIELD VALUES ARE NULL ( country_code, object_id, owner, object_name ) ) LOCATION (‘gbr1.txt’, ‘gbr2.txt’, ‘ire1.txt’, ‘ire2.txt’) REJECT LIMIT UNLIMITED ) inline_ext_tab GROUP BY country_code ORDER BY 1; COUNTRY_CODE AMOUNT GBR 2000 IRE 2000 SQL>

در مثال ذیل از یک دایرکتوری متفاوت و در Location Clause از لیست فایلهای متفاوت استفاده می نماییم، بدون تعجب نتیجه متفاوتی به ما نشان می دهد.

SELECT country_code, COUNT(*) AS amount
FROM EXTERNAL (
(
country_code VARCHAR2(3),
object_id NUMBER,
owner VARCHAR2(128),
object_name VARCHAR2(128)
)
TYPE oracle_loader
DEFAULT DIRECTORY tmp_dir2
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
BADFILE tmp_dir2
LOGFILE tmp_dir2:’inline_ext_tab_%a_%p.log’
DISCARDFILE tmp_dir2
FIELDS CSV WITH EMBEDDED TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
MISSING FIELD VALUES ARE NULL (
country_code,
object_id,
owner,
object_name
)
)
LOCATION (‘gbr1.txt’, ‘gbr2.txt’)
REJECT LIMIT UNLIMITED
) inline_ext_tab
GROUP BY country_code
ORDER BY 1;

COUNTRY_CODE AMOUNT

GBR 2000

SQL>

بخش تعریف Inline External Table کمی زشت به نظر می رسد، بنابراین اگر می خواهید آن را به جداول دیگر Join کنید ترجیحا آن را در With Clause قرار دهید.
WITH inline_ext_tab AS (
SELECT *
FROM EXTERNAL (
(
country_code VARCHAR2(3),
object_id NUMBER,
owner VARCHAR2(128),
object_name VARCHAR2(128)
)
TYPE oracle_loader
DEFAULT DIRECTORY tmp_dir2
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
BADFILE tmp_dir2
LOGFILE tmp_dir2:’inline_ext_tab_%a_%p.log’
DISCARDFILE tmp_dir2
FIELDS CSV WITH EMBEDDED TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
MISSING FIELD VALUES ARE NULL (
country_code,
object_id,
owner,
object_name
)
)
LOCATION (‘gbr1.txt’, ‘gbr2.txt’)
REJECT LIMIT UNLIMITED
)
)
SELECT country_code, COUNT(*) AS amount
FROM inline_ext_tab
GROUP BY country_code
ORDER BY 1;

COUNTRY_CODE AMOUNT

GBR 2000

SQL>

پشتیبانی از PL/SQL
این syntax در PL/SQL نیز قابل استفاده می باشد، در اینجا مثالی از for-loop Cursor نشان داده شده است.

SET SERVEROUTPUT ON
BEGIN
FOR cur_rec IN (SELECT country_code, COUNT(*) AS amount
FROM EXTERNAL (
(
country_code VARCHAR2(3),
object_id NUMBER,
owner VARCHAR2(128),
object_name VARCHAR2(128)
)
TYPE oracle_loader
DEFAULT DIRECTORY tmp_dir1
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
BADFILE tmp_dir1
LOGFILE tmp_dir1:’inline_ext_tab_%a_%p.log’
DISCARDFILE tmp_dir1
FIELDS CSV WITH EMBEDDED TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
MISSING FIELD VALUES ARE NULL (
country_code,
object_id,
owner,
object_name
)
)
LOCATION (‘gbr1.txt’, ‘gbr2.txt’, ‘ire1.txt’, ‘ire2.txt’)
REJECT LIMIT UNLIMITED
) inline_ext_tab
GROUP BY country_code
ORDER BY 1)
LOOP
DBMS_OUTPUT.put_line(cur_rec.country_code || ‘:’ || cur_rec.amount);
END LOOP;
END;
/
GBR:2000
IRE:2000

PL/SQL procedure successfully completed.

SQL>

مثالی از Select …. into

DECLARE
l_country_code VARCHAR2(3);
l_amount NUMBER;
BEGIN
SELECT country_code, COUNT(*) AS amount
INTO l_country_code, l_amount
FROM EXTERNAL (
(
country_code VARCHAR2(3),
object_id NUMBER,
owner VARCHAR2(128),
object_name VARCHAR2(128)
)
TYPE oracle_loader
DEFAULT DIRECTORY tmp_dir1
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
BADFILE tmp_dir1
LOGFILE tmp_dir1:’inline_ext_tab_%a_%p.log’
DISCARDFILE tmp_dir1
FIELDS CSV WITH EMBEDDED TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
MISSING FIELD VALUES ARE NULL (
country_code,
object_id,
owner,
object_name
)
)
LOCATION (‘gbr1.txt’, ‘gbr2.txt’)
REJECT LIMIT UNLIMITED
) inline_ext_tab
GROUP BY country_code;

DBMS_OUTPUT.put_line(l_country_code || ‘:’ || l_amount);
END;
/
GBR:2000

PL/SQL procedure successfully completed.

SQL>

راهکار های امنیتی
دسترسی به object های Directory ها بدون در نظر گرفتن اینکه از External Table ها استفاده می شود یا خیر، بایستی با دقت کنترل گردد.
همانطور که Pete Feningan هم به آن اشاره کرده است، زمانیکه External Table تعریف نموده اید بایستی مراقبت خاصی نسبت به Directory object ها و مکان فایل هایی که استفاده می نمایید داشته باشید، امکان تغییر این پارامتر ها در زمان اجرا به این معنی است که شما باید توجه ویژه ای به Directory object هایی که در دسترس کاربر هست داشته باشید که در غیر اینصورت با یک ریسک بزرگ امنیتی مواجه می شوید.

سایر موارد
برخی از ایده و نظرات در رابطه با این قابلیت به شرح ذیل می باشد:
همانطور که در اسناد ذکر شده است ، این قابلیت از جداول خارجی پارتیشن بندی شده پشتیبانی نمی کند ، اما این امر لازم نیست زیرا شما کنترل کامل پرونده های قابل دسترسی را دارید ، بنابراین نیازی به در نظر گرفتن پارتیشن بندی نیست.
همانطور که در بالا ذکر شد ، راهکارهای امنیتی مربوط به این قابلیت وجود دارد.
نتیجه SQL آن واقعاً زشت است.
این قابلیت در شرایطی بکار می آیدکه شما دسترسی برای ایجاد Metadata Object ندارید ، به عنوان مثال یک پایگاه داده فقط Read-only مانند دیتا گارد.

مقالات مرتبط:

artarad_oracle_dbتفاوت نسخه های دیتابیس اورکل Artarad_Oracle_SQL_Performance_Tuningراهکارها و تکنیکهای Performance Tuning artarad_flex_1دیسک‌ گروه‌های ASM FLEX، گروه‌های QUOTA و شبیه‌سازی ASM SPLIT Artarad_18Cکپی کردن pdb در محیط دیتاگارد – اوراکل ۱۸c