Skip to content

Instantly share code, notes, and snippets.

@kzhangkzhang

kzhangkzhang/APEX_CheatSheet.md

Last active October 2, 2023 19:01
Show Gist options
  • Save kzhangkzhang/e34a10e58f96de73afdca1d9a98b9090 to your computer and use it in GitHub Desktop.
Save kzhangkzhang/e34a10e58f96de73afdca1d9a98b9090 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
APEX_CheatSheet.md

Oracle APEX Cheat Sheet

Support\APEX\APEX_CheatSheet.md

ORDS

How to check APEX version?

http://myapexserver.mycompany.com:8087/i/apex_version.txt -- result Application
Express Version: 19.2

HTTP Request - within company network

select apex_web_service.make_rest_request('http://ke-web.knowles.com', 'GET') as "keweb" from dual;

PLSQL

APEX Views

SELECT * FROM APEX_DICTIONARY;
SELECT * FROM APEX_APPLICATIONS;
SELECT * FROM APEX_APPLICATION_ITEMS;

Stop Apex Engine

BEGIN
  apex_application.stop_apex_engine();
EXCEPTION
  WHEN apex_application.e_stop_apex_engine THEN
    raise;
END;

Session/URL/Debug

Attach Session (APEX 18)

-- Attach to an existing session to simulate an APEX context
BEGIN
    apex_session.attach(
                   p_app_id     => 118,
                   p_page_id    => 1,
                   p_session_id => 15069765951328);
END;
/

SELECT COUNT(*) FROM apex_application_files;

Create Session

BEGIN
  apex_session.create_session(
                      p_app_id=>498,
                      p_page_id=>200,
                      p_username=>'KZHANG');
END;

Detach Session

BEGIN
  apex_session.detach;
END;

Turn on Debug

BEGIN

