Oracle HTMLDB_UTIL
Version 19c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Undocumented in the Types and Packages documentation this package contains supporting utilities for Application Express (APEX)
AUTHID DEFINER
Constants
Name Data Type Value
c_must_not_be_public_user VARCHAR2(30) 'MUST_NOT_BE_PUBLIC_USER'
Dependencies
APEX_UTIL WWV_FLOW_DEBUG_API WWV_FLOW_SECURITY
DBA_USERS WWV_FLOW_DML WWV_FLOW_SESSION
DBMS_LOB WWV_FLOW_ERROR WWV_FLOW_SESSIONS$
DBMS_OUTPUT WWV_FLOW_ESCAPE WWV_FLOW_SESSION_STATE
DBMS_RANDOM WWV_FLOW_F4000_PLUGINS WWV_FLOW_STEPS
DBMS_STANDARD WWV_FLOW_F4000_UTIL WWV_FLOW_STEP_ITEMS
F WWV_FLOW_FEEDBACK WWV_FLOW_STEP_PROCESSING
HTP WWV_FLOW_FEEDBACK_FOLLOWUP WWV_FLOW_TEAM_FILE
NV WWV_FLOW_FILE_MGR WWV_FLOW_TEMPLATES_UTIL
OWA_UTIL WWV_FLOW_FILE_OBJECTS$ WWV_FLOW_USER_API
PLITBLM WWV_FLOW_FLASH_CHART5 WWV_FLOW_UTILITIES
UTL_HTTP WWV_FLOW_FND_USER_API WWV_FLOW_WORKSHEET
V WWV_FLOW_GEN_API2 WWV_FLOW_WORKSHEET_API
V_$DBLINK WWV_FLOW_GLOBAL WWV_FLOW_WORKSHEET_DIALOGUE
WWV_DBMS_SQL WWV_FLOW_GRID_LAYOUT_DEV WWV_FLOW_WORKSHEET_FORM
WWV_FLOW WWV_FLOW_ID WWV_FLOW_WORKSHEET_STANDARD
WWV_FLOWS WWV_FLOW_LANG WWV_FLOW_WORKSHEET_UTIL
WWV_FLOW_4000_UI WWV_FLOW_LANGUAGES WWV_FLOW_WS_ATTACHMENT
WWV_FLOW_AJAX WWV_FLOW_LANGUAGE_MAP WWV_FLOW_WS_DIALOG
WWV_FLOW_API WWV_FLOW_NATIVE_ITEM WWV_FLOW_WS_FLASH_CHART
WWV_FLOW_AUTHORIZATION WWV_FLOW_PAGE WWV_FLOW_WS_FORM
WWV_FLOW_CGI WWV_FLOW_PATCHES WWV_FLOW_WS_STICKIES
WWV_FLOW_COMPANY_SCHEMAS WWV_FLOW_PKG_APP_INSTALL WWV_FLOW_WS_UI
WWV_FLOW_CSS WWV_FLOW_PLUGIN_DEV WWV_FLOW_WS_WEBPAGE
WWV_FLOW_DATA WWV_FLOW_PREFERENCES WWV_RENDER_REPORT3
WWV_FLOW_DATA_UPLOAD WWV_FLOW_PROCESS Z
WWV_FLOW_DEBUG WWV_FLOW_PROCESS_NATIVE  
Documented No
First Available 12.1
Security Model Owned by the APEX owner with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/apex/core/htmldb_util.sql
Subprograms
AGENDA_CALENDAR GET_FILE_ID PURGE_REGIONS_BY_APP
CACHE_GET_DATE_OF_PAGE_CACHE GET_FIRST_NAME PURGE_REGIONS_BY_NAME
CACHE_GET_DATE_OF_REGION_CACHE GET_GROUPS_USER_BELONGS_TO PURGE_REGIONS_BY_PAGE
CACHE_PURGE_BY_APPLICATION GET_GROUP_ID REDIRECT_URL
CACHE_PURGE_BY_PAGE GET_GROUP_NAME REMOVE_PREFERENCE
CACHE_PURGE_STALE GET_HIGH_CONTRAST_MODE_TOGGLE REMOVE_SORT_PREFERENCES
CHANGE_CURRENT_USER_PW GET_LANGUAGE_SELECTOR_LIST REMOVE_USER
CHANGE_PASSWORD_ON_FIRST_USE GET_LANGUAGE_SELECTOR_UL RESET_AUTHORIZATIONS
CLEAR_APP_CACHE GET_LANGUAGE_SELECTOR_UL2 RESET_PW
CLEAR_PAGE_CACHE GET_LAST_NAME RESET_SESSION_LANG
CLEAR_USER_CACHE GET_NUMERIC_SESSION_STATE RESET_SESSION_TERRITORY
CLOSE_OPEN_DB_LINKS GET_PREFERENCE RESET_SESSION_TIME_ZONE
COMPRESS_INT GET_PRINT_DOCUMENT SAVEKEY_NUM
COUNT_CLICK GET_SCREEN_READER_MODE_TOGGLE SAVEKEY_VC2
CREATE_USER GET_SESSION_LANG SET_ATTRIBUTE
CREATE_USER_FROM_FILE GET_SESSION_STATE SET_AUTHENTICATION_RESULT
CREATE_USER_GROUP GET_SESSION_TERRITORY SET_BLOB
CURRENT_USER_IN_GROUP GET_SESSION_TIME_ZONE SET_BUILD_OPTION_STATUS
CUSTOM_CALENDAR GET_SINCE SET_COMPATIBILITY_MODE
DAILY_CALENDAR GET_SINCE_TSWLTZ SET_CUSTOM_AUTH_STATUS
DECREMENT_CALENDAR GET_SINCE_TSWTZ SET_EDITION
DELETE_USER_GROUP GET_TIMEFRAME SET_EMAIL
DOWNLOAD_PRINT_DOCUMENT GET_USERNAME SET_FIRST_NAME
EDIT_USER GET_USER_ID SET_LAST_NAME
END_USER_ACCOUNT_DAYS_LEFT GET_USER_ROLES SET_PREFERENCE
EXPIRE_END_USER_ACCOUNT GET_UUID SET_REPORT_COLUMN_FORMAT
EXPIRE_WORKSPACE_ACCOUNT HAS_RESTRICTED_CHARS SET_SECURITY_GROUP_ID
EXPORT_APPLICATION HOST_URL SET_SESSION_HIGH_CONTRAST_OFF
EXPORT_APPLICATION_COMPONENT HTML_PCT_GRAPH_MASK SET_SESSION_HIGH_CONTRAST_ON
EXPORT_APPLICATION_PAGE INCREMENT_CALENDAR SET_SESSION_LANG
EXPORT_USERS IR_CLEAR SET_SESSION_LIFETIME_SECONDS
FETCH_APP_ITEM IR_DELETE_REPORT SET_SESSION_MAX_IDLE_SECONDS
FETCH_USER IR_DELETE_SUBSCRIPTION SET_SESSION_SCREEN_READER_OFF
FILESIZE_MASK IR_FILTER SET_SESSION_SCREEN_READER_ON
FIND_SECURITY_GROUP_ID IR_RESET SET_SESSION_STATE
FIND_WORKSPACE IS_HIGH_CONTRAST_SESSION SET_SESSION_TERRITORY
FLASH2 IS_HIGH_CONTRAST_SESSION_YN SET_SESSION_TIME_ZONE
GET_ACCOUNT_LOCKED_STATUS IS_LOGIN_PASSWORD_VALID SET_USERNAME
GET_APEX_CHAR_ID IS_SCREEN_READER_SESSION SHOW_HIGH_CONTRAST_MODE_TOGGLE
GET_APEX_ID IS_SCREEN_READER_SESSION_YN SHOW_SCREEN_READER_MODE_TOGGLE
GET_APPLICATION_ID_STATUS IS_USERNAME_UNIQUE STRING_TO_TABLE
GET_APPLICATION_NAME JSON_FROM_ARRAY STRONG_PASSWORD_CHECK
GET_ATTRIBUTE JSON_FROM_ITEMS STRONG_PASSWORD_VALIDATION
GET_AUTHENTICATION_RESULT JSON_FROM_SQL SUBMIT_FEEDBACK
GET_BLOB JSON_FROM_STRING SUBMIT_FEEDBACK_FOLLOWUP
GET_BLOB_FILE KEYVAL_NUM TABLE_TO_STRING
GET_BLOB_FILE_SRC KEYVAL_VC2 TODAY_CALENDAR
GET_BUILD_OPTION_STATUS LOCK_ACCOUNT UNEXPIRE_END_USER_ACCOUNT
GET_CURRENT_USER_ID MINIMUM_FREE_APPLICATION_ID UNEXPIRE_WORKSPACE_ACCOUNT
GET_DEFAULT_SCHEMA MONTH_CALENDAR UNLOCK_ACCOUNT
GET_EDITION PASSWORD_FIRST_USE_OCCURRED URL_ENCODE
GET_EMAIL PAUSE USER_IN_GROUP
GET_FEEDBACK_FOLLOW_UP PREPARE_URL WEEKLY_CALENDAR
GET_FILE PUBLIC_CHECK_AUTHORIZATION WORKSPACE_ACCOUNT_DAYS_LEFT
 
