Accessing Flat Files with Oracle SQL

ifsapp

step1.Defining anexternal table

Let’s say you want Oracle to refer to this comma-delimited flat file: (Invent_phyical.csv)
物料代碼,物料名稱,規格型號,批號,倉位名稱,數量,實盤,差額
01.01.001,PA6T,塑膠料,C230K,070818M,    昆山實倉,789.5,786       -3.5
01.01.002,PA6T,塑膠料,A335,071006X,昆山實倉,25  ,25,  0
01.01.003,PA6T,塑膠料,AE4200N,123,昆山實倉,80   ,77   ,-3
01.01.005,PA6T,塑膠料,C215,060703J,昆山實倉,600 ,600 ,0
01.01.006,PA6T,塑膠料,C215N,123,昆山實倉,42,20,-22
01.01.007,PA6T,塑膠料,C215NK  060505C,昆山實倉,204,222,18
01.01.008,PA6T,塑膠料,C230N,123,昆山實倉,20,20,0
01.01.009,       PA6T,塑膠料,CH230N   070703S,昆山實倉,516,592.5,76.5
01.01.012,PA6T,塑膠料,CH230NWX-N54567,123       昆山實倉,25,25,0
01.01.013,PA6T,塑膠料,CH230NW0444,123,昆山實倉,5,5,0
01.01.017,PA6T,塑膠料,CH230NK,070208G,昆山實倉,0,4,4
01.01.018,PA6T,塑膠料,CH230NC,060703Cc,昆山實倉,30,30,0
The file contains the followinginventory(庫存帳) information:

    CONTRACT(廠區)
    LZ_PART_NO(老料號)
    PART_NAME(物料名稱)
    dim_quality(規格型號)
    LOT_BATCH_NO(批號)
    LOCATION_NAME(倉位名稱)
    QUANTITY(實盤數量)
    QUANTITY_PHY(原帳面數量)
    CONTRACT(廠區)
    PART_NO(料號)
    LOCATION_NO(倉庫代號)
    LOCATION_NAME(倉位名稱)
    OBJECT_NO(異動代碼)
    QUANTITY(實盤數量)
    QUANTITY_PHY(原帳面數量)


So, how do we define this file to Oracle?
First, we must create an Oracle directory entry in the data dictionarythat
points to the Windows directory where the flat file resides.
In this example, we’ll name the directory inventdirand point it to c:\tmp:

step2.文法:create or replace directory tmp as 'c:\tmp ';      
        SQL> create directory inventdir  as  'c:\tmp';                       
                     Directory Created.
               note: In order to prevent a ORA-29913, someone must grant read and write on the directory to the user that uses the directory:
step3.SQL> grant read, write on directory inventdir to ifsapp;                       
  (grant select on ifsapp.CUSTOMER_EXT to PUBLIC;)
                        (grant select on ifsapp.CUSTOMER_EXT to PUBLIC;)
             (**grant read, write on directory inventdir to lzfsal;  not neccesary)
Now that we have the directory, we can define the structure of theexternal file to Oracle. You’ll see this code in Listing A.

step4.Defining an Oracle external table
Create table
   INV_ext
(
LZ_PART_NO                 VARCHAR2(25),
PART_Name                 VARCHAR2(25) ,
DIM_QUALITY               VARCHAR2(25),
LOT_BATCH_NO            VARCHAR2(20),
LOCATION_NAME            VARCHAR2(35),
  QTY_ONHAND              NUMBER,
QTY_COUNT               NUMBER
)
organization external
(
   type oracle_loader
   default directory inventdir
   access parameters
   (
      records delimited bynewline
      fields terminated by ','
      missing field values are null
   )
location (‘Invent_phyical.csv’)
)
reject limit 1000;


Note : ORA-30657: 外部組織表格不支援作業  
IF vachar2(25) not null or number notnull
In this syntax, we define the column of the external table in much the same wayas you would an internal Oracle table. The external definitions occur in theorganization external clause, as shown in Table A.
Table A
default directory inventdir                    The directory where the file resides
records delimited by newline                The new line character
fields terminated by ‘,’                   The column termination character
location (‘Invent_phyical.csv’)      The name of the external file

External definitions for thecomma-delimited file

SQL> grant select on inv_extto ifsapp;
Login with IFSAPP
SQL> select * from sys.INV_EXT

Now that we’ve defined the external table, we can run reports against theexternal table using SQL, just as if the table resided inside the database.