20240127 - Oralce Logger 설치

권한 부여


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
시간: 댓글 없음:

20240123 - Flashback table example

create or replace PROCEDURE PROC_FLASHBACK_TEMP

AS

BEGIN

  delete from TST_PO_MST_TEMP;


  FOR rec IN (SELECT

                TO_CHAR(TRUNC(TO_DATE('2024-01-11 02:30:00', 'YYYY-MM-DD HH24:MI:SS') - (LEVEL - 1) / (24 * 6), 'MI'), 'YYYY-MM-DD HH24:MI:SS') AS interval_start

              FROM dual

              CONNECT BY LEVEL <= ((TO_DATE('2024-01-11 02:30:00', 'YYYY-MM-DD HH24:MI:SS')) - (TO_DATE('2024-01-06 02:30:00', 'YYYY-MM-DD HH24:MI:SS'))) * 24 * 6)

  LOOP

    INSERT INTO TST_PO_MST_TEMP

    SELECT rec.interval_start, po_mst.*

    FROM po_mst AS OF TIMESTAMP TO_TIMESTAMP(rec.interval_start, 'YYYY-MM-DD HH24:MI:SS')

    WHERE PO_NUM = '312937194_218905789';

  END LOOP;

END PROC_FLASHBACK_TEMP;

/

시간: 댓글 없음:
피드 구독하기: 글 (Atom)

20250202 - IG 다운로드 버튼 바로 보이기

JS initialization Code : function (config) {     var $ = apex.jQuery,         toolbarData = $.apex.interactiveGrid.copyDefaultToolbar(),  ...