Oracle APEX, set value of shuttle via javascript

In Oracle APEX Javascript API, you would typically set the value of page item by 

$x('P1_ITEM').value = 5;

This would work with textfields, select list. But for more complex input items like shuttle, it would not work.

If one examines an APEX shuttle item with Firebug or Chrome Dev tools, one can see its composed of 2 items fields, suffixed with "_LEFT" and "RIGHT".

Solution is to use apex.item("<<name of item>>")>setValue(<<value>>); which takes into consideration the item type.

apex.item( "P1_ITEM" ).setValue( "10" );

You can read more about apex.item in the Oracle documentation for APEX's JavaScript API:

http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/javascript_api.htm#AEAPI29494

Oracle Apex Tabular Form: Delete unsaved rows without submitting

A common shortfall in Tabular Form functionality in APEX (amongst many) is that when you add a couple of rows and you wish to delete one without saving the form, you will end up losing the whole lot of rows entered.

This piece of code will help you avoid the problem. Put this in page header and in the DELETE button's attribute, set type as URL and javascript:delete_rows(); as target.

The way it works, if the checked row wasn't saved and it will be removed without page submission, otherwise the page will be submitted to remove it via normal mechanism.

<script language="JavaScript" type="text/javascript">
    function delete_rows()
    {
        var need_submit = 0;
        $("[name='f01']").each(function(){

        if($(this).attr("checked"))
        {
            if(this.value == 0) // new row, not saved. Checkbox value is 0
            {
                $(this).closest("tr").remove();
            }
            else    // row was saved before, need to be removed via page submission.
                need_submit = need_submit + 1;
           
        }

        });
       
        if(need_submit > 0)    // require submission?
            apex.submit('MULTI_ROW_DELETE');
           
        addTotal();   

    }

</script>

Oracle Apex - Dynamically Color Text field

It is often useful to highlight certain fields when certain (usually incorrect) values are entered as a form of on the fly validation. 

Suppose you had a fairly large form with many fields many of which only accept numbers. It would be much more user friendly if you could alert end-users to incorrect values as they enter it, rather than let them submit and have a big list of invalid entries to sift through.

1) Put the following code in page header.

<script language="JavaScript" type="text/javascript">

function setCol(pThis)

{

var vv = $v(pThis);

var cls = '#'+pThis;

if(isNaN(vv))

{

$(cls).css("background-color","red");

}

else

{

$(cls).css("background-color","white");

}

}

</script>

2) Create Dynamic Action with following configuration:

1. Type Advanced (for pre-4.2)
2. Event: change
3. Selection Type: item
4. Item(s): P1_ENAME, P1_MOB (comma seperate the list of items)
5. True Action: 
1. Action: Execute Javascript Code
2. Code: setCol(this.triggeringElement.id);
6. Affected Items:
1. Pick the items in #4 above.

What this does is basically highlight textfield when non-numeric characters are entered (isNAN). 

Here is how it should look like if implemented properly. You can take this and do more fancy stuff like validating against regex. 

http://apex.oracle.com/pls/apex/f?p=21796:1

Oracle Apex: Adding Up Tabular Form Column Values

Often with tabular form we get the requirement to tally up the total of one of the columns as user add/edit/delete rows of data.

In this post I'll show an easy & efficient way to achieve this using Javascript.

Step 1

Get your Javascript function to add-up the total ready. I have created a simple function which you can customise for your application. You will need to know the ID of the tabular form element you are trying to add.

All editable APEX tabular forms that are ID'ed like f0x_000x. You can find them out by using Firebug (chrome, firefox) or IE Developer Tool by hitting F12 as below. 

In my case I need add up the total in Salary column as I add rows or edit entries and add it to the Total Salary text field above the tabular form.

So here is the script. Find out the ID of your field and update the italics part. 

<script language="JavaScript" type="text/javascript">

 function addTotal()
 {
  var items = document.getElementsByName("f07"); // Tabular form column to add up
  
  $total = 0;
  $itemValue = 0;
  for (var i = 0; i < items.length; i++)
  {  
   // if non-numeric character was entered, it will be considered as 0, 
   // isNaN returns true if anything but number was entered
   if(isNaN(items[i].value) || items[i].value == null || items[i].value == "")
    $itemValue = 0;
   else
    $itemValue = parseFloat(items[i].value); // convert to number
   
   $total =$total+ $itemValue; // add up
  } 
  
  // $x sets the text field to be updated to the column total just calculated
  $x('P5_TOTAL_SALARY').value = $total;
 }

</script>

STEP 2. Test out the javascript.

Its a good idea to make sure your code works before putting it page attribute. So open up Firebug, go to the console tab and copy & paste the body of the function (everything in between the curly braces) and hit enter. If your Total Salary textfield gets updated as below then the function is working fine. 

STEP 3. 

Go to page attribute and in HTML Header & Body Attribute enter the code above.

STEP 4.
Next in page attribute, go to Javascript tab and  and add the newly created function addTotal() to where it says Execute when Page Loads. This will ensure that columns are totaled when page is loaded.

STEP 5. 

I will wrap that tabular form with a < DIV> tag and call this addTotal() function with onkeyup event. This will update the column total on every subsequent entries. 

With these steps, you should be able to add up the total of a tabular form column.

Here is how the it should behave if you implement the above steps correctly. 

http://apex.oracle.com/pls/apex/f?p=63895:5 

Hiding a button in Oracle Apex

There may be a situations where you want to hide a button without removing it or making it's Condition = Never.

Simply add the following: style="display:none" to Button's attribute to accomplish this.

Hashing passwords in Oracle Database using MD5

When building applications (especially with Oracle Apex) we often have the requirement for managing users with a decent authentication system set up. A security consideration here is hashing password.

You can use the following function to hash your password using MD5.

However if you recursively hash it several times, you can secure your application and prevent dictionary attacks.

Here is a very simple function I wrote for this purpose.



create or replace Function hashPass
   ( raw_pass IN varchar2 )
   RETURN varchar2
IS
    I NUMBER;
    RES VARCHAR2(500);
BEGIN
    res:=raw_pass; -- start by putting raw value into result

    FOR I IN 1..95 LOOP -- hash it 95 times to prevent dictionary attacks
        SELECT WWV_FLOW_ITEM.MD5(res) into res FROM DUAL;
    END LOOP;

    RETURN RES; -- return hash
END;​

Note: 

1. Hashing with MD5 once is never secure as there is possibility of dictionary attacks.
2. I used 95 times for my example. You may use any arbitrary number. 

Oracle Apex: Resizing an image (BLOB) in interactive report

If you are putting an image in one of your columns in an interactive/classic report, you may want to reduce them all to same size or make the oversized images appear smaller.

Simply add this CSS to your page HTML header section:

<style type="text/css">

.apexir_WORKSHEET_DATA  td[headers="IMG_COL"] img
{

  width: 100px;
  height: 100px; 
}

</style>

Replace IMG_COL with the name of the BLOB column holding the image.

Note: This may not work with Internet Explorer.

A very basic AJAX form validation in Oracle APEX

AJAX can be used efficiently with Oracle APEX to create dynamic form validation on the fly. Here is a simple and clumsy example of how it might be of use.


1. Create your Region and form items. For every form item, we would create a corresponding Display Only item as well to hold validation messages.


2. The Javascript/AJAX code to go in Edit Page >> HTML Header and Body Attributes

Here is the full code that is supposed to go above:


<script language="JavaScript" type="text/javascript">


 function chck()
 { 


var txtItem = $v('P1_NUMBER');

if (txtItem != null && !isNaN(txtItem))
{
var get = new htmldb_Get(null,null,'APPLICATION_PROCESS=VALIDATE',0);
get.add('MSG',txtItem);
gReturn = get.get();

if(gReturn)
{
$x('P1_MSG').innerHTML= gReturn;
}
else
{
$x('P1_MSG').innerHTML='nothing to show';
}
get = null;


} 
else if (isNaN(txtItem))
{
$x('P1_MSG').innerHTML='You must enter a number in text box';
}
else
{
$x('P1_MSG').innerHTML='';
}
}

</script>

3. The code makes a call to an Application Process called VALIDATE which I have created (below) in shared Components. It extracts value from the page item with $v( ) and passes it to the application Item MSG.




4. We now create the application process VALIDATE. This will simply run a query to check if the number entered exists in database and output (via htp.p) the appropriate message.

5.  As you can see above, the process makes use of an item called MSG. This is an application Item we create from Shared Components of the application.

6. Lastly we go to the form Item (P1_NUMBER) which will trigger the process with onBlur event. 

You can test it out here: http://apex.oracle.com/pls/apex/f?p=56259:1

Valid Entries: 0123456789

Adding existing tabset to your page

If you have a page without tabset (you didn't include during creation by accident) you can easily add it again.
To view a list of pages without tabs, go to Home >> Shared Components >> Tabs (under Navigation)
In the next page there is a summary telling you how many tabs are there in the set etc. At the bottom of the section (or region) it tells you pages without tabs. In my case I have:


Pages with no tabs:
21, 24, 101

101 is login page so I wouldn't bother adding tabs there. But I accidentally didn't include tabs in page 21 and 24.

To add tabs:

1) Go to page definition of the page without tab. In my case it's 21 and 24.

2) Click on Application default under Page Rendering >> Page >> Template.

3) Scroll down or click on the section "Display Attribute".

4) Under Standard Tab Set select list should be the tabs you created. Select and Apply Changes.

Your existing tabset should now appear on the page.

Lost APEX ADMIN password?

You need to locate and run the file C:\oraclexe\app\oracle\product\11.2.0\server\apex\apxchpwd.sql

If you are running your database in Linux, find the apxchpwd.sql file by

$ locate apxchpwd.sql

Then go to that directory and run SQLPlus.

Follow these instructions to recover your ADMIN password. Note this is not for reseting your workspace ADMIN account password. Its for resetting the password for ADMIN account for your entire APEX installation.

1. Open command prompt and cd to C:\oraclexe\app\oracle\product\11.2.0\server\apex
2. Run sqlplus from command prompt.
3. Run the command alter session set current_schema='apex_040000';

Run the command @apxchpwd.sql and enter your password.

You should now be able to log into http://localhost:8080/apex/apex_admin. APEX will ask you change  your password again after logging in.

Exporting Schema in Oracle Apex

You can export Schema from Oracle APEX by following these steps:

1) Head over to SQL Workshop >> Utilities >> Generate DDL

2) Click on Create Script

3) Pick the Schema

4) Check schema objects you wish to import or check all

5) Click on Genereate DDL