HTMLDB_UTIL

CHANGE_CURRENT_USER_PW Procedure

This procedure changes the password of the currently authenticated user, assuming HTML DB user accounts are in use.

Syntax

HTMLDB_UTIL.CHANGE_CURRENT_USER_PW(
    p_new_password IN VARCHAR2);

Parameters

Table 16-1 describes the parameters available in the CHANGE_CURRENT_USER_PW procedure.

Example

BEGIN
HTMLDB_UTIL.CHANGE_CURRENT_USER_PW ('secret99');
END;

CLEAR_APP_CACHE Procedure

This procedure removes session state for a given application for the current session.

Syntax

HTMLDB_UTIL.CLEAR_APP_CACHE (
    p_app_id    IN    VARCHAR2 DEFAULT NULL);

Parameters

p_app_id is the ID of the application for which session state will be cleared for current session.

Example

BEGIN
        HTMLDB_UTIL.CLEAR_APP_CACHE('100');
END;

CLEAR_PAGE_CACHE Procedure

This procedure removes session state for a given page for the current session.

Syntax

HTMLDB_UTIL.CLEAR_PAGE_CACHE (
    p_page_id IN NUMBER DEFAULT NULL);

Parameters

p_page_id is the ID of the page in the current application for which session state will be cleared for current session.

Example

BEGIN
HTMLDB_UTIL.CLEAR_PAGE_CACHE('10');
END;

CLEAR_USER_CACHE Procedure

This procedure removes session state and application system preferences for the current user's session. Run this procedure if you reuse session IDs and want to run applications without the benefit of existing session state.

Syntax

HTMLDB_UTIL.CLEAR_USER_CACHE;

Example

BEGIN
       HTMLDB_UTIL.CLEAR_USER_CACHE;
END;

COUNT_CLICK Procedure

This procedure counts clicks from an Oracle HTML DB application to an external site. You can also use the shorthand version procedure Z in place of HTMLDB_UTIL.COUNT_CLICK.

Syntax

HTMLDB_UTIL.COUNT_CLICK (
    p_url         IN    VARCHAR2,
    p_cat         IN    VARCHAR2,
    p_id          IN    VARCHAR2    DEFAULT NULL,
    p_user        IN    VARCHAR2    DEFAULT NULL,
    p_workspace   IN    VARCHAR2    DEFAULT NULL);

Parameters

Table 16-2 describes the parameters available in the COUNT_CLICK procedure.

Example

BEGIN
htp.p('<a href=HTMLDB_UTIL.COUNT_CLICK?p_url=http://yahoo.com&p_cat=yahoo&p_workspace=NNN> Click</a>'); end; 

Where NNN equals your workspace ID.

CREATE_USER Procedure

This procedure creates a new account record in the HTML DB user account table. To execute this procedure, the current user must have administrative privileges.

Syntax

HTMLDB_UTIL.CREATE_USER(
    p_user_id                      NUMBER                  IN     DEFAULT NULL
    p_user_name                    VARCHAR2                IN
    p_first_name                   VARCHAR2                IN     DEFAULT NULL
    p_last_name                    VARCHAR2                IN     DEFAULT NULL
    p_description                  VARCHAR2                IN     DEFAULT NULL
    p_email_address                VARCHAR2                IN     DEFAULT NULL
    p_web_password                 VARCHAR2                IN
    p_web_password_format          VARCHAR2                IN     DEFAULT NULL
    p_group_ids                    VARCHAR2                IN     DEFAULT NULL
    p_developer_privs              VARCHAR2                IN     DEFAULT NULL
    p_default_schema               VARCHAR2                IN     DEFAULT NULL
    p_allow_access_to_schemas      VARCHAR2                IN     DEFAULT NULL
    p_attribute_01                 VARCHAR2                IN     DEFAULT NULL
    p_attribute_02                 VARCHAR2                IN     DEFAULT NULL
    p_attribute_03                 VARCHAR2                IN     DEFAULT NULL
    p_attribute_04                 VARCHAR2                IN     DEFAULT NULL
    p_attribute_05                 VARCHAR2                IN     DEFAULT NULL
    p_attribute_06                 VARCHAR2                IN     DEFAULT NULL
    p_attribute_07                 VARCHAR2                IN     DEFAULT NULL
    p_attribute_08                 VARCHAR2                IN     DEFAULT NULL
    p_attribute_09                 VARCHAR2                IN     DEFAULT NULL
    p_attribute_10                 VARCHAR2                IN     DEFAULT NULL)

