根据需求我们需要创建部分所需的配置表,各表说明如下:
1、文件FTP主机配置表:SHELL_HOST_CFG
COLUMN_NAME | DATA_TYPE | COMMENTS |
LATN_ID | NUMBER |
|
HOST_ID | NUMBER | 主机ID |
HOST_NAME | VARCHAR2 | 主机名 |
HOST_IP | VARCHAR2 | 主机IP |
HOST_PORT | VARCHAR2 | 主机端口 |
USERNAME | VARCHAR2 | 用户名 |
PASSWD | VARCHAR2 | 密码 |
HOST_PATH | VARCHAR2 | 默认路径 |
NOTE | VARCHAR2 | 备注 |
STATE_DATE | DATE | 时间 |
create table SHELL_HOST_CFG
(
host_id NUMBER(2) not null,
host_name VARCHAR2(30),
host_ip VARCHAR2(15) not null,
host_port VARCHAR2(10),
username VARCHAR2(20),
passwd VARCHAR2(20),
host_path VARCHAR2(60),
note VARCHAR2(50),
state_date DATE,
latn_id NUMBER(4)
);
comment on column SHELL_HOST_CFG.host_id
is '主机ID';
comment on column SHELL_HOST_CFG.host_name
is '主机名';
comment on column SHELL_HOST_CFG.host_ip
is '主机IP';
comment on column SHELL_HOST_CFG.host_port
is '主机端口';
comment on column SHELL_HOST_CFG.username
is '用户名';
comment on column SHELL_HOST_CFG.passwd
is '密码';
comment on column SHELL_HOST_CFG.host_path
is '默认路径';
comment on column SHELL_HOST_CFG.note
is '备注';
comment on column SHELL_HOST_CFG.state_date
is '时间';
-- Create/Recreate primary, unique and foreign key constraints
alter table SHELL_HOST_CFG
add constraint PK_SHELL_HOST_CFG primary key (HOST_ID)
using index
tablespace TBS_CSS_INT_IDX;
2、文件入库配置表 SHELL_PROC_GATHER_CFG
COLUMN_NAME | DATA_TYPE | COMMENTS |
PROC_ID | NUMBER | 流程ID |
OWNER_NAME | VARCHAR2 | 目标库模式名 |
FILE_HEAD | VARCHAR2 | 文件头如果要添加日志如:BML.MKT_LO_D.${l_date} |
DATE_FORMAT | VARCHAR2 | 日期格式 如:yyyymmdd |
TARGET_TABLE | VARCHAR2 | 目标表 |
FIELD_LIST | VARCHAR2 | 控制文件中的字段 |
HOST_ID | NUMBER | 主机ID,配置表见:shell_host_cfg |
SRC_DIR | VARCHAR2 | 源文件目录 |
FILE_TAIL | VARCHAR2 | 文件后缀 |
CHECK_FILE | VARCHAR2 | check文件 |
LATN_ID | NUMBER | 本地网标识 |
DESC_DIR | VARCHAR2 | 目标目录 |
TERMINATE | VARCHAR2 | 分隔符 |
IS_CHECK | CHAR | 是否需要check文件 |
AFTER_FTP_SHELL | VARCHAR2 | FTP之后需要执行的shell命令 |
BEFORE_LOAD_SQL | VARCHAR2 | SQLLDR之前预操作 |
DATA_FILES | VARCHAR2 | 数据文件名称 |
IF_CHECK_DDL | CHAR | 是否检查表结构 |
TAB_DDL | VARCHAR2 | 表结构来源 |
SHORT_PROC_ID | VARCHAR2 | 流程短号 |
CHARACTERSET | VARCHAR2 | 导入使用的字符集,默认UTF8 |
SHELL_AFTER_ALL | VARCHAR2 | 导入完成后需要执行的SHELL脚本 |
create table SHELL_PROC_GATHER_CFG
(
proc_id NUMBER(20) not null,
owner_name VARCHAR2(200),
file_head VARCHAR2(200),
date_format VARCHAR2(100),
target_table VARCHAR2(100),
field_list VARCHAR2(4000),
host_id NUMBER(12),
src_dir VARCHAR2(1000),
file_tail VARCHAR2(200),
check_file VARCHAR2(100),
latn_id NUMBER(4),
desc_dir VARCHAR2(1000),
terminate VARCHAR2(100),
is_check CHAR(1),
after_ftp_shell VARCHAR2(4000),
before_load_sql VARCHAR2(4000),
data_files VARCHAR2(1000),
if_check_ddl CHAR(1),
tab_ddl VARCHAR2(4000),
short_proc_id as (SUBSTR(TO_CHAR("PROC_ID"),1,4)),
characterset VARCHAR2(100) default 'UTF8',
shell_after_all VARCHAR2(4000)
);
-- Add comments to the columns
comment on column SHELL_PROC_GATHER_CFG.proc_id
is '流程ID';
comment on column SHELL_PROC_GATHER_CFG.owner_name
is '目标库模式名';
comment on column SHELL_PROC_GATHER_CFG.file_head
is '文件头如果要添加日志如:BML.MKT_LO_D.${l_date}';
comment on column SHELL_PROC_GATHER_CFG.date_format
is '日期格式 如:yyyymmdd';
comment on column SHELL_PROC_GATHER_CFG.target_table
is '目标表';
comment on column SHELL_PROC_GATHER_CFG.field_list
is '控制文件中的字段';
comment on column SHELL_PROC_GATHER_CFG.host_id
is '主机ID,配置表见:shell_host_cfg';
comment on column SHELL_PROC_GATHER_CFG.src_dir
is '源文件目录';
comment on column SHELL_PROC_GATHER_CFG.file_tail
is '文件后缀';
comment on column SHELL_PROC_GATHER_CFG.check_file
is 'check文件';
comment on column SHELL_PROC_GATHER_CFG.latn_id
is '本地网标识';
comment on column SHELL_PROC_GATHER_CFG.desc_dir
is '目标目录';
comment on column SHELL_PROC_GATHER_CFG.terminate
is '分隔符';
comment on column SHELL_PROC_GATHER_CFG.is_check
is '是否需要check文件';
comment on column SHELL_PROC_GATHER_CFG.after_ftp_shell
is 'FTP之后需要执行的shell命令';
comment on column SHELL_PROC_GATHER_CFG.before_load_sql
is 'SQLLDR之前预操作';
comment on column SHELL_PROC_GATHER_CFG.data_files
is '数据文件名称';
comment on column SHELL_PROC_GATHER_CFG.if_check_ddl
is '是否检查表结构';
comment on column SHELL_PROC_GATHER_CFG.tab_ddl
is '表结构来源';
comment on column SHELL_PROC_GATHER_CFG.short_proc_id
is '流程短号';
comment on column SHELL_PROC_GATHER_CFG.characterset
is '导入使用的字符集,默认UTF8';
comment on column SHELL_PROC_GATHER_CFG.shell_after_all
is '导入完成后需要执行的SHELL脚本';
-- Create/Recreate primary, unique and foreign key constraints
alter table SHELL_PROC_GATHER_CFG
add constraint PK_SHELL_PROC_GATHER_CFG primary key (PROC_ID)
using index
tablespace TBS_CSS_INT_IDX;
alter table SHELL_PROC_GATHER_CFG
add constraint FK_SHELL_PROC_GATHER_CFG foreign key (PROC_ID)
references SHELL_PROC_RUN_CFG (PROC_ID) on delete cascade;
alter table SHELL_PROC_GATHER_CFG
add constraint FK_SHELL_PROC_GATHER_CFG_HOST foreign key (HOST_ID)
references SHELL_HOST_CFG (HOST_ID);
3、流程节点配置表SHELL_PROC_RUN_CFG
COLUMN_NAME | DATA_TYPE | COMMENTS |
PROC_ID | NUMBER | 流程ID |
PROC_NAME | VARCHAR2 | 流程名称 |
REPORT_ID | NUMBER | 报表ID |
REPORT_NAME | VARCHAR2 | 报表名称 |
RUN_DAY | VARCHAR2 | 运行日期,月报需要配置 |
RUN_HOUR | VARCHAR2 | 运行小时,月报,日报,周报需要配置 |
RUN_MINUTE | VARCHAR2 | 运行分钟,月报,日报,周报,小时报表需要配置 |
LAST_SUCC_CYCLE | VARCHAR2 | 上次执行成功账期 |
RUN_TYPE | VARCHAR2 | 运行类型(1:月报 2:日报 3:周报 4:小时报 5:分钟报表 6:只执行一次) |
RUN_SQL | VARCHAR2 | 运行SQL |
LATN_ID | NUMBER | 本地网标识 |
STATE | CHAR | 流程状态 |
MODIFY_DT | DATE | 新增修改时间 |
OWNER | VARCHAR2 | 所有者 |
ORDER_ID | NUMBER | 执行顺序 |
ERR_CNT | NUMBER | 同一账期内允许重复运行的报错次数 |
IF_EXECUTED | CHAR | 针对只执行一次的调度 |
ETL_TYPE | VARCHAR2 | 调度类型 SHELL:需要在RUN_SQL中填写 SHELL文件全路径以及需要传入的参数 |
IF_CONVERT | CHAR | 是否需要转换数据,如后续抽取整合到其他用户下的结果表 |
SHORT_PROC_ID | VARCHAR2 |
|
GATHER_TYPE | VARCHAR2 | EDW方式或者ODSO方式 |
APPOINT_TIME | VARCHAR2 | 约定时间 |
RUN_WEEK_DAY | VARCHAR2 |
|
create table SHELL_PROC_RUN_CFG
(
proc_id NUMBER(20) not null,
proc_name VARCHAR2(4000),
report_id NUMBER(12),
report_name VARCHAR2(200),
run_day VARCHAR2(2),
run_hour VARCHAR2(2),
run_minute VARCHAR2(2),
last_succ_cycle VARCHAR2(100),
run_type VARCHAR2(20),
run_sql VARCHAR2(4000),
latn_id NUMBER(4),
state CHAR(1) default 'A',
modify_dt DATE default SYSDATE,
owner VARCHAR2(100),
order_id NUMBER(12),
err_cnt NUMBER(12),
if_executed CHAR(1) default 'N',
etl_type VARCHAR2(100),
if_convert CHAR(1) default 'Y',
short_proc_id as (SUBSTR(TO_CHAR("PROC_ID"),1,4)),
gather_type VARCHAR2(20) default 'EDW',
appoint_time VARCHAR2(100),
run_week_day VARCHAR2(2) default 0
);
-- Add comments to the columns
comment on column SHELL_PROC_RUN_CFG.proc_id
is '流程ID';
comment on column SHELL_PROC_RUN_CFG.proc_name
is '流程名称';
comment on column SHELL_PROC_RUN_CFG.report_id
is '报表ID';
comment on column SHELL_PROC_RUN_CFG.report_name
is '报表名称';
comment on column SHELL_PROC_RUN_CFG.run_day
is '运行日期,月报需要配置';
comment on column SHELL_PROC_RUN_CFG.run_hour
is '运行小时,月报,日报,周报需要配置';
comment on column SHELL_PROC_RUN_CFG.run_minute
is '运行分钟,月报,日报,周报,小时报表需要配置';
comment on column SHELL_PROC_RUN_CFG.last_succ_cycle
is '上次执行成功账期';
comment on column SHELL_PROC_RUN_CFG.run_type
is '运行类型(1:月报 2:日报 3:周报 4:小时报 5:分钟报表 6:只执行一次)';
comment on column SHELL_PROC_RUN_CFG.run_sql
is '运行SQL';
comment on column SHELL_PROC_RUN_CFG.latn_id
is '本地网标识';
comment on column SHELL_PROC_RUN_CFG.state
is '流程状态';
comment on column SHELL_PROC_RUN_CFG.modify_dt
is '新增修改时间';
comment on column SHELL_PROC_RUN_CFG.owner
is '所有者';
comment on column SHELL_PROC_RUN_CFG.order_id
is '执行顺序';
comment on column SHELL_PROC_RUN_CFG.err_cnt
is '同一账期内允许重复运行的报错次数';
comment on column SHELL_PROC_RUN_CFG.if_executed
is '针对只执行一次的调度';
comment on column SHELL_PROC_RUN_CFG.etl_type
is '调度类型 SHELL:需要在RUN_SQL中填写 SHELL文件全路径以及需要传入的参数';
comment on column SHELL_PROC_RUN_CFG.if_convert
is '是否需要转换数据,如后续抽取整合到其他用户下的结果表';
comment on column SHELL_PROC_RUN_CFG.gather_type
is 'EDW方式或者ODSO方式';
comment on column SHELL_PROC_RUN_CFG.appoint_time
is '约定时间';
-- Create/Recreate primary, unique and foreign key constraints
alter table SHELL_PROC_RUN_CFG
add constraint PK_SHELL_PROC_RUN_CFG primary key (PROC_ID)
using index
tablespace TBS_CSS_INT_IDX;
4、入库日志表SHELL_PROC_RUN_LOG
COLUMN_NAME | DATA_TYPE | COMMENTS |
PROC_ID | NUMBER | 流程ID |
STAT_CYCLE_ID | NUMBER | 账期 |
RUN_STATE | VARCHAR2 | 运行状态 0:运行成功 1:未运行 2:运行中 3:报错终止 其他:运行报错 |
RUN_MSG | VARCHAR2 | 运行情况 |
START_TIME | DATE | 开始时间 |
END_TIME | DATE | 结束时间 |
ERR_CNT | NUMBER | 报错次数 |
LATN_ID | NUMBER | 本地网标识 |
SID | NUMBER | 会话ID |
SERIAL# | NUMBER | 系列号 |
INST_ID | NUMBER | 数据库实例ID(针对多实例) |
RUN_TYPE | VARCHAR2 | |
SPID | NUMBER | |
IS_SYNC | VARCHAR2 | 日志状态是否已经同步 |
SHORT_PROC_ID | VARCHAR2 |
create table SHELL_PROC_RUN_LOG
(
proc_id NUMBER(20),
stat_cycle_id NUMBER(20),
run_state VARCHAR2(100),
run_msg VARCHAR2(4000),
start_time DATE default SYSDATE,
end_time DATE,
err_cnt NUMBER(12),
latn_id NUMBER(4),
sid NUMBER,
serial# NUMBER,
inst_id NUMBER,
run_type VARCHAR2(20),
spid NUMBER,
is_sync VARCHAR2(100) default 'N',
short_proc_id as (SUBSTR(TO_CHAR("PROC_ID"),1,4))
);
-- Add comments to the columns
comment on column SHELL_PROC_RUN_LOG.proc_id
is '流程ID';
comment on column SHELL_PROC_RUN_LOG.stat_cycle_id
is '账期';
comment on column SHELL_PROC_RUN_LOG.run_state
is '运行状态 0:运行成功 1:未运行 2:运行中 3:报错终止 其他:运行报错';
comment on column SHELL_PROC_RUN_LOG.run_msg
is '运行情况';
comment on column SHELL_PROC_RUN_LOG.start_time
is '开始时间';
comment on column SHELL_PROC_RUN_LOG.end_time
is '结束时间';
comment on column SHELL_PROC_RUN_LOG.err_cnt
is '报错次数';
comment on column SHELL_PROC_RUN_LOG.latn_id
is '本地网标识';
comment on column SHELL_PROC_RUN_LOG.sid
is '会话ID';
comment on column SHELL_PROC_RUN_LOG.serial#
is '系列号';
comment on column SHELL_PROC_RUN_LOG.inst_id
is '数据库实例ID(针对多实例)';
create table SHELL_PROC_RUN_RELA
(
proc_id NUMBER(20),
sum_proc_id NUMBER(20),
comments VARCHAR2(200),
modify_dt DATE default SYSDATE
)
tablespace TBS_CSS_RPT_4