Oracle Database -External Table – How to populate/ outbound file creation using External table


As you know we can load data from OS flat files into the Oracle environment,Now here showing how to create an external file / populating external table

Lets take a simple example: –

Step 1: – Create a directory

SQL> create directory my_dir as ‘/usr/abc’; —

Make sure Oracle OS user hasprivilege to write on this directory.

Step 2: – Grant permission to user SCOTT

SQL> grant read,write on directory my_dir to scott;

Step 3: – Create the External Table:

SQL> CREATE TABLE scott.extract_emp_dept

ORGANIZATION EXTERNAL

(TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY my_dir

LOCATION (’emp.exp’))

reject limit unlimited

AS

select e.ename,d.dname from emp e, dept d where

e.deptno=d.deptno;

SQL> select * from scott.extract_emp_dept;

And you will find a file generated i.e. ’emp.exp’ in /usr/abc directory. Now you can take this file to the target database and create an external table and associate the file with that table.

Step 4: – to be performed on the source database

SQL> set long 2000

SQL> Select dbms_metadata.get_ddl(‘TABLE’,’EXTRACT_EMP_DEPT’) from dual;

The above command will give you the DDL command to create the table at target database.

Advertisements

Oracle Database : External Tables Overview and Usage


What are External Tables?

External tables are like regular SQL tables with some exceptions:
          – The metadata of external tables is created using the SQL "CREATE TABLE … ORGANIZATION EXTERNAL" statement. 
          – The data resides outside the database in OS files, thus the EXTERNAL organization.
          – The OS files are identified inside the database through a logical directory defining the OS physical directory where they are located. The owner of the files is Oracle. Users granted access via the Database can access the files through the external table. 
          – The data is read only. 
          – You cannot perform any DML operations, nor create indexes. 
          – The external table can be queried and joined directly, in parallel using the SQL statement SELECT.

Example
——— 
A. Create the external table to extract the data without loading them inside the database

1. Create flat files emp1.dat

2 Create a directory that defines the location of the directory where the flat files reside on the OS
3 Create the external table (metadata)
4 Select data from the external table to verify that data are visible

  • Create flat files emp1.dat

$ vi /u01/ora9i/data/emp1.dat
7369,SMITH,CLERK,7902,17-DEC-80,100,0,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,250,0,30
7521,WARD,SALESMAN,7698,22-FEB-81,450,0,30
7566,JONES,MANAGER,7839,02-APR-81,1150,0,20

Check OS level permissions and ensure the user has read/write privileges to this directory.

  • Create a logical directory to map the OS directory where the external files reside and grant permissions to SCOTT
  • SQL> create directory emp_dir as ‘/u01/ora9i/data’ ;
    Directory created.
    SQL> GRANT READ ON DIRECTORY emp_dir TO scott;
    Grant succeeded.
    SQL> GRANT WRITE ON DIRECTORY emp_dir TO scott;
    Grant succeeded.

  • Create the external table :
  • SQL> create table scott.emp_ext
    (emp_id number(4), ename varchar2(12),
    job varchar2(12) , mgr_id number(4) ,
    hiredate date, salary number(8), comm number(8),
    dept_id number(2))
    organization external
    (type oracle_loader
    default directory emp_dir
    access parameters (records delimited by newline
    fields terminated by ‘,’)
    location (’emp1.dat’);
    Table created.

  • Check the data retrieval:

SQL> select * from scott.emp_ext;

EMP_ID ENAME    JOB        MGR_ID  HIREDATE  SALARY  COMM  DEPT_ID
—— ——-  ——–   ——- ——— ——  —– ——-
7369   SMITH    CLERK      7902    17-DEC-80 100     0     20
7499   ALLEN    SALESMAN   7698    20-FEB-81 250     0     30
7521   WARD     SALESMAN   7698    22-FEB-81 450     0     30
7566   JONES    MANAGER    7839    02-APR-81 1150    0     20
7654   MARTIN   SALESMAN   7698    28-SEP-81 1250    0     30
7698   BLAKE    MANAGER    7839    01-MAY-81 1550    0     30
7934   MILLER   CLERK      7782    23-JAN-82 3500    0     10
7 rows selected.