Parameters

Table 16-3 describes the parameters available in CREATE_USER procedure.

Example

BEGIN
HTMLDB_UTIL.CREATE_USER 
    P_USER_NAME    => 'NEWUSER1',
    P_WEB_PASSWORD => 'secret99'); 
END;

CREATE_USER_GROUP Procedure

This procedure changes the password of the currently authenticated user, assuming HTML DB user accounts are in use. To execute this procedure, the current user must have administrative privilege in the workspace.

Syntax

HTMLDB_UTIL.CREATE_USER_GROUP(
    p_id                       NUMBER                  IN
    p_group_name               VARCHAR2                IN
    p_security_group_id        NUMBER                  IN
    p_group_desc               VARCHAR2                IN);

Parameter

Table 16-4 describes the parameters available in the CREATE_USER_GROUP procedure.

Example

BEGINHTMLDB_UTIL.CREATE_USER_GROUP (
    p_id                => 0 - trigger will assign PK,
    p_group_name        => 'Managers',
    p_security_group_id => null, -- defaults to current workspace ID
    p_group_desc        => 'text');
END;

CURRENT_USER_IN_GROUP Function

This function returns a Boolean result based on whether the current user is a member of the specified group. You may use the group name or group ID to identify the group.

Syntax

HTMLDB_UTIL.CURRENT_USER_IN_GROUP(
    p_group_name    IN VARCHAR2)
RETURN BOOLEAN;

HTMLDB_UTIL.CURRENT_USER_IN_GROUP(
    p_group_id    IN NUMBER)
RETURN BOOLEAN;

Parameters

Table 16-5 describes the parameters available in CURRENT_USER_IN_GROUP function.

Example

DECLARE VAL BOOLEAN;
BEGIN
  VAL := HTMLDB_UTIL.CURRENT_USER_IN_GROUP(p_group_name=>'Managers');
END;

EDIT_USER Procedure

This procedure enables a user account record to be altered. To execute this procedure, the current user must have administrative privileges in the workspace.

Syntax

EDIT_USER (
    p_user_id                      NUMBER                  IN
    p_user_name                    VARCHAR2                IN
    p_first_name                   VARCHAR2                IN     DEFAULT
    p_last_name                    VARCHAR2                IN     DEFAULT
    p_web_password                 VARCHAR2                IN     DEFAULT
    p_new_password                 VARCHAR2                IN     DEFAULT
    p_email_address                VARCHAR2                IN     DEFAULT
    p_start_date                   VARCHAR2                IN     DEFAULT
    p_end_date                     VARCHAR2                IN     DEFAULT
    p_employee_id                  VARCHAR2                IN     DEFAULT
    p_allow_access_to_schemas      VARCHAR2                IN     DEFAULT
    p_person_type                  VARCHAR2                IN     DEFAULT
    p_default_schema               VARCHAR2                IN     DEFAULT
    p_group_idS                    VARCHAR2                IN     DEFAULT
    P_DEVELOPER_ROLES              VARCHAR2                IN     DEFAULT
    P_DESCRIPTION                  VARCHAR2                IN     DEFAULTIN);

Parameters

Table 16-6 describes the parameters available in EDIT_USER procedure.

EXPORT_USERS Procedure

When called from an Oracle HTML DB page, this procedure produces an export file of the current workspace definition, workspace users, and workspace groups. To execute this procedure, the current user must have administrative privilege in the workspace.

Syntax

HTMLDB_UTIL.EXPORT_USERS(
    p_export_format in VARCHAR2 DEFAULT 'UNIX')

Parameters

Table 16-7 describes the parameters available in EXPORT_USERS procedure.

Example

BEGIN
  HTMLDB_UTIL.EXPORT_USERS;
END;

FETCH_APP_ITEM Function

This function fetches session state for the current or specified application in the current or specified session.

Syntax

HTMLDB_UTIL.FETCH_APP_ITEM(
    p_item    IN VARCHAR2,
    p_app     IN NUMBER DEFAULT NULL,
    p_session IN NUMBER DEFAULT NULL)
