Oracle Apex: Get Display Value from Select List

In this tutorial, you will learn how to get display value from the Select List in Oracle Apex.

Usually, when we get the value using PL/SQL V() or binding methods (:), then we get the return value from the Select List.

But if you want to get the display value from the Select List, then you have to use the JavaScript API method displayValueFor() with apex.item namespace.

Method displayValueFor() returns the display value of the selected element on behalf of the return value.

Meaning to get the display value from the Select List, you have to pass the return value as an argument to the displayValueFor() method.

Getting Display Value from The Select List in Oracle Apex

For example, we have a Select List item P2_PRODUCT_LIST based on the following query:

Select product_name d, id r 
   from eba_cust_products
order by product_name;

In this case, the Select List P2_PRODUCT_LIST will display the product name and will return the value product id.

First, we will see how to get the returned value from the P2_PRODUCT_LIST using the JavaScript, because this is also needed to get the display value.

Get Return Value from Select List Using JavaScript

apex.item('P2_PRODUCT_LIST').getValue();

The above JavaScript code will get the return value from the Select List P2_PRODUCT_LIST.

You can get this value into a variable or can show directly using the alert message. For example:

var retValue;
retValue = apex.item('P2_PRODUCT_LIST').getValue();
apex.message.alert(retValue);

Get Display Value from Select Using JavaScript

To get the display value of the selected element from P2_PRODUCT_LIST use the following code:

var retValue, displayValue;
retValue = apex.item('P2_PRODUCT_LIST').getValue();
displayValue = apex.item('P2_PRODUCT_LIST').displayValueFor(retValue);
apex.message.alert(displayValue);

Disable ESC Key to Prevent Close Dialog

Open your dialog page in Oracle Apex. Then right side in the property palette, scroll down to the Dialog section and add the following code to the Attribute property:

closeOnEscape:false
Now save the changes and run its parent page to test and you will see, on Escape key press the dialog will not close. But what if the user will close the dialog from the title bar (X) close button? Maybe you want the user to close the dialog via close/cancel button only, because you may have written some code on it. To disable the title bar close (X) button, follow these steps:

Hide Dialog Window Title Bar Close (X) Button

Open the parent page from where you are opening the dialog page and add the following CSS code in the Inline CSS property section:

.no-close .ui-dialog-titlebar-close {
   display: none;
}

Save the changes to the parent page.

Now open your dialog page and in the Dialog property section add the no-close class in the CSS Classes property. You will find this property below the Attribute property, you can see it in the above image.

Save the changes and to test it at runtime, refresh the parent page first, and then open the dialog, you will not see the title bar close (X) button

Open the parent page from where you are opening the dialog page and add the following CSS code in the Inline CSS property section:

.no-close .ui-dialog-titlebar-close {
   display: none;
}

Save the changes to the parent page.

Now open your dialog page and in the Dialog property section add the no-close class in the CSS Classes property. You will find this property below the Attribute property, you can see it in the above image.

Save the changes and to test it at runtime, refresh the parent page first, and then open the dialog, you will not see the title bar close (X) button

Download BLOB From Database in Oracle APEX

For this example, we are going to assume you have a table called BLOB_FILES that holds your files in a BLOB column, along with a column for the MIME type. Here is the code we would use to initiate the file download.

create or replace procedure download_blob (p_file_id  IN NUMBER) IS
  v_blob_content  blob;
  v_mime_type     varchar2(500);
  v_filename      varchar2(500);

BEGIN
  SELECT "FILE",
         MIMETYPE,
         FILENAME
  INTO   v_blob_content,
         v_mime_type,
         v_filename
  FROM   BLOB_FILES
  WHERE  FILE_ID = p_file_id;

  sys.HTP.init;
  sys.OWA_UTIL.mime_header(v_mime_type, FALSE);
  sys.HTP.p('Content-Length: ' || DBMS_LOB.getlength(v_blob_content));
  sys.HTP.p('Content-Disposition: filename="' || v_filename || '"');
  sys.OWA_UTIL.http_header_close;

  sys.WPG_DOCLOAD.download_file(v_blob_content);
  apex_application.stop_apex_engine;
EXCEPTION
  WHEN apex_application.e_stop_apex_engine THEN
    NULL;
END;

APEX Item and Process

The following APEX setup is required, whether you plan to initiate the download from a link or a button.

1) Create Application Item

  • Shared Components > Application Items
  • Click the “Create” button.
  • Enter the following details.
    • Name: FILE_ID
    • Scope: Application
    • Session State Protection: Checksum Required – User Level
  • Click the “Create Application Item” button.

