Saturday 17 May 2008

Problems loading Oracle Forums in a browser?

I had this with my old laptop and thought that is my "overloaded" FireFox with all those extensions. However, that continued with my new notebook as well. Now, I noticed this is not only FireFox but also the same thing happens when I use Internet Explorer (which I use only then when I need to open the same ApEx application twice).

See the picture below. It looks like while loading the forum the page is not rendered completely. This behaviour is only there when using Oracle Forums and they happen in both cases - while loading the main page or a single thread. Therefor my question to all readers of this blog: have you ever noticed something like that? I have this from time to time regardless of the connection I use. Currently, I use DSL 16000.





Monday 12 May 2008

Limit or Extend a Datepicker in ApEx

I have received a couple of questions per email, similar to this one on limiting the value of a date picker item. That shows this is an issue that needs to be solved from the development team. Whereby this is an easy issue. If you look behind the code, you will se there are one procedure and one view involved. The procedure code is generating a small pop-up window showing a calendar based on the number of the years the view is returning. I managed to get that customized by doing the following:

First of all, create a package like this:

1.

CREATE OR REPLACE PACKAGE limit_datepicker
AS
x NUMBER;
y NUMBER;

FUNCTION get_x
RETURN NUMBER;

FUNCTION get_y
RETURN NUMBER;

PROCEDURE show_as_popup_calendar (
p_request IN VARCHAR2 DEFAULT NULL,
p_title IN VARCHAR2 DEFAULT NULL,
p_yyyy IN VARCHAR2 DEFAULT NULL,
p_mm IN VARCHAR2 DEFAULT NULL,
p_dd IN VARCHAR2 DEFAULT NULL,
p_hh IN VARCHAR2 DEFAULT NULL,
p_mi IN VARCHAR2 DEFAULT NULL,
p_pm IN VARCHAR2 DEFAULT NULL,
p_element_index IN VARCHAR2 DEFAULT NULL,
p_form_index IN VARCHAR2 DEFAULT NULL,
p_date_format IN VARCHAR2 DEFAULT 'MM/DD/YYYY',
p_bgcolor IN VARCHAR2 DEFAULT '#336699',
p_white_foreground IN VARCHAR2 DEFAULT 'Y',
p_application_format IN VARCHAR2 DEFAULT 'N',
p_lang IN VARCHAR2 DEFAULT NULL,
p_application_id IN VARCHAR2 DEFAULT NULL,
p_security_group_id IN VARCHAR2 DEFAULT NULL,
p_start_year IN NUMBER DEFAULT 1919,
p_end_year IN NUMBER DEFAULT 2050
);
END limit_datepicker;
/

CREATE OR REPLACE PACKAGE BODY limit_datepicker
AS
FUNCTION get_x
RETURN NUMBER
IS
BEGIN
RETURN limit_datepicker.x;
END get_x;

FUNCTION get_y
RETURN NUMBER
IS
BEGIN
RETURN limit_datepicker.y;
END get_y;

PROCEDURE show_as_popup_calendar (
p_request IN VARCHAR2 DEFAULT NULL,
p_title IN VARCHAR2 DEFAULT NULL,
p_yyyy IN VARCHAR2 DEFAULT NULL,
p_mm IN VARCHAR2 DEFAULT NULL,
p_dd IN VARCHAR2 DEFAULT NULL,
p_hh IN VARCHAR2 DEFAULT NULL,
p_mi IN VARCHAR2 DEFAULT NULL,
p_pm IN VARCHAR2 DEFAULT NULL,
p_element_index IN VARCHAR2 DEFAULT NULL,
p_form_index IN VARCHAR2 DEFAULT NULL,
p_date_format IN VARCHAR2 DEFAULT 'MM/DD/YYYY',
p_bgcolor IN VARCHAR2 DEFAULT '#336699',
p_white_foreground IN VARCHAR2 DEFAULT 'Y',
p_application_format IN VARCHAR2 DEFAULT 'N',
p_lang IN VARCHAR2 DEFAULT NULL,
p_application_id IN VARCHAR2 DEFAULT NULL,
p_security_group_id IN VARCHAR2 DEFAULT NULL,
p_start_year IN NUMBER DEFAULT 1919,
p_end_year IN NUMBER DEFAULT 2050
)
IS
BEGIN
limit_datepicker.x := p_start_year;
limit_datepicker.y := p_end_year;
wwv_flow_utilities.show_as_popup_calendar (p_request,
p_title,
p_yyyy,
p_mm,
p_dd,
p_hh,
p_mi,
p_pm,
p_element_index,
p_form_index,
p_date_format,
p_bgcolor,
p_white_foreground,
p_application_format,
p_lang,
p_application_id,
p_security_group_id
);
END;
END limit_datepicker;
/


2. GRANT EXECUTE ON limit_datepicker TO PUBLIC;

3. CREATE OR REPLACE PUBLIC SYNONYM limit_datepicker FOR <<your_schema>>.limit_datepicker;

To be able to limit the values the view returns you need to do the following:

1. log in a SYS

2. ALTER SESSION SET current_schema=FLOWS_030000 (yor flows schema, FLOWS_030000 in my case)

3.

CREATE OR REPLACE VIEW wwv_flow_years
AS
SELECT NVL (b.start_year - 1, 1918) + LEVEL
FROM DUAL a,
(SELECT limit_datepicker.get_x start_year
FROM DUAL) b,
(SELECT limit_datepicker.get_y end_year
FROM DUAL) c
CONNECT BY LEVEL < NVL ((c.end_year + 1) - (b.start_year - 1),
2051 - 1918)


The view will now return the same result for the standard date picker or the value you request if you use the custom date picker.

And finaly, create the following on your page, which will contain the date picker item, limiting the years to the values you determine;

1. Create a hidden item on your page (Page 1 in my case)

P1_SECURITY_GROUP_ID

with a source PL/SQL Expression or Function

htmldb_custom_auth.get_security_group_id

2. Create the javascript and put it in the header of your page. Please note, you need to take care of the parameters yourself - for example p_yyyy or p_mm:

<script type="text/javascript">
function f_popup_date(p_this)
{
var item_name = $x(p_this).name
var app_id = &APP_ID.
var sec_gr_id = $x('P1_SECURITY_GROUP_ID').value

w = open("limit_datepicker.show_as_popup_calendar" +
"?p_element_index=" + escape(item_name) +
"&p_form_index=" + escape('0') +
"&p_date_format=" + escape('DD-MON-RR') +
"&p_bgcolor=" + escape('#666666') +
"&p_dd=" + escape('') +
"&p_hh=" + escape('') +
"&p_mi=" + escape('') +
"&p_pm=" +
"&p_yyyy=" + escape('2008') +
"&p_lang=" + escape('en') +
"&p_application_format=" + escape('N') +
"&p_application_id=" + escape(app_id) +
"&p_security_group_id=" + escape(sec_gr_id) +
"&p_start_year=" + escape('2007') +
"&p_end_year=" + escape('2008') +
"&p_mm=" + escape('01'),
"winLov",
"Scrollbars=no,resizable=yes,width=258,height=210");
if (w.opener == null)
w.opener = self;
w.focus();
}
</script>



You will need to replace the values to what you need (2007 to 2008 in my case).

3. Date-Picker Item (P1_DATE_FROM in my case) is a normal text item with the following code in the Post Element Text:


<a href="javascript:f_popup_date('P1_DATE_FROM');">
<img src="/i/asfdcldr.gif"
style="cursor:pointer;valign:bottom" /></a>




The downside of this approach is that you need to modify one of the apex views and take care you recreate it after the next update. I still didn't have time to find out why this doesn't work on XE. I will have a look into that issue soon and post the results here.