create table REP_LOGS

(

  log_s_no NUMBER not null,

  log_date DATE default sysdate not null,

  log_type VARCHAR2(1) default 'E' not null,

  log_node VARCHAR2(60) not null,

  log_mesg VARCHAR2(300) not null

);

-- Add comments to the table

comment on table REP_LOGS

  is '系统操作日志信息表';

-- Add comments to the columns

comment on column REP_LOGS.log_s_no

  is '日志序列号 由序列SEQ_REP_LOGS生成';

comment on column REP_LOGS.log_date

  is '日志时间';

comment on column REP_LOGS.log_type

  is '日志类型 ''E''：异常（默认）；''N''：正常；''W''：警告';

comment on column REP_LOGS.log_node

  is '写入日志的节点';

comment on column REP_LOGS.log_mesg

  is '详细信息';

create or replace procedure p_messagelogging(str_i_logtype in varchar2,

                                                 str_i_lognode in varchar2,

                                                 str_i_logmesg in varchar2)

/*************************************************************************

**    Name :      p_messagelogging

**    Purpose :   记录日志

*************************************************************************/

is

    pragma autonomous_transaction;

begin

    insert into rep_logs(log_s_no, log_type, log_node, log_mesg)

    values(seq_rep_logs.nextval, str_i_logtype, str_i_lognode, str_i_logmesg);

    commit;

end p_messagelogging;

create or replace procedure p_myproc(arg1 in number, arg2 in varchar2)

is

     str_l_errmsg rep_logs.log_mesg%type; --异常信息

    str_l_errloc varchar2(30);

begin

     str_l_errloc:='my mark 1';

     .....

     str_l_errloc:='my mark 2';

exception

     when others then

     str_l_errmsg := substrb('Tips:'||str_l_errloc || '-' || sqlerrm, 1, 300);

     p_messagelogging('error type', 'current procedure name', str_l_errmsg);

     raise;

end p_myproc;