external_oracle_loader_tables

ext_tbl_sqlldr.sql

CREATE DIRECTORY HYDRA_TMP_DIR AS '/tmp/hydra_files'
/
CREATE TABLE EX_ACCOUNTING_DATA(
  N_FIRM_ID      NUMBER,
  N_GOOD_ID      NUMBER,
  N_OBJECT_ID    NUMBER,
  N_USER_ID      NUMBER,
  VC_EXT_ID      VARCHAR2(1000),
  D_BEGIN        DATE,
  D_END          DATE,
  N_QUANT_OPER   NUMBER,
  N_UNIT_ID      NUMBER,
  N_CDR_ID       NUMBER,
  VC_EXT_SERVICE VARCHAR2(1000))
ORGANIZATION EXTERNAL(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY HYDRA_TMP_DIR
ACCESS PARAMETERS (
  records delimited BY newline
  badfile HYDRA_TMP_DIR:'ex_accounting_data.bad'
  fields terminated BY ';'
  optionally enclosed BY '"'
  lrtrim
  missing field VALUES are NULL(
    N_FIRM_ID, N_GOOD_ID, N_OBJECT_ID, N_USER_ID, VC_EXT_ID,
    D_BEGIN DATE 'DD.MM.YYYY HH24:MI:SS',
    D_END   DATE 'DD.MM.YYYY HH24:MI:SS',
    N_QUANT_OPER, N_UNIT_ID, N_CDR_ID, VC_EXT_SERVICE))
LOCATION ('ex_accounting_data.csv'))
REJECT LIMIT UNLIMITED
/
CREATE TABLE EX_BAD_ACCOUNTING(
  VC_FIRM_ID      VARCHAR2(4000),
  VC_GOOD_ID      VARCHAR2(4000),
  VC_OBJECT_ID    VARCHAR2(4000),
  VC_USER_ID      VARCHAR2(4000),
  VC_EXT_ID       VARCHAR2(4000),
  VC_BEGIN        VARCHAR2(4000),
  VC_END          VARCHAR2(4000),
  VC_QUANT_OPER   VARCHAR2(4000),
  VC_UNIT_ID      VARCHAR2(4000),
  VC_CDR_ID       VARCHAR2(4000),
  VC_EXT_SERVICE  VARCHAR2(4000))
ORGANIZATION EXTERNAL(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY HYDRA_TMP_DIR
ACCESS PARAMETERS (
  records delimited BY newline
  fields terminated BY ';'
  optionally enclosed BY '"'
  lrtrim
  missing field VALUES are NULL)
LOCATION ('ex_accounting_data.bad'))
/