Oracle Veritabanı: External Tables - Harici Tablolar

Harici tablolar ile Oracle veritabanı dışında bulunan dosyalar okunabilinir ve sorgulanabilinir. Oracle_Loader driver'ı  dışarıdaki dosyaların formatlarının düzenlenmesiyle birlikte içerideki dataların okunmasını sağlar. Harici dosyalar üzerine DML gerçekleştirilemez. Bu tablolara veri eklenip çıkartılamaz. Üzerlerine index tanımlanamaz. Harici dosyalar üzerine view ve synonym'ler yaratılabilinir; çünkü daha önceden bu tabloların tanımları yapılmıştır.

Harici tabloların oluşturulmasından önce bu tabloların bulunduğu klasörün belirtilmesi gerekir. Yerleştirilecek klasör veritabanında metadata olarak belirtilmelidir. Bunu da aşağıdaki klasör yaratma komutuyla yaratabiliriz.

create or replace directory temp_dir as '/home/users/oracle';

Silmek için de drop directory klasör_ismi;

Harici tabloları depolayacağımız klasörler belirtildikten sonra bu tabloların veritabanındaki eşleniklerini yaratırız. Yani tablomuzun tanımını veritabanında oluştururuz.

 CREATE TABLE emp (  
  name   VARCHAR2(50),  
  surname VARCHAR2(50)  
 )  
 ORGANIZATION EXTERNAL (  
  TYPE ORACLE_LOADER  
  DEFAULT DIRECTORY temp_dir  
  ACCESS PARAMETERS (  
   RECORDS DELIMITED BY NEWLINE  
   FIELDS TERMINATED BY ','  
   MISSING FIELD VALUES ARE NULL  
   (  
    name   CHAR(50),  
    surname CHAR(50)  
   )  
  )  
  LOCATION ('emp.txt')  
 ) 

Tablomuzun oluşturulmasındaki adımlar şöyledir.

1-Veritabanında sorgulayacağımız kolonları belirleriz.
2-"Organization External ile harici tablomuzun özelliklerini belirleriz.
   -Tablomuzun bulunduğu klasör
   -Access Parameters yani kayıtların nasıl formatlandığı
   -Verilerin text dosyasından nasıl okunacağını belirten kısım
   -Harici tablonun bulunduğu işletim sistemindeki dosya adı. (Bu örneğimizdeki adı 'emp.txt'. Bu dosya /home/users/oracle altındadır.)

Örnek olarak aşağıdaki resimden de görebiliriz.

Dosyamız veritabanında yaratıldığı anda harici dosyamızın bulunduğu klasördeki log dosyası oluşturulur.

 more EMP_20250962.log   
   
   
  LOG file opened at 03/17/14 14:42:03  
   
 Field Definitions for table EMP  
  Record format DELIMITED BY NEWLINE  
  Data in file has same endianness as the platform  
  Rows with all null fields are accepted  
   
  Fields in Data Source:   
   
   NAME              CHAR (20)  
    Terminated by ","  
    Trim whitespace same as SQL Loader  
   SURNAME             CHAR (20)  
    Terminated by ","  
    Trim whitespace same as SQL Loader  
 EMP_20250962.log: END  

Harici tablomuzun bulunduğu yeri ve içeriğini aşağıdan görebiliriz.

 >pwd  
 /home/users/oracle  
 >more emp.txt   
 berke,oz  
 ali,veli  
 mehmet,ahmet 

Harici tablomuzun bulunduğu dosyayı silersek ya da yerini değiştirirsek ORA-29913 hatasını alırız.

 ORA-29913: ODCIEXTTABLEOPEN başvurusunu yürütme hatası  
 ORA-29400: veri kartuşu hatası  
 KUP-04040: file emp.txt in TEMP_DIR not found  

Bu hatayla dosyamızın bulunamadığını anlayabiliriz.

Harici dosyadan okunamayan veriler "bad file" adı verilen bir dosyaya yazılırlar. Bununla ilgili bir parametre de vardır. Aynı şekilde "log file"'ın nereye çıkarılabileceğini gösteren bir parametre de yer alır. Aşağıdaki gibi örneğimizde görebiliriz.

  CREATE TABLE emp (   
  name  VARCHAR2(50),   
  surname VARCHAR2(50)   
  )   
  ORGANIZATION EXTERNAL (   
  TYPE ORACLE_LOADER   
  DEFAULT DIRECTORY temp_dir   
  ACCESS PARAMETERS (   
   RECORDS DELIMITED BY NEWLINE   
  BADFILE bdump:'%a_%p.bad'  
  LOGFILE bdump:'%a_%p.log'  
   FIELDS TERMINATED BY ','   
   MISSING FIELD VALUES ARE NULL   
   (   
   name  CHAR(50),   
   surname CHAR(50)   
   )   
  )   
  LOCATION ('emp.txt')   
  ) 

Access Parametreleri:

Access parametreleri içerisinde harici tablo dosyasının nasıl formatlandığı yazılır. Harici dosyadan her sütun alınamayabilinir ya da data tipleri farklı olabilir.

 create table apps.emp   
 (name varchar2(20)  
 )  
  organization external  
  (  
  type ORACLE_LOADER  
  Default Directory temp_dir  
  Access Parameters   
  (  
   Records Delimited By Newline  
   Fields terminated by ','  
   Missing Field values are null  
   (name char(20),  
   surname char(20)  
   )  
  )  
  Location ('emp.txt')  
  )  
    
  ;  
  select * from emp; 

Tablonun sütun tanım kısmından "surname" tablosunu çıkartırsak dosyadan "surname " bilgileri alınmaz. Ancak aşağıdaki gibi bir örnek yaratırsak hata alırız. Çünkü tablodaki 2. sütunu belirtmememize rağmen 2.sütunu veritabanına tanımlayınca okunamayan sütun bilgisi hatası alırız.

 create table apps.emp   
 (name varchar2(20),surname  varchar2(20)  
 )  
  organization external  
  (  
  type ORACLE_LOADER  
  Default Directory temp_dir  
  Access Parameters   
  (  
   Records Delimited By Newline  
   Fields terminated by ','  
   Missing Field values are null  
   (name char(20)  
   )  
  )  
  Location ('emp.txt')  
  )  
    
  ;  

Delimited By:

"Delimited by" ile tablonun içerisindeki satırların nasıl ayrıldığını gösteririz. Bu satırlar çeşitli karakter ifadeleriyle ayrılabilinirler.

Referanslar:
External Tables: http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN12898