Jeff Holoman's Blog

Posts Tagged ‘Oracle’

Adding Time to jQuery Datepicker

Posted by jholoman on May 15, 2009

A number of folks have blogged about using the jQuery datepicker in APEX. One of the redeeming qualities of the APEX datepicker widget is it’s support for a time component, which the jQuery datepicker lacks. I’m sure most of the time the ability to pick just the date is enough.  There are however times when you need finer granularity.   There are some seperate timepicker plugins, but having the date/time tied to one element is more natural in relation to the standard APEX datepicker. I’m posting to share one solution, but mostly to get other ideas on the best way to handle this occasion. I like the functionality and ease of implementation of the jQuery datepicker, but I’m not crazy about the work-around shown here to handle the time.

Check out this small demo in action here if you don’t want to read the whole post.

The jQuery docs show that you can use ‘…’ – literal text in your format string.

This allows the opportunity to append a time component to the standard format mask.  The solution presented here requires the user to manually edit the time component, so in order to accommodate this, a database function is created to validate the date. This could be done of course just using JavaScript but I think using the database to do the validation is easier.

create or replace function is_date(p_date in varchar2, p_format_mask_in in varchar2)
return boolean
  l_date date;
  l_format_mask varchar2(50);
  l_format_mask := 'fx'||p_format_mask_in;
  l_date :=  to_date(p_date, l_format_mask);
   return true;
exception when others then
  return false;

An on-demand application process, VALIDATE_DATE  is created to call the validation function, along with a corresponding javascript function to call it. I kept the is_date function a separate db function instead of rolling the functionality into the app process in case I had need for it elsewhere.

 l_return number(1);
 l_date varchar2(50);
l_date := wwv_flow.g_x01;
 if (is_date(l_date, v('PICK_DATE_FORMAT_MASK')))
  l_return := 1;
  l_return := 0;
 end if;


function validateDate (pDate, pOraFormatMask) {
 var get = new htmldb_Get(null,$v('pFlowId'), 'APPLICATION_PROCESS=VALIDATE_DATE', 0);
 var lDate = $(pDate).val();
 get.addParam('x01', lDate);
 var gReturn = get.get();
 get = null;
 if (gReturn==0)
 {   alert('Invalid Date Format Entered.\n Dates should be in '+ pOraFormatMask +' format.'); }

We’ll need two application items and corresponding computations to hold the different format masks, PICK_DATE_FORMAT_MASK and JS_DATE_FORMAT_MASK.

The format mask I want to use for Oracle  is ‘YYYY-MM-DD HH24:MI’, the jQuery datepicker mask that matches that is ‘yy-mm-dd’, but no time component.

I then create a hidden item on page zero, P0_JS_SYSDATE, with a source value of:

to_char(sysdate, ‘&PICK_DATE_FORMAT_MASK.’);

This will allow me to grab the time component easily for the datepicker.

All that’s left now is to call the the function that creates the datepicker:

function dpWithTime(pOraFormatMask, pDpFormatMask, pSysdate) {
   var timeFormat = " " + pSysdate.split(" ")[1];
        dateFormat: pDpFormatMask + timeFormat,
        showOn: "button",
        buttonImage: "/i/asfdcldr.gif",
        buttonImageOnly: true
        }).change(function(event) {validateDate(this, pOraFormatMask)});

In the html header for the page, or the page template:

<script type="text/javascript">
var oraFormatMask = "&PICK_DATE_FORMAT_MASK.";
var jsFormatMask = "&JS_DATE_FORMAT_MASK.";
var l_sysdate =  $('#P0_JS_SYSDATE').val();
dpWithTime(oraFormatMask, jsFormatMask, l_sysdate);

Again the example is here.

Let me know if you have any other ideas, like I said at the start, I’m not thrilled with this approach, but if the rest of your app is already using the jQuery datepicker, and you need one with a time component maybe this will work. Thanks to Tyler Muth for his earlier blog/sample app using jQuery datepicker and for the bit about using the fieldset selector which he blogged about here.

Posted in APEX, Oracle | Tagged: , , , | 3 Comments »

Show / Hide Report Columns with jQuery

Posted by jholoman on April 7, 2009

I have been a benefactor of others work regarding the Hide/Show columns functionality that I believe Carl first implemented here:

Vikas and  Denes also have examples. The one thing that always kind of bothered me about the example was that in order to save the user’s preference, you needed to click a button. This doesn’t seem so intuitive to me, so I decided to revamp the example a bit and save the preference based on a cookie.

You can see this in action here:

I rewrote the code with jQuery and strayed a bit from the original implementation, but I think it works ok

Posted in APEX | Tagged: , , | Leave a Comment »