c_log_level_error        CONSTANT t_log_level := 1; -- critical error
c_log_level_warn         CONSTANT t_log_level := 2; -- less critical error
c_log_level_info         CONSTANT t_log_level := 4; -- default level if debugging is enabled (e.g. used by apex_application.debug)
c_log_level_app_enter    CONSTANT t_log_level := 5; -- application: messages when procedures/functions are entered
c_log_level_app_trace    CONSTANT t_log_level := 6; -- application: other messages within procedures/functions
c_log_level_engine_enter CONSTANT t_log_level := 8; -- apex engine: messages when procedures/functions are entered
c_log_level_engine_trace CONSTANT t_log_level := 9; -- apex engine: other messages within procedures/functions

  -- https://dgielis.blogspot.com/2019/06/see-apex-debug-info-in-plsql-and-sql.html
  apex_debug.enable(
              p_level => apex_debug.c_log_level_info
  );

  apex_debug.message(p_message => 'Debug enabled.');

  l_procedure := 'my_test_proc';
  apex_debug.message("Debug enabled on %s', l_procedure);

  select * from apex_debug_messages
  where application_id = :app_id
  order by message_timestamp desc;

END;

View Debug

SELECT
  *
FROM
  apex_debug_messages
WHERE
  session_id = 16458652970080
ORDER BY message_timestamp;

Success/Error Message

Instead of using declarative way (Success Message and Error Message in process definition), you can use below PLSQL API

  • for Success : apex_application.g_print_success variable
  • for Error : apex_error.add_error procedure

Redirect URL

apex_util.redirect_url(p_url => apex_page.get_url(p_page => 1));

-- Warning: redirect_url raises the exception ORA-20876: Stop APEX Engine, so it’s probably preferable to avoid combining this with other PL/SQL code that might need to be committed first.
BEGIN
  -- you need manually call apex_application.stop_apex_engine()
  owa_util.redirect_url('https://apex.oracle.com/pls/apex/f?p=42:100', true);
  apex_application.stop_apex_engine();
END;

Create URL

apex_page.get_url(
        p_page => 4,
        p_items => 'P4_UNIQUE_ID,P4_REVISION',
        p_values => unique_id||','||revision
    ) car_number_link

apex_page.get_url(
        p_page          => 2,
        p_clear_cache   => '2,RIR,RP',
        p_items         => 'IR_OPEN_FLAG,IR_SOURCE_TYPE,IR_ANALYSIS_LOCATION',
        P_values        => 'Y,RELIABILITY,'||:P1_ANALYSIS_LOCATION
) url

Set Http status code

BEGIN
  -- https://blog.longacre.ch/2019/06/setting-http-response-status-code-301.html
  owa_util.status_line(
                    nstatus        => 301,
                    bclose_header  => false);

Unescape URL: utl_url.unescape()

            utl_url.unescape(
                    apex_page.get_url(
                    p_page          => 2,
                    p_clear_cache   => '2,RIR,RP',
                    p_items         => 'IR_OPEN_FLAG,IR_STATUS_CODE',
                    P_values        => apex_string.format('%s,%s', 'Y', status_code)
            ))


-- result: page attention to %20
Using utl_url.unescape()        : f?p=:2:::NO:2,RIR,RP:IR_OPEN_FLAG,IR_STATUS_CODE,IR_ANALYSIS_LOCATION:Y,NOT STARTED,KEI
Without using utl_url.unescape(): f?p=:2:::NO:2,RIR,RP:IR_OPEN_FLAG,IR_STATUS_CODE,IR_ANALYSIS_LOCATION:Y,NOT%20STARTED,KEI

Get build option status

SELECT
  apex_util.get_build_option_status(122, 'KEVIN_DEV_ONLY')
FROM dual;

Misc

apex_string package

-- split()
l_location_tbl := apex_string.split(p_str => l_loc_req_loc_approval_list, p_sep => ':');

IF p_car_location MEMBER OF l_location_tbl
THEN
    l_return_value := GC_YES;
END IF;

SELECT *
FROM
    repositoryapex.ke_kcgr_gift_header_v
WHERE
    1 = 1
AND (
      1 = (CASE WHEN :USER_IS_ADMIN_FLAG = 'Y' || :USER_IS_DATA_VIEWER_FLAG THEN 1 ELSE 0 END)
      OR employee_login_name = :APP_USER
      OR created_by = :APP_USER
      OR site_location IN (SELECT * FROM TABLE(apex_string.split(:USER_COMPLIANCE_OFFICER_SITES, ':')))
    )
ORDER BY
    UPDATED DESC;

    l_user_email_list_tab   apex_t_varchar2;
	l_return_value          VARCHAR2(4000)	  := NULL;

-- join()
FUNCTION get_user_role_email_list(
    p_interal_role_name IN VARCHAR2,
    p_separator         IN VARCHAR2 DEFAULT ','
)
RETURN VARCHAR2 RESULT_CACHE
IS

    l_user_email_list_tab   apex_t_varchar2;
	l_return_value          VARCHAR2(4000)	  := NULL;

BEGIN

    SELECT
        usr.mail
    BULK COLLECT INTO
        l_user_email_list_tab
    FROM
        repositoryapex.ke_gss_ad_user              usr,
        repositoryapex.ke_gss_user_role_mapping    urm,
        repositoryapex.ke_gss_role                 rl
    WHERE
        1 = 1
    AND rl.internal_role_name = p_interal_role_name
    AND rl.id                 = urm.role_id
    AND urm.enabled_flag      = GC_YES
    AND urm.user_name         = usr.login_name;

    l_return_value := apex_string.join(l_user_email_list_tab, p_separator);

    RETURN l_return_value;

EXCEPTION
	WHEN OTHERS THEN
    	RETURN NULL;

END get_user_role_email_list;

String Format

  • apex_string.format()
apex_page.get_url(
p_page          => 2,
p_clear_cache   => '2,RIR,RP',
p_items         => 'IR_OPEN_FLAG,IR_STATUS_CODE',
P_values        => apex_string.format('%s,%s', 'Y', status_code)

-- result:
f?p=:2:::NO:2,RIR,RP:IR_OPEN_FLAG,IR_STATUS_CODE:Y,NEW

SELECT   apex_string.format (
           q'!begin
             !    if not valid then
             !        apex_debug.info('validation failed');
             !   end if;
             !end;!',
           p_prefix => '!')
FROM dual;

-- result
begin
    if not valid then
        apex_debug.info('validation failed');
   end if;
end;

select
  apex_string.format(
    'My name is %0 %1 and my first name is %0 and last name is %1',
    'Kevin',
    'Zhang') me
from dual;

-- result
ME
--------------------------------
My name is Kevin Zhang and my first name is Kevin and last name is Zhang

select apex_string.format('Today is the %s of the Month', to_char(SYSDATE, 'ddth')) title from dual;

-- result
TITLE
------------------------------
Today is the 26th of the Month

select apex_string.format('%s+%s=%s', 1, 2, 'three') math_example from dual;

-- result
MATH_EXAMPLE
-----------------------------
1+2=three

JavaScript/jQuery

Browser Shortcuts (Chrome)

Action Command
Open Dev Tools F12 or Ctl + Shift + I
Open Inspector Ctl + Shift + C
Open Console Ctl + Shift + J
View Source Ctl + U

Browser Shortcuts (Firefox)

Action Command
Open Dev Tools F12 or Ctl + Shift + I
Open Inspector Ctl + Shift + C
Open Console Ctl + Shift + K
View Source Right Click > View Page Source

Events in APEX

Browser Events

  • change, focus, blur, ready, load, select
  • keydown, keypress, keyup
  • click, dblclick, mouseover

Framework Events

  • apexafterrefresh
  • apexbeforepagesubmit
  • apexbeforerefresh
  • apexafterclosedialog

Component Events

  • modechange, save
  • apexcalendardateselect, apexcalendarviewchange
  • facetschange
  • treeviewselectionchange

Basics/Debug

console.log(apex.item('P3_SUBMITTED').getValue());

apex.debug.info("==> Start calculating ROI ......");

this.triggeringElement.value > apex.item('P3_BUILD_QTY').getValue()

-- disable link
<a href="javascript:void(0)" id="show_more">Click Me</a>

-- Date
var today = new Date().toJSON()
"2021-06-18T18:09:29.005Z"

-- UpperCase/LowerCase
'Kevin'.toLowerCase()
"kevin"

'Kevin'.toUpperCase()
"KEVIN"

Dynamic Action related attributes

console.log(this.triggeringElement);
console.log(this.affectedElements);
console.log(this.action);
console.log(this.browserEvent);
console.log(this.data);

console.log($(this.triggeringElement.id);
console.log($(this.triggeringElement).text());
console.log(this.triggeringElement.value);

apex.navigation namespace

apex.navigation.redirect($("td[headers='EMPNO'] a").attr("href"));

apex.navigation.redirect("f?p=102:1:&APP_SESSION.:::::");

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

apex.event namespace

# jQuery API
javascript:$.event.trigger({type: 'empEvent', empno: '#EMPNO#', ename: '#ENAME#'})

# APEX API
apex.event.trigger('#viewButton', 'click')

Trigger page refresh from modal dialog if and only if target page is page # X

var $ = apex.jQuery;

// Need to refresh whole page after alter EEE Engr action given authority is impacted
if (this.data.dialogPageId === X) {
  apex.submit();
}

interface: item

node

// node: the DOM element that best represents the value of APEX item
apex.item("P101_USERNAME").node;

isEmpty

// isEmpty(): APEX JS function
// https://docs.oracle.com/en/database/oracle/application-express/19.2/aexjs/item.html#isEmpty
if (apex.item("P2_EMPNO").isEmpty()) {
   $x_Hide("btnDel");
} else {
   $x_Show("btnDel");
}

-- below 2 are equivalent; using isEmpty() is more modern
if ($v("P3_UNIQUE_ID") === "") {console.log("I am new")}
if (apex.item("P3_UNIQUE_ID").isEmpty()) {console.log("I am new")}

-- Client-side condition for DA: trigger DA if value is either NULL or N
-- ATT, P3, Crating Cost calculation
this.triggeringElement.value === "" || 
this.triggeringElement.value === "N"

Set Value

apex
  .item("P3_MYCOMPANY_PROJECT")
  .setValue($v("P3_DEFAULT_PROJECT_ID"), $v("P3_DEFAULT_PROJECT_DESC"));

Get the return value for page item with LOV

-- using jQuery API
$('#P3_CAPEX_START_DATE option:selected').val();

-- using APEX API
apex.item('P3_CAPEX_START_DATE').getValue()

# $v is a shurthand to apex.item().getValue
$v('P3_CAPEX_START_DATE')

-- for DA, using this.triggeringElement
$v(this.triggeringElement.id)

Get the display value for page item with LOV

-- using jQuery API
$('#P3_CAPEX_START_DATE option:selected').text();

-- using APEX API
retValue = apex.item('P3_CAPEX_START_DATE').getValue();
displayValue = apex.item('P3_CAPEX_START_DATE').displayValueFor(retValue);

loop through multiple sections from check box, selected value

-- javascript is 0 based index

usedByLoc = $v2("P3_USED_BY_LOCATION");

-- using for loop
for (i=0; i <usedByLoc.length; i++) {
  console.log("Used By Location " + i + ": " + usedByLoc[i] );
}

-- using native JS forEach function: same result as above
usedByLoc.forEach(function(loc, idx) {
  console.log("Used By Location " + idx + ": " + loc );
})


-- result
Used By Location 0: KEM
Used By Location 1: KES

Get the key pressed and char code

apex
  .item("PX_TYPED")
  .setValue(
    apex.item("PX_TYPED").getValue() +
      String.fromCharCode(this.browserEvent.which)
  );

set focus

apex.item("P3_DIVISION").setFocus();

jQuery - simple traversing

// Parents: parent(), parents(), closest()
$("li.done").parent();
// Children: children(), find()
$("ul").find("li");
// Siblings: siblings(), next(), prev()
$("li.pending").next();
// Filter: eq(), filter(), first(), last()
$("li").eq(1);
$("li:first").css("font-size", "1.5em");

make form item read-only

this.affectedElements.css("opacity", .4);
this.affectedElements.attr("readonly", true);
this.affectedElements.attr("tabIndex", -1);

// undo it
this.affectedElements.css("opacity", 1);
this.affectedElements.attr("readonly", false);
this.affectedElements.attr("tabIndex", null);

jQuery - DOM manipulation

// hide&show fade in/out: hide(), show(), fadeIn(), fadeOut()
$('#P6_PRODUCT_IMAGE').closest('.t-Form-fieldContainer').show();
$('#P6_PRODUCT_IMAGE').closest('.t-Form-fieldContainer').hide();
$('#P6_PRODUCT_IMAGE').closest('.t-Form-fieldContainer').fadeIn(2000);
$('#P6_PRODUCT_IMAGE').closest('.t-Form-fieldContainer').fadeOut(3000);

// add/remove classes: addClass(), removeClass(), toggleClass(), hasClass()
$("#P3_PROJECT_TYPE").addClass("apex_disabled cool nice");
$('li.done').removeClass('done').addClass('pending big smart');
$("#P3_PROJECT_TYPE").removeClass("apex_disabled");

$("#P3_PROJECT_TYPE").hasClass("apex_disabled");

// modify attributes: attr(), removeAttr(), prop(), removeProp(), val()
$('input').attr('disabled', 'disabled');
$('input').removeAttr('disabled');
$(".xxreadonly").attr("readonly", true);
$("input[type='text']").attr('disabled', 'disabled');

// DOM insertion: html(), text(), append(), prepend()
$('ul').append('<li class="cool">Hello</li>');
$('#question').text('I changed you');
$('#question').html('<strong>I changed you</strong>');

// DOM removal: remove(), empty()
$('ul').empty()
$('ul').remove()

// Change CSS styles: css()
$('h1').css('color', 'red');
$('h1').css({opacity: 0.2})
$("h1").css({
  "color"      : "red",
  "font-size"  : "2em",
  "font-family": "'Times New Roman', Times, serif"
});

// select by even or odd
$("tr:even").css("background-color", "red");
$("tr:odd").attr("class", "shaded_row");

DOM elements vs. jQuery Objects

var elmt = document.getElementById("message-1");
var $elmt = $("#message-1");
var match;

match = elmt === $elmt; // false
match = elmt === $elmt[0]; // true
match = elmt === $elmt.get(0); // true

apex.message namespace

-- show successful message
apex.message.showPageSuccess( "Salary is raised!" );

-- show error message
// First clear the errors
apex.message.clearErrors();

// Now show new errors
apex.message.showErrors([
    {
        type:       "error",
        location:   "inline",
        pageItem:   "P2_RAISE_PERCENT",
        message:    $v('P2_ERROR'),
        unsafe:     false
    }
]);

-- fade out success message
$("#t_Alert_Success").fadeIn( 300 ).delay( 3000 ).fadeOut( 600 );

-- show success message and refresh report region
var $ = apex.jQuery;
if ( this.data.successMessage ) {
    // use new API to show the success message if any that came from the dialog
    apex.message.showPageSuccess(this.data.successMessage.text);
}
// Refresh the report region
$("#notes_rpt").trigger("apexrefresh");

jQuery dialog

apex
  .jQuery(
    '<div style="margin:4px; padding: 4px;">Please enter your name and email to receive weekly news letter</div>'
  )
  .dialog({
    modal: true,
    resizable: false,
    height: 200px,
    width: 300px,
    title: "Sign Up",
    buttons: {
        "OK": function () {
            apex.jQuery(this).dialog("close");
        },
        "Cancel": function () {
            apex.jQuery(this).dialog("close");
        }
    }
  });

Validate Email address using regular expression

// https://stackoverflow.com/questions/46370725/how-to-do-email-validation-using-regular-expression-in-typescript
var re = /^(([^<>()\[\]\\.,;:\s@"]+(\.[^<>()\[\]\\.,;:\s@"]+)*)|(".+"))
@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$/);

var email = apex.item("PX_EMAIL").getValue();

if (re.test(email)){
    console.log("this is a valid email address");
} else {
    apex
  .jQuery(
    '<div style="margin:4px; padding: 4px;">' + email + 'is not valid</div>'
  )
  .dialog({
    modal: true,
    resizable: false,
    height: 200px,
    width: 300px,
    title: "Invalid Email",
    buttons: {
        "OK": function () {
            apex.jQuery(this).dialog("close");
        }
    }
  });
}

catch mouse current coordination (x,y)

"x: " + this.browserEvent.clientX + "y: " + this.browserEvent.clientY;

detect which mouse button is clicked

// left button
this.browserEvent.button == 0;
// middle button
this.browserEvent.button == 1;
// right button
this.browserEvent.button == 2;

jQuery

Bind event handler to one more more events: on()

$('#P3_DIVISION, #P3_LEGAL_REVIEW_FLAG').on('change', hideShowLegalMgr);

<input id="input-test" type="input" name="input">
<script>
  $('#input-test').on('change', function(event) {
    console.log(event);   // Event object
    console.log(this);    // DOM element with id of 'input-test'
    $(this).hide();       // DOM element converted to jQuery object
  });
</script>

Event delegation

$('#report').on('click', '.report-button', function(){
  console.log('event delegated binding');
});
-- multiple event are separated by a space
$('#report').on('click dblclick', '.report-button', function(){
  console.log('event delegated binding');
});

trigger custom event

$(document).trigger("aftergiveraise");

CSS

hide

img.invisible {
  display: none;
}

Set width of classic report column

Example: CART, P4, Oracle CAR Info ==> CAR Number

<span style="display:block; width:120px">#CAR_NUMBER#</span>

Increase Font Awesome size

Medium

Icons and text not aligning: quick fix (u-align)

reduce opacity for read-only items

.xxreadonly {
  opacity: 0.8;
}

overwrite

.t-Form-inputContainer .roi_output {
  background-color: #e8f2fb !important;
}

center items

#P1_DIVISION {
  margin: auto;
}

.t-Region-title {
  margin: auto;
}

pseudo-element ::before, ::after

/* insert a horizontal line between Working Capital and Payback Year */
#P4_PAYBACK_YEAR_CONTAINER::before {
  content: "";
  width: 100%;
  height: 2px;
  background: #0572ce;
}

/* CSS3 syntax */
::before/* CSS2 syntax */
: before;
@SullivanC137
Copy link

wow, nice cheat sheet man

@kzhangkzhang
Copy link
Author

image

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment