Oracle Database Tips by Donald Burleson

APEX's HTMLDB_UTIL package

The HTMLDB_UTIL packages provide several procedures that can be used in APEX or in stored procedures within the database.  The APEX development environment provides other methods of performing the same function as many of these functions.  However, when developing stored procedures, the same functionality is available through the use of the HTMLDB_UTIL package.

clear_app_cache
(p_app_id in varchar2 default null ); 

This procedure will clear all session state for the application provided in the parameter.

Example: 

   begin
      clear_app_cache( :APP_ID );
   end; 

clear_page_cache(
p_page_id in number default null );

This procedure clears the session state in the current application for the page provided in the parameter.

Example:

    begin
       clear_page_cache( :APP_PAGE_ID );
   end;

clear_user_cache;

This procedure will clear the cache for the current user.  It will also remove all preferences for the user.  The added benefit of this procedure is it will clear the cache for all applications in which the user has session state.  The down fall is it clears the preferences.  CAUTION: Doing this for your development login will reset any preferences that have been set, such as the View being set to Icon or Details for the page definition and other attribute pages.

Example:

   begin
      clear_user_cache;
   end;

get_current_user_id return number;

This returns the ID number of the currently logged in user.

Example: 

   declare
      n_id number;
   begin
      n_id := get_current_user_id;
   end;

get_email( p_username in varchar2 ) return varchar2;

This returns the email stored in the database for the provided username.  This procedure can be used with the HTMLDB_MAIL package for sending mail.

Example:

   declare
      s_email varchar2(255);
   begin
      s_email := get_email( 'EASYDEV' );
   end;

get_last_name( p_username in varchar2 ) return varchar2;

This returns the Last Name of the username specified.  There is also a get_first_name function to return the user's First Name.

Example:

   declare
      s_lname varchar2(255);
   begin
      s_lname := get_last_name( 'EASYDEV' );
   end;

get_username( p_userid ) return varchar2;

This returns the username of the user ID provided.

Example:

   declare
      s_username varchar2(255);
   begin
      s_username := get_username( get_current_user_id );
   end;

get_numeric_session_state( p_item in varchar2) return number;

This returns the value of session state converted to a number.  APEX provides a shorthand function to the same thing, named NV.

Example:

   declare
      n_salary number;
   begin
      n_salary := get_numeric_session_state(:P100_SALARY);
      n_salary := nv(:P100_SALARY);  -- this does the same
   end;

get_preference(
                     p_preference in varchar2 default null,
                     p_user in varchar2 default v( 'USER' ) )
                     return varchar2;

This returns the value of a preference for the user provided.

Example:

   declare
      s_sort_pref varchar(255);
   begin
      s_sort_pref := get_preference('SORT_PREF',:APP_USER);
   end;

get_session_state( p_item in varchar2) return varchar2;

This returns the value of an item in session state.  APEX provides a shorthand function to do the same thing, named V.

Example:

   declare
      s_last_name varchar2(255);
   begin
      s_last_name := get_session_state( :P100_LAST_NAME );
      s_last_name := nv( :P100_LAST_NAME ); --does the same
   end;

remove_preference(
                    p_preference in varchar2 default null,
                    p_user in varchar2 default v( 'USER' ) );

This removes a stored preference for the username provided.

Example:

   begin
      remove_preference( 'SORT_PREF', V( 'USER' ) );
   end;

remove_sort_preference( p_user in varchar2 default v( 'USER' ) );

This removes the sorting preferences stored for the user.  The sorting preferences keep track of how a user sorted a report in a prior visit to the web page.  This is a way for APEX to sort the report the same way the next time the user visits the web page.

Example:

   begin
      remove_sort_preference( V( 'USER' ) );
   end; 

set_preference(
                    p_preference in varchar2 default null,
                    p_value in varchar2 default null,
                    p_user in varchar2 default null ); 

This procedure will store a value for the user in persistent session state.  The persistent session state is called a preference.  The next time the user visits the application, the preference can be restored.

Example:

   begin
      set_preference( 'SORT_PREF',
         'LNAME:FNAME', V( 'USER' ) );
   end;

set_session_state(
                   p_name in varchar2 default null,
                   p_value in varchar2 default null ); 

This procedure will set the session state for an item.  This is valuable because it allows the session state of page items to be set from within a stored procedure.

Example:

   begin
      set_session_state(:P100_CUSTOMER_ID,n_customer_id);
   end;

string_to_table(
                    p_string in varchar2,
                    p_separator in varchar2 default ':' )
                    return htmldb_application_global.vc_arr2; 

This converts a delimited string into a PL/SQL array.  This function is primarily used when working with checkboxes, multi-select lists and radio buttons.  Examples are shown in detail in another chapter of this book.

table_to_string(
                    p_table in htmldb_application_global.vc_arr2,
                    p_string in varchar2 default ':' )
                    return varchar2;

This function is the partner to string_to_table.  It will convert an array into a delimited string.  When selecting data from a database table, this function can be used to set the session state for a checkboxes, multi-select lists and radio buttons.  Examples are shown in detail in another chapter of this book.

url_encode( p_url in varchar2 ) return varchar2;

This converts special characters to hexadecimal value.

Example:

   declare
      s_url varchar2(255);
   begin
      s_url := url_encode(
         'http://localhost/search?hl=en&lr=&q=htmldb' );
   end;         

The above book excerpt is from:

Easy HTML-DB Oracle Application Express

Create Dynamic Web Pages with OAE

ISBN 0-9761573-1-4   

Michael Cunningham & Kent Crotty

http://www.rampant-books.com/book_2005_2_html_db.htm