2) Create Application Process

  • Shared Components > Application Processes
  • Click the “Create” button.
  • Enter the following details.
    • Name: DOWNLOAD_BLOB
    • Sequence: {accept the default}
    • Process Point: Ajax Callback: Run this application process when requested by a page process.
  • Click the “Next” button.
  • Enter the PL/SQL to perform the download.
BEGIN
  download_blob(:FILE_ID);
END;
  • Click the “Next” button.
  • Select the “User is Authenticated (not public)” condition type.
  • Click the “Create Process” button.
  • If you need to add authorisation, click on the new application process, select the authorization scheme and click the “Apply Changes” button.

3) APEX Button

The following process associates the button with the DOWNLOAD_BLOB application process, passing in a FILE_ID value of page item.

  • Highlight the button you want to use to initiate the download.
  • In the property panel, under the “Behavior” section, select the “Action” of “Redirect to URL”.
  • Click the “No Link Defined” button next to the “Target”.
  • Enter the following URL.
f?p=&APP_ID.:1:&APP_SESSION.:APPLICATION_PROCESS=DOWNLOAD_BLOB:::FILE_ID:&P11_FILE.
  • Click the “OK” button.

4) APEX Link

The following HTML defines a link that calls the DOWNLOAD_BLOB application process, passing in a FILE_ID value of page item.

<a href="f?p=&APP_ID.:1:&APP_SESSION.:APPLICATION_PROCESS=DOWNLOAD_FILE:::FILE_ID:&P11_FILE." download> Click Here </a> to download file. 

Congratulations  All Done

PL/SQL: Extract XML Data using SQL

PL/SQL: Extract XML Data using SQL

Few examples to extract xml data in a SQL query.

#1. Extract Master Child xml using xmltable
XMLTABLE