RETURN VARCHAR2;

Parameters

Table 16-8 describes the parameters available in the FETCH_APP_ITEM function.

Example

DECLARE VAL VARCHAR2(30);
BEGIN
VAL := HTMLDB_UTIL.FETCH_APP_ITEM (p_item=>'F300_NAME',p_app=>300);
END;

FETCH_USER Procedure

This procedure fetches a user account record. To execute this procedure, the current user must have administrative privileges in the workspace.

Syntax

FETCH_USER (
    P_USER_ID                      NUMBER                  IN
    P_WORKSPACE                    VARCHAR2                OUT
    P_USER_NAME                    VARCHAR2                OUT
    P_FIRST_NAME                   VARCHAR2                OUT
    P_LAST_NAME                    VARCHAR2                OUT
    P_WEB_PASSWORD                 VARCHAR2                OUT
    P_EMAIL_ADDRESS                VARCHAR2                OUT
    P_START_DATE                   VARCHAR2                OUT
    P_END_DATE                     VARCHAR2                OUT
    P_EMPLOYEE_ID                  VARCHAR2                OUT
    P_ALLOW_ACCESS_TO_SCHEMAS      VARCHAR2                OUT
    P_PERSON_TYPE                  VARCHAR2                OUT
    P_DEFAULT_SCHEMA               VARCHAR2                OUT
    P_GROUPS                       VARCHAR2                OUT
    P_DEVELOPER_ROLE               VARCHAR2                OUT);

Parameters

Table 16-9 describes the parameters available in the FETCH_USER procedure.

FIND_SECURITY_GROUP_ID Function

This function returns the numeric security group ID of the named workspace.

Syntax

HTMLDB_UTIL.FIND_SECURITY_GROUP_ID(
    p_workspace    IN VARCHAR2
RETURN NUMBER;

Parameters

p_workspace is the name of the workspace.

Example

DECLARE VAL NUMBER;
BEGIN
  VAL := HTMLDB_UTIL.FIND_SECURITY_GROUP_ID (p_workspace=>'DEMOS');
END;

FIND_WORKSPACE Function

This function returns the workspace name associated with a security group ID.

Syntax

HTMLDB_UTIL.FIND_WORKSPACE(
    p_security_group_id    IN VARCHAR2)
RETURN VARCHAR2;

Parameters

p_security_group_id is the security group ID of a workspace.

Example

DECLARE VAL NUMBER;
BEGIN
  VAL := HTMLDB_UTIL.FIND_ FIND_WORKSPACE (p_security_group_id =>'20');
END;

GET_ATTRIBUTE Function

This function returns the value of one of the attribute values (1 through 10) of a named user in the HTML DB accounts table.

Syntax

HTMLDB_UTIL.GET_ATTRIBUTE(
    p_username                IN VARCHAR2
    p_attribute_number        IN NUMBER)
RETURN VARCHAR2;

Parameters

Table 16-10 describes the parameters available in the GET_ATTRIBUTE function.

Example

DECLARE VAL VARCHAR2(30);
BEGIN
  VAL := HTMLDB_UTIL.GET_ATTRIBUTE (
                          p_username => 'SCOTT',
                          p_attribute_number => 1);
END;

GET_CURRENT_USER_ID Function

This function returns the numeric user ID of the current user.

Syntax

HTMLDB_UTIL.GET_CURRENT_USER_ID;
RETURN NUMBER;

Example

DECLARE VAL NUMBER;
BEGIN
  VAL := HTMLDB_UTIL.GET_CURRENT_USER_ID;
END;

GET_DEFAULT_SCHEMA Function

This function returns the default schema name associated with the current user.

Syntax

HTMLDB_UTIL.GET_DEFAULT_SCHEMA;
RETURN VARCHAR2;

Example

DECLARE VAL VARCHAR2;
BEGIN
  VAL := HTMLDB_UTIL. GET_DEFAULT_SCHEMA;
END;

GET_EMAIL Function

This function returns the e-mail address associated with the named user.

Syntax

HTMLDB_UTIL.GET_EMAIL(
   p_username IN VARCHAR2);
RETURN VARCHAR2;

Parameters

p_username is the user name in the account.

Example

DECLARE VAL VARCHAR2;
BEGIN
  VAL := HTMLDB_UTIL.GET_EMAIL(p_username => 'SCOTT');
END;

GET_FILE Procedure

This procedure downloads files from the Oracle HTML DB file repository.

Syntax

HTMLDB_UTIL.GET_FILE (
    p_file_id    IN   VARCHAR2,
    p_mime_type  IN   VARCHAR2 DEFAULT NULL,
    p_inline     IN   VARCHAR2 DEFAULT 'NO');

Parameters

Table 16-11 describes the parameters available in GET_FILE procedure.

DECLARE
    l_file_id NUMBER;
BEGIN
        SELECT id INTO l_file_id FROM HTMLDB_APPLICATION_FILES
WHERE filename = 'myxml';
        --
        HTMLDB_UTIL.GET_FILE(
              p_file_id   => l_file_id, 
              p_mime_type => 'text/xml',
              p_inline    => 'YES');  
END;

Example

BEGIN
        HTMLDB_UTIL.GET_FILE(
              p_file_id   => '8675309', 
              p_mime_type => 'text/xml',
              p_inline    => 'YES');    
END;

GET_FILE_ID Function

This function obtains the primary key of a file in the Oracle HTML DB file repository.

Syntax

HTMLDB_UTIL.GET_FILE_ID (
    p_fname    IN   VARCHAR2)
RETURN NUMBER;

Parameters

p_fname is NAME in HTMLDB_APPLICATION_FILES of the file to be downloaded. HTMLDB_APPLICATION_FILES is a view on all files uploaded to your workspace

Example

DECLARE
        l_name VARCHAR2(255);
        l_file_id NUMBER;
BEGIN
        SELECT name INTO l_name FROM HTMLDB_APPLICATION_FILES
        WHERE filename = 'F125.sql';
--
        l_file_id := HTMLDB_UTIL.GET_FILE_ID(p_fname => );
END;

GET_FIRST_NAME Function

This function returns the FIRST_NAME field stored in the named user account record.

Syntax

HTMLDB_UTIL.GET_FIRST_NAME
   p_username IN VARCHAR2);
