External Table

http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19211-01/et_concepts.html#671

create directory default_directory as '/u04/oradata/PROD/directory';
create table w001_cust_load(
    ntt_id            number
,    cust_name        varchar2(100)
,    cust_kana        varchar2(100)
)
organization external
(
    type oracle_loader
    default directory default_directory
    access parameters
    (    records delimited by newline
        fields terminated by ',' optionally enclosed by '"'
        lrtrim
        (
            ntt_id,
            cust_name,
            cust_kana
        )
    )
    location('proof.txt')
)
DROP TABLE SAMPLE;
CREATE TABLE SAMPLE (
    KAZU    NUMBER,
    MOJI    VARCHAR2(5),
    HIZUKE  DATE)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY external_data
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
        (
            KAZU,
            MOJI,
            HIZUKE CHAR DATE_FORMAT DATE MASK 'yyyy/mm/dd'
        )
    )
    LOCATION (
        'sample.csv'
    )
)
create table employee
organization external
(
type oracle_datapump
default directory default_directory
location('employee.dmp')
)
as select * from user

[[/code]]
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License