AGENDA_CALENDAR
Undocumented htmldb_util.agenda_calendar(p_date_type_field IN VARCHAR2 DEFAULT NULL);
exec htmldb_util.agenda_calendar(SYSDATE);
 
CACHE_GET_DATE_OF_PAGE_CACHE
Returns the date and time the specified application page was cached either for the user issuing the call or for all users if the page was not set to be cached by user htmldb_util.cache_get_date_of_page_cache(
p_application IN NUMBER,
p_page        IN NUMBER)
RETURN DATE;
exec htmldb_util.cache_get_date_of_page_cache(1, 101);
 
CACHE_PURGE_BY_APPLICATION
Purges all cached pages and regions for a given application htmldb_util.cache_purge_by_application(p_application IN NUMBER);
exec htmldb_util.cache_purge_by_application(1);
 
CACHE_PURGE_BY_PAGE
Purges all cached pages and regions for a given application and page.
If p_user_name is supplied, only that user's cached pages and regions will be purged
htmldb_util.cache_purge_by_page(
p_application IN NUMBER,
p_page        IN NUMBER,
p_user_name   IN VARCHAR2 DEFAULT NULL);
exec htmldb_util.cache_purge_by_application(1, 101, USER);
 
CACHE_PURGE_STALE
Deletes all cached pages and regions for the specified application that have passed their timeout htmldb_util.cache_purge_by_page(p_application IN NUMBER);
exec htmldb_util.cache_purge_stale(1);
 