RETURN VARCHAR2;

Parameters

p_username identifies the user name in the account.

Example

DECLARE VAL VARCHAR2;
BEGIN
  VAL := HTMLDB_UTIL.GET_FIRST_NAME(p_username => 'SCOTT');
END;

GET_GROUPS_USER_BELONGS_TO Function

This function returns a colon separated list of group names to which the named user is a member.

Syntax

HTMLDB_UTIL.GET_GROUPS_USER_BELONGS_TO(
   p_username IN VARCHAR2);
RETURN VARCHAR2;

Parameters

p_username identifies the user name in the account.

Example

DECLARE VAL VARCHAR2;
BEGIN
  VAL := HTMLDB_UTIL.GET_GROUPS_USER_BELONGS_TO(p_username => 'SCOTT');
END;

GET_GROUP_ID Function

This function returns the numeric ID of a named group in the workspace.

Syntax

HTMLDB_UTIL.GET_GROUP_ID(
   p_group_name);
RETURN VARCHAR2;

Parameters

p_group_name identifies the user name in the account.

Example

DECLARE VAL NUMBER;
BEGIN
  VAL := HTMLDB_UTIL.GET_GROUP_ID(p_group_name => 'Managers');
END;

GET_GROUP_NAME Function

This function returns the name of a group identified by a numeric ID.

Syntax

HTMLDB_UTIL.GET_GROUP_NAME(
   p_group_id);
RETURN NUMBER;

Parameters

p_group_id identifies a numeric ID of a group in the workspace.

Example

DECLARE VAL VARCHAR2;
BEGIN
  VAL := HTMLDB_UTIL.GET_GROUP_NAME(p_group_id => 8922003);
END;

GET_LAST_NAME Function

This function returns the LAST_NAME field stored in the named user account record.

Syntax

HTMLDB_UTIL.GET_LAST_NAME(
   p_username IN VARCHAR2);
RETURN VARCHAR2;

Parameters

p_username is the user name in the user account record.

Example

DECLARE VAL VARCHAR2;
BEGIN
  VAL := HTMLDB_UTIL.GET_LAST_NAME(p_username => 'SCOTT');
END;

GET_USERNAME Function

This function returns the user name of a user account identified by a numeric ID.

Syntax

HTMLDB_UTIL.GET_USERNAME(
   p_userid);
RETURN NUMBER;

Parameters

p_userid identifies the numeric ID of a user account in the workspace.

Example

DECLARE VAL VARCHAR2;
BEGIN
  VAL := HTMLDB_UTIL.GET_USERNAME(p_userid => 228922003);
END;

GET_NUMERIC_SESSION_STATE Function

This function returns a numeric value for a numeric item. You can use this function in Oracle HTML DB applications wherever you can use PL/SQL or SQL. You can also use the shorthand, function NV, in place of HTMLDB_UTIL.GET_NUMERIC_SESSION_STATE.

Syntax

HTMLDB_UTIL.GET_NUMERIC_SESSION_STATE (
    p_item     IN VARCHAR2) 
    RETURN NUMBER;

Parameters

p_item is the case insensitive name of the item for which you want to have the session state fetched.

Example

DECLARE
      l_item_value    Number;
BEGIN
      l_item_value := HTMLDB_UTIL.GET_NUMERIC_SESSION_STATE('my_item');
END;

GET_PREFERENCE Function

This function retrieves the value of a previously saved preference for a given user.

Syntax

HTMLDB_UTIL.GET_PREFERENCE (
    p_preference  IN    VARCHAR2 DEFAULT NULL,
    p_user        IN    VARCHAR2 DEFAULT V('USER')) 
    RETURN VARCHAR2;

Parameters

Table 16-12 describes the parameters available in the GET_PREFERENCE function.

Example

DECLARE
      l_default_view    VARCHAR2(255);
BEGIN
      l_default_view := HTMLDB_UTIL.GET_PREFERENCE(      
                   p_preference => 'default_view',
                   p_user       => :APP_USER);
END;

GET_SESSION_STATE Function

This function returns the value for an item. You can use this function in your Oracle HTML DB applications wherever you can use PL/SQL or SQL. You can also use the shorthand, function V, in place of HTMLDB_UTIL.GET_SESSION_STATE.

Syntax

HTMLDB_UTIL.GET_SESSION_STATE (
    p_item    IN   VARCHAR2) 
    RETURN VARCHAR2;

Parameters

p_item is the case insensitive name of the item for which you want to fetch session state.

Example

DECLARE
      l_item_value  VARCHAR2(255);
BEGIN
      l_item_value := HTMLDB_UTIL.GET_SESSION_STATE('my_item');
END;

GET_USER_ID Function

This function returns the numeric ID of a named user in the workspace.

Syntax

HTMLDB_UTIL.GET_USER_ID(
   p_username);
RETURN VARCHAR2;

Parameters

p_username identifies the name of a user in the workspace.

Example

DECLARE VAL NUMBER;
BEGIN  VAL := HTMLDB_UTIL.GET_USER_ID(p_username => 'Managers');END;

GET_USER_ROLES Function

This function returns the DEVELOPER_ROLE field stored in the named user account record.

Syntax

HTMLDB_UTIL.GET_USER_ROLES(
   p_username IN VARCHAR2);
RETURN VARCHAR2;

Parameters

p_username identifies a user name in the account.

Example

DECLARE VAL VARCHAR2;
BEGIN
  VAL := HTMLDB_UTIL.GET_USER_ROLES(p_username=>'SCOTT');
END;

IS_LOGIN_PASSWORD_VALID Function

This function returns a Boolean result based on the validity of the password for a named user account in the current workspace. Returns true if the password matches and false if the password does not match.

Syntax

HTMLDB_UTIL.IS_LOGIN_PASSWORD_VALID(
   p_username IN VARCHAR2,
   p_password IN VARCHAR2);
RETURN BOOLEAN;

Parameters

Table 16-13 describes the parameters available in the IS_LOGIN_PASSWORD_VALID function.

Example

DECLARE VAL BOOLEAN;
BEGIN
  VAL := HTMLDB_UTIL. IS_LOGIN_PASSWORD_VALID (
             p_username=>'SCOTT'
             p_password=>'tiger');
END;

IS_USERNAME_UNIQUE Function

This function returns a Boolean result based on whether the named user account is unique in the workspace.

Syntax

HTMLDB_UTIL.IS_USERNAME_UNIQUE(
   P_username IN VARCHAR2);
RETURN BOOLEAN;

Parameters

p_username identifies the user name to be tested.

Example

DECLARE VAL BOOLEAN;
BEGIN
  VAL := HTMLDB_UTIL.IS_USERNAME_UNIQUE(
             p_username=>'SCOTT');
END;

KEYVAL_NUM Function

This function gets the value of the package variable (wwv_flow_utilities.g_val_num) set by HTMLDB_UTIL.SAVEKEY_NUM.

Syntax

HTMLDB_UTIL.KEYVAL_NUM;

Parameters

p_val is the numeric value previously saved.

Example

DECLARE
BEGIN
    VAL := HTMLDB_UTIL.KEYVAL_NUM;
END;

KEYVAL_VC2 Function

This function gets the value of the package variable (wwv_flow_utilities.g_val_vc2) set by HTMLDB_UTIL.SAVEKEY_VC2.

Syntax

HTMLDB_UTIL.KEYVAL_VC2;

Parameters

p_val is the VARCHAR2 value previously saved.

Example

DECLARE
VAL VARCHAR2(4000);
BEGIN
    VAL := HTMLDB_UTIL.KEYVAL_VC2;

END;

PREPARE_URL Function

Given a ready-to-render f?p relative URL, this function adds a Session State Protection checksum argument (&cs=) if one is required.

Syntax

HTMLDB_UTIL.PREPARE_URL (
   p_url IN VARCHAR2
   p_url_charset IN VARCHAR2 default null,
   p_checksum_type IN VARCHAR2 default null)
RETURN VARCHAR2;

Parameters

Table 16-14 describes the parameters available in the PREPARE_URL function.

Example

DECLARE
l_url varchar2(2000);
l_session number := v('APP_SESSION');
BEGIN
l_url := 
HTMLDB_UTIL.PREPARE_URL('f?p=100:1:'||l_session||'::NO::P1_ITEM:xyz');
END;

PUBLIC_CHECK_AUTHORIZATION Function

Given the name of a security scheme, this function determines if the current user passes the security check.

Syntax

HTMLDB_UTIL.PUBLIC_CHECK_AUTHORIZATION (
    p_security_scheme    IN    VARCHAR2) 
    RETURN BOOLEAN;

Parameters

p_security_name is the name of the security scheme that determines if the user passes the security check.

Example

DECLARE
      l_check_security  BOOLEAN;
BEGIN
      l_check_security := HTMLDB_UTIL.PUBLIC_CHECK_AUTHORIZATION('my_auth_scheme');
END;

REMOVE_PREFERENCE Procedure

This function removes the preference for the supplied user.

Syntax

HTMLDB_UTIL.REMOVE_PREFERENCE(
    p_preference    IN    VARCHAR2 DEFAULT NULL,
    p_user          IN    VARCHAR2 DEFAULT V('USER'));

Parameters

Table 16-15 describes the parameters available in the REMOVE_PREFERENCE procedure.

Example

BEGIN
       HTMLDB_UTIL.REMOVE_PREFERENCE(
                    p_preference => 'default_view',
                    p_user       => :APP_USER);    
END;

REMOVE_SORT_PREFERENCES Procedure

This procedure removes the user's column heading sorting preference value.

Syntax

HTMLDB_UTIL.REMOVE_SORT_PREFERENCES (
    p_user  IN 	VARCHAR2 DEFAULT V('USER'));

Parameters

p_user identifies the user for whom sorting preferences will be removed.

Example

BEGIN
      HTMLDB_UTIL.REMOVE_SORT_PREFERENCES(:APP_USER);
END;

REMOVE_USER Procedure

This procedure removes the user account identified by the primary key or a user name. To execute this procedure, the current user must have administrative privilege in the workspace.

Syntax

HTMLDB_UTIL.REMOVE_USER(
    p_user_id IN NUMBER,
    p_user_name IN VARCHAR2);

Parameters

Table 16-16 describes the parameters available in the REMOVE_USER procedure.

Example

BEGIN
HTMLDB_UTIL.REMOVE_USER(p_user_id=>'99997');
END;

BEGIN
HTMLDB_UTIL.REMOVE_USER(p_user_name => 'SCOTT');
END;

RESET_PW Procedure

This procedure resets the password for a named user and emails it in a message to the e-mail address located for the named account in the current workspace. To execute this procedure, the current user must have administrative privilege in the workspace.

Syntax

HTMLDB_UTIL.RESET_PW(
    p_user IN VARCHAR2,
    p_msg  IN VARCHAR2);

Parameters

Table 16-17describes the parameters available in the RESET_PW procedure.

Example

BEGIN
HTMLDB_UTIL.RESET_PW(
    p_user => 'SCOTT',
    p_msg => 'Contact help desk at 555-1212 with questions');
END;

RESET_AUTHORIZATIONS Procedure

To increase performance, Oracle HTML DB caches the results of authorization schemes after they have been evaluated. You can use this procedure to undo caching thus requiring each authorization scheme be revalidated when it is next encountered during page show or accept processing. You can use this procedure if you want users to have the ability to change their responsibilities (their authorization profile) within your application.

Syntax

HTMLDB_UTIL.RESET_AUTHORIZATIONS; 

Example

BEGIN
HTMLDB_UTIL.RESET_AUTHORIZATIONS;
END;

SAVEKEY_NUM Function

This function sets a package variable (wwv_flow_utilities.g_val_num) so that it can be retrieved using the function KEYVAL_NUM.

Syntax

HTMLDB_UTIL.SAVEKEY_NUM( 
    p_val IN NUMBER);

Parameters

p_val is the numeric value to be saved.

Example

DECLARE
VAL NUMBER;
BEGIN
    VAL := HTMLDB_UTIL.SAVEKEY_NUM(
        p_val => 10);
END;

SAVEKEY_VC2 Function

This function sets a package variable (wwv_flow_utilities.g_val_vc2) so that it can be retrieved using the function KEYVAL_VC2.

Syntax

HTMLDB_UTIL.SAVEKEY_VC2
    p_val IN VARCHAR2);

Parameters

p_val is the VARCHAR2 value to be saved.

Example

DECLARE
VAL VARCHAR2(4000);
BEGIN
    VAL := HTMLDB_UTIL.SAVEKEY_VC2(
        p_val => 'XXX');
END;

SET_ATTRIBUTE Procedure

This procedure sets the value of one of the attribute values (1 through 10) of a user in the HTML DB accounts table.

Syntax

HTMLDB_UTIL.SET_ATTRIBUTE( 
    p_userid IN NUMBER, 
    p_attribute_number IN NUMBER,
    p_attribute_value  IN VARCHAR2); 

Parameters

Table 16-18 describes the parameters available in the SET_ATTRIBUTE procedure.

Example

DECLARE VAL VARCHAR2(30); 
BEGIN 
    HTMLDB_UTIL.SET_ATTRIBUTE ( 
        p_userid => htmldb_util.get_user_id(p_username => 'SCOTT'), 
        p_attribute_number => 1, 
        p_attribute_value => 'foo'); 
END; 

SET_EMAIL Procedure

This procedure updates a user account with a new e-mail address. To execute this procedure, the current user must have administrative privileges in the workspace.

Syntax

HTMLDB_UTIL.SET_EMAIL(
    p_userid IN NUMBER,
    p_email  IN VARCHAR2);

Parameters

Table 16-19 describes the parameters available in the SET_EMAIL procedure.

Example

BEGIN
HTMLDB_UTIL.SET_EMAIL(
    p_userid  => '888883232',
    P_email   => 'scott.scott@oracle.com');
END;

SET_FIRST_NAME Procedure

This procedure updates a user account with a new FIRST_NAME value. To execute this procedure, the current user must have administrative privileges in the workspace.

Syntax

HTMLDB_UTIL.SET_FIRST_NAME(
    p_userid      IN NUMBER,
    p_first_name  IN VARCHAR2);

Parameters

Table 16-21 describes the parameters available in the SET_FIRST_NAME procedure.

Example

BEGIN	
HTMLDB_UTIL.SET_FIRST_NAME(
    p_userid       => '888883232',
    P_first_name   => 'Scott');
END;

SET_LAST_NAME Procedure

This procedure updates a user account with a new LAST_NAME value. To execute this procedure, the current user must have administrative privileges in the workspace.

Syntax

HTMLDB_UTIL.SET_LAST_NAME(
    p_userid      IN NUMBER,
    p_last_name  IN VARCHAR2);

Parameters

Table 16-21 describes the parameters available in the SET_LAST_NAME procedure.

Example

BEGIN	
HTMLDB_UTIL.SET_LAST_NAME(
    p_userid       => '888883232',
    p_last_name   => 'SMITH');
END;

SET_USERNAME Procedure

This procedure updates a user account with a new USER_NAME value. To execute this procedure, the current user must have administrative privileges in the workspace.

Syntax

HTMLDB_UTIL.USERNAME(
    p_userid      IN NUMBER,
    p_username    IN VARCHAR2);

Parameters

Table 16-22 describes the parameters available in the SET_USERNAME procedure.

Example

BEGIN	
HTMLDB_UTIL.SET_USERNAME(
    p_userid       => '888883232',
    P_username   => 'USER-XRAY');
END;

SET_PREFERENCE Procedure

This procedure sets a preference that will persist beyond the user's current session.

Syntax

HTMLDB_UTIL.SET_PREFERENCE (
    p_preference   IN    VARCHAR2 DEFAULT NULL,
    p_value        IN    VARCHAR2 DEFAULT NULL,
    p_user         IN    VARCHAR2 DEFAULT NULL);

Parameters

Table 16-23 describes the parameters available in the SET_PREFERENCE procedure.

Example

BEGIN
       HTMLDB_UTIL.SET_PREFERENCE(        
             p_preference => 'default_view',
             p_value      => 'WEEKLY',      
             p_user       => :APP_USER); 
END;

SET_SESSION_STATE Procedure

This procedure sets session state for a current Oracle HTML DB session.

Syntax

HTMLDB_UTIL.SET_SESSION_STATE (
    p_name     IN    VARCHAR2 DEFAULT NULL,
    p_value    IN    VARCHAR2 DEFAULT NULL);

Parameters

Table 16-24 describes the parameters available in the SET_SESSION_STATE procedure.

Example

BEGIN
HTMLDB_UTIL.SET_SESSION_STATE('my_item','myvalue');
END;

STRING_TO_TABLE Function

Given a string, this function returns a PL/SQL array of type HTMLDB_APPLICATION_GLOBAL.VC_ARR2. This array is a VARCHAR2(32767) table.

Syntax

HTMLDB_UTIL.STRING_TO_TABLE (
    p_string       IN VARCHAR2,
    p_separator    IN VARCHAR2 DEFAULT ':') 
    RETURN HTMLDB_APPLICATION_GLOBAL.VC_ARR2;

Parameters

Table 16-25 describes the parameters available in the STRING_TO_TABLE function.

Example

DECLARE
       l_vc_arr2    HTMLDB_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
       l_vc_arr2 := HTMLDB_UTIL.STRING_TO_TABLE('One:Two:Three');
       FOR z IN 1..l_vc_arr2.count LOOP
             	htp.p(l_vc_arr2(z));
       END LOOP;
END;

TABLE_TO_STRING Function

Given a a PL/SQL table of type HTMLDB_APPLICATION_GLOBAL.VC_ARR2, this function returns a delimited string separated by the supplied separator, or by the default separator, a colon (:).

Syntax

HTMLDB_UTIL.TABLE_TO_STRING (
    p_table       IN     HTMLDB_APPLICATION_GLOBAL.VC_ARR2,
    p_string      IN     VARCHAR2 DEFAULT ':') 
    RETURN VARCHAR2;

Parameters

Table 16-26 describes the parameters available in the TABLE_TO_STRING function.

Example

DECLARE
       l_string     VARCHAR2(255);
       l_vc_arr2    HTMLDB_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
       l_vc_arr2 := HTMLDB_UTIL.STRING_TO_TABLE('One:Two:Three');

       l_string := HTMLDB_UTIL.TABLE_TO_STRING(l_vc_arr2);
END;

URL_ENCODE Function

This function encodes (into HEX) all special characters that include spaces, question marks, and ampersands.

Syntax

HTMLDB_UTIL.URL_ENCODE (
    p_url   IN    VARCHAR2) 
    RETURN VARCHAR2;

Parameters

p_url is the string you would like to have encoded.

Example

DECLARE
      l_url  VARCHAR2(255);
BEGIN
      l_url := HTMLDB_UTIL.URL_ENCODE('http://www.myurl.com?id=1&cat=foo');
END;