External Table in Oracle EBS

 

An external table is a table whose data come from flat files stored outside of the database.

No DML can be performed on external tables but they can be used for query, join and sort operations

Example:-

  CREATE TABLE "XXUNVDL"."XXUNV_AP_INVOICE_UPLOAD" 

   ( "COL1" VARCHAR2(150 BYTE), 

"COL2" VARCHAR2(150 BYTE), 

"COL3" VARCHAR2(150 BYTE), 

"COL4" VARCHAR2(150 BYTE), 

"COL5" VARCHAR2(150 BYTE), 

"COL6" VARCHAR2(150 BYTE), 

"COL7" VARCHAR2(150 BYTE), 

"COL8" VARCHAR2(150 BYTE), 

"COL9" VARCHAR2(150 BYTE), 

"COL10" VARCHAR2(150 BYTE), 

"COL11" VARCHAR2(150 BYTE), 

"COL12" VARCHAR2(150 BYTE), 

"COL13" VARCHAR2(150 BYTE), 

"COL14" VARCHAR2(150 BYTE)

   ) 

   ORGANIZATION EXTERNAL 

    ( TYPE ORACLE_LOADER

      DEFAULT DIRECTORY "GP_CONV"

      ACCESS PARAMETERS

      ( records delimited by newline

         badfile GP_CONV:'xxunv_ap_invoice_upload_bad.txt'

         logfile GP_CONV:'xxunv_ap_invoice_upload_log.txt'

         fields terminated by ',' optionally enclosed by '"'

    (

 col1   ,

 col2   ,

 col3   ,

 col4   ,

 col5   ,

 col6   ,

 col7   ,

 col8   ,

 col9   ,

 col10  ,

 col11  ,

 col12  ,

 col13  ,

 col14  )

                         )

      LOCATION

       ( "GP_CONV":'xxunv_ap_invoice_upload.CSV'

       )

    )

   REJECT LIMIT UNLIMITED ;

  GRANT SELECT ON "XXUNVDL"."XXUNV_AP_INVOICE_UPLOAD" TO "REPORTING";

 GRANT SELECT ON "XXUNVDL"."XXUNV_AP_INVOICE_UPLOAD" TO "READONLYAPPS";

  GRANT SELECT ON "XXUNVDL"."XXUNV_AP_INVOICE_UPLOAD" TO "APPS";

  GRANT SELECT ON "XXUNVDL"."XXUNV_AP_INVOICE_UPLOAD" TO "XXUNV";