CHANGE_CURRENT_USER_PW
Change current user password htmldb_util.change_current_user_pw(p_new_password IN VARCHAR2);
SQL> exec htmldb_util.change_current_user_pw('N0Access4You');
 
CHANGE_PASSWORD_ON_FIRST_USE
Force changing the existing password with the first login htmldb_util.change_password_on_first_use(p_user_name IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
  IF htmldb_util.change_password_on_first_use('UWCLASS') THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/
 
CLEAR_APP_CACHE
For the current session remove session state identified application htmldb_util.clear_app_cache(p_app_id IN VARCHAR2 DEFAULT NULL);
exec htmldb_util.clear_app_cache('101');
 
CLEAR_PAGE_CACHE
Reset all cached items for a given page to null htmldb_util.clear_page_cache(p_page_id IN NUMBER DEFAULT NULL);
exec htmldb_util.clear_page_cache(1);
 
CLEAR_USER_CACHE
For the current user's session remove session state and flow system preferences htmldb_util.clear_user_cache;
exec htmldb_util.clear_user_cache;
 
CLOSE_OPEN_DB_LINKS
Close all database links open in the current session htmldb_util.close_open_db_links;
exec htmldb_util.close_open_db_links;
 
COMPRESS_INT
Return a minimum of 4 character alphanumeric compressed value for an integer htmldb_util.compress_int(p_number IN INTEGER)
RETURN VARCHAR2;
SELECT htmldb_util.compress_int(1)
FROM dual;

HTMLDB_UTIL.COMPRESS_INT(1)
--------------------------------------------------
AAAB


SELECT htmldb_util.compress_int(42)
FROM dual;

HTMLDB_UTIL.COMPRESS_INT(42)
--------------------------------------------------
AABQ
 
CURRENT_USER_IN_GROUP
Given a group name return a boolean true or false if the current application user is part of that group htmldb_util.curent_user_in_group(p_group_name IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
DECREMENT_CALENDAR
Decreases the calendar date by one day htmldb_util.decrement_calendar;
exec htmldb_util.procedure decrement_calendar;
 
DELETE_USER_GROUP
Allows for removal of group from wwv_flow_fnd_user_groups

Overload 1
htmldb_util.delete_user_group(p_group_id IN NUMBER);
exec htmldb_util.delete_user_group(42);
Overload 2 htmldb_util.delete_user_group(p_group_name IN VARCHAR2);
exec htmldb_util.delete_user_group('UWGrp');
 
EDIT_USER
Edit user information in  wwv_flow_fnd_user, wwv_flow_fnd_group_users and wwv_flow_developers tables htmldb_util.edit_user(
p_user_id                      IN NUMBER,
p_user_name                    IN VARCHAR2,
p_first_name                   IN VARCHAR2 DEFAULT NULL,
p_last_name                    IN VARCHAR2 DEFAULT NULL,
p_web_password                 IN VARCHAR2 DEFAULT NULL,
p_new_password                 IN VARCHAR2 DEFAULT NULL,
p_email_address                IN VARCHAR2 DEFAULT NULL,
p_start_date                   IN VARCHAR2 DEFAULT NULL,
p_end_date                     IN VARCHAR2 DEFAULT NULL,
p_employee_id                  IN VARCHAR2 DEFAULT NULL,
p_allow_access_to_schemas      IN VARCHAR2 DEFAULT NULL,
p_person_type                  IN VARCHAR2 DEFAULT NULL,
p_default_schema               IN VARCHAR2 DEFAULT NULL,
p_default_date_format          IN VARCHAR2 DEFAULT NULL,
p_group_ids                    IN VARCHAR2 DEFAULT NULL,
p_developer_roles              IN VARCHAR2 DEFAULT NULL,
p_description                  IN VARCHAR2 DEFAULT NULL,
p_account_expiry               IN DATE     DEFAULT NULL,
p_account_locked               IN VARCHAR2 DEFAULT 'N',
p_failed_access_attempts       IN NUMBER   DEFAULT 0,
p_change_password_on_first_use IN VARCHAR2 DEFAULT 'Y',
p_first_password_use_occurred  IN VARCHAR2 DEFAULT 'N');
exec htmldb_util.edit_user(1, 'MORGAN', p_failed_access_attempts=>3);
 
END_USER_ACCOUNT_DAYS_LEFT
Returns the number of days left before an end user account expires htmldb_util.end_user_account_days_left(p_user_name IN VARCHAR2)
RETURN NUMBER;
SELECT htmldb_util.end_user_account_days_left('MORGAN')
FROM dual;
 
EXPIRE_END_USER_ACCOUNT
Causes the immediate expiration of an end user's account htmldb_util.expire_end_user_account(p_user_name IN VARCHAR2);
exec htmldb_util.expire_end_user_account('MORGAN');
 
EXPIRE_WORKSPACE_ACCOUNT
Causes the immediate expiration of an end user's workspace account htmldb_util.expire_workspace_account(p_user_name IN VARCHAR2);
exec htmldb_util.expire_workspace_account('MORGAN');
 
EXPORT_APPLICATION
For use from SQL prompt: Application export generated to HTP buffer htmldb_util.export_application(
p_workspace_id   IN NUMBER,
p_application_id IN NUMBER);
exec htmldb_util.export_application(1, 2);
 
EXPORT_APPLICATION_COMPONENT
For use from SQL prompt: Application component export generated to HTP buffer htmldb_util.export_application_component(
p_workspace_id   IN NUMBER,
p_application_id IN NUMBER,
p_component_id   IN NUMBER,
p_component_type IN VARCHAR2);
TBD
 
EXPORT_APPLICATION_PAGE
For use from SQL prompt: Application page export generated to HTP buffera htmldb_util.export_application_page(
p_workspace_id   IN NUMBER,
p_application_id IN NUMBER,
p_page_id        IN NUMBER);
exec htmldb_util.export_application(1, 2, 3);
 
EXPORT_USERS
Designed to be called from dev: exports all users in a the current workspace htmldb_util.export_users(p_export_format IN VARCHAR2 DEFAULT 'UNIX')
exec htmldb_util.export_users;
 
FETCH_APP_ITEM
Given an application-level item name, locate item in current or specified application and current or specified session and return item value htmldb_util.fetch_app_item(
p_item    IN VARCHAR2,
p_app     IN NUMBER DEFAULT NULL,
p_session IN NUMBER DEFAULT NULL)
RETURN VARCHAR2;
SELECT htmldb_util.fetch_app_item(p_item=>'CUST_NAME', p_app=>1)
FROM dual;
 
FETCH_USER
Fetch user information from wwv_flow_fnd_user, wwv_flow_fnd_group_users and wwv_flow_developers table

Overload 1
htmldb_util.fetch_user(
p_user_id                 IN  NUMBER,
p_workspace               OUT VARCHAR2,
p_user_name               OUT VARCHAR2,
p_first_name              OUT VARCHAR2,
p_last_name               OUT VARCHAR2,
p_web_password            OUT VARCHAR2,
p_email_address           OUT VARCHAR2,
p_start_date              OUT VARCHAR2,
p_end_date                OUT VARCHAR2,
p_employee_id             OUT VARCHAR2,
p_allow_access_to_schemas OUT VARCHAR2,
p_person_type             OUT VARCHAR2,
p_default_schema          OUT VARCHAR2,
p_groups                  OUT VARCHAR2,
p_developer_role          OUT VARCHAR2,
p_description             OUT VARCHAR2);
TBD
Overload 2 htmldb_util.fetch_user(
p_user_id        IN  NUMBER,
p_user_name      OUT VARCHAR2,
p_first_name     OUT VARCHAR2,
p_last_name      OUT VARCHAR2,
p_email_address  OUT VARCHAR2,
p_groups         OUT VARCHAR2,
p_developer_role OUT VARCHAR2,
p_description    OUT VARCHAR2);
DECLARE
 uname   VARCHAR2(30);
 fname   VARCHAR2(30);
 lname   VARCHAR2(30);
 email   VARCHAR2(64);
 grp     VARCHAR2(30);
 devrole VARCHAR2(64);
 desc    VARCHAR2(64);
BEGIN
  htmldb_util.fetch_user(42, uname, fname, lname, email, grp, devrole, desc);
END;
/
Overload 3 htmldb_util.fetch_user(
p_user_id                      IN  NUMBER,
p_workspace                    OUT VARCHAR2,
p_user_name                    OUT VARCHAR2,
p_first_name                   OUT VARCHAR2,
p_last_name                    OUT VARCHAR2,
p_web_password                 OUT VARCHAR2,
p_email_address                OUT VARCHAR2,
p_start_date                   OUT VARCHAR2,
p_end_date                     OUT VARCHAR2,
p_employee_id                  OUT VARCHAR2,
p_allow_access_to_schemas      OUT VARCHAR2,
p_person_type                  OUT VARCHAR2,
p_default_schema               OUT VARCHAR2,
p_groups                       OUT VARCHAR2,
p_developer_role               OUT VARCHAR2,
p_description                  OUT VARCHAR2,
p_account_expiry               OUT DATE,
p_account_locked               OUT VARCHAR2,
p_failed_access_attempts       OUT NUMBER,
p_change_password_on_first_use OUT VARCHAR2,
p_first_password_use_occurred  OUT VARCHAR2);
TBD

Related Topics
Built-in Functions
Built-in Packages
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx