Oracle® Database Express Edition Application Express User's Guide Release 2.1 Part Number B25309-01 |
|
|
View PDF |
This section describes the APIs available in Oracle Application Express.
This section contains the following topics:
The HTMLDB_UTIL
package provides utilities you can use when programming in the Oracle Application Express environment. You can use the HTMLDB_UTIL
package to get and set session state, get files, check authorizations for users, reset different states for users, and also to get and set preferences for users.
Topics in this section include:
This procedure changes the password of the currently authenticated user, assuming Application Express user accounts are in use.
Syntax
HTMLDB_UTIL.CHANGE_CURRENT_USER_PW( p_new_password IN VARCHAR2);
Parameters
Table 15-1 describes the parameters available in the CHANGE_CURRENT_USER_PW
procedure.
Table 15-1 CHANGE_CURRENT_USER_PW Parameters
Parameter | Description |
---|---|
|
The new password value in clear text |
Example
BEGIN HTMLDB_UTIL.CHANGE_CURRENT_USER_PW ('secret99'); END;
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
Table 15-2 describes the parameters available in the CLEAR_APP_CACHE
procedure.
Table 15-2 CLEAR_APP_CACHE Parameters
Parameter | Description |
---|---|
|
The ID of the application for which session state will be cleared for current session |
Example
BEGIN HTMLDB_UTIL.CLEAR_APP_CACHE('100'); END;
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
Table 15-3 describes the parameters available in the CLEAR_APP_CACHE
procedure.
Table 15-3 CLEAR_PAGE_CACHE Parameters
Parameter | Description |
---|---|
|
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;
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;
Parameters
None.
Example
BEGIN HTMLDB_UTIL.CLEAR_USER_CACHE; END;
This procedure counts clicks from an application built in Application Builder 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 15-4 describes the parameters available in the COUNT_CLICK
procedure.
Table 15-4 COUNT_CLICK Parameters
Parameter | Description |
---|---|
|
The URL to which to redirect |
|
A category to classify the click |
|
Secondary ID to associate with the click (optional) |
|
The application user ID (optional) |
|
The workspace associated with the application (optional) |
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.
See Also:
"Purging the External Clicks Log"This procedure creates a new account record in the Application Express 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_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 15-5 describes the parameters available in the CREATE_USER
procedure.
Table 15-5 CREATE_USER Procedure Parameters
Parameter | Description |
---|---|
|
Numeric primary key of user account |
|
Alphanumeric name used for login |
|
Informational |
|
Informational |
|
Informational |
|
Email address |
|
Clear text password |
|
Colon separated list of numeric group IDs |
|
Arbitrary text accessible with an API |
Example
BEGIN HTMLDB_UTIL.CREATE_USER P_USER_NAME => 'NEWUSER1', P_WEB_PASSWORD => 'secret99'); END;
This procedure changes the password of the currently authenticated user, assuming Application Express 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 15-6 describes the parameters available in the CREATE_USER_GROUP
procedure.
Table 15-6 CREATE_USER_GROUP Parameters
Parameter | Description |
---|---|
|
Primary key of group |
|
Arbitrary name |
|
Workspace ID |
|
Descriptive text |
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;
This function returns a Boolean result based on whether or not the current user is a member of the specified group. You can 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 15-7 describes the parameters available in the CURRENT_USER_IN_GROUP
function.
Table 15-7 CURRENT_USER_IN_GROUP Parameters
Parameter | Description |
---|---|
|
Identifies the name of an existing group in the workspace |
|
Identifies the numeric ID of an existing group in the workspace |
Example
DECLARE VAL BOOLEAN; BEGIN VAL := HTMLDB_UTIL.CURRENT_USER_IN_GROUP(p_group_name=>'Managers'); END;
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 15-8 describes the parameters available in the EDIT_USER
procedure.
Table 15-8 EDIT_USER Parameters
Parameter | Description |
---|---|
|
Numeric primary key of the user account |
|
Alphanumeric name used for login |
|
Informational |
|
Informational |
|
Clear text password |
|
Unused |
|
Unused |
|
Unused |
|
A list of schemas assigned to the user's workspace to which the user is restricted |
|
Unused |
|
A database schema assigned to the user's workspace, used by default for browsing |
|
Colon-separated list of numeric group IDs |
|
Colon-separated list of developer privileges (only ADMIN: has meaning to Application Express) |
|
Informational |
When called from an 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 15-9 describes the parameters available in the EXPORT_USERS
procedure.
Table 15-9 EXPORT_USERS Parameters
Parameter | Description |
---|---|
|
Indicates how rows in the export file will be formatted. Specify |
Example
BEGIN HTMLDB_UTIL.EXPORT_USERS; END;
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 15-10 describes the parameters available in the FETCH_APP_ITEM function.
Table 15-10 FETCH_APP_ITEM Parameters
Parameter | Description |
---|---|
|
The name of an application-level item (not a page item) whose current value is to be fetched |
|
The ID of the application that owns the item (leave null for the current application) |
|
The session ID from which to obtain the value (leave null for the current session) |
Example
DECLARE VAL VARCHAR2(30); BEGIN VAL := HTMLDB_UTIL.FETCH_APP_ITEM (p_item=>'F300_NAME',p_app=>300); END;
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 15-11 describes the parameters available in the FETCH_USER
procedure.
Table 15-11 Fetch_User Parameters
Parameter | Description |
---|---|
|
Numeric primary key of the user account |
|
The name of the workspace |
|
Alphanumeric name used for login |
|
Informational |
|
Informational |
|
Informational |
|
Email address |
|
Unused |
|
Unused |
|
Unused |
|
A list of schemas assigned to the user's workspace to which user is restricted |
|
Unused |
|
A database schema assigned to the user's workspace, used by default for browsing |
|
Unused |
|
Unused |
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
Table 15-12 describes the parameters available in the FIND_SECURITY_GROUP_ID
function.
Table 15-12 FIND_SECURITY_GROUP_ID Parameters
Parameter | Description |
---|---|
|
The name of the workspace |
Example
DECLARE VAL NUMBER; BEGIN VAL := HTMLDB_UTIL.FIND_SECURITY_GROUP_ID (p_workspace=>'DEMOS'); END;
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
Table 15-13 describes the parameters available in the FIND_WORKSPACE
function.
Table 15-13 FIND_WORKSPACE Parameters
Parameter | Description |
---|---|
|
The security group ID of a workspace |
Example
DECLARE VAL NUMBER; BEGIN VAL := HTMLDB_UTIL.FIND_ FIND_WORKSPACE (p_security_group_id =>'20'); END;
This function returns the value of one of the attribute values (1 through 10) of a named user in the Application Express accounts table.
Syntax
HTMLDB_UTIL.GET_ATTRIBUTE( p_username IN VARCHAR2 p_attribute_number IN NUMBER) RETURN VARCHAR2;
Parameters
Table 15-14 describes the parameters available in the GET_ATTRIBUTE function.
Table 15-14 GET_ATTRIBUTE Parameters
Parameter | Description |
---|---|
|
User name in the account. |
|
Number of attributes in the user record (1 through 10) |
Example
DECLARE VAL VARCHAR2(30); BEGIN VAL := HTMLDB_UTIL.GET_ATTRIBUTE ( p_username => 'SCOTT', p_attribute_number => 1); END;
This function returns the numeric user ID of the current user.
Syntax
HTMLDB_UTIL.GET_CURRENT_USER_ID; RETURN NUMBER;
Parameters
None.
Example
DECLARE VAL NUMBER; BEGIN VAL := HTMLDB_UTIL.GET_CURRENT_USER_ID; END;
This function returns the default schema name associated with the current user.
Syntax
HTMLDB_UTIL.GET_DEFAULT_SCHEMA; RETURN VARCHAR2;
Parameters
None.
Example
DECLARE VAL VARCHAR2; BEGIN VAL := HTMLDB_UTIL. GET_DEFAULT_SCHEMA; END;
This function returns the email address associated with the named user.
Syntax
HTMLDB_UTIL.GET_EMAIL( p_username IN VARCHAR2); RETURN VARCHAR2;
Parameters
Table 15-15 describes the parameters available in GET_EMAIL
function.
Example
DECLARE VAL VARCHAR2; BEGIN VAL := HTMLDB_UTIL.GET_EMAIL(p_username => 'SCOTT'); END;
This procedure downloads files from the Oracle Application Express 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 15-16 describes the parameters available in GET_FILE
procedure.
Table 15-16 GET_FILE Parameters
Parameter | Description |
---|---|
|
ID in 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; |
|
Mime type of the file to download |
|
Valid values include |
Example
BEGIN HTMLDB_UTIL.GET_FILE( p_file_id => '8675309', p_mime_type => 'text/xml', p_inline => 'YES'); END;
This function obtains the primary key of a file in the Oracle Application Express file repository.
Syntax
HTMLDB_UTIL.GET_FILE_ID ( p_fname IN VARCHAR2) RETURN NUMBER;
Parameters
Table 15-17 describes the parameters available in GET_FILE_ID
function.
Table 15-17 GET_FILE_ID Parameters
Parameter | Description |
---|---|
|
The NAME in |
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;
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
Table 15-18 describes the parameters available in GET_FIRST_NAME
function.
Table 15-18 GET_FIRST_NAME Parameters
Parameter | Description |
---|---|
|
Identifies the user name in the account |
Example
DECLARE VAL VARCHAR2; BEGIN VAL := HTMLDB_UTIL.GET_FIRST_NAME(p_username => 'SCOTT'); END;
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
Table 15-19 describes the parameters available in GET_GROUPS_USER_BELONGS_TO
function.
Table 15-19 GET_GROUPS_USER_BELONGS_TO Parameters
Parameter | Description |
---|---|
|
Identifies the user name in the account |
Example
DECLARE VAL VARCHAR2; BEGIN VAL := HTMLDB_UTIL.GET_GROUPS_USER_BELONGS_TO(p_username => 'SCOTT'); END;
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
Table 15-20 describes the parameters available in GET_GROUP_ID
function.
Table 15-20 GET_GROUP_ID Parameters
Parameter | Description |
---|---|
|
Identifies the user name in the account |
Example
DECLARE VAL NUMBER; BEGIN VAL := HTMLDB_UTIL.GET_GROUP_ID(p_group_name => 'Managers'); END;
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
Table 15-21 describes the parameters available in GET_GROUP_NAME
function.
Table 15-21 GET_GROUP_NAME Parameters
Parameter | Description |
---|---|
|
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;
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
Table 15-22 describes the parameters available in GET_LAST_NAME
function.
Table 15-22 GET_LAST_NAME Parameters
Parameter | Description |
---|---|
|
The user name in the user account record |
Example
DECLARE VAL VARCHAR2; BEGIN VAL := HTMLDB_UTIL.GET_LAST_NAME(p_username => 'SCOTT'); END;
This function returns the user name of a user account identified by a numeric ID.
Syntax
HTMLDB_UTIL.GET_USERNAME( p_userid); RETURN VARCHAR2;
Parameters
Table 15-23 describes the parameters available in GET_USERNAME
function.
Table 15-23 GET_USERNAME Parameters
Parameter | Description |
---|---|
|
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;
This function returns a numeric value for a numeric item. You can use this function in Oracle Application Express 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
Table 15-24 describes the parameters available in GET_NUMERIC_SESSION_STATE
function.
Table 15-24 GET_NUMERIC_SESSION_STATE Parameters
Parameter | Description |
---|---|
|
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;
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 15-25 describes the parameters available in the GET_PREFERENCE
function.
Table 15-25 GET_PREFERENCE Parameters
Parameter | Description |
---|---|
|
Name of the preference to retrieve the value |
|
Value of the preference |
|
User for whom the preference is being retrieved |
Example
DECLARE l_default_view VARCHAR2(255); BEGIN l_default_view := HTMLDB_UTIL.GET_PREFERENCE( p_preference => 'default_view', p_user => :APP_USER); END;
This function returns the value for an item. You can use this function in your Oracle Application Express 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
Table 15-26 describes the parameters available in GET_SESSION_STATE
function.
Table 15-26 GET_SESSION_STATE Parameters
Parameter | Description |
---|---|
|
The case insensitive name of the item for which you want to have the session state fetched |
Example
DECLARE l_item_value VARCHAR2(255); BEGIN l_item_value := HTMLDB_UTIL.GET_SESSION_STATE('my_item'); END;
This function returns the numeric ID of a named user in the workspace.
Syntax
HTMLDB_UTIL.GET_USER_ID( p_username); RETURN VARCHAR2;
Parameters
Table 15-27 describes the parameters available in GET_USER_ID
function.
Table 15-27 GET_USER_ID Parameters
Parameter | Description |
---|---|
|
Identifies the name of a user in the workspace |
Example
DECLARE VAL NUMBER; BEGIN VAL := HTMLDB_UTIL.GET_USER_ID(p_username => 'Managers');END;
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
Table 15-28 describes the parameters available in GET_USER_ROLES
function.
Table 15-28 GET_USER_ROLES Parameters
Parameter | Description |
---|---|
|
Identifies a user name in the account |
Example
DECLARE VAL VARCHAR2; BEGIN VAL := HTMLDB_UTIL.GET_USER_ROLES(p_username=>'SCOTT'); END;
This function returns a Boolean result based on the validity of the password for a named user account in the current workspace. This function returns true if the password matches and it returns 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 15-29 describes the parameters available in the IS_LOGIN_PASSWORD_VALID
function.
Table 15-29 IS_LOGIN_PASSWORD_VALID Parameters
Parameter | Description |
---|---|
|
User name in account |
|
Password to be compared with password stored in the account |
Example
DECLARE VAL BOOLEAN; BEGIN VAL := HTMLDB_UTIL. IS_LOGIN_PASSWORD_VALID ( p_username=>'SCOTT' p_password=>'tiger'); END;
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
Table 15-30 describes the parameters available in IS_USERNAME_UNIQUE
function.
Table 15-30 IS_USERNAME_UNIQUE Parameters
Parameter | Description |
---|---|
|
Identifies the user name to be tested |
Example
DECLARE VAL BOOLEAN; BEGIN VAL := HTMLDB_UTIL.IS_USERNAME_UNIQUE( p_username=>'SCOTT'); END;
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
Table 15-31 describes the parameters available in KEYVAL_NUM
function.
Example
DECLARE BEGIN VAL := HTMLDB_UTIL.KEYVAL_NUM; END;
See Also:
"SAVEKEY_NUM 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;
See Also:
"SAVEKEY_VC2 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 15-32 describes the parameters available in the PREPARE_URL function.
Table 15-32 PREPARE_URL Parameters
Parameter | Description |
---|---|
p_url |
An f?p relative URL with all substitutions resolved |
p_url_charset |
The character set name (for example, |
p_checksum type |
Null or any of the following six values, |
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;
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
Table 15-33 describes the parameters available in PUBLIC_CHECK_AUTHORIZATION
function.
Table 15-33 PUBLIC_CHECK_AUTHORIZATION Parameters
Parameter | Description |
---|---|
|
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;
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 15-34 describes the parameters available in the REMOVE_PREFERENCE
procedure.
Table 15-34 REMOVE_PREFERENCE Parameters
Parameter | Description |
---|---|
|
Name of the preference to remove |
|
User for whom the preference is defined |
Example
BEGIN HTMLDB_UTIL.REMOVE_PREFERENCE( p_preference => 'default_view', p_user => :APP_USER); END;
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
Table 15-35 describes the parameters available in REMOVE_SORT_PREFERENCES
function.
Table 15-35 REMOVE_SORT_PREFERENCES Parameters
Parameter | Description |
---|---|
|
Identifies the user for whom sorting preferences will be removed |
Example
BEGIN HTMLDB_UTIL.REMOVE_SORT_PREFERENCES(:APP_USER); END;
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 15-36 describes the parameters available in the REMOVE_USER
procedure.
Table 15-36 REMOVE_USER Parameters
Parameter | Description |
---|---|
|
The numeric primary key of the user account record |
|
The the user name of the user account |
Example
BEGIN HTMLDB_UTIL.REMOVE_USER(p_user_id=>'99997'); END; BEGIN HTMLDB_UTIL.REMOVE_USER(p_user_name => 'SCOTT'); END;
This procedure resets the password for a named user and emails it in a message to the email 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 15-37 describes the parameters available in the RESET_PW
procedure.
Table 15-37 RESET_PW Parameters
Parameter | Description |
---|---|
|
The user name of the user account |
|
Message text to be mailed to a user |
Example
BEGIN HTMLDB_UTIL.RESET_PW( p_user => 'SCOTT', p_msg => 'Contact help desk at 555-1212 with questions'); END;
To increase performance, Oracle Application Express caches the results of authorization schemes after they have been evaluated. You can use this procedure to undo caching, 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;
Parameters
None.
Example
BEGIN HTMLDB_UTIL.RESET_AUTHORIZATIONS; END;
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
Table 15-38 describes the parameters available in the SAVEKEY_NUM
procedure.
Example
DECLARE VAL NUMBER; BEGIN VAL := HTMLDB_UTIL.SAVEKEY_NUM( p_val => 10); END;
See Also:
"KEYVAL_NUM 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
Table 15-39 describes the parameters available in the SAVEKEY_VC2
procedure.
Table 15-39 SAVEKEY_VC2 Parameters
Parameter | Description |
---|---|
|
The is the VARCHAR2 value to be saved |
Example
DECLARE VAL VARCHAR2(4000); BEGIN VAL := HTMLDB_UTIL.SAVEKEY_VC2( p_val => 'XXX'); END;
See Also:
"KEYVAL_VC2 Function"This procedure sets the value of one of the attribute values (1 through 10) of a user in the Application Express accounts table.
Syntax
HTMLDB_UTIL.SET_ATTRIBUTE( p_userid IN NUMBER, p_attribute_number IN NUMBER, p_attribute_value IN VARCHAR2);
Parameters
Table 15-40 describes the parameters available in the SET_ATTRIBUTE
procedure.
Table 15-40 SET_ATTRIBUTE Parameters
Parameter | Description |
---|---|
|
The numeric ID of the user account |
|
Attribute number in the user record (1 through 10) |
|
Value of the attribute located by |
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;
This procedure updates a user account with a new email 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 15-41 describes the parameters available in the SET_EMAIL
procedure.
Table 15-41 SET_EMAIL Parameters
Parameter | Description |
---|---|
|
The numeric ID of the user account |
|
The email address to be saved in user account |
Example
BEGIN HTMLDB_UTIL.SET_EMAIL( p_userid => '888883232', P_email => 'scott.scott@oracle.com'); END;
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 15-42 describes the parameters available in the SET_FIRST_NAME
procedure.
Table 15-42 SET_FIRST_NAME Parameters
Parameter | Description |
---|---|
|
The numeric ID of the user account |
|
|
Example
BEGIN HTMLDB_UTIL.SET_FIRST_NAME( p_userid => '888883232', P_first_name => 'Scott'); END;
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 15-43 describes the parameters available in the SET_LAST_NAME
procedure.
Table 15-43 SET_LAST_NAME Parameters
Parameter | Description |
---|---|
|
The numeric ID of the user account |
|
|
Example
BEGIN HTMLDB_UTIL.SET_LAST_NAME( p_userid => '888883232', p_last_name => 'SMITH'); END;
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 15-44 describes the parameters available in the SET_USERNAME
procedure.
Table 15-44 SET_USERNAME Parameters
Parameter | Description |
---|---|
|
The numeric ID of the user account |
|
|
Example
BEGIN HTMLDB_UTIL.SET_USERNAME( p_userid => '888883232', P_username => 'USER-XRAY'); END;
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 15-45 describes the parameters available in the SET_PREFERENCE
procedure.
Table 15-45 SET_PREFERENCE Parameters
Parameter | Description |
---|---|
|
Name of the preference (case-sensitive) |
|
Value of the preference |
|
User for whom the preference is being set |
Example
BEGIN HTMLDB_UTIL.SET_PREFERENCE( p_preference => 'default_view', p_value => 'WEEKLY', p_user => :APP_USER); END;
This procedure sets session state for a current Oracle Application Express session.
Syntax
HTMLDB_UTIL.SET_SESSION_STATE ( p_name IN VARCHAR2 DEFAULT NULL, p_value IN VARCHAR2 DEFAULT NULL);
Parameters
Table 15-46 describes the parameters available in the SET_SESSION_STATE
procedure.
Table 15-46 SET_SESSION_STATE Parameters
Parameter | Description |
---|---|
|
Name of the application-level or page-level item for which you are setting sessions state |
|
Value of session state to set |
Example
BEGIN HTMLDB_UTIL.SET_SESSION_STATE('my_item','myvalue'); END;
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 15-47 describes the parameters available in the STRING_TO_TABLE
function.
Table 15-47 STRING_TO_TABLE Parameters
Parameter | Description |
---|---|
|
String to be converted into a PL/SQL table of type |
|
String separator. The default is a colon |
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;
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 15-48 describes the parameters available in the TABLE_TO_STRING
function.
Table 15-48 TABLE_TO_STRING Parameters
Parameter | Description |
---|---|
|
String separator. Default separator is a colon (:) |
|
PL/SQL table that is to be converted into a delimited string |
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;
This function encodes (into hexadecimal) all special characters that include spaces, question marks, and ampersands.
Syntax
HTMLDB_UTIL.URL_ENCODE ( p_url IN VARCHAR2) RETURN VARCHAR2;
Parameters
Table 15-49 describes the parameters available in the URL_ENCODE
function.
Example
DECLARE l_url VARCHAR2(255); BEGIN l_url := HTMLDB_UTIL.URL_ENCODE('http://www.myurl.com?id=1&cat=foo'); END;
You can use the HTMLDB_MAIL
package to send an email from an Oracle Application Express application. This package is built on top of the Oracle supplied UTL_SMTP
package. Because of this dependence, the UTL_SMTP
package must be installed and functioning in order to use HTMLDB_MAIL.
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about the UTL_SMTP packageHTMLDB_MAIL
contains two procedures. Use HTMLDB_MAIL.SEND
to send an outbound email message from your application. Use HTMLDB_MAIL.PUSH_QUEUE
to deliver mail messages stored in HTMLDB_MAIL_QUEUE
.
Topics in this section include:
Note:
The most efficient approach to sending email is to create a background job (using a DBMS_JOB package) to periodically send all mail messages stored in the active mail queue.See Also:
"Sending Email from an Application"This procedure sends an outbound email message from an application. Although you can use this procedure to pass in either a VARCHAR2
or a CLOB
to p_body
and p_body_html
, the data types must be the same. In other words, you cannot pass a CLOB
to P_BODY
and a VARCHAR2
to p_body_html
.
When using HTMLDB_MAIL.SEND
, remember the following:
No single line may exceed 1000 characters. The SMTP/MIME specification dictates that no single line shall exceed 1000 characters. To comply with this restriction, you must add a carriage return or line feed characters to break up your p_body
or p_body_html
parameters into chunks of 1000 characters or less. Failing to do so will result in erroneous email messages, including partial messages or messages with extraneous exclamation points.
Plain text and HTML email content. Passing a value to p_body,
but not p_body_html
results in a plain text message. Passing a value to p_body
and p_body_html
yields a multi-part message that includes both plain text and HTML content. The settings and capabilities of the recipient's email client determine what displays. Although most modern email clients can read a HTML formatted email, remember that some users disable this functionality to address security issues.
Avoid images. When referencing images in p_body_html
using the <img />
tag, remember that the images must be accessible to the recipient's email client in order for them to see the image.
For example, suppose you reference an image on your network called hello.gif
as follows:
<img src="http://someserver.com/hello.gif" alt="Hello" />]
In this example, the image is not attached to the email, but is referenced by the email. For the recipient to see it, they must be able to access the image using a Web browser. If the image is inside a firewall and the recipient is outside of the firewall, the image will not display. For this reason, avoid using images. If you must include images, be sure to include the ALT attribute to provide a textual description in the event the image is not accessible.
Syntax
HTMLDB_MAIL.SEND( p_to IN VARCHAR2, p_from IN VARCHAR2, p_body IN [ VARCHAR2 | CLOB ], p_body_html IN [ VARCHAR2 | CLOB ] DEFAULT, p_subj IN VARCHAR2 DEFAULT) p_cc IN VARCHAR2 DEFAULT) p_bcc IN VARCHAR2 DEFAULT);
Parameters
Table 15-50 describes the parameters available in the SEND
procedure.
Table 15-50 Send Parameters
Parameter | Description |
---|---|
|
Valid email address to which the email will be sent (required). For multiple email addresses, use a comma separated list |
|
Email address from which the email will be sent (required). This email address must be a valid address. Otherwise, the message will not be sent |
|
Body of the email in plain text, not HTML (required). If a value is passed to |
|
Body of the email in HTML format. This must be a full HTML document including the |
|
Subject of the email |
|
Valid email addresses to which the email is copied. For multiple email addresses, use a comma separated list |
|
Valid email addresses to which the email is blind copied. For multiple email addresses, use a comma separated list |
Examples
The following example demonstrates how to use HTMLDB_MAIL.SEND
to send a plain text email message from an application.
-- Example One: Plain Text only message DECLARE l_body CLOB; BEGIN l_body := 'Thank you for your interest in the HTMLDB_MAIL package.'||utl_tcp.crlf||utl_tcp.crlf; l_body := l_body ||' Sincerely,'||utl_tcp.crlf; l_body := l_body ||' The HTMLDB Dev Team'||utl_tcp.crlf; htmldb_mail.send( p_to => 'some_user@somewhere.com', -- change to your email address p_from => 'some_sender@somewhere.com', -- change to a real senders email address p_body => l_body, p_subj => 'HTMLDB_MAIL Package - Plain Text message'); END; /
The following example demonstrates how to use HTMLDB_MAIL.SEND
to send a HTML email message from an application. Remember, you must include a carriage return or line feed (CRLF) every 1000 characters. The example that follows uses utl_tcp.crlf
.
-- Example Two: Plain Text / HTML message DECLARE l_body CLOB; l_body_html CLOB; BEGIN l_body := 'To view the content of this message, please use an HTML enabled mail client.'||utl_tcp.crlf; l_body_html := '<html> <head> <style type="text/css"> body{font-family: Arial, Helvetica, sans-serif; font-size:10pt; margin:30px; background-color:#ffffff;} span.sig{font-style:italic; font-weight:bold; color:#811919;} </style> </head> <body>'||utl_tcp.crlf; l_body_html := l_body_html ||'<p>Thank you for your interest in the <strong>HTMLDB_MAIL</strong> package.</p>'||utl_tcp.crlf; l_body_html := l_body_html ||' Sincerely,<br />'||utl_tcp.crlf; l_body_html := l_body_html ||' <span class="sig">The HTMLDB Dev Team</span><br />'||utl_tcp.crlf; htmldb_mail.send( p_to => 'some_user@somewhere.com', -- change to your email address p_from => 'some_sender@somewhere.com', -- change to a real senders email address p_body => l_body, p_body_html => l_body_html, p_subj => 'HTMLDB_MAIL Package - HTML formatted message'); END; /
Oracle Application Express stores unsent email messages in a table named HTMLDB_MAIL_QUEUE
. You can manually deliver mail messages stored in this queue to the specified SMTP gateway by invoking the HTMLDB_MAIL.PUSH_QUEUE
procedure.
Oracle Application Express logs successfully submitted message in the table HTMLDB_MAIL_LOG
with the timestamp reflecting your server's local time. Keep in mind, the most efficient approach to sending email is to create a background job (using a DBMS_JOB
package) to periodically send all mail messages stored in the active mail queue.
See Also:
"Sending Email Using a Background Job"Syntax
HTMLDB_MAIL.PUSH_QUEUE( p_smtp_hostname IN VARCHAR2 DEFAULT, p_smtp_portno IN NUMBER DEFAULT;
Parameters
Table 15-51 describes the parameters available in the PUSH_QUEUE
procedure.
Table 15-51 PUSH_QUEUE Parameters
Parameters | Description |
---|---|
|
SMTP gateway host name |
|
SMTP gateway port number |
Note that these parameter values are provided for backward compatibility, but their respective values are ignored. The SMTP gateway hostname and SMTP gateway port number are exclusively derived from the Application Express environment settings when sending e-mail.
See Also:
"Configuring Email Environment Settings"Example
The following example demonstrates the use of the HTMLDB_MAIL.PUSH_QUEUE
procedure using a shell script. This example only applies to UNIX/LINUX installations. In this example, the SMTP gateway host name is defined as smtp01.oracle.com
and the SMTP gateway port number is 25
.
SQLPLUS / <<EOF FLOWS_020100.HTMLDB_MAIL.PUSH_QUEUE('smtp01.oracle.com','25'); DISCONNECT EXIT EOF
See Also:
"Sending Email from an Application"You can use the HTMLDB_ITEM
package to create form elements dynamically based on a SQL query instead of creating individual items page by page.
Topics in this section include:
This function creates check boxes.
Syntax
HTMLDB_ITEM.CHECKBOX( p_idx IN NUMBER, p_value IN VARCHAR2 DEFAULT, p_attributes IN VARCHAR2 DEFAULT, p_checked_values IN VARCHAR2 DEFAULT, p_checked_values_delimiter IN VARCHAR2 DEFAULT) RETURN VARCHAR2;
Parameters
Table 15-52 describes the parameters available in the CHECKBOX
function.
Table 15-52 CHECKBOX Parameters
Parameter | Description |
---|---|
|
Number that determines which |
|
Value of a check box, hidden field, or input form item |
|
Controls HTML tag attributes (such as disabled) |
|
Values to be checked by default |
|
Delimits the values in the previous parameter, |
Examples of Default Check Box Behavior
The following example demonstrates how to create a selected check box for each employee in the emp
table.
SELECT HTMLDB_ITEM.CHECKBOX(1,empno,'CHECKED') " ", ename, job FROM emp ORDER BY 1
The following example demonstrates how to have all check boxes for employees display without being selected.
SELECT HTMLDB_ITEM.CHECKBOX(1,empno) " ", ename, job FROM emp ORDER BY 1
The following example demonstrates how to select the check boxes for employees who work in department 10.
SELECT HTMLDB_ITEM.CHECKBOX(1,empno,DECODE(deptno,10,'CHECKED',null)) " ", ename, job FROM emp ORDER BY 1
The next example demonstrates how to select the check boxes for employees who work in department 10 or department 20.
SELECT HTMLDB_ITEM.CHECKBOX(1,deptno,NULL,'10:20',':') " ", ename, job FROM emp ORDER BY 1
Creating an On-Submit Process
If you are using check boxes in your application, you might need to create an On Submit process to perform a specific type of action on the selected rows. For example, you could have a Delete button that utilizes the following logic:
SELECT HTMLDB_ITEM.CHECKBOX(1,empno) " ", ename, job FROM emp ORDER by 1
Consider the following sample on-submit process:
FOR I in 1..HTMLDB_APPLICATION.G_F01.COUNT LOOP DELETE FROM emp WHERE empno = to_number(HTMLDB_APPLICATION.G_F01(i)); END LOOP;
Use this function with forms that include date fields. The DATE_POPUP
function dynamically generates a date field that has a popup calendar button.
Syntax
HTMLDB_ITEM.DATE_POPUP( p_idx IN NUMBER, p_row IN NUMBER, p_value IN VARCHAR2 DEFAULT, p_date_format IN DATE DEFAULT, p_size IN NUMBER DEFAULT, p_maxlength IN NUMBER DEFAULT, p_attributes IN VARCHAR2 DEFAULT) RETURN VARCHAR2;
Parameters
Table 15-53 describes the parameters available in the DATE_POPUP
function.
Table 15-53 DATE_POPUP Parameters
Parameter | Description |
---|---|
|
Number that determines which |
|
This parameter is deprecated. Anything specified for this value will be ignored |
|
Value of a field item |
|
Valid database date format |
|
Controls HTML tag attributes (such as disabled) |
|
Determines the maximum number of enterable characters. Becomes the maxlength attribute of the |
|
Extra HTML parameters you want to add |
Example
The following example demonstrates how to use HTMLDB_ITEM.DATE_POPUP
to create popup calendar buttons for the hiredate
column.
SELECT empno, HTMLDB_ITEM.HIDDEN(1,empno)|| HTMLDB_ITEM.TEXT(2,ename) ename, HTMLDB_ITEM.TEXT(3,job) job, mgr, HTMLDB_ITEM.DATE_POPUP(4,rownum,hiredate,'dd-mon-yyyy') hd, HTMLDB_ITEM.TEXT(5,sal) sal, HTMLDB_ITEM.TEXT(6,comm) comm, deptno FROM emp ORDER BY 1
Use this function to display an item as text, but save its value to session state.
Syntax
HTMLDB_ITEM.DISPLAY_AND_SAVE( p_idx IN NUMBER, p_value IN VARCHAR2 DEFAULT NULL p_item_id IN VARCHAR2 DEFAULT NULL, p_item_label IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
Parameters
Table 15-54 describes the parameters available in the DISPLAY_AND_SAVE
function.
Table 15-54 DISPLAY_AND_SAVE Parameters
Parameter | Description |
---|---|
|
Number that determines which |
|
Current value |
|
HTML attribute ID for the |
|
Label of the text field item |
Example
The following example demonstrates how to use the HTMLDB_ITEM.DISPLAY_AND_SAVE
function.
SELECT HTMLDB_ITEM.DISPLAY_AND_SAVE(10,empno) c FROM emp
This function dynamically generates hidden form items.
Syntax
HTMLDB_ITEM.HIDDEN( p_idx IN NUMBER, p_value IN VARCHAR2 DEFAULT) RETURN VARCHAR2;
Parameters
Table 15-55 describes the parameters available in the HIDDEN
function.
Table 15-55 HIDDEN Parameters
Parameter | Description |
---|---|
|
Number to identify the item you want to generate. The number will determine which See Also: "HTMLDB_APPLICATION" |
|
Value of the hidden input form item |
Example
Typically, the primary key of a table is stored as a hidden column and used for subsequent update processing, for example:
SELECT empno, HTMLDB_ITEM.HIDDEN(1,empno)|| HTMLDB_ITEM.TEXT(2,ename) ename, HTMLDB_ITEM.TEXT(3,job) job, mgr, HTMLDB_ITEM.DATE_POPUP(4,rownum,hiredate,'dd-mon-yyyy') hiredate, HTMLDB_ITEM.TEXT(5,sal) sal, HTMLDB_ITEM.TEXT(6,comm) comm, deptno FROM emp ORDER BY 1
The previous query could use the following page process to process the results:
BEGIN FOR i IN 1..HTMLDB_APPLICATION.G_F01.COUNT LOOP UPDATE emp SET ename=HTMLDB_APPLICATION.G_F02(i), job=HTMLDB_APPLICATION.G_F03(i), hiredate=to_date(HTMLDB_APPLICATION.G_F04(i),'dd-mon-yyyy'), sal=HTMLDB_APPLICATION.G_F05(i), comm=HTMLDB_APPLICATION.G_F06(i) WHERE empno=to_number(HTMLDB_APPLICATION.G_F01(i)); END LOOP; END;
Note that the G_F01
column (which corresponds to the hidden EMPNO
) is used as the key to update each row.
This function passes values to HTMLDB_ITEM
.MULTI_ROW_UPDATE
and is used for lost update detection. Lost update detection ensures data integrity in applications where data can be accessed concurrently.
Syntax
HTMLDB_ITEM.MD5_CHECKSUM( p_value01 IN VARCHAR2 DEFAULT, p_value02 IN VARCHAR2 DEFAULT, p_value03 IN VARCHAR2 DEFAULT, ... p_value50 IN VARCHAR2 DEFAULT, p_col_sep IN VARCHAR2 DEFAULT) RETURN VARCHAR2;
Parameters
Table 15-56 describes the parameters available in the MD5_CHECKSUM
function.
Table 15-56 MD5_CHECKSUM Parameters
Parameter | Description |
---|---|
...
|
Fifty available inputs. If no parameters are supplied, the default to null |
|
String used to separate |
Example
SELECT HTMLDB_ITEM.MD5_CHECKSUM(ename,job,sal) FROM emp
This function is used for lost update detection. Lost update detection ensures data integrity in applications where data can be accessed concurrently.
This function produces a hidden form field and includes 50 inputs. HTMLDB_ITEM
.MD5_HIDDEN
also produces an MD5 checksum using the Oracle database DBMS_OBFUSCATION_TOOLKIT
:
UTL_RAW.CAST_TO_RAW(DBMS_OBFUSCATION_TOOLKIT.MD5())
An MD5 checksum provides data integrity through hashing and sequencing to ensure that data is not altered or stolen as it is transmitted over a network
Syntax
HTMLDB_ITEM.MD5_HIDDEN( p_idx IN NUMBER, p_value01 IN VARCHAR2 DEFAULT, p_value02 IN VARCHAR2 DEFAULT, p_value03 IN VARCHAR2 DEFAULT, ... p_value50 IN VARCHAR2 DEFAULT, p_col_sep IN VARCHAR2 DEFAULT) RETURN VARCHAR2;
Parameters
Table 15-57 describes the parameters available in the MD5_HIDDEN
function.
Table 15-57 MD5_HIDDEN Parameters
Parameter | Description |
---|---|
|
Indicates the form element to be generated. For example, 1 equals |
...
|
Fifty available inputs. Parameters not supplied default to null |
|
String used to separate |
Example
The p_idx
parameter specifies the FXX form element to be generated. In the following example, 7 generates F07
. Also note that an HTML hidden form element will be generated.
SELECT HTMLDB_ITEM.MD5_HIDDEN(7,ename,job,sal), ename, job, sal FROM emp
Use this procedure within a Multi Row Update process type. This procedure takes a string containing a multiple row update definition in the following format:
OWNER:TABLE:pk_column1,pk_idx:pk_column2,pk_idx2|col,idx:col,idx...
Syntax
HTMLDB_ITEM.MULTI_ROW_UPDATE( p_mru_string IN VARCHAR2 DEFAULT) RETURN VARCHAR2;
Example
To use this procedure indirectly within an application-level process, you need to create a query to generate a form of database data. The following example demonstrates how to create a multiple row update on the emp
table.
SELECT empno, HTMLDB_ITEM.HIDDEN(1,empno), HTMLDB_ITEM.HIDDEN(2,deptno), HTMLDB_ITEM.TEXT(3,ename), HTMLDB_ITEM.SELECT_LIST_FROM_QUERY(4,job,'SELECT DISTINCT job FROM emp'), HTMLDB_ITEM.TEXT(5,sal), HTMLDB_ITEM.TEXT(7,comm), HTMLDB_ITEM.MD5_CHECKSUM(ename,job,sal,comm), deptno FROM emp WHERE deptno = 20
Note the call to HTMLDB_ITEM.MD5_CHECKSUM
, instead of HTMLDB_ITEM.MD5_HIDDEN
. Since HTMLDB_ITEM.MULTI_ROW_UPDATE
gets the checksum from HTMLDB_APPLICATION.G_FCS
, you need to call HTMLDB_ITEM.MD5_CHECKSUM
in order to populate HTMLDB_APPLICATION.G_FCS
when the page is submitted. Additionally, the columns in HTMLDB_ITEM.MD5_CHECKSUM
must be in the same order those in the MULTI_ROW_UPDATE
process. These updates can then processed (or applied to the database) using an after submit page process of Multi Row Update in a string similar to the following:
SCOTT:emp:empno,1:deptno,2|ename,3:job,4:sal,5:comm,7:,:,:,:,
This function generates an HTML popup select list from an application list of values (LOV). Similar from other available functions in the HTMLDB_ITEM
package, POPUP_FROM_LOV
function is designed to generate forms with F01
to F50
form array elements.
Syntax
HTMLDB_ITEM.POPUP_FROM_LOV( p_idx IN NUMBER, p_value IN VARCHAR2 DEFAULT, p_lov_name IN VARCHAR2, p_width IN VARCHAR2 DEFAULT, p_max_length IN VARCHAR2 DEFAULT, p_form_index IN VARCHAR2 DEFAULT, p_escape_html IN VARCHAR2 DEFAULT, p_max_elements IN VARCHAR2 DEFAULT, p_attributes IN VARCHAR2 DEFAULT, p_ok_to_query IN VARCHAR2 DEFAULT, p_item_id IN VARCHAR2 DEFAULT NULL, p_item_label IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
Parameters
Table 15-58 describes the some parameters in the POPUP_FROM_LOV
function.
Table 15-58 POPUP_FROM_LOV Parameters
Parameter | Description |
---|---|
|
Form element name. For example, |
|
Form element current value. This value should be one of the values in the |
|
Named LOV used for this popup |
|
Width of the text box |
|
Maximum number of characters that can be entered in the text box |
|
HTML form on the page in which an item is contained. Defaults to 0 and rarely used. Only use this parameter when it is necessary to embed a custom form in your page template (such as a search field that posts to a different Web site). If this form comes before the |
|
Replacements for special characters that require an escaped equivalent:
Range of values is |
|
Limit on the number of rows that can be returned by your query. Limits the performance impact of user searches. By entering a value in this parameter, you force the user to search for a narrower set of results. |
|
Additional HTML attributes to use for the form item. |
|
Range of values is |
|
ID attribute of the form element. |
|
Invisible label created for the item. |
Example
The following example demonstrates a sample query the generates a popup from a LOV named DEPT
.
SELECT HTMLDB_ITEM.POPUP_FROM_LOV (1,deptno,'DEPT_LOV') dt FROM emp
This function generates an HTML popup select list from a query. Like other available functions in the HTMLDB_ITEM
package, the POPUP_FROM_QUERY
function is designed to generate forms with F01
to F50
form array elements.
Syntax
HTMLDB_ITEM.POPUP_FROM_QUERY( p_idx IN NUMBER, p_value IN VARCHAR2 DEFAULT, p_lov_query IN VARCHAR2, p_width IN VARCHAR2 DEFAULT, p_max_length IN VARCHAR2 DEFAULT, p_form_index IN VARCHAR2 DEFAULT, p_escape_html IN VARCHAR2 DEFAULT, p_max_elements IN VARCHAR2 DEFAULT, p_attributes IN VARCHAR2 DEFAULT, p_ok_to_query IN VARCHAR2 DEFAULT, p_item_id IN VARCHAR2 DEFAULT NULL, p_item_label IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
Parameters
Table 15-59 describes the parameters in the POPUP_FROM_QUERY
function.
Table 15-59 POPUP_FROM_QUERY Parameters
Parameter | Description |
---|---|
|
Form element name. For example, |
|
Form element current value. This value should be one of the values in the |
|
SQL query that is expected to select two columns (a display column and a return column). For example: SELECT dname, deptno FROM dept |
|
Width of the text box. |
|
Maximum number of characters that can be entered in the text box. |
|
HTML form on the page in which an item is contained. Defaults to 0 and rarely used. Only use this parameter when it is necessary to embed a custom form in your page template (such as a search field that posts to a different Web site). If this form comes before the |
|
Replacements for special characters that require an escaped equivalent.
Range of values is |
|
Limit on the number of rows that can be returned by your query. Limits the performance impact of user searches. By entering a value in this parameter, you force the user to search for a narrower set of results. |
|
Additional HTML attributes to use for the form item. |
|
Range of values is |
|
ID attribute of the form element. |
|
Invisible label created for the item. |
Example
The following example demonstrates a sample query the generates a popup select list from the emp
table.
SELECT HTMLDB_ITEM.POPUP_FROM_QUERY (1,deptno,'SELECT dname, deptno FROM dept') dt FROM emp
This function generates a popup key select list from a shared list of values (LOV). Similar to other available functions in the HTMLDB_ITEM
package, the POPUPKEY_FROM_LOV
function is designed to generate forms with F01
to F50
form array elements.
Syntax
HTMLDB_ITEM.POPUPKEY_FROM_LOV( p_idx IN NUMBER, p_value IN VARCHAR2 DEFAULT, p_lov_name IN VARCHAR2, p_width IN VARCHAR2 DEFAULT, p_max_length IN VARCHAR2 DEFAULT, p_form_index IN VARCHAR2 DEFAULT, p_escape_html IN VARCHAR2 DEFAULT, p_max_elements IN VARCHAR2 DEFAULT, p_attributes IN VARCHAR2 DEFAULT, p_ok_to_query IN VARCHAR2 DEFAULT, RETURN VARCHAR2;
Although the text field associated with the popup displays in the first column in the LOV query, the actual value is specified in the second column in the query.
Parameters
Table 15-60 describes the some parameters in the POPUPKEY_FROM_LOV
function.
Table 15-60 POPUPKEY_FROM_LOV Parameters
Parameter | Description |
---|---|
|
Identifies a form element name. For example, Because of the behavior of SELECT HTMLDB_ITEM.POPUPKEY_FROM_LOV (1,deptno,'DEPT') dt, HTMLDB_ITEM.HIDDEN(3,empno) eno |
|
Indicates the current value. This value should be one of the values in the |
|
Identifies a named LOV used for this popup. |
|
Width of the text box. |
|
Maximum number of characters that can be entered in the text box. |
|
HTML form on the page in which an item is contained. Defaults to 0 and rarely used. Only use this parameter when it is necessary to embed a custom form in your page template (such as a search field that posts to a different Web site). If this form comes before the |
|
Replacements for special characters that require an escaped equivalent.
This parameter is useful if you know your query will return illegal HTML. |
|
Limit on the number of rows that can be returned by your query. Limits the performance impact of user searches. By entering a value in this parameter, you force the user to search for a narrower set of results. |
|
Additional HTML attributes to use for the form item. |
|
Range of values is |
Example
The following example demonstrates how to generate a popup key select list from a shared list of values (LOV).
SELECT HTMLDB_ITEM.POPUPKEY_FROM_LOV (1,deptno,'DEPT') dt FROM emp
This function generates a popup key select list from a SQL query. Similar to other available functions in the HTMLDB_ITEM
package, the POPUPKEY_FROM_QUERY
function is designed to generate forms with F01
to F50
form array elements.
Syntax
HTMLDB_ITEM.POPUPKEY_FROM_QUERY( p_idx IN NUMBER, p_value IN VARCHAR2 DEFAULT, p_lov_query IN VARCHAR2, p_width IN VARCHAR2 DEFAULT, p_max_length IN VARCHAR2 DEFAULT, p_form_index IN VARCHAR2 DEFAULT, p_escape_html IN VARCHAR2 DEFAULT, p_max_elements IN VARCHAR2 DEFAULT, p_attributes IN VARCHAR2 DEFAULT, p_ok_to_query IN VARCHAR2 DEFAULT, p_item_id IN VARCHAR2 DEFAULT NULL, p_item_label IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
Parameters
Table 15-61 describes the some parameters in the POPUPKEY_FROM_QUERY
function.
Table 15-61 POPUPKEY_FROM_QUERY Parameters
Parameter | Description |
---|---|
|
Form element name. For example, Because of the behavior of SELECT HTMLDB_ITEM.POPUPKEY_FROM_QUERY (1,deptno,'SELECT dname, deptno FROM dept') dt, HTMLDB_ITEM.HIDDEN(3,empno) eno |
|
Form element current value. This value should be one of the values in the |
|
LOV query used for this popup. |
|
Width of the text box. |
|
Maximum number of characters that can be entered in the text box. |
|
HTML form on the page in which an item is contained. Defaults to 0 and rarely used. Only use this parameter when it is necessary to embed a custom form in your page template (such as a search field that posts to a different Web site). If this form comes before the |
|
Replacements for special characters that require an escaped equivalent.
This parameter is useful if you know your query will return illegal HTML. |
|
Limit on the number of rows that can be returned by your query. Limits the performance impact of user searches. By entering a value in this parameter, you force the user to search for a narrower set of results. |
|
Additional HTML attributes to use for the form item. |
|
Range of values is |
|
ID attribute of the form element. |
|
Invisible label created for the item. |
Example
The following example demonstrates how to generate a popup select list from a SQL query.
SELECT HTMLDB_ITEM.POPUPKEY_FROM_QUERY (1,deptno,'SELECT dname, deptno FROM dept') dt FROM emp
This function generates a radio group from a SQL query.
Syntax
HTMLDB_ITEM.RADIOGROUP( p_idx IN NUMBER, p_value IN VARCHAR2 DEFAULT, p_selected_value IN VARCHAR2 DEFAULT, p_display IN VARCHAR2 DEFAULT, p_attributes IN VARCHAR2 DEFAULT, p_onblur IN VARCHAR2 DEFAULT, p_onchange IN VARCHAR2 DEFAULT, p_onfocus IN VARCHAR2 DEFAULT,) RETURN VARCHAR2;
Parameters
Table 15-62 describes the parameters available in the RADIOGROUP
function.
Table 15-62 RADIOGROUP Parameters
Parameter | Description |
---|---|
|
Number that determines which |
|
Value of the radio group. |
|
Value that should be selected. |
|
Text to display next to the radio option. |
|
Extra HTML parameters you want to add. |
|
JavaScript to execute in the onBlur event. |
|
JavaScript to execute in the onChange event. |
|
JavaScript to execute in the onFocus event. |
Example
The following example demonstrates how to select department 20 from the emp
table as a default in a radio group.
SELECT HTMLDB_ITEM.CHECKBOX(1,deptno,'20',dname) dt FROM dept ORDER BY 1
This function dynamically generates a static select list. Similar to other functions available in the HTMLDB_ITEM
package, these select list functions are designed to generate forms with F01
to F50
form array elements.
Syntax
HTMLDB_ITEM.SELECT_LIST( p_idx IN NUMBER, p_value IN VARCHAR2 DEFAULT, p_list_values IN VARCHAR2 DEFAULT, p_attributes IN VARCHAR2 DEFAULT, p_show_null IN VARCHAR2 DEFAULT, p_null_value IN VARCHAR2 DEFAULT, p_null_text IN VARCHAR2 DEFAULT, p_item_id IN VARCHAR2 DEFAULT, p_item_label IN VARCHAR2 DEFAULT, p_show_extra IN VARCHAR2 DEFAULT) RETURN VARCHAR2;
Parameters
Table 15-63 describes the parameters available in the SELECT_LIST
function.
Table 15-63 SELECT_LIST Parameters
Parameter | Description |
---|---|
|
Form element name. For example, |
|
Current value. This value should be a value in the |
|
List of static values separated by commas. Displays values and returns values that are separated by semicolons. Note that this is only available in the |
|
Extra HTML parameters you want to add. |
|
Extra select option to enable the NULL selection. Range of values is |
|
Value to be returned when a user selects the null option. Only relevant when |
|
Value to be displayed when a user selects the null option. Only relevant when |
|
HTML attribute ID for the <input> tag. |
|
Label of the select list. |
|
Shows the current value even if the value of p_value is not located in the select list. |
Example
The following example demonstrates a static select list that displays Yes
, returns Y
, defaults to Y
, and generates a F01
form item.
SELECT HTMLDB_ITEM.SELECT_LIST(1,'Y','Yes;Y,No;N') FROM emp
This function dynamically generates select lists from a shared list of values (LOV). Similar to other functions available in the HTMLDB_ITEM
package, these select list functions are designed to generate forms with F01
to F50
form array elements.
Syntax
HTMLDB_ITEM.SELECT_LIST_FROM_LOV( p_idx IN NUMBER, p_value IN VARCHAR2 DEFAULT, p_lov IN VARCHAR2, p_attributes IN VARCHAR2 DEFAULT, p_show_null IN VARCHAR2 DEFAULT, p_null_value IN VARCHAR2 DEFAULT, p_null_text IN VARCHAR2 DEFAULT, p_item_id IN VARCHAR2 DEFAULT, p_item_label IN VARCHAR2 DEFAULT) RETURN VARCHAR2;
Parameters
Table 15-64 describes the parameters available in the SELECT_LIST_FROM_LOV
function.
Table 15-64 SELECT_LIST_FROM_LOV Parameters
Parameter | Description |
---|---|
|
Form element name. For example, |
|
Current value. This value should be a value in the |
|
Text name of an application list of values. This list of values must be defined in your application. This parameter is used only by the |
|
Extra HTML parameters you want to add. |
|
Extra select option to enable the NULL selection. Range of values is |
|
Value to be returned when a user selects the null option. Only relevant when |
|
Value to be displayed when a user selects the null option. Only relevant when |
|
HTML attribute ID for the |
|
Label of the select list. |
Example
The following example demonstrates a select list based on an LOV defined in the application.
SELECT HTMLDB_ITEM.SELECT_LIST_FROM_LOV(2,job,'JOB_FLOW_LOV') FROM emp
This function dynamically generates very large select lists (greater than 32K) from a shared list of values (LOV). Similar to other functions available in the HTMLDB_ITEM
package, these select list functions are designed to generate forms with F01
to F50
form array elements. This function is the same as SELECT_LIST_FROM_LOV
, but its return value is CLOB. This enables you to use it in SQL queries where you need to handle a column value longer than 4000 characters.
Syntax
HTMLDB_ITEM.SELECT_LIST_FROM_LOV_XL( p_idx IN NUMBER, p_value IN VARCHAR2 DEFAULT, p_lov IN VARCHAR2, p_attributes IN VARCHAR2 DEFAULT, p_show_null IN VARCHAR2 DEFAULT, p_null_value IN VARCHAR2 DEFAULT, p_null_text IN VARCHAR2 DEFAULT, p_item_id IN VARCHAR2 DEFAULT, p_item_label IN VARCHAR2 DEFAULT) RETURN CLOB;
Parameters
Table 15-65 describes the parameters available in the SELECT_LIST_FROM_LOV_XL
function.
Table 15-65 SELECT_LIST_FROM_LOV_XL Parameters
Parameter | Description |
---|---|
|
Form element name. For example, |
|
Current value. This value should be a value in the |
|
Text name of a list of values. This list of values must be defined in your application. This parameter is used only by the |
|
Extra HTML parameters you want to add. |
|
Extra select option to enable the NULL selection. Range of values is |
|
Value to be returned when a user selects the null option. Only relevant when |
|
Value to be displayed when a user selects the null option. Only relevant when |
|
HTML attribute ID for the |
|
Label of the select list. |
Example
The following demonstrates a select list based on a LOV defined in the application.
SELECT HTMLDB_ITEM.SELECT_LIST_FROM_LOV_XL(2,job,'JOB_FLOW_LOV') FROM emp
This function is the same as This function dynamically generates a select list from a query. Similar to other functions available in the HTMLDB_ITEM
package, these select list functions are designed to generate forms with F01
to F50
form array elements.
Syntax
HTMLDB_ITEM.SELECT_LIST_FROM_QUERY( p_idx IN NUMBER, p_value IN VARCHAR2 DEFAULT, p_query IN VARCHAR2, p_attributes IN VARCHAR2 DEFAULT, p_show_null IN VARCHAR2 DEFAULT, p_null_value IN VARCHAR2 DEFAULT, p_null_text IN VARCHAR2 DEFAULT, p_item_id IN VARCHAR2 DEFAULT, p_item_label IN VARCHAR2 DEFAULT, p_show_extra IN VARCHAR2 DEFAULT) RETURN VARCHAR2;
Parameters
Table 15-66 describes the parameters available in the SELECT_LIST_FROM_QUERY
function.
Table 15-66 SELECT_LIST_FROM_QUERY Parameters
Parameter | Description |
---|---|
|
Form element name. For example, |
|
Current value. This value should be a value in the |
|
SQL query that is expected to select two columns, a display column, and a return column. For example: SELECT dname, deptno FROM dept Note that this is used only by the |
|
Extra HTML parameters you want to add. |
|
Extra select option to enable the NULL selection. Range of values is |
|
Value to be returned when a user selects the null option. Only relevant when |
|
Value to be displayed when a user selects the null option. Only relevant when |
|
HTML attribute ID for the |
|
Label of the select list. |
|
Show the current value even if the value of |
Example
The following example demonstrates a select list based on a SQL query.
SELECT HTMLDB_ITEM.SELECT_LIST_FROM_QUERY(3,job,'SELECT DISTINCT job FROM emp') FROM emp
This function is the same as SELECT_LIST_FROM_QUERY
, but its return value is a CLOB. This allows its use in SQL queries where you need to handle a column value longer than 4000 characters. Similar to other functions available in the HTMLDB_ITEM
package, these select list functions are designed to generate forms with F01
to F50
form array elements.
Syntax
HTMLDB_ITEM.SELECT_LIST_FROM_QUERY_XL( p_idx IN NUMBER, p_value IN VARCHAR2 DEFAULT, p_query IN VARCHAR2, p_attributes IN VARCHAR2 DEFAULT, p_show_null IN VARCHAR2 DEFAULT, p_null_value IN VARCHAR2 DEFAULT, p_null_text IN VARCHAR2 DEFAULT, p_item_id IN VARCHAR2 DEFAULT, p_item_label IN VARCHAR2 DEFAULT, p_show_extra IN VARCHAR2 DEFAULT) RETURN CLOB;
Parameters
Table 15-67 describes the parameters available in the SELECT_LIST_FROM_QUERY_XL
function.
Table 15-67 SELECT_LIST_FROM_QUERY_XL Parameters
Parameter | Description |
---|---|
|
Form element name. For example, |
|
Current value. This value should be a value in the |
|
SQL query that is expected to select two columns, a display column, and a return column. For example: SELECT dname, deptno FROM dept Note that this is used only by the |
|
Extra HTML parameters you want to add. |
|
Extra select option to enable the NULL selection. Range of values is |
|
Value to be returned when a user selects the null option. Only relevant when |
|
Value to be displayed when a user selects the null option. Only relevant when |
|
HTML attribute ID for the |
|
Label of the select list. |
|
Show the current value even if the value of |
Example
The following example demonstrates a select list based on a SQL query.
SELECT HTMLDB_ITEM.SELECT_LIST_FROM_QUERY_XL(3,job,'SELECT DISTINCT job FROM emp') FROM emp
This function creates text areas.
Syntax
HTMLDB_ITEM.TEXTAREA( p_idx IN NUMBER, p_value IN VARCHAR2 DEFAULT NULL, p_rows IN NUMBER DEAULT 40, p_cols IN NUMBER DEFAULT 4 p_attributes IN VARCHAR2 DEFAULT, p_item_id IN VARCHAR2 DEFAULT NULL, p_item_label IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
Parameters
Table 15-68 describes the parameters available in the TEXTAREA
function.
Table 15-68 TEXTAREA Parameters
Parameter | Description |
---|---|
|
Number to identify the item you want to generate. The number will determine which See Also: "HTMLDB_APPLICATION" |
|
Value of the text area item. |
p_rows |
Height of the text area (HTML rows attribute) |
p_cols |
Width of the text area (HTML column attribute). |
|
Extra HTML parameters you want to add. |
|
HTML attribute ID for the |
|
Label of the text area item. |
Example
The following example demonstrates how to create a text area based on a SQL query.
SELECT HTMLDB_ITEM.TEXTAREA(3,ename,5,80) a FROM emp
This function generates text fields (or text input form items) from a SQL query.
Syntax
HTMLDB_ITEM.TEXT( p_idx IN NUMBER, p_value IN VARCHAR2 DEFAULT NULL, p_size IN NUMBER DEFAULT NULL, p_maxlength IN NUMBER DEFAULT NULL, p_attributes IN VARCHAR2 DEFAULT NULL, p_item_id IN VARCHAR2 DEFAULT NULL, p_item_label IN VARCHAR2 DEFAULT NULL)
Parameters
Table 15-69 describes the parameters available in the TEXT
function.
Table 15-69 TEXT Parameters
Parameter | Description |
---|---|
|
Number to identify the item you want to generate. The number will determine which See Also: "HTMLDB_APPLICATION" |
|
Value of a text field item. |
|
Controls HTML tag attributes (such as disabled). |
|
Maximum number of characters that can be entered in the text box. |
|
Extra HTML parameters you want to add. |
|
HTML attribute ID for the |
|
Label of the text field item. |
Example
The following sample query demonstrates how to generate one update field for each row. Note that the ename
, sal
, and comm
columns use the HTMLDB_ITEM
.TEXT
function to generate an HTML text field for each row. Also, notice that each item in the query is passed a unique p_idx
parameter to ensure that each column is stored in its own array.
SELECT empno, HTMLDB_ITEM.HIDDEN(1,empno)|| HTMLDB_ITEM.TEXT(2,ename) ename, HTMLDB_ITEM.TEXT(3,job) job, mgr, HTMLDB_ITEM.DATE_POPUP(4,rownum,hiredate,'dd-mon-yyyy') hiredate, HTMLDB_ITEM.TEXT(5,sal) sal, HTMLDB_ITEM.TEXT(6,comm) comm, deptno FROM emp ORDER BY 1
Use this function to display an item as text, deriving the display value of the named LOV.
Syntax
HTMLDB_ITEM.TEXT_FROM_LOV ( p_value IN VARCHAR2 DEFAULT NULL, p_lov IN VARCHAR2, p_null_text IN VARCHAR2 DEFAULT '%') RETURN VARCHAR2;
Parameters
Table 15-70 describes the parameters available in the TEXT_FROM_LOV
function.
Table 15-70 TEXT_FROM_LOV Parameters
Parameter | Description |
---|---|
|
Value of a field item. |
|
Text name of a shared list of values. This list of values must be defined in your application. |
|
Value to be displayed when the value of the field item is null or a corresponding entry is not located for the value |
Example
The following example demonstrates how to derive the display value from a named LOV (EMPNO_ENAME_LOV
).
SELECT HTMLDB_ITEM.TEXT_FROM_LOV(empno,'EMPNO_ENAME_LOV') c FROM emp
Use this function to display an item as text, deriving the display value from a list of values query.
Syntax
HTMLDB_ITEM.TEXT_FROM_LOV_QUERY ( p_value IN VARCHAR2 DEFAULT NULL, p_query IN VARCHAR2, p_null_text IN VARCHAR2 DEFAULT '%') RETURN VARCHAR2;
Parameters
Table 15-71 describes the parameters available in the TEXT_FROM_LOV_QUERY
function.
Table 15-71 TEXT_FROM_LOV_QUERY Parameters
Parameter | Description |
---|---|
|
Value of a field item. |
|
SQL query that is expected to select two columns, a display column and a return column. For example: SELECT dname, deptno FROM dept |
|
Value to be displayed when the value of the field item is null or a corresponding entry is not located for the value |
Example
The following example demonstrates how to derive the display value from a query.
SELECT HTMLDB_ITEM.TEXT_FROM_LOV_QUERY(empno,'SELECT ename, empno FROM emp') c from emp
The HTMLDB_APPLICATION
package is a PL/SQL package that implements the Oracle Application Express rendering engine. You can use this package to take advantage of a number of global variables. Table 15-72 describes the global variables available in the HTMLDB_APPLICATION package
.
Table 15-72 Global Variables Available in HTMLDB_APPLICATION
Global Variable | Description |
---|---|
|
Specifies the currently logged in user. |
|
Specifies the ID of the currently running application. |
|
Specifies the ID of the currently running page. |
|
Specifies the schema to parse for the currently running application. |
|
Specifies the value of the request variable most recently passed to or set within the show or accept modules. |
Topics in this section include:
Items are typically HTML form elements such as text fields, select lists, and check boxes. When you create a new form item using a wizard, the wizard uses a standard naming format. The naming format provides a handle so you can retrieve the value of the item later on.
If you need to create your own items, you can access them after a page is submitted by referencing HTMLDB_APPLICATION.G_F01
to HTMLDB_APPLICATION.G_F50
arrays. You can create your own HTML form fields by providing the input parameters using the format F01
, F02
, F03
and so on. You can create up to 50 input parameters ranging from F01
to F50
, for example:
<INPUT TYPE="text" NAME="F01" SIZE="32" MAXLENGTH="32" VALUE="some value"> <TEXTAREA NAME="F02" ROWS=4 COLS=90 WRAP="VIRTUAL">this is the example of a text area.</TEXTAREA> <SELECT NAME="F03" SIZE="1"> <OPTION VALUE="abc">abc <OPTION VALUE="123">123 </SELECT>
Because the F01
to F50
input items are declared as PL/SQL arrays, you can have multiple items named the same value. For example:
<INPUT TYPE="text" NAME="F01" SIZE="32" MAXLENGTH="32" VALUE="array element 1"> <INPUT TYPE="text" NAME="F01" SIZE="32" MAXLENGTH="32" VALUE="array element 2"> <INPUT TYPE="text" NAME="F01" SIZE="32" MAXLENGTH="32" VALUE="array element 3">
Note that following PL/SQL code produces the same HTML as show in the previous example.
FOR i IN 1..3 LOOP HTMLDB_ITEM.TEXT(P_IDX => 1, p_value =>'array element '||i , p_size =>32, p_maxlength =>32); END LOOP;
You can reference the values posted by an HTML form using the PL/SQL variable HTMLDB_APPLICATION
.G_F01
to HTMLDB_APPLICATION
.G_F50
. Because this element is an array, you can reference values directly, for example:
FOR i IN 1.. HTMLDB_APPLICATION.G_F01.COUNT LOOP htp.p('element '||I||' has a value of '||HTMLDB_APPLICATION.G_F01(i)); END LOOP;
Note that check boxes displayed using HTMLDB_ITEM.CHECKBOX
will only contain values in the HTMLDB_APPLICATION
arrays for those rows which are checked. Unlike other items (TEXT, TEXTAREA, DATE_POPUP) which can contain an entry in the corresponding HTMLDB_APPLICATION
array for every row submitted, a check box will only have an entry in the HTMLDB_APPLICATION
array if it is selected.
You can also use Oracle Application Express public utility functions to convert an array into a single value. The resulting string value is a colon-separated list of the array element values. The resulting string value is a colon-separated list of the array element values. For example:
htp.p(HTMLDB_UTIL.TABLE_TO_STRING(HTMLDB_APPLICATION.G_F01));
This function enables you to reference G_F01
to G_F50
values in an application process that performs actions on data. The following sample process demonstrates how values are inserted into a table:
FOR i IN 1..HTMLDB_APPLICATION.G_F01.COUNT LOOP INSERT INTO my_table (my_column) VALUES HTMLDB_APPLICATION.G_F01(i); END LOOP;
You can use the HTMLDB_CUSTOM_AUTH
package to perform various operations related to authentication and session management.
Topics in this section include:
This function checks for the existence of page-level item within an application. This function requires the parameter p_item_name
. This function returns a Boolean value (true or false).
Syntax
FUNCTION APPLICATION_PAGE_ITEM_EXISTS( p_item_name IN VARCHAR2) RETURN BOOLEAN;
This function checks whether the current page's authentication attribute is set to Page Is Public and returns a Boolean value (true or false)
See Also:
"Editing Page Attributes" and "Security" for information about setting this page attributeSyntax
FUNCTION CURRENT_PAGE_IS_PUBLIC RETURN BOOLEAN;
This procedure combines the SET_USER
and SET_SESSION_ID
functions to create one call.
Syntax
PROCEDURE DEFINE_USER_SESSION( p_user IN VARCHAR2) p_session_id IN NUMBER);
This procedure obtains the properties of the session cookie used in the current authentication scheme for the specified application. These properties can be viewed directly in the Application Builder by viewing the authentication scheme attributes.
Syntax
HTMLDB_CUSTOM_AUTH.GET_COOKIE_PROPS( p_app_id IN NUMBER, p_cookie_name OUT VARCHAR2, p_cookie_path OUT VARCHAR2, p_cookie_domain OUT VARCHAR2);
Parameters
Table 15-73 describes the parameters available in the GET_COOKIE_PROPS
procedure.
Table 15-73 GET_COOKIE_PROPS Parameters
Parameter | Description |
---|---|
|
An application ID in the current workspace. |
|
The cookie name. |
|
The cookie path. |
|
The cookie domain. |
Example
DECLARE l_cookie_name varchar2(256); l_cookie_path varchar2(256); l_cookie_domain varchar2(256); BEGIN HTMLDB_CUSTOM_AUTH.GET_COOKIE_PROPS ( p _cookie_name => l_cookie_name, p _cookie_path => l_cookie_path, p _cookie_domain => l_cookie_domain); END;
This procedure obtains the LDAP attributes of the current authentication scheme for the current application. These properties can be viewed directly in Application Builder by viewing the authentication scheme attributes.
Syntax
HTMLDB_CUSTOM_AUTH.GET_LDAP_PROPS( p_ldap_host OUT VARCHAR2, p_ldap_port OUT NUMBER, p_ldap_dn OUT VARCHAR2, p_ldap_edit_function OUT VARCHAR2);
Parameters
Table 15-74 describes the parameters available in the GET_LDAP_PROPS procedure.
Table 15-74 GET_LDAP_PROPS Parameters
Parameter | Description |
---|---|
|
LDAP host name. |
|
LDAP port number. |
|
LDAP DN string. |
|
LDAP edit function name. |
Example
DECLARE l_ldap_host varchar2(256); l_ldap_port number; l_ldap_dn varchar2(256); l_ldap_edit_function varchar2(256); BEGIN HTMLDB_CUSTOM_AUTH.GET_LDAP_PROPS ( p_ldap_host => l_ldap_host, p_ldap_port => l_ldap_port, p_ldap_dn => l_ldap_dn,' p_ldap_edit_function => l_ldap_edit_function); END;
This function generates the next session ID from the Oracle Application Express sequence generator. This function returns a number.
Syntax
FUNCTION GET_NEXT_SESSION_ID RETURN NUMBER;
This function returns the Oracle Application Express session ID located by the session cookie in the context of a page request in the current browser session.
Syntax
HTMLDB_CUSTOM_AUTH.GET_SESSION_ID_FROM_COOKIE; RETURN NUMBER;
Example
DECLARE VAL NUMBER; BEGIN VAL := HTMLDB_CUSTOM_AUTH.GET_SESSION_ID_FROM_COOKIE; END;
This function returns user name registered with the current Oracle Application Express session in the internal sessions table. This user name is usually the same as the authenticated user running the current page.
Syntax
HTMLDB_CUSTOM_AUTH.GET_USERNAME; RETURN VARCHAR2;
Example
DECLARE VAL VARCHAR2(256); BEGIN VAL := HTMLDB_CUSTOM_AUTH.GET_USERNAME; END;
This function returns a number with the value of the security group ID that identifies the workspace of the current user.
Syntax
FUNCTION GET_SECURITY_GROUP_ID RETURN NUMBER;
This function returns HTMLDB_APPLICATION
.G_INSTANCE
global variable. GET_SESSION_ID
returns a number.
Syntax
PROCEDURE GET_SESSION_ID RETURN NUMBER;
This function returns the HTMLDB_APPLICATION
.G_USER
global variable (VARCHAR2
).
Syntax
FUNCTION GET_USER RETURN VARCHAR2;
This function is a Boolean result obtained from executing the current application's authentication scheme to determine if a valid session exists. This function returns the Boolean result of the authentication scheme's page sentry.
Syntax
HTMLDB_CUSTOM_AUTH.IS_SESSION_VALID; RETURN BOOLEAN;
Example
DECLARE VAL VARCHAR2(256); BEGIN VAL := HTMLDB_CUSTOM_AUTH.IS_SESSION_VALID; END;
Also referred to as the "Login API," this procedure performs authentication and session registration.
Syntax
HTMLDB_CUSTOM_AUTH.LOGIN( p_uname IN VARCHAR2, p_password IN VARCHAR2, p_session_id IN VARCHAR2, p_app_page IN VARCHAR2, p_entry_point IN VARCHAR2, p_preserve_case IN BOOLEAN);
Parameter
Table 15-75 describes the parameters available in the LOGIN
procedure.
Table 15-75 LOGIN Parameters
Parameter | Description |
---|---|
|
Login name of the user. |
|
Clear text user password. |
|
Current Oracle Application Express session ID. |
|
Current application ID. After login page separated by a colon (:). |
|
Internal use only. |
|
If true, do not upper |
Example
BEGIN HTMLDB_CUSTOM_AUTH.LOGIN ( p_uname => 'SCOTT', p_password => 'secret99', p_session_id => V('APP_SESSION'), p_app_page => :APP_ID||':1'); END;
Note:
:Do not use bind variable notations forp_session_id
argument.This procedure effects a logout from the current session by unsetting the session cookie and redirecting to a new location.
Syntax
HTMLDB_CUSTOM_AUTH.LOGOUT( p_this_app IN VARCHAR2, p_next_app_page_sess IN VARCHAR2, p_next_url IN VARCHAR2);
Parameter
Table 15-76 describes the parameters available in the LOGOUT
procedure.
Table 15-76 LOGOUT Parameters
Parameter | Description |
---|---|
|
Current application ID. |
|
Application and page ID to redirect to. Separate multiple pages using a colon (:) and optionally followed by a colon (:) and the session ID (if control over the session ID is desired). |
|
URL to redirect to (use this instead of |
Example
BEGIN HTMLDB_CUSTOM_AUTH.LOGOUT ( p_this_app => '1000', p_next_app_page_sess => '1000:99'); END;
This procedure performs session registration, assuming the authentication step has been completed. It can be called only from within an Oracle Application Express application page context.
Syntax
HTMLDB_CUSTOM_AUTH.POST_LOGIN( p_uname IN VARCHAR2, p_session_id IN VARCHAR2, p_app_page IN VARCHAR2, p_preserve_case IN BOOLEAN);
Parameter
Table 15-77 describes the parameters available in the POST_LOGIN
procedure.
Table 15-77 POST_LOGIN Parameters
Parameter | Description |
---|---|
|
Login name of user. |
|
Current Oracle Application Express session ID. |
|
Current application ID and after login page separated by a colon (:). |
|
If true, do not include |
Example
BEGIN HTMLDB_CUSTOM_AUTH.POST_LOGIN ( p_uname => 'SCOTT', p_session_id => V('APP_SESSION'), p_app_page => :APP_ID||':1'); END;
This function returns a Boolean result based on the global package variable containing the current Oracle Application Express session ID. Returns true if the result is a positive number. returns false if the result is a negative number.
Syntax
FUNCTION SESSION_ID_EXISTS RETURN BOOLEAN;
Example
DECLARE VAL BOOLEAN; BEGIN VAL := HTMLDB_CUSTOM_AUTH.SESSION_ID_EXISTS; END;
This procedure sets the HTMLDB_APPLICATION
.G_USER
global variable. SET_USER
requires the parameter P_USER
(VARCHAR2
) which defines a user ID.
Syntax
PROCEDURE SET_USER( p_user IN VARCHAR2)
This procedure sets HTMLDB_APPLICATION
.G_INSTANCE
global variable. SET_SESSION_ID
returns a number. This procedure requires the parameter P_SESSION_ID
(NUMBER
) which specifies a session ID.
Syntax
PROCEDURE SET_SESSION_ID( p_session_id IN NUMBER)
You can use HTMLDB_LDAP
to perform various operations related to Lightweight Directory Access Protocol (LDAP) authentication.
Topics in this section include:
The AUTHENTICATE
function returns a boolean true if the username and password can be used to perform a SIMPLE_BIND_S
call using the provided search base, host, and port.
Syntax
FUNCTION AUTHENTICATE( p_username in VARCHAR2 DEFAULT NULL, p_password in VARCHAR2 DEFAULT NULL, p_search_base in VARCHAR2, p_host in VARCHAR2, p_port in VARCHAR2 DEFAULT 389) RETURN BOOLEAN;
Parameters
Table 15-78 describes the parameters available in the AUTHENTICATE
function.
The IS_MEMBER
function returns a boolean true if the user named by p_username
(with password if required) is a member of the group specified by the p_group
and p_group_base
parameters using the provided auth base, host, and port.
Syntax
FUNCTION IS_MEMBER( p_username in VARCHAR2 DEFAULT NULL, p_pass in VARCHAR2 DEFAULT NULL, p_auth_base in VARCHAR2, p_host in VARCHAR2, p_port in VARCHAR2 DEFAULT 389, p_group in VARCHAR2, p_group_base in VARCHAR2) RETURN BOOLEAN;
Parameters
Table 15-79 describes the parameters available in the IS_MEMBER
function.
Table 15-79 IS_MEMBER Parameters
Parameter | Description |
---|---|
|
Login name of the user. |
|
Password for |
|
LDAP search base, for example, |
|
LDAP server host name. |
|
LDAP server port number. |
|
Name of the group to be search for membership. |
|
The base from which the search should be started. |
The MEMBER_OF
function returns an array of groups the username designated by p_username
(with password if required) belongs to, using the provided auth base, host, and port.
Syntax
FUNCTION MEMBER_OF( p_username in VARCHAR2 DEFAULT NULL, p_pass in VARCHAR2 DEFAULT NULL, p_auth_base in VARCHAR2, p_host in VARCHAR2, p_port in VARCHAR2 DEFAULT 389) RETURN wwv_flow_global.vc_arr2;
Parameters
Table 15-80 describes the parameters available in the MEMBER_OF
function.
The MEMBER_OF2
function returns an VARCHAR2
list of groups the username designated by p_username
(with password if required) belongs to, using the provided auth base, host, and port.
Syntax
FUNCTION MEMBER_OF2( p_username in VARCHAR2 DEFAULT NULL, p_pass in VARCHAR2 DEFAULT NULL, p_auth_base in VARCHAR2, p_host in VARCHAR2, p_port in VARCHAR2 DEFAULT 389) RETURN VARCHAR2;
Parameters
Table 15-81 describes the parameters available in the MEMBER_OF2
function.
The GET_USER_ATTRIBUTES
procedure returns an OUT array of user_attribute values for the username designated by p_username
(with password if required) corresponding to the attribute names passed in p_attributes
, using the provided auth base, host, and port.
Syntax
PROCEDURE GET_USER_ATTRIBUTES( p_username in VARCHAR2 DEFAULT NULL, p_pass in VARCHAR2 DEFAULT NULL, p_auth_base in VARCHAR2, p_host in VARCHAR2, p_port in VARCHAR2 DEFAULT 389, p_attributes in wwv_flow_global.vc_arr2, p_attribute_values out wwv_flow_global.vc_arr2);
Parameters
Table 15-82 describes the parameters available in the GET_USER_ATTRIBUTES
procedure.
Table 15-82 GET_USER_ATTRIBUTES Parameters
Parameter | Description |
---|---|
|
Login name of the user. |
|
Password for |
|
LDAP search base, for example, |
|
LDAP server host name. |
|
LDAP server port number. |
|
An array of attribute names for which values are to be returned. |
|
An array of values returned for each corresponding attribute name in |
The GET_ALL_USER_ATTRIBUTES
procedure returns two OUT arrays of user_attribute
names and values for the username designated by p_username
(with password if required) using the provided auth base, host, and port.
Syntax
PROCEDURE GET_ALL_USER_ATTRIBUTES( p_username in VARCHAR2 DEFAULT NULL, p_pass in VARCHAR2 DEFAULT NULL, p_auth_base in VARCHAR2, p_host in VARCHAR2, p_port in VARCHAR2 DEFAULT 389, p_attributes out wwv_flow_global.vc_arr2, p_attribute_values out wwv_flow_global.vc_arr2);
Parameters
Table 15-83 describes the parameters available in the GET_ALL_USER_ATTRIBUTES
procedure.
Table 15-83 GET_ALL_USER_ATTRIBUTES Parameters
Parameter | Description |
---|---|
|
Login name of the user. |
|
Password for |
|
LDAP search base, for example, |
|
LDAP server host name. |
|
LDAP server port number. |
|
An array of attribute names returned. |
|
An array of values returned for each corresponding attribute name returned in p_attributes. |