20231007 - Generate APEX URL in JavaScript

var vItemValue = 'New Item Value';

// Example 1 - get URL to set item value on page 1 with value from variable vItemValue

var vUrl = "f?p=" + $v( "pFlowId" ) + ":1:" + $v( "pInstance" ) + "::" + $v( "pdebug" ) + "::P1_ITEM:"+vItemValue;

// Example 2 - get URL to set item value on current page with value from variable vItemValue

var vUrl = "f?p=" + $v( "pFlowId" ) + ":" + $v( "pFlowStepId" ) + ":" + $v( "pInstance" ) + "::" + $v( "pdebug" ) + "::P1_ITEM:"+vItemValue;

// redirect to generated URL

apex.navigation.redirect(vUrl);

 

var vItemValue = 'New Item Value';

// Example 1 - just get URL to go to the page 1

vUrl = apex.util.makeApplicationUrl({pageId:1);

// Example 2 - go to page 1 and set value of item P1_ITEM

vUrl = apex.util.makeApplicationUrl({

  pageId:1,

  itemNames:['P1_ITEM'],

  itemValues:[vItemValue]

});

// Example 3 - you can also set multiple items

var vItem2Value = 'New Item 2 Value';

vUrl = apex.util.makeApplicationUrl({

  pageId:1,

  itemNames:['P1_ITEM', 'P1_ITEM_2'],

  itemValues:[vItemValue, vItem2Value]

});

// Example 4 - all options

vUrl = apex.util.makeApplicationUrl({

  appId: 100,                   // default is $v("pFlowId") - current app

  pageId:1,                     // default is $v("pFlowStepId") - current page

  session: $v( "pInstance" ),   // default is $v("pInstance") - current session

  request: 'TEST_REQUEST',      // default is $v("pRequest") - current request

  debug: 'YES',                 // default is $v("pdebug") - debug YES/NO

  itemNames:['P1_ITEM'],        // item names array (no value by default)

  itemValues:[vItemValue],      // item values array (no value by default)

  printerFriendly: 'YES'        // no value by default

});

--------------------- 다른 방법 : 서버스크립트 + 자바스크립트

서버스크립트

begin

  if :P15_SID is not null then

    :P15_REDIRECT_URL := apex_util.prepare_url('f?p=&APP_ID.:Store:&APP_SESSION.::NO:RP,7:P7_SID:'||:P15_SID);

  else

    :P15_REDIRECT_URL := apex_util.prepare_url('f?p=&APP_ID.:Home:&APP_SESSION.::NO:RP::');

  end if;

end;

자바스크립트

apex.navigation.redirect (apex.item("P15_REDIRECT_URL").getValue());

참고

http://apexbyg.blogspot.com/2019/05/generate-apex-url-in-javascript.html

20231006 - AJAX 에러 핸들링 with PL/SQL JSON 결과 리턴

PL/SQL :

  if l_sid_om != l_sid_m then

    apex_json.open_object;

    apex_json.write('error', true);

    apex_json.write('code', 'ECD-1');

    apex_json.close_object;

    return;

  end if;

  apex_json.open_object;

  apex_json.write('success', true);

  apex_json.write('message', '담기 성공!');

  apex_json.close_object;

Javascript :

apex.server.process('AJAX-ADDtoCART2', {

  x01: selectedMODIDs,

  x02: buttonQty,

  x03: initialPrice,

  pageItems: "#P13_SID,#P13_MID,#P13_MNM,#P13_UMID"

}, {

  success: function(data) {

    apex.message.showPageSuccess(data.message);

    setTimeout(function() {

      OnClickBack();

    }, 1000);

  },

  error: function(xhr, status, error) {

    var JSONCD = xhr.responseJSON.code;

    var vTitle, vText;

    if (JSONCD == "ECD-1") {

        vTitle = "매장확인필요";

        vText  = "장바구니에 저장된 메뉴의 매장과 추가로 담으려는 매장이 다릅니다. 다른 매장의 메뉴와 같이 주문할 수 없습니다.";

    }

    apex.message.alert(vText, function(){

    }, {

        title: vTitle,

        style: "warning"

    } );

  }  

});

참고

https://docs.oracle.com/en/database/oracle/apex/23.1/aexjs/apex.server.html#.process

https://docs.oracle.com/en/database/oracle/apex/23.1/aexjs/apex.message.html#.showErrors

20230927 - IG 퍼센트 그래프 Percent Graph 색상 바꾸기

Query :

select rn

     , tenantname

     , budgetname

     , amount_usd

     , actualspend_usd

     , pct

     , '<div class="a-Report-percentChart"><div role="meter" aria-valuenow="'||pct100||'" class="a-Report-percentChart-fill" aria-label="Percent Graph" style="width:'||pct100||'%;background-color:'||bkcolor||';"><span class="a-Report-percentChart-value">'||pct||'%</span></div></div>' pct_style

  from (

         select row_number() over (order by tenantname, amount_usd desc) rn

              , tenantname

              , decode(tenancyid, targetcompartmentid, '<span class="u-bold u-color-18">'||budgetname||'</span>'

                                                     , '<span>'||budgetname||'</span>') budgetname

              , round(amount_usd) amount_usd

              , nvl(round(actualspend_usd),0) actualspend_usd

              , round(nvl(actualspend_usd,0)/amount_usd*100) pct

              , case when round(nvl(actualspend_usd,0)/amount_usd*100) >= 100 then 100

                else round(nvl(actualspend_usd,0)/amount_usd*100)

                end pct100

              , case when round(nvl(actualspend_usd,0)/amount_usd*100) >= 100 then '#CB4E3E'

                     when round(nvl(actualspend_usd,0)/amount_usd*100) <   61 then '#4B835E'

                else '#897342'

                end bkcolor

           from OCI_BUDGETS()

       )

Column - Identification - Type : 

HTML Expression

Column - Settings - HTML Expression :

&PCT_STYLE.

20230927 - 리포트 컬럼에 뱃지 색상 바꾸기 Badge

Query : 

select mst.*

     , case mst.status

         when 'STOPPED'   then 'success'

         when 'INACTIVE'  then 'success'

         when 'RUNNING'   then 'danger'

         when 'ACTIVE'    then 'danger'

         when 'AVAILABLE' then 'danger'

         else 'warning'

       end as badge_status

  from VW_OCI_SHOWOCI_RESOURCES_MST mst

Column - Column Formatting - HTML Expression :

{with/}

    LABEL:=STATUS

    VALUE:=#STATUS#

    STATE:=#BADGE_STATUS#

    ICON:=None

    LABEL_DISPLAY:=N

    STYLE:=t-Badge--subtle u-bold

    SHAPE:=t-Badge--rectangle

{apply THEME$BADGE/}

20230911 - OCI Autoscale and Stop Script

https://github.com/AnykeyNL/OCI-AutoScale

https://github.com/sinanpetrustoma/autostopping

20230911 - OCI MFA Issue

https://idcs-*******.identity.oraclecloud.com/ui/v1/myconsole?root=my-info&my-info=my_profile_security

20230908 - Materialized View Example

declare

    tune_mv varchar2(20) := 'mview_task';

begin

dbms_advisor.tune_mview

(

task_name=>tune_mv,

mv_create_stmt=>'create materialized view usage.c_s_mv

enable query rewrite

as

select tenant_name

     , usage_interval_start

     , team_compartment

     , prd_region

     , prd_service

     , prd_resource

     , sum(cost_my_cost_usd) cost_my_cost_usd

  from usage.oci_cost

group by tenant_name

     , usage_interval_start

     , team_compartment

     , prd_region

     , prd_service

     , prd_resource'

);

end;

select * from user_tune_mview;

drop materialized view log on oci_cost;

create materialized view log on oci_cost 

  with rowid, sequence 

  (tenant_name

 , usage_interval_start

 , prd_service

 , prd_resource

 , prd_region

 , cost_product_sku

 , cost_my_cost_usd

 , team_compartment)

including new values;

drop materialized view mv_oci_cost;

create materialized view mv_oci_cost

refresh fast with rowid enable query rewrite 

as 

elect tenant_name

     , trunc(usage_interval_start) usage_interval_start

     , team_compartment

     , team_manager

     , prd_region

     , prd_resource

     , prd_service

     , cost_product_sku

     , sum(cost_my_cost_usd) cost_my_cost_usd

     , count(cost_my_cost_usd) cost_my_cost_cnt

     , count(*) ttl_cnt

 from oci_cost

group by tenant_name

     , trunc(usage_interval_start)

     , team_compartment

     , team_manager

     , prd_region

     , prd_resource

     , prd_service

     , cost_product_sku;

BEGIN

  DBMS_MVIEW.REFRESH('mv_oci_cost');

END;