oracle记录存储过程中的错误

柳思756

CREATE OR REPLACE PROCEDURE PRC_LOG_WRT
(v_txdate   IN VARCHAR2,
v_dealprc  IN VARCHAR2,
v_stepnum  IN NUMBER,
v_sql      IN VARCHAR2,
v_errmsg   IN VARCHAR2,
v_rcount   IN NUMBER
) AS
----------------------------------------------------------------------------------------
-- 名    称：日志记录存储过程
-- 功能说明：记录日志
-- 源表信息：
-- 目 标 表：ETL_BI_LOG
-- 创建信息：
-- 修改项1 ：
-- 修改项2 ：
----------------------------------------------------------------------------------------
BEGIN
--INSERT INTO PETL.ETL_BI_LOG VALUES(v_txdate,v_dealprc,v_stepnum,REGEXP_REPLACE(v_sql,'[ ]{1,}',' '),v_errmsg,to_char(SYSDATE, 'YYYYMMDD HH24:MI:SS'),v_rcount);
INSERT INTO ETL_BI_LOG VALUES(v_txdate,v_dealprc,v_stepnum,v_sql, v_errmsg,to_char(SYSDATE, 'YYYYMMDD HH24:MI:SS'),v_rcount);
COMMIT;
END;
-- Create table
create table ETL_BI_LOG
(
DEAL_DATE VARCHAR2(8),
PRC_NAME  VARCHAR2(50),
JOB_STEP  INTEGER,
SQLSTR    CLOB,
ERR_MSG   VARCHAR2(1024),
RUN_TIME  VARCHAR2(30),
ROW_COUNT INTEGER
)
tablespace TS_RWD_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 80K
next 10M
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table ETL_BI_LOG
is '作业运行分步记录';
-- Add comments to the columns
comment on column ETL_BI_LOG.DEAL_DATE
is '统计日期';
comment on column ETL_BI_LOG.PRC_NAME
is '过程名称';
comment on column ETL_BI_LOG.JOB_STEP
is '操作步骤';
comment on column ETL_BI_LOG.SQLSTR
is 'SQL语句';
comment on column ETL_BI_LOG.ERR_MSG
is '错误信息';
comment on column ETL_BI_LOG.RUN_TIME
is '运行时间';
comment on column ETL_BI_LOG.ROW_COUNT
is '操作记录数';
-- Create/Recreate indexes
create index I_DATE_BI_LOG on ETL_BI_LOG (DEAL_DATE, PRC_NAME)
tablespace TS_RWD_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 10M
minextents 1
maxextents unlimited
);
create index I_DATE_BI_LOG_RUN on ETL_BI_LOG (RUN_TIME)
tablespace TS_CHN_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 10M
minextents 1
maxextents unlimited
);