Oracle APEX Korea

ORA-29273: HTTP request failed

select * from dba_host_acls;
select * from dba_host_aces;

-- Run as SYS or DBA user
declare
    l_principal varchar2(20) := apex_application.g_flow_schema_owner;
    l_proxy varchar2(100)    := null; -- e.g. 'proxy.example.org'
    l_proxy_port number      := 80;
    l_hosts apex_t_varchar2  := apex_t_varchar2(
                                  '*.openai.com'
                                --
                                -- Commenting out domains needed for push
                                -- notification, but leave uncommented if
                                -- you plan to also use Push Notification 
                                -- features in apex
                                --
                                --'*.push.apple.com',
                                --'*.notify.windows.com',
                                --'updates.push.services.mozilla.com',
                                --'android.googleapis.com',
                                --'fcm.googleapis.com'
                                );
    procedure add_priv(p_priv varchar2, p_host varchar2, p_port number) is
    begin
        dbms_network_acl_admin.append_host_ace (
            host       => p_host, 
            lower_port => p_port,
            upper_port => p_port,
            ace        => 
                xs$ace_type(privilege_list => xs$name_list(p_priv),
                            principal_name => l_principal,
                            principal_type => xs_acl.ptype_db));
    end;
begin
    if l_proxy is not null then
        add_priv('connect',l_proxy,l_proxy_port);
    end if;
    for j in (select column_value as hostname from table(l_hosts)) loop
        add_priv('connect',j.hostname,443);
    end loop;
    commit;
end;

참고

https://apex.oracle.com/pls/apex/r/apex_pm/apex-pwa-reference/push-notifications

20240406 - Push Notification

https://apex.oracle.com/pls/apex/r/apex_pm/apex-pwa-reference/push-notifications

https://diveintoapex.com/2023/05/10/pushing-the-pwa-envelope/

20240315 - Social 로그인 구글 + 기본 로그인 혼합

구글 API 콘솔에서 OAuth 동의화면 설정

https://console.cloud.google.com/apis

구글 API 콘솔에서 사용자 인증 정보 설정

https://apex.oracle.com/pls/apex/apex_authentication.callback

APEX에서 Web Credential 생성

Authentication Scheme 생성

** 다른 Authentication Scheme과 혼합해야 하는 경우

양쪽 모두 Authentication Scheme - Login Processing - Switch in Session : Enabled

로그인 페이지에서 Redirection 버튼을 통해 APEX_AUTHENTICATION=GAuthS 로 Request 처리

참고

https://console.cloud.google.com/apis

How To Authenticate APEX Application Using Google? (Doc ID 2430891.1)

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=284767079108674&id=2430891.1&_afrWindowMode=0&_adf.ctrl-state=3xll7mvfo_53

https://developers.google.com/identity/gsi/web/guides/migration?hl=ko#redirect-mode_1

https://blog.cloudnueva.com/setting-an-apex-authentication-scheme-at-run-time

https://dgielis.blogspot.com/2018/06/facebook-google-and-custom.html

20240309 - STANDARD_HASH

해쉬 키 생성을 위해 테이블 항목 추가

alter table "TBLA" add ("HASH_KEY" VARCHAR2(4000) GENERATED ALWAYS AS (standard_hash(po_id)));

테이블 명세에 보면 자동 생성하면서 RAWTOHEX로 변형

  "HASH_KEY" VARCHAR2(4000) GENERATED ALWAYS AS (RAWTOHEX(STANDARD_HASH("PO_ID"))) VIRTUAL , 

그래서 이렇게 Query하면

select hash_key from TBLA where po_id = 26352
union all
select standard_hash(26352) from dual

Error at line 1/8: ORA-01790: expression must have same datatype as corresponding expression

결론은

select hash_key from TBLA where po_id = 26352
union all
select RAWTOHEX(standard_hash(26352)) from dual

189B82A617C42996E6AA4D5F5C291DBA18402F19
189B82A617C42996E6AA4D5F5C291DBA18402F19

성능대비 복잡도를 고려한 효율성을 따진다면 SAH-1 (다른 사이트 결과 참고)

참고

https://mikesmithers.wordpress.com/2024/01/04/using-standard_hash-to-generate-synthetic-key-values/

https://asktom.oracle.com/ords/f?p=100:11:::::P11_QUESTION_ID:9541066400346811041

20240210 - PL/SQL 배열 Table 타입으로 서브쿼리 실행

커스텀 타입 생성

create or replace type poid_t as object (poid number);
create or replace type poid_tbl as TABLE OF poid_t;
create or replace type poodgrpid_t as object (poodgrpid number);
create or replace type poodgrpid_tbl as TABLE OF poodgrpid_t;
create or replace type csid_t as object (csid number);
create or replace type csid_tbl as TABLE OF csid_t;

JSON 구조 l_values를 l_poids 배열에 입력 후 select * from table(l_poids)로 읽어들임

declare
  l_values     clob;
  l_poids      poid_tbl := poid_tbl();
  l_poodgrpids poodgrpid_tbl := poodgrpid_tbl();
  l_csids      csid_tbl := csid_tbl();
begin
  l_values := '{"rows":[{"PO_ID":"117629"},{"PO_ID":"117630"},{"PO_ID":"117631"}]}';

  select poid_t(poid) bulk collect into l_poids
    from json_table(l_values, '$.rows[*]' columns (poid number path '$.PO_ID'));

  dbms_output.put_line('l_poids:');
  for i in 1..l_poids.count loop
    dbms_output.put_line(l_poids(i).poid);
  end loop;

  select poodgrpid_t(po_odgrp_id) bulk collect into l_poodgrpids
    from po_mstx
   where po_id in (select * from table(l_poids));

  dbms_output.put_line(chr(10)||'l_poodgrpids:');
  for i in 1..l_poodgrpids.count loop
    dbms_output.put_line(l_poodgrpids(i).poodgrpid);
  end loop;

  select csid_t(cs_id) bulk collect into l_csids
    from po_csx
   where po_id in (select * from table(l_poids))
      or po_id in (select * from table(l_poodgrpids));

  dbms_output.put_line(chr(10)||'l_csids:');
  for i in 1..l_csids.count loop
    dbms_output.put_line(l_csids(i).csid);
  end loop;
end;

Result :

l_poids:
117629
117630
117631

l_poodgrpids:
16900
16900
74

l_csids:
18475

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