CREATE OR REPLACE PACKAGE my_apex_url
IS
/* Don't hand-construct your URLs inside APEX code. Instead call one of these
functions to do the work for you!
Explanations of non-self-evident parameters:
add_href_in - if TRUE surround URL in <a> tags
href_name_in - text used for href
external_url_in - you want the URL to be outside of APEX? then provide the "base" domain string
open_new_window_in - adds target="_blank" so you don't have to remember that silly syntax
hashtag_in - when you want to go to a specific # location on page
*/
/* All arrays are assumed to be empty or densely-filled from index value 1 */
TYPE strings_t IS TABLE OF VARCHAR2 (32767);
FUNCTION for_this (app_id_in IN VARCHAR2,
app_pg_id_in IN VARCHAR2,
app_session_in IN VARCHAR2,
request_in IN VARCHAR2,
debug_in IN VARCHAR2 DEFAULT 'NO',
cache_in IN VARCHAR2,
page_items_in IN strings_t,
values_in IN strings_t,
printer_friendly_in IN VARCHAR2 DEFAULT NULL,
add_href_in IN BOOLEAN DEFAULT FALSE,
href_name_in IN VARCHAR2 DEFAULT NULL,
external_url_in IN VARCHAR2 DEFAULT NULL,
open_new_window_in IN BOOLEAN DEFAULT TRUE,
onclick_in IN VARCHAR2 DEFAULT NULL,
image_in IN VARCHAR2 DEFAULT NULL,
add_ssp_in IN BOOLEAN DEFAULT TRUE,
hashtag_in IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;
/* Single parameter overloading */
FUNCTION for_this (app_id_in IN VARCHAR2,
app_pg_id_in IN VARCHAR2,
app_session_in IN VARCHAR2,
request_in IN VARCHAR2,
debug_in IN VARCHAR2 DEFAULT 'NO',
cache_in IN VARCHAR2,
page_item_in IN VARCHAR2 DEFAULT NULL,
value_in IN VARCHAR2 DEFAULT NULL,
printer_friendly_in IN VARCHAR2 DEFAULT NULL,
add_href_in IN BOOLEAN DEFAULT FALSE,
href_name_in IN VARCHAR2 DEFAULT NULL,
external_url_in IN VARCHAR2 DEFAULT NULL,
open_new_window_in IN BOOLEAN DEFAULT TRUE,
onclick_in IN VARCHAR2 DEFAULT NULL,
image_in IN VARCHAR2 DEFAULT NULL,
add_ssp_in IN BOOLEAN DEFAULT TRUE,
hashtag_in IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;
/* Single parameter overloading for use in SQL: 1 = TRUE, 0 (or anything else!) FALSE. */
FUNCTION for_this (app_id_in IN VARCHAR2,
app_pg_id_in IN VARCHAR2,
app_session_in IN VARCHAR2,
request_in IN VARCHAR2,
debug_in IN VARCHAR2 DEFAULT 'NO',
cache_in IN VARCHAR2,
page_item_in IN VARCHAR2 DEFAULT NULL,
value_in IN VARCHAR2 DEFAULT NULL,
printer_friendly_in IN VARCHAR2 DEFAULT NULL,
add_href_in IN INTEGER DEFAULT 0,
href_name_in IN VARCHAR2 DEFAULT NULL,
external_url_in IN VARCHAR2 DEFAULT NULL,
open_new_window_in IN INTEGER DEFAULT 1,
onclick_in IN VARCHAR2 DEFAULT NULL,
image_in IN VARCHAR2 DEFAULT NULL,
add_ssp_in IN INTEGER DEFAULT 1,
hashtag_in IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;
END;Package created.
CREATE OR REPLACE PACKAGE BODY my_apex_url
IS
FUNCTION for_this (app_id_in IN VARCHAR2,
app_pg_id_in IN VARCHAR2,
app_session_in IN VARCHAR2,
request_in IN VARCHAR2,
debug_in IN VARCHAR2 DEFAULT 'NO',
cache_in IN VARCHAR2,
page_items_in IN strings_t,
values_in IN strings_t,
printer_friendly_in IN VARCHAR2 DEFAULT NULL,
add_href_in IN BOOLEAN DEFAULT FALSE,
href_name_in IN VARCHAR2 DEFAULT NULL,
external_url_in IN VARCHAR2 DEFAULT NULL,
open_new_window_in IN BOOLEAN DEFAULT TRUE,
onclick_in IN VARCHAR2 DEFAULT NULL,
image_in IN VARCHAR2 DEFAULT NULL,
add_ssp_in IN BOOLEAN DEFAULT TRUE,
hashtag_in IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
IS
l_return VARCHAR2(32767);
BEGIN
l_return :=
'f?p='
|| app_id_in
|| ':'
|| app_pg_id_in
|| ':'
|| app_session_in
|| ':'
|| request_in
|| ':'
|| debug_in
|| ':'
|| cache_in
|| ':';
IF page_items_in.COUNT = 0
THEN
l_return := l_return || ':';
ELSE
FOR indx IN 1 .. page_items_in.COUNT
LOOP
l_return :=
l_return
|| CASE indx WHEN 1 THEN NULL ELSE ',' END
|| page_items_in (indx);
END LOOP;
FOR indx IN 1 .. page_items_in.COUNT
LOOP
l_return :=
l_return
|| CASE indx WHEN 1 THEN ':' ELSE ',' END
|| values_in (indx);
END LOOP;
END IF;
l_return := l_return || ':' || printer_friendly_in;
l_return :=
CASE
WHEN add_ssp_in
THEN
l_return || '&cs=CHECKSUM-PLEASE!'
/* Sorry, APEX_UTIL not yet available on LiveSQL,
so just un-comment this in your own environment
APEX_UTIL.prepare_url (l_return,
'UTF-8',
1,
p_dialog => 'NOT DIALOG')*/
ELSE
l_return
END
|| CASE
WHEN hashtag_in IS NOT NULL THEN '#' || hashtag_in
ELSE NULL
END;
l_return := external_url_in || l_return;
IF add_href_in AND href_name_in IS NOT NULL
THEN
l_return :=
'<a href="'
|| l_return
|| '"'
|| CASE
WHEN open_new_window_in THEN ' target="_blank"'
ELSE NULL
END
|| CASE
WHEN onclick_in IS NOT NULL
THEN
' onclick="javascript:apex.submit('''
|| onclick_in
|| ''');"'
ELSE
NULL
END
|| '>'
|| NVL (href_name_in, l_return)
|| CASE
WHEN image_in IS NOT NULL
THEN
'<img src="#IMAGE_PREFIX#menu/'
|| image_in
|| '" alt="">'
ELSE
NULL
END
|| '</a>';
END IF;
RETURN l_return;
END;
/* Single parameter overloading */
FUNCTION for_this (app_id_in IN VARCHAR2,
app_pg_id_in IN VARCHAR2,
app_session_in IN VARCHAR2,
request_in IN VARCHAR2,
debug_in IN VARCHAR2 DEFAULT 'NO',
cache_in IN VARCHAR2,
page_item_in IN VARCHAR2 DEFAULT NULL,
value_in IN VARCHAR2 DEFAULT NULL,
printer_friendly_in IN VARCHAR2 DEFAULT NULL,
add_href_in IN BOOLEAN DEFAULT FALSE,
href_name_in IN VARCHAR2 DEFAULT NULL,
external_url_in IN VARCHAR2 DEFAULT NULL,
open_new_window_in IN BOOLEAN DEFAULT TRUE,
onclick_in IN VARCHAR2 DEFAULT NULL,
image_in IN VARCHAR2 DEFAULT NULL,
add_ssp_in IN BOOLEAN DEFAULT TRUE,
hashtag_in IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
IS
l_items strings_t := strings_t ();
l_values strings_t := strings_t ();
BEGIN
IF page_item_in IS NOT NULL
THEN
l_items.EXTEND;
l_items (1) := page_item_in;
l_values.EXTEND;
l_values (1) := value_in;
END IF;
RETURN for_this (app_id_in => app_id_in,
app_pg_id_in => app_pg_id_in,
app_session_in => app_session_in,
request_in => request_in,
debug_in => debug_in,
cache_in => cache_in,
page_items_in => l_items,
values_in => l_values,
printer_friendly_in => printer_friendly_in,
add_href_in => add_href_in,
href_name_in => href_name_in,
external_url_in => external_url_in,
open_new_window_in => open_new_window_in,
onclick_in => onclick_in,
image_in => image_in,
add_ssp_in => add_ssp_in,
hashtag_in => hashtag_in);
END;
/* Single parameter overloading for use in SQL: 1 = TRUE, 0 (or anything else!) FALSE. */
FUNCTION for_this (app_id_in IN VARCHAR2,
app_pg_id_in IN VARCHAR2,
app_session_in IN VARCHAR2,
request_in IN VARCHAR2,
debug_in IN VARCHAR2 DEFAULT 'NO',
cache_in IN VARCHAR2,
page_item_in IN VARCHAR2 DEFAULT NULL,
value_in IN VARCHAR2 DEFAULT NULL,
printer_friendly_in IN VARCHAR2 DEFAULT NULL,
add_href_in IN INTEGER DEFAULT 0,
href_name_in IN VARCHAR2 DEFAULT NULL,
external_url_in IN VARCHAR2 DEFAULT NULL,
open_new_window_in IN INTEGER DEFAULT 1,
onclick_in IN VARCHAR2 DEFAULT NULL,
image_in IN VARCHAR2 DEFAULT NULL,
add_ssp_in IN INTEGER DEFAULT 1,
hashtag_in IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
IS
BEGIN
RETURN for_this (
app_id_in => app_id_in,
app_pg_id_in => app_pg_id_in,
app_session_in => app_session_in,
request_in => request_in,
debug_in => debug_in,
cache_in => cache_in,
page_item_in => page_item_in,
value_in => value_in,
printer_friendly_in => printer_friendly_in,
add_href_in => CASE add_href_in
WHEN 1 THEN TRUE
ELSE FALSE
END,
href_name_in => href_name_in,
external_url_in => external_url_in,
open_new_window_in => CASE open_new_window_in
WHEN 1 THEN TRUE
ELSE FALSE
END,
onclick_in => onclick_in,
image_in => image_in,
add_ssp_in => CASE add_ssp_in
WHEN 1 THEN TRUE
ELSE FALSE
END,
hashtag_in => hashtag_in);
END;
END;Package Body created.
DECLARE
l_items my_apex_url.strings_t
:= my_apex_url.strings_t ('P659_QUIZ_ID',
'P659_COMP_EVENT_ID',
'P659_QUESTION_ID');
l_values my_apex_url.strings_t
:= my_apex_url.strings_t (1392416, 871639, NULL);
l_url VARCHAR2 (32767);
BEGIN
/* Single item call */
DBMS_OUTPUT.put_line (my_apex_url.for_this (
app_id_in => 10000,
app_pg_id_in => 650,
app_session_in => NULL,
request_in => 'SHOW',
debug_in => 'NO',
cache_in => 650,
page_item_in => NULL,
value_in => NULL,
printer_friendly_in => 'No',
add_href_in => TRUE,
href_name_in => 'Library',
external_url_in => 'https://plsqlchallenge.oracle.com/pls/apex/',
open_new_window_in => TRUE,
onclick_in => NULL,
image_in => NULL,
add_ssp_in => TRUE,
hashtag_in => NULL));
/* And in SQL */
SELECT my_apex_url.for_this (
app_id_in => 10000,
app_pg_id_in => 650,
app_session_in => NULL,
request_in => 'SHOW',
debug_in => 'NO',
cache_in => 650,
page_item_in => NULL,
value_in => NULL,
printer_friendly_in => 'No',
add_href_in => 1,
href_name_in => 'Library',
external_url_in => 'https://plsqlchallenge.oracle.com/pls/apex/',
open_new_window_in => 1,
onclick_in => NULL,
image_in => NULL,
add_ssp_in => 1,
hashtag_in => NULL)
INTO l_url
FROM DUAL;
DBMS_OUTPUT.put_line (l_url);
/* Multiple items */
DBMS_OUTPUT.put_line (my_apex_url.for_this (
app_id_in => 10000,
app_pg_id_in => 659,
app_session_in => NULL,
request_in => 'CLEAR_HA',
debug_in => 'NO',
cache_in => 659,
page_items_in => l_items,
values_in => l_values,
printer_friendly_in => 'Yes',
add_href_in => TRUE,
href_name_in => 'Quick Quiz: 2 January - 8 January 2016',
external_url_in => 'https://plsqlchallenge.oracle.com/pls/apex/',
open_new_window_in => TRUE,
onclick_in => NULL,
image_in => NULL,
add_ssp_in => TRUE,
hashtag_in => NULL));
END;