1. Overview
This blog explains about, how we can shows a text box to display the current date and time for the selected country in oracle forms.
2. Technologies and Tools Used
The following technology has been used to achieve the same.
- Oracle FORMS
- Oracle PL/SQL
3. Use Case
Oracle FORMS end users can able to see the country list and a text box to display the current date and time for the selected country
4. Architecture
Oracle FORMS
- Using FORMS BUILDER
5. Examples
Step 1: Below Objects to Create in Oracle Form
- Data Block
- Canvas
- List Item
- Text Box
- Record Group
- When-New-Form-Instance Trigger
- When-List-Changed Trigger
- Database Function
Step 2: Record Group Query
Record Group created to get the country list from V$TIMEZONE_NAMES view. The query is as follows:
Code:
SELECT DISTINCT tzname, tzname tzname1
FROM V$TIMEZONE_NAMES
ORDER BY tzname;
Step 2: When-New-Form-Instance Trigger
Trigger When-New-Form-Instance created to populate the list as shown below:
Code:
Declare
n number;
Begin
clear_list(‘demo.tzonelist’);
n := populate_group(‘tzone’);
populate_list(‘demo.tzonelist’, ‘tzone’);
End;
Step 3: When-List-Changed Trigger
Trigger When-List-Changed created to display current date and time in the text item. Trigger code is as follows:
Code:
Begin
SELECT get_current_local_time (:demo.tzonelist)
into :demo.ctime FROM DUAL;
:tlabel := ‘Current Date and Time at: ‘|| :demo.tzonelist;
End;
Step 4: Database Function
Created a database function get_current_local_time to get the current time for a country passed as a parameter.
Code:
CREATE OR REPLACE FUNCTION get_current_local_time (country_name IN VARCHAR2)
RETURN VARCHAR2
IS
CURSOR c_tz (p_country_name IN VARCHAR2)
IS
SELECT DISTINCT tzname, TZ_OFFSET (tzname) z_offset
FROM V$TIMEZONE_NAMES
WHERE UPPER (tzname) = UPPER (p_country_name) AND ROWNUM = 1;
v_offset VARCHAR2 (100);
vtime VARCHAR2 (100);
BEGIN
FOR c IN c_tz (country_name)
LOOP
v_offset := c.z_offset;
END LOOP;
EXECUTE IMMEDIATE ‘Alter Session Set time_zone = ‘
|| CHR (39)
|| v_offset
|| CHR (39);
SELECT TO_CHAR (CURRENT_TIMESTAMP, ‘dd-mon-yyyy HH:mi:ss PM’)
INTO vtime
FROM DUAL;
/* restore local time for current session*/
EXECUTE IMMEDIATE ‘Alter Session Set time_zone = local’;
RETURN (vtime);
EXCEPTION
WHEN OTHERS
THEN
RETURN ”;
END;
/
6. Conclusion
I have created an Oracle Form which shows a country list and a text box to display the current date and time for the selected country. Below is an image.