select header_row.department_id
       ,header_row.department_name  
       ,child_row.employee_id  
       ,child_row.first_name
       ,child_row.last_name  
 fromXMLTABLE(
               XMLNAMESPACES(default'http://johnytips.blogspot.com.au/ns/department')  
              ,'/department'  
          PASSINGxmltype(  
              '<department xmlns="http://johnytips.blogspot.com.au/ns/department" id="1">  
                    <department_name>Research</department_name>  
                    <employees>  
                         <employee id="1">  
                              <first_name>ANOOP</first_name>  
                              <last_name>JOHNY</last_name>  
                         </employee>  
                         <employee id="2">  
                              <first_name>ANISH</first_name>  
                              <last_name>JOHNY</last_name>  
                         </employee>  
                    </employees>  
               </department>')  
          COLUMNS   
               department_id   VARCHAR2(30) PATH'@id',  
               department_name VARCHAR2(10) PATH'department_name',  
               child_rows XMLTYPEPATH'employees'  
              ) header_row  
     ,XMLTABLE(
               XMLNAMESPACES(default'http://johnytips.blogspot.com.au/ns/department')  
              ,'/employees/employee' PASSING header_row.child_rows  
          COLUMNS  
               employee_id NUMBERPATH'@id',   
               first_name  VARCHAR2(30) PATH'first_name',  
               last_name   VARCHAR2(30) PATH'last_name'
              ) child_row;  

#2. EXTRACTVALUE

with t as  
  (selectxmltype('<employee>  
                        <employee_id>1</employee_id>  
                        <first_name>ANOOP</first_name>  
                        <last_name>JOHNY</last_name>  
                   </employee>') str   
   from dual)  
 selectextractvalue(str,'/employee/employee_id') EMPLOYEE_ID  
       ,extractvalue(str,'/employee/first_name')  FIRST_NAME  
       ,extractvalue(str,'/employee/last_name')   LAST_NAME  
 from t;  

#3.EXTRACT

with t as  
  (selectxmltype('<employee>  
                        <employee_id>1</employee_id>  
                        <first_name>ANOOP</first_name>  
                        <last_name>JOHNY</last_name>  
                   </employee>') str   
   from dual)  
 selectextract(str,'/employee/employee_id/text()') EMPLOYEE_ID  
       ,extract(str,'/employee/first_name/text()')  FIRST_NAME  
       ,extract(str,'/employee/last_name/text()')   LAST_NAME  
 from t;  

#4. To extract with the xml tag

with t as  
  (selectxmltype('<employee>  
                        <employee_id>1</employee_id>  
                        <first_name>ANOOP</first_name>  
                        <last_name>JOHNY</last_name>  
                   </employee>') str   
   from dual)  
 selectextract(str,'/employee/employee_id') EMPLOYEE_ID  
       ,extract(str,'/employee/first_name')  FIRST_NAME  
       ,extract(str,'/employee/last_name')   LAST_NAME  
 from t;  

#5. Prior to Oracle Database 10g Release 2 using xmlsequence
XMLSEQUENCE

selectextractvalue(column_value, '/employee/first_name') "FIRST_NAME"  
       ,extractvalue(column_value, '/employee/last_name')  "LAST_NAME"  
 fromtable(xmlsequence(xmltype('<employees>  
                                      <employee>  
                                           <employee_id>1</employee_id>  
                                           <first_name>ANOOP</first_name>  
                                           <last_name>JOHNY</last_name>  
                                      </employee>  
                                      <employee>   
                                           <employee_id>2</employee_id>  
                                           <first_name>ANISH</first_name>  
                                           <last_name>JOHNY</last_name>  
                                      </employee>  
                                 </employees>').extract('/employees/employee')));  

Feel free to point out if anything is missing/wrong in this blog

Dynamic action based Expand all and collapsed all in oracle apex

Dynamic action based Expand all and collapsed all in oracle apex

  • Create two buttons like Expand all and Collapse All
  • Create the Cargo Status ad Shipment details region and select the collapsible template and put the static id for each region like below
  • Click the Expand button and create the dynamic action for Execute JavaScript code like below
  • Place the code like ( This is for Expand all dynamic action)
    $('#CARGO_STATUS.a-Collapsible.is-collapsed').removeClass('is-collapsed').addClass('is-expanded');
    $('#SHIPMENT_DETAILS.a-Collapsible.is-collapsed').removeClass('is-collapsed').addClass('is-expanded');
    $('#CARGO_STATUS.a-Collapsible .a-Collapsible-content').show();
    $('#SHIPMENT_DETAILS.a-Collapsible .a-Collapsible-content').show();
  • Place the code like (This is for Collapse all dynamic action
    $('#CARGO_STATUS.a-Collapsible.is-expanded').removeClass('is-expanded').addClass('is-collapsed');
    $('#SHIPMENT_DETAILS.a-Collapsible.is-expanded').removeClass('is-expanded').addClass('is-collapsed');
    $('#CARGO_STATUS.a-Collapsible .a-Collapsible-content').show();
    $('#SHIPMENT_DETAILS.a-Collapsible .a-Collapsible-content').show();
  • The same way you can create the N number of region and handle the unique static id and handle the JavaScript code

Interactive Grids button’s customization

One of the features I always hated in the previous versions of APEX (before 5) were tabular forms. I never got then, they were always behaving funky, it was hard to debug and I preferred to write dynamic tabular forms by myself then with the built in functionality. It changed with the release of Interactive Grids with the Oracle APEX version 5.

When I’ve started working with them it was in the English speaking company and I didn’t face the problem I had later in Czech company, where I wanted to customize or translate buttons. It was not difficult to understand words like “edit” or “save” for users, but I wanted to have consistency in these buttons across the whole application.

The first button I wanted to change was “add new row”. Luckily to change this label it’s pretty straightforward as it has its own setting in the Interactive Grid Attributes – Toolbar menu, as shown on the picture below.

Sadly this is the extent of the customization you have about the buttons. If you want to add icon, change the button to “hot” or change the names of the other buttons, you simply have to dive into the Interactive Grid JavaScript model. I’ll maybe dig more into details later, but for now, I’ll just show you the pieces, which are related to the topic today. There are few settings you can adjust for each button.

button.iconOnly = true / false;
button.hot = true / false;
button.label = "label"
button.title = "tooltip"
button.iconBeforeLabel = true / false;

I believe all the settings are self explanatory. The only thing which remains, is how to use this in the real example. If you have a look into attributes of Interactive Grid region, you can see, it has field for something called JavaScript Initialization Code (in advanced section of attributes). There you can adjust the configuration of the Interactive Grid region. First we copy the default toolbar, get all the buttons we want to edit and setup their properties, like icons, labels and icon position as shown below. Lastly you save the configuration of your toolbar into the configuration of your Interactive Grid and return the config to be processed.

function(config) {
    let $             = apex.jQuery,
        toolbarData   = $.apex.interactiveGrid.copyDefaultToolbar(),
        addrowAction  = toolbarData.toolbarFind("selection-add-row"),
        saveAction    = toolbarData.toolbarFind("save"),
        editAction    = toolbarData.toolbarFind("edit");
     
    addrowAction.icon = "icon-ig-add-row";
    addrowAction.iconBeforeLabel = true;
    addrowAction.hot = true;
    saveAction.label = "Uložit změny";
    saveAction.iconBeforeLabel = true;
    saveAction.icon ="icon-ig-save-as";
    saveAction.hot = true;
    editAction.label = "Editovat";

    config.toolbarData = toolbarData;
    return config;
}

After applying the settings and refreshing the page, you can see the results of your labor.

thanks to https://www.stinolez.com/2018/10/19/interactive-grids-buttons-customization/

ORACLE APEX 5.1 Fade Success Message automatically

We have all wait a long time of APEX 5.1.
One oft he most important feature of 5.1 is Interactive grid.

Were i was playing with 5.1 i see that the success message dont fade out like in 5.0.
For 5.0 i found a solution and i try the same one for 5.1, but it doesnt work.

So i inspect the HTML code and found the change with the APEX_SUCCESS_MESSAGE.

I search the div container to see the success message but i can find it.
Were he is?

After i start an save process i saw what i want the little green box in the upper right corner

I inspect the HTML again and saw that there is the div with the id t_Alert_Success.
Okay the div is didnt render if i come on the page the first time.

I need a function to check permanently the changes of APEX_SUCCESS_MESSAGE.
Because, APEX add classes to APEX_SUCCESS_MESSAGE.
So i found MutationObserver and it was what i need.
I Implement it on a global page to have the outofade on all pages.

1. Create a Dynamic Action on Global page
– Event: Page Load

2. True
-Action: Execute JavaScript Code


// The node to be monitored
var target = $( "#APEX_SUCCESS_MESSAGE" )[0];

// Configuration of the observer:
var config = { 
  attributes: true, 
  childList: true, 
  characterData: true 
};

// Create an observer instance
var observer = new MutationObserver(function( mutations ) {
  mutations.forEach(function( mutation ) {
    var newNodes = mutation.addedNodes; // DOM NodeList
    if( newNodes !== null ) { // If there are new nodes added
      var $nodes = $( newNodes ); // jQuery set
      $nodes.each(function() {
        var $node = $( this );
        if(  $('#APEX_SUCCESS_MESSAGE').hasClass( "u-visible" ) ) {
          // do something
            $('#t_Alert_Success').ready(function() { 
                setTimeout(function() { 
                   $('#t_Alert_Success .t-Button--closeAlert').click();
                     }, 3000); // here u can change the view time
            });
        }
      });
    }
  });    
});
 
// Pass in the target node, as well as the observer options
observer.observe(target, config);

Make the cursor a hand when a user hovers over a list item

Use for li:

li:hover {
    cursor: pointer;
}

See more cursor properties with examples after running snippet option:

.auto          { cursor: auto; }
.default       { cursor: default; }
.none          { cursor: none; }
.context-menu  { cursor: context-menu; }
.help          { cursor: help; }
.pointer       { cursor: pointer; }
.progress      { cursor: progress; }
.wait          { cursor: wait; }
.cell          { cursor: cell; }
.crosshair     { cursor: crosshair; }
.text          { cursor: text; }
.vertical-text { cursor: vertical-text; }
.alias         { cursor: alias; }
.copy          { cursor: copy; }
.move          { cursor: move; }
.no-drop       { cursor: no-drop; }
.not-allowed   { cursor: not-allowed; }
.all-scroll    { cursor: all-scroll; }
.col-resize    { cursor: col-resize; }
.row-resize    { cursor: row-resize; }
.n-resize      { cursor: n-resize; }
.e-resize      { cursor: e-resize; }
.s-resize      { cursor: s-resize; }
.w-resize      { cursor: w-resize; }
.ns-resize     { cursor: ns-resize; }
.ew-resize     { cursor: ew-resize; }
.ne-resize     { cursor: ne-resize; }
.nw-resize     { cursor: nw-resize; }
.se-resize     { cursor: se-resize; }
.sw-resize     { cursor: sw-resize; }
.nesw-resize   { cursor: nesw-resize; }
.nwse-resize   { cursor: nwse-resize; }

.cursors > div {
    float: left;
    box-sizing: border-box;
    background: #f2f2f2;
    border:1px solid #ccc;
    width: 20%;
    padding: 10px 2px;
    text-align: center;
    white-space: nowrap;
    &:nth-child(even) {
       background: #eee;
    }
    &:hover {
       opacity: 0.25
    }
}