Oracle | HTMLDB

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 VACHAR2 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