--eseguire come utente DBCGO

spool createExtTable.log;


--EXTERNAL TABLE
-------------------------------------------------------------------------
DROP TABLE EXT_DBM_ANAG_MAR;

CREATE TABLE EXT_DBM_ANAG_MAR
(
  tipo_record VARCHAR(2),
  cod_mar   VARCHAR(2),
  desc_mar VARCHAR(40)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY DBCGO_DBM_PATH
     ACCESS PARAMETERS 
      (RECORDS DELIMITED BY NEWLINE
       LOAD WHEN tipo_record!='01' AND tipo_record!='03'
       BADFILE 'ext_dbm_anag_mar.bad'
       LOGFILE 'ext_dbm_anag_mar.log' 
       FIELDS ( tipo_record CHAR(2),cod_mar CHAR(2),desc_mar CHAR(40))
      )
     LOCATION (DBCGO_DBM_PATH:'DBM_ANAG_MAR.DAT')
  )
REJECT LIMIT UNLIMITED
NOLOGGING 
NOCACHE
NOPARALLEL;

-------------------------------------------------------------------------
DROP TABLE EXT_DBM_ANAG_CLU;

CREATE TABLE EXT_DBM_ANAG_CLU
(
  tipo_record VARCHAR(2),
  cod_clu     VARCHAR(2),
  desc_clu    VARCHAR(40),
  cod_mar     VARCHAR(2)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY DBCGO_DBM_PATH
     ACCESS PARAMETERS 
      ( RECORDS DELIMITED BY NEWLINE
        LOAD WHEN tipo_record!='01' AND tipo_record!='03'
        BADFILE 'ext_dbm_anag_clu.bad'
        LOGFILE 'ext_dbm_anag_clu.log'
        FIELDS (tipo_record CHAR(2) ,cod_clu CHAR(2),desc_clu CHAR(40),cod_mar CHAR(2))
      )
     LOCATION (DBCGO_DBM_PATH:'DBM_ANAG_CLU.DAT')
  )
REJECT LIMIT UNLIMITED
NOLOGGING 
NOCACHE
NOPARALLEL;

-------------------------------------------------------------------------
DROP TABLE EXT_DBM_LT_INF;

CREATE TABLE EXT_DBM_LT_INF
(
  tipo_record VARCHAR(2),
  numtel  VARCHAR(13),
  cod_mar VARCHAR(2),
  cod_mar_old VARCHAR(2),
  dt_last_agg_mar VARCHAR(6),
  cod_clu VARCHAR(2),
  cod_clu_old VARCHAR(2),
  dt_last_agg_clu VARCHAR(6),
  ot_legale VARCHAR(2),
  ot_geo VARCHAR(2),
  fam_linea VARCHAR(1),
  fam_linea_old VARCHAR(1),
  dt_last_agg_fam VARCHAR(6),
  cod_fiscale VARCHAR(16),
  cod_agent_team  VARCHAR(2)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY DBCGO_DBM_PATH
     ACCESS PARAMETERS 
      ( RECORDS DELIMITED BY NEWLINE
        LOAD WHEN tipo_record!='01' AND tipo_record!='03'
        BADFILE 'ext_dbm_lt_inf.bad'
        LOGFILE 'ext_dbm_lt_inf.log'
        FIELDS (tipo_record CHAR(2),numtel CHAR(13),cod_mar CHAR(2),cod_mar_old CHAR(2),dt_last_agg_mar char(6) date_format date mask "YYYYMM",
         cod_clu CHAR(2),cod_clu_old CHAR(2),dt_last_agg_clu char(6) date_format date mask "YYYYMM",
         ot_legale CHAR(2),ot_geo CHAR(2),fam_linea CHAR(1),fam_linea_old CHAR(1),dt_last_agg_fam char(6) date_format date mask "YYYYMM",
         cod_fiscale CHAR(16),cod_agent_team CHAR(2))
      )
     LOCATION (DBCGO_DBM_PATH:'DBM_LT_INF.DAT')
  )
REJECT LIMIT UNLIMITED
NOLOGGING 
NOCACHE
NOPARALLEL;
-------------------------------------------------------------------------


spool off;