Sunday, March 3, 2013

0 External Table

External table allow we query the data in the file system and not have to import the data to the oracle storage. The external table is using SQL loader to load the data.

Create the external data directory

CREATE OR REPLACE DIRECTORY external_data AS
  '/u01/app/oracle/oradata';

image

 

Create the external table

 CREATE TABLE emp_external
  (
    EMP_ID     NUMBER,
    FIRST_NAME VARCHAR2(30 BYTE),
    HIRE_DATE DATE,
    SALARY     NUMBER
  )
  organization external
  ( default directory external_data
    access parameters
    ( records delimited by newline
      FIELDS TERMINATED BY','
      OPTIONALLY ENCLOSED BY '"' AND '"'
      ( 
        EMP_ID ,
        FIRST_NAME ,
        HIRE_DATE DATE "DD-MON-YYYY HH24:MI:SS",
        SALARY 
        )
      )
     location ('emp.csv')  
   );  
image

Now we can query the data.

image

We can not execute DML ( update/delete..) against external table and can not create index on it because the data on the file system can be changed anytime.

In order to change the data, we have to update the file itself directly .

image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates