Primer
Kreiranje Eksternih Tabela
Mapiranje
direktorijuma u bazi
Dodeljivanje
privilegija na direktorijumima
Kreiranje
testne tabele EMPLOYEES
Definicija
obe eksterne tabele
Unos
podataka iz eksterne u internu tabelu
1. Datoteka D:\external\empxt1.dat sadrži podatke:
360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus
361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper
362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr
363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda
2. Datoteka D:\external\empxt2.dat sadrži podatke:
401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel
402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega
403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins
404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard
Enter user-name: / as sysdba
Connected to:
Oracle Database 10g
With the Partitioning, OLAP and Data Mining options
SQL> CREATE OR REPLACE DIRECTORY admin_dat_dir as 'D:\external';
Directory created.
SQL> CREATE OR REPLACE DIRECTORY admin_log_dir as 'D:\external\log';
Directory created.
SQL> CREATE OR REPLACE DIRECTORY admin_bad_dir as 'D:\external\bad';
Directory created.
SQL> GRANT READ ON DIRECTORY admin_dat_dir TO scott;
Grant succeeded.
SQL> GRANT READ ON DIRECTORY admin_log_dir TO scott;
Grant succeeded.
SQL> GRANT READ ON DIRECTORY admin_bad_dir TO scott;
Grant succeeded.
GRANT WRITE ON DIRECTORY admin_dat_dir TO scott;
Grant succeeded.
GRANT WRITE ON DIRECTORY admin_log_dir TO scott;
Grant succeeded.
GRANT WRITE ON DIRECTORY admin_bad_dir TO scott;
Grant succeeded.
--Testna tabela
CREATE TABLE EMPLOYEES
(
EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20 BYTE),
LAST_NAME VARCHAR2(25 BYTE) CONSTRAINT EMP_LAST_NAME_NN NOT NULL,
EMAIL VARCHAR2(25 BYTE) CONSTRAINT EMP_EMAIL_NN NOT NULL,
PHONE_NUMBER VARCHAR2(20 BYTE),
HIRE_DATE DATE CONSTRAINT EMP_HIRE_DATE_NN NOT NULL,
JOB_ID VARCHAR2(10 BYTE) CONSTRAINT EMP_JOB_NN NOT NULL,
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4)
);
Definicija obe eksterne tabele
CREATE
TABLE admin_ext_employees
(employee_id NUMBER(4),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
job_id VARCHAR2(10),
manager_id NUMBER(4),
hire_date DATE,
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
department_id NUMBER(4),
email VARCHAR2(25)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY admin_dat_dir
ACCESS PARAMETERS
(
records
delimited by newline
badfile
admin_bad_dir:'empxt%a_%p.bad'
logfile
admin_log_dir:'empxt%a_%p.log'
fields
terminated by ','
missing field
values are null
( employee_id,
first_name, last_name, job_id, manager_id,
hire_date
char date_format date mask "dd-mon-yyyy",
salary,
commission_pct, department_id, email
)
)
LOCATION ('empxt1.dat', 'empxt2.dat')
)
PARALLEL
REJECT LIMIT UNLIMITED;
--ako je neophodno paralel load
ALTER SESSION ENABLE PARALLEL DML;
podatke iz eksterne u internu tabelu
INSERT INTO employees (employee_id, first_name, last_name, job_id, manager_id,
hire_date, salary, commission_pct, department_id, email)
SELECT * FROM admin_ext_employees;
8 rows created.
SQL> select * from admin_ext_employees
/
EMPLOYEE_ID
FIRST_NAME LAST_NAME JOB_ID MANAGER_ID HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID EMAIL
-----------
-------------------- ------------------------- ---------- ---------- ---------
---------- -------------- ------------- -------------------------
401 Jesse Cromwell HR_REP 203 17-MAY-01 7000 0 40 jcromwel
402 Abby Applegate IT_PROG 103 17-MAY-01 9000 .2 60 aapplega
403 Carol Cousins AD_VP 100 17-MAY-01 27000 .3 90 ccousins
404 John Richardson AC_ACCOUNT 205 17-MAY-01 5000 0 110 jrichard
360 Jane Janus ST_CLERK 121 17-MAY-01 3000 0 50 jjanus
361 Mark Jasper SA_REP 145 17-MAY-01 8000 .1 80 mjasper
362 Brenda Starr AD_ASST 200 17-MAY-01 5500
0 10 bstarr
363 Alex Alda AC_MGR 145 17-MAY-01 9000 .15 80 aalda
8
rows selected.
SQL>
select object_name,object_type from user_objects where
object_name='ADMIN_EXT_EMPLOYEES'
/
OBJECT_NAME OBJECT_TYPE
------------------------------
-------------------
ADMIN_EXT_EMPLOYEES TABLE
SQL>
SELECT TABLE_NAME,DEFAULT_DIRECTORY_NAME FROM USER_EXTERNAL_TABLES
/
TABLE_NAME DEFAULT_DIRECTORY_NAME
------------------------------
------------------------------
ADMIN_EXT_EMPLOYEES ADMIN_DAT_DIR