권한 부여
grant connect,create view, create job, create table, create sequence,
create trigger, create procedure, create any context to existing_user;
스크립트 실행 (@logger_install.sql)
확인 및 설정
begin logger.status; end;
추가 생성 function get_param_json
(LOGGER PKG 파라미터 텍스트 CLOB >> 파라미터 JSON CLOB)
function get_param_json(p_params in logger.tab_param)
return clob
as
l_return clob;
l_no_vars constant varchar2(255) := 'No params defined';
l_index pls_integer;
begin
$if $$no_op $then
return null;
$else
apex_json.initialize_clob_output;
apex_json.open_object;
if p_params.count > 0 then
l_index := p_params.first;
while true loop
if p_params(l_index).name is not null and p_params(l_index).val is not null then
apex_json.write(p_params(l_index).name, substr(p_params(l_index).val, 1, 4000));
end if;
l_index := p_params.next(l_index);
if l_index is null then exit; end if;
end loop;
else
apex_json.write('message', 'No parameters defined');
end if;
apex_json.close_object;
l_return := apex_json.get_clob_output;
--select unistr(replace(replace(replace(extra, '\u', '\'), '\/', chr(47)), '\"', chr(34))) from LOGGER_LOGS
return l_return;
$end
end get_param_json;
호출하는 함수 조정 function set_extra_with_params
get_param_clob >> get_param_json
function set_extra_with_params(
p_extra in logger_logs.extra%type,
p_params in tab_param
)
return logger_logs.extra%type
as
l_extra logger_logs.extra%type;
begin
$if $$no_op $then
return null;
$else
if p_params.count = 0 then
return p_extra;
else
--l_extra := p_extra || gc_line_feed || gc_line_feed || '*** Parameters ***' || gc_line_feed || gc_line_feed || get_param_clob(p_params => p_params);
--20240127
l_extra := get_param_json(p_params => p_params);
end if;
return l_extra;
$end
end set_extra_with_params;
PL/SQL 처리부분 로깅 코드
declare
... ...
--20240127 : logger
l_scope logger_logs.scope%type := lower($$plsql_unit) || '.' || 'PROC_BTNX_PC';
l_params logger.tab_param;
----------
begin
--20240127 : logger
logger.append_param(l_params, 'g_dt', F_SYSDATE_KRT_CHAR());
logger.append_param(l_params, 'g_action', '상품변경');
logger.append_param(l_params, 'g_userid', v('G_USERID'));
logger.append_param(l_params, 'g_usernm', v('G_USERNM'));
logger.log_permanent('START', l_scope, null, l_params);
----------
... ...
--20240127 : logger
logger.append_param(l_params, 'g_poid_pre_1', pomst_rec.po_id);
logger.append_param(l_params, 'g_poid_pst_1', poid_out);
logger.append_param(l_params, 'g_prodid_pre_1', pomst_rec.prod_id);
logger.append_param(l_params, 'g_prodid_pst_1', prodid);
logger.append_param(l_params, 'g_finalqty_pre_1', pomst_rec.final_qty);
logger.append_param(l_params, 'g_finalqty_pst_1', to_number(:P305_C004));
----------
... ...
--20240127 : logger
logger.append_param(l_params, 'g_poodgrpid', g_poodgrpid);
----------
... ...
--20240127 : logger
logger.log_permanent('END', l_scope, null, l_params);
----------
end;
F_SYSDATE_KRT_CHAR 생성
create or replace FUNCTION "F_SYSDATE_KRT_CHAR" RETURN varchar2
IS
BEGIN
RETURN to_char(from_tz(cast(SYSDATE as timestamp ), to_char(systimestamp, 'TZR')) at time zone 'Asia/Seoul', 'yyyy-mm-dd hh24:mi:ss');
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/
application item
확인 SQL (PO_MSTX)
select g_id
, g_dt
, g_action
, g_usernm
, g_poid_pre
, g_poid_pst
, g_dtlid_pre
, g_dtlid_pst
, case
when g_action in ('상품변경', '수량변경', 'AS', '재배송') then g_prodid_pre_nm||' ('||g_finalqty_pre||')'
when g_action = '상품변경 그룹화' then '그룹번호'||' '||to_char(g_poodgrpid_pre)
when g_action = '배송사변경' then g_provider_pre_nm
when g_action = '취소' then 'POID: '||to_char(g_poid_pre)
when g_action in ('교환', '반품') then g_prodid_pre_nm||' ('||g_finalqty_pre||') : '||g_return_sts_nm
when g_action in ('AS상세', '재배송상세') then g_prodid_pre_nm||' ('||g_finalqty_pre||')'
when g_action in ('교환상세', '반품상세') then g_prodid_pre_nm||' ('||g_finalqty_pre||') : '||g_return_sts_nm
when g_action = '지사지정' then g_provider1_pre_nm
when g_action = '주문상세저장 CS구분' then g_cstype_pre_nm
when g_action = '주문상세저장 매출처' then g_pomall_pre_nm
when g_action = '주문상세저장 주문일' then g_podate_pre
end as state_pre
, case
when g_action in ('상품변경', '수량변경', 'AS', '재배송') then g_prodid_pst_nm||' ('||g_finalqty_pst||')'
when g_action = '상품변경 그룹화' then '그룹번호'||' '||to_char(g_poodgrpid_pst)
when g_action = '배송사변경' then g_provider_pst_nm
when g_action = '취소' then null
when g_action in ('교환', '반품') then g_prodid_pst_nm||' ('||g_finalqty_pst||')'
when g_action in ('AS상세', '재배송상세') then g_prodid_pst_nm||' ('||g_finalqty_pst||')'
when g_action in ('교환상세', '반품상세') then g_prodid_pst_nm||' ('||g_finalqty_pst||')'
when g_action = '지사지정' then g_provider1_pst_nm
when g_action = '주문상세저장 CS구분' then g_cstype_pst_nm
when g_action = '주문상세저장 매출처' then g_pomall_pst_nm
when g_action = '주문상세저장 주문일' then g_podate_pst
end as state_pst
from (
select loggerlogs.id g_id
, jsont.*
, (select prod_nm from prod_mstx where prod_id = jsont.g_prodid_pre) g_prodid_pre_nm
, (select prod_nm from prod_mstx where prod_id = jsont.g_prodid_pst) g_prodid_pst_nm
, (select nm from provider_mstx where mid = jsont.g_provider_pre) g_provider_pre_nm
, (select nm from provider_mstx where mid = jsont.g_provider_pst) g_provider_pst_nm
, (select nm from provider_mstx where mid = jsont.g_provider1_pre) g_provider1_pre_nm
, (select nm from provider_mstx where mid = jsont.g_provider1_pst) g_provider1_pst_nm
, (select grp1 from cd_mst where grp_id = 1 and cd = jsont.g_return_sts) g_return_sts_nm
, (select grp1 from cd_mst where grp_id = 3 and cd = jsont.g_cstype_pre) g_cstype_pre_nm
, (select grp1 from cd_mst where grp_id = 3 and cd = jsont.g_cstype_pst) g_cstype_pst_nm
, (select grp1 from cd_mst where grp_id = 7 and cd = jsont.g_pomall_pre) g_pomall_pre_nm
, (select grp1 from cd_mst where grp_id = 7 and cd = jsont.g_pomall_pst) g_pomall_pst_nm
from
(select id, extra from logger_logs where text = 'HIST=Y') loggerlogs,
json_table(
loggerlogs.extra,
'$'
columns (
g_dt varchar2(50) path '$.g_dt'
, g_action varchar2(50) path '$.g_action'
, g_userid number path '$.g_userid'
, g_usernm varchar2(50) path '$.g_usernm'
, g_poid_pre number path '$.g_poid_pre'
, g_poid_pst number path '$.g_poid_pst'
, g_prodid_pre number path '$.g_prodid_pre'
, g_prodid_pst number path '$.g_prodid_pst'
, g_finalqty_pre number path '$.g_finalqty_pre'
, g_finalqty_pst number path '$.g_finalqty_pst'
, g_poodgrpid_pre number path '$.g_poodgrpid_pre'
, g_poodgrpid_pst number path '$.g_poodgrpid_pst'
, g_provider_pre varchar2(20) path '$.g_provider_pre'
, g_provider_pst varchar2(20) path '$.g_provider_pst'
, g_provider1_pre varchar2(20) path '$.g_provider1_pre'
, g_provider1_pst varchar2(20) path '$.g_provider1_pst'
, g_return_sts varchar2(20) path '$.g_return_sts'
, g_dtlid_pre number path '$.g_dtlid_pre'
, g_dtlid_pst number path '$.g_dtlid_pst'
, g_cstype_pre varchar2(20) path '$.g_cstype_pre'
, g_cstype_pst varchar2(20) path '$.g_cstype_pst'
, g_pomall_pre varchar2(20) path '$.g_pomall_pre'
, g_pomall_pst varchar2(20) path '$.g_pomall_pst'
, g_podate_pre varchar2(20) path '$.g_podate_pre'
, g_podate_pst varchar2(20) path '$.g_podate_pst'
)
) jsont
) mst
order by g_id desc
확인 SQL (PO_MST)
select g_id
, g_dt
, g_action
, g_usernm
, g_poid_pre
, g_poid_pst
, case
when g_action in ('상품변경', '수량변경', 'AS', '재배송') then g_prodid_pre_nm||' ('||g_finalqty_pre||')'
when g_action = '상품변경 그룹화' then '그룹번호'||' '||to_char(g_poodgrpid_pre)
when g_action = '배송사변경' then g_provider_pre_nm
when g_action = '취소' then 'POID: '||to_char(g_poid_pre)
when g_action in ('교환', '반품') then g_prodid_pre_nm||' ('||g_finalqty_pre||') : '||g_return_sts_nm
when g_action = '주문상세저장 CS구분' then g_cstype_pre_nm
when g_action = '주문상세저장 매출처' then g_pomall_pre_nm
when g_action = '주문상세저장 주문일' then g_podate_pre
end as state_pre
, case
when g_action in ('상품변경', '수량변경', 'AS', '재배송') then g_prodid_pst_nm||' ('||g_finalqty_pst||')'
when g_action = '상품변경 그룹화' then '그룹번호'||' '||to_char(g_poodgrpid_pst)
when g_action = '배송사변경' then g_provider_pst_nm
when g_action = '취소' then null
when g_action in ('교환', '반품') then g_prodid_pst_nm||' ('||g_finalqty_pst||')'
when g_action = '주문상세저장 CS구분' then g_cstype_pst_nm
when g_action = '주문상세저장 매출처' then g_pomall_pst_nm
when g_action = '주문상세저장 주문일' then g_podate_pst
end as state_pst
from (
select loggerlogs.id g_id
, jsont.*
, (select prod_nm from prod_mst where prod_id = jsont.g_prodid_pre) g_prodid_pre_nm
, (select prod_nm from prod_mst where prod_id = jsont.g_prodid_pst) g_prodid_pst_nm
, (select grp1 from cd_mst where grp_id = 2 and cd = jsont.g_provider_pre) g_provider_pre_nm
, (select grp1 from cd_mst where grp_id = 2 and cd = jsont.g_provider_pst) g_provider_pst_nm
, (select grp1 from cd_mst where grp_id = 1 and cd = jsont.g_return_sts) g_return_sts_nm
, (select grp1 from cd_mst where grp_id = 3 and cd = jsont.g_cstype_pre) g_cstype_pre_nm
, (select grp1 from cd_mst where grp_id = 3 and cd = jsont.g_cstype_pst) g_cstype_pst_nm
, (select grp1 from cd_mst where grp_id = 7 and cd = jsont.g_pomall_pre) g_pomall_pre_nm
, (select grp1 from cd_mst where grp_id = 7 and cd = jsont.g_pomall_pst) g_pomall_pst_nm
from
(select id, extra from logger_logs where text = 'HIST=Y') loggerlogs,
json_table(
loggerlogs.extra,
'$'
columns (
g_dt varchar2(50) path '$.g_dt'
, g_action varchar2(50) path '$.g_action'
, g_userid number path '$.g_userid'
, g_usernm varchar2(50) path '$.g_usernm'
, g_poid_pre number path '$.g_poid_pre'
, g_poid_pst number path '$.g_poid_pst'
, g_prodid_pre number path '$.g_prodid_pre'
, g_prodid_pst number path '$.g_prodid_pst'
, g_finalqty_pre number path '$.g_finalqty_pre'
, g_finalqty_pst number path '$.g_finalqty_pst'
, g_poodgrpid_pre number path '$.g_poodgrpid_pre'
, g_poodgrpid_pst number path '$.g_poodgrpid_pst'
, g_provider_pre varchar2(20) path '$.g_provider_pre'
, g_provider_pst varchar2(20) path '$.g_provider_pst'
, g_return_sts varchar2(20) path '$.g_return_sts'
, g_cstype_pre varchar2(20) path '$.g_cstype_pre'
, g_cstype_pst varchar2(20) path '$.g_cstype_pst'
, g_pomall_pre varchar2(20) path '$.g_pomall_pre'
, g_pomall_pst varchar2(20) path '$.g_pomall_pst'
, g_podate_pre varchar2(20) path '$.g_podate_pre'
, g_podate_pst varchar2(20) path '$.g_podate_pst'
)
) jsont
) mst
order by g_id desc
----- Good to Know
- APEX Debug Logs
apex_debug_messages - APEX Activity Logs
apex_activity_log - APEX Activity Detail Logs
apex_workspace_activity_log - APEX Failed Login Attempts
apex_workspace_access_log - APEX Automation Logs
apex_automation_log, apex_automation_msg_log - REST Data Synchronization Logs
apex_rest_source_sync_log - REST Web Service Activity Logs
apex_webservice_log - Logger (if you are using it)
logger_logs
-----
참고
https://orcl-logger.readthedocs.io/en/latest/Installation/
https://blog.cloudnueva.com/action-your-oracle-apex-logs
https://github.com/OraOpenSource/Logger
https://nuijten.blogspot.com/2015/04/speed-up-development-with-logger.html
https://livesql.oracle.com/apex/livesql/file/content_C1WQP69V5QL547I8EERSEVE81.html
댓글 없음:
댓글 쓰기