Print

Email Regex Validation

Posted in Validation & Regex.

This email regular expression validation proved to be quite useful, and picks up most errors:

^[a-zA-Z0-9][a-zA-Z0-9\.\-]{1,}@[a-zA-Z0-9]{1}[a-zA-Z0-9\.\-]{1,}\.{1}[a-zA-Z]{2,4}$
Print

SYS_GUID

Posted in Useful Functions.

 

SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes. On most platforms, the generated identifier consists of a host identifier, a process or thread identifier of the process or thread invoking the function, and a nonrepeating value (sequence of bytes) for that process or thread.

You can use the SYS_GUID() function to generate a Globally Unique Identifier in your insert statement:

insert into mytable (guid_col, data) values (sys_guid(), 'xxx');

The preferred datatype for storing GUIDs is RAW(16).

Alternatively, create an ID column in your table with the type NUMBER, and use the following in your trigger:

 

IF :new."ID" IS NULL THEN
SELECT to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
INTO :new.id
FROM dual;
END IF;

 

Print

Using time & date localisation throughout your app

Posted in General Application.

It is quite likely that the application you are developing will be used in more than one time zone. Even if it isn't part of the inital intention of the application, it may be wise to consider localisation to make your application more scalable in the future.

When applying this to an APEX application, we need to pay special attention to the different formats that are used for dates. 

Database Columns

To store the correct data, we need to make sure those columns/attributes that need to store a timetamp with the local timezone, have been created with the correct data type. Examples of this column would be the record "CREATED_DATE" or "LAST_UPDATED".

Make sure to create or change these columns to type: TIMESTAMP WITH LOCAL TIME ZONE.

Session Values

Since APEX runs within Oracle Database, the session timezone can be changed easily.

This can be done with the procedure:

APEX_UTIL.SET_SESSION_TIME_ZONE(
    p_time_zone IN VARCHAR2);

Create a table to store User Preferences

If you want to store the timezone preference of each of your users, you will have to create a table to store this value, or update an existing table to store their timezone preference.

If you are creating a new table, at a minimum you should have:

CREATE TABLE  "USER_TZ_PREF" 
   (    "ID" NUMBER NOT NULL ENABLE,  
    "USERID" VARCHAR2(255) NOT NULL ENABLE, 
    "TIMEZONE_PREFERENCE" VARCHAR2(255) NOT NULL ENABLE, 
   )
You will also need to create a sequence, and a trigger to generate a new ID from the sequence when a new record is created.

Create a form page to allow the user to save their preference

Oracle's example of this is shown here:

localisation form

You will need one field:

  • Name: PX_TIME_ZONE
  • Display As: Select List
  • Label: Time Zone
  • List of Values Definition
select distinct tzname d, tzname r
from V$TIMEZONE_NAMES
order by 1
  • Source Used: Only when current value in session state is null
  • Source Type: SQL Query (return single value)
  • Source Value or Expression:
select timezone_preference 
from cms_tz_pref
where userid = :APP_USER

 

You will need at minimum one button:

  • Button Name: APPLY
  • Text Label/Alt: Apply
  • Action: Submit Page

Then create a page process:

  • Name: set timezone
  • Process Point: On Submit - After Computations and Validations
  • Process:

declare
c integer := 0;
begin
for c1 in (select id, timezone_preference
from user_tz_pref
where userid = :APP_USER) loop
update user_tz_pref
set timezone_preference = nvl(:PX_TIME_ZONE,'UTC')
where id = c1.id;
c := c + 1;
end loop;
if c = 0 then
insert into user_tz_pref (userid, timezone_preference)
values (:APP_USER,:PX_TIME_ZONE);
end if;
APEX_UTIL.SET_SESSION_TIME_ZONE (
P_TIME_ZONE => :PX_TIME_ZONE);
commit;
end;

 

  • Process Success Message: Time Zone Zet
  • Condition - When Button Pressed: APPLY
You may also want to create an item to store the page that the user just navigated from, and a redirect back to this page. A cancel button is also useful on this page.

Add Link to form in footer

Add a link to this form in the footer of the page.

Create a PL/SQL display region on Page 0, and place it in the "Before Footer" Display Point.

 

sys.htp.prn('<p>&nbsp;&nbsp;&nbsp;'||'Dates and Times are displayed in the '||
'<a href="'||apex_util.prepare_url('f?p='||:APP_ID||':215:'||:APP_SESSION)||'">'||nvl(
sys.htf.escape_sc(apex_util.get_session_time_zone),'unknown')||'</a> timezone.</p>');

 

You may need to set the condition on this item to 'Current Page is NOT in Expression 1' option, and list pages that this should not be displayed on, i.e. pg 101.

Get User's preference at start of session

When the user logs in, we need to get their time zone preference from the table, or alternatively set it to a default.

In the application's shared components, create a new Application Process.

  • Process Point: On Load: Before Header
  • Name: set time zone
  • Type: PL/SQL Anonymous Block
  • Process Text: (as taken from Oracle's pre-built apps)

 

declare
c integer := 0;
begin
for c1 in (
select TIMEZONE_PREFERENCE
from user_tz_pref
where USERID = upper(:APP_USER)) loop
--
if c1.TIMEZONE_PREFERENCE is not null then
c := c + 1;
APEX_UTIL.SET_SESSION_TIME_ZONE (P_TIME_ZONE => c1.TIMEZONE_PREFERENCE );
end if;
exit;
end loop;
if c = 0 then
if apex_util.get_session_time_zone is null then
APEX_UTIL.SET_SESSION_TIME_ZONE (P_TIME_ZONE => 'US/Pacific');
end if;
end if;
end;

  • Process Error Message: Unable to set time Zone

 

Reports

Now you can display your date columns in your reports, and they will show the relevant date appropriate to your timezone.

See the article on "SInce Format Mask" for more detail.

About Me

I currently specialise in developing bespoke applications using Oracle APEX.I have worked with APEX since it was known as HTMLDB, but have also dabbled with E-Business Suite, Oracle AS Portal, ECM, Webcenter and Website Design.

I enjoy working with new clients and assisting them develop custom applications to enhance and improve internal business processes.

Find Me

mandy nerd2