Manual Row Lock for DML Operation in Oracle Apex

In this article we are gonna see that how to prevent or avoid data losses if two user update the same record at same time.

In Oracle Apex, if we use Automatic Row Processing for DML(insert,update,delete) operation then automatically Apex prevent the data losses from the DML operation. But when we do the same manually, Oracle Apex application won't prevent data loses from the update/delete.

But we can also prevent this data loses manually by using GET_HASH Function which is provided by Oracle Apex API. For more info(GET_HASH Function). Let see how to achieve this with one example.

Before that I would like to add what GET_HASH Function is all about:
This function computes a hash value for all given values. Use this function to implement lost update detection for data records.


Let's consider I have two pages, one is normal report page (e.g page 8).













and another one is form page (e.g page 10).









Let's consider I am retrieving and updating the data manually.

In form page, I have created one page item (i.e P10_HASH) for getting the hash value using the below given query:(This code need to write Pre-rendering Section)

select apex_util.get_hash(apex_t_varchar2 (
                  ENAME, sal, JOB ))
         into :P10_hash
         from emp
        where empno = :P10_EMPID;









In Validation section, I am retrieving the value from database and checking whether the retrieved data is latest one. If not raise a validation error otherwise allow for DML operation. Code given below.

declare
       l_hash varchar2(4000);
   begin
       select apex_util.get_hash(apex_t_varchar2 (
                  ename, sal, job ))
         into l_hash
         from emp
        where empno = :P10_EMPID;
 
       if :P10_HASH <> l_hash then
          RETURN 'Somebody already updated SAL/JOB/NAME. Please refresh the page and tryagain.';
       end if;
 
   exception when no_data_found then
      RETURN 'Employee not found';
   end;










Thank you for reading this article. I hope this article gave some idea about how to prevent the data loses manually from the DML operation. If you have any clarification or suggestion, leave it message in the comment section.

Refresh Interactive Grid (IG) when Model Dialog Closed in Oracle Apex

We are going to see in this article, how to refresh an Interactive Grid when Model dialog is closed.


For example, let us consider I have one normal page [Page number:6] and one model dialog page [ Page number:7].

Here when I close model dialog, I want to trigger a Dynamic action to refresh the Interactive Grid (IG). how to do it?

We can call the Dynamic action on "dialog closed" event to refresh the Interactive Grid (IG) when user press the button [e.g Cancel] from the Model Dialog.

If suppose user press cross (X) icon in the model dialog, then the model dialog will be closed but "Dialog Closed" event will not work. We are going to see, how this will make it work.

I hope, you were aware of  how to trigger a dynamic action on Dialog closed when user pressed cancel button.
In this article we are going to look into how to trigger a Dynamic action when user press cross icon (X) in the model dialog. Let's start

Let us consider, I have created one normal page and one model dialog already.

Step 1: create a dynamic action on "Dialog Closed" event in Interactive Grid(IG).



Step 2: In true action, create Refresh event and mapped with Grid which you want to refresh.




Step 3: In Model Dialog page property, paste the following code in attribute property in Model Dialog page :

close: function(event, ui) {apex.navigation.dialog.close(true,{dialogPageId:7});}


Note: Chained Property changed to 'NO'. otherwise, the whole page will reload instead of refresh the Interactive Grid.
Here is a video tutorial:



I hope this article gives you the enough information of how to refresh the Interactive Grid(IG) when model dialog/popup is closed.


Force Upper Case in Text Field


We are going to see how to make force Upper Case Text field in Oracle Apex.

1.Create a Text Field [Page Item]: For Example, I have created "Name" Text Field [ Page Item]


2. Go to Text Field [Page Item] Properties in Page Designer and Find Custom Attribute under Advanced Property:


3. Now Paste the Following code:

style="text-transform: uppercase;" onKeyUp="this.value=this.value.toUpperCase();"

I guess above given code will work even in normal HTML as well.



I hope, this article will help you make a particular field force upper case. Thank you


Refresh IG region using Javascript in Oracle Apex

In this article we are going to see how to refresh a Interactive grid dynamically using JavaScript. We can also have an a separate option to refresh region in dynamic action instead of JavaScript. But In some cases we may require JavaScript to refresh an region. Now let see how to do it.


  • First thing is you need to set static id to your region. For example, I have gave EMP as a static id  for an Interactive grid.
  • And then you need to create dynamic action and place the following code in true action of JavaScript action type.
    •  apex.region("regionStaticID").refresh();
  • For example, I have created an Dynamic action on Button click and executing the following code in Javascript to refresh a IG region.
    •  apex.region("EMP").refresh();

    In this article I have given basic idea of how to refresh region and hope you got enough information from this article. If you have any clarification and suggestion then leave a message in comment section.

Select/De-select all rows in IG in Oracle Apex

In this article we going to see how to select and de-select all rows in IG in Dynamic action. This work is done by execute a JavaScript code in dynamic action of button click.In the end of the article I have included the demo application for your reference.

Let see step by step approach how to do it.
Step 1: Create an IG (Interactive Grid) region and paste required SQL code. For demonstration purpose I used emp table (select * from emp).

Step 2: Assign static id for that IG region. E.g emp



Step 3: make sure that IG is editable.



Step 4: Create button (name- select_all) and create a dynamic action (on click event) for that button. Create true action and you can see many action types but we going to use JavaScript. So change it’s action type as “Execute JavaScript code”.

Step 5: paste the following code for select all row in a IG.
apex.region("emp").widget().interactiveGrid("getViews", "grid").view$.grid("selectAll");
apex.message.showPageSuccess( "All Rows selected successfully." );
Step 6: Similarly for de-select all rows in a IG, create another button and create dynamic action of JavaScript action type. Paste following code.

var ig$     = apex.region("emp").widget();
var selectedRecords = apex.region("emp").widget().interactiveGrid("getViews","grid").view$.grid("getSelectedRecords");
for (idx=0; idx < selectedRecords.length; idx++) {
    ig$.interactiveGrid("getViews", ig$.interactiveGrid("getCurrentViewId")).setSelection($());
}
if (selectedRecords.length==0){
    apex.message.clearErrors();
    apex.message.showErrors( { type:       "error",
                               location:  [ "page"],
                               message:   "Please select atleast one row to de-select.",  
                               unsafe:     false
                             }
                           );
}
else    {
    apex.message.showPageSuccess( "All Rows de-selected successfully." );
}




I hope this article will help you to select and de-select the row in IG. In case if you need any clarification or suggestion, kindly leave a message in the comments box.

Tag: Oracle apex 5, Oracle Apex IG, Process Rows in IG, Select all rows in IG , De-select all the row in IG.

Refresh IG (Interactive Grid) Column Header in Oracle APEX

In this article, we are going to see how to refresh IG (Interactive grid) header using JavaScript without page submission in Oracle APEX. In that end of the page, I have given demo application for your reference.

Now the following step will get you there to achieve your objective.

Step1:
  • create an Interactive grid in Apex and set "Static ID" for that region. for example, I have given my IG region id is 'emp'. Similarly set a "Static ID" for a column in which you want to refresh a header column. For example, I have given a static id to ename column 'ENAME'.



Step 2:

  • Create a button and set dynamic action "onclick event" for that button to achieve our objective. In that dynamic action event, create 'Execute JavaScript Code' action and paste the following code:

  •  In the above-given JavaScript Code, emp and ENAME are the Static ID for corresponding region and column.
  • After placing the JS code set 'Affected Element' to that particular region in that JavaScript Property.
That's it. Now you can run the application to which will refresh the header name correctly when you click the button. 


If you have any clarification or doubt or anything else, just leave a message in the comments box.

Create Oracle Apex account and Workspace for create application

As we know that, Oracle Application Express is a web application development tool for Oracle database. In this article, we going to see how to create Oracle Apex account and workspace for developing the web application in Oracle Apex. Let's see how to create an account and workspace step by step.

  • Visit https://apex.oracle.com and click " Get started " button. See the picture below.


  • Select " Free Workspace". See in the picture below.
  • Choose request a workspace " application development ".

  • Fill the identification detail here. see in the picture below. Note: Workspace field is unique*.


  • Create schema name i.e development_schema and select space allocation '' 25 MB".


  • Select yes to all.


  • Select the checkbox to accept term and condition.


  • Submit the request. the confirmation email will send to your account for approval.


A confirmation email will receive regarding your Oracle apex account approval. After the confirmation, you will redirect to your account to create the password. Now you can log in to your account to create the web application in Oracle Apex.

Hope above given information will helpful. If you have any queries or comment leave it below in the comment blog.