Wednesday, July 9, 2014

Oracle External Table with Multiple Data File in Directory

External Table of Oracle can be loaded by multiple file all together in single directory dynamically by constructing location value:

CREATE TABLE emp_load
  2      (
  3       employee_number      CHAR(5),
  4       employee_dob         CHAR(20),
  5       employee_last_name   CHAR(20),
  6       employee_first_name  CHAR(15),
  7       employee_middle_name CHAR(15),
  8       employee_hire_date   DATE
  9      )
 10    ORGANIZATION EXTERNAL
 11      (
 12       TYPE ORACLE_LOADER
 13       DEFAULT DIRECTORY tmp
 14       ACCESS PARAMETERS
 15         (
 16          RECORDS DELIMITED BY NEWLINE
 17          FIELDS (
 18                  employee_number      CHAR(2),
 19                  employee_dob         CHAR(20),
 20                  employee_last_name   CHAR(18),
 21                  employee_first_name  CHAR(11),
 22                  employee_middle_name CHAR(11),
 23                  employee_hire_date   CHAR(10) date_format DATE mask "mm/dd/yyyy"
 24                 )
 25         )
 26       LOCATION ('info*.dat')
 27      )
 28  /

Table created.

SQL> select * from emp_load;
select * from emp_load
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout


SQL> set serveroutput on
SQL> declare
  2      v_exists      boolean;
  3      v_file_length number;
  4      v_blocksize   number;
  5      v_stmt        varchar2(1000) := 'alter table emp_load location(';
  6      i             number := 1;
  7  begin
  8      loop
  9        utl_file.fgetattr(
 10                          'TMP',
 11                          'info' || i || '.dat',
 12                          v_exists,
 13                          v_file_length,
 14                          v_blocksize
 15                         );
 16        exit when not v_exists;
 17        v_stmt := v_stmt || '''info' || i || '.dat'',';
 18        i := i + 1;
 19      end loop;
 20      v_stmt := rtrim(v_stmt,',') || ')';
 21      dbms_output.put_line(v_stmt);
 22      execute immediate v_stmt;
 23  end;
 24  /
alter table emp_load location('info1.dat','info2.dat')

PL/SQL procedure successfully completed.

SQL> select * from emp_load;

EMPLO EMPLOYEE_DOB         EMPLOYEE_LAST_NAME   EMPLOYEE_FIRST_ EMPLOYEE_MIDDLE
----- -------------------- -------------------- --------------- ---------------
EMPLOYEE_
---------
56    november, 15, 1980   baker                mary            alice     0
01-SEP-04

87    december, 20, 1970   roper                lisa            marie     0
01-JAN-99


SQL> 

No comments:

Post a Comment