This section describes the APIs available in Oracle HTML DB.
This section contains the following topics:
The HTMLDB_UTIL package provides utilities you can use when programming in the Oracle HTML DB environment. You can use HTMLDB_UTIL 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 HTML DB user accounts are in use.
Syntax
HTMLDB_UTIL.CHANGE_CURRENT_USER_PW(
p_new_password IN VARCHAR2);
Parameters
Table 17-1 describes the parameters available in the CHANGE_CURRENT_USER_PW procedure.
Table 17-1 CHANGE_CURRENT_USER_PW Parameters
| Parameter | Description |
|---|---|
p_new_password |
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
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;
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;
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;
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 17-2 describes the parameters available in the COUNT_CLICK procedure.
Table 17-2 COUNT_CLICK Parameters
| Parameter | Description |
|---|---|
p_url |
The URL to redirect to. |
p_cat |
A category to classify the click. |
p_id |
Secondary ID to associate with the click (optional). |
p_user |
The application user ID (optional). |
p_workspace |
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.
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 17-3 describes the parameters available in CREATE_USER procedure.
Table 17-3 CREATE_USER Procedure Parameters
| Parameter | Description |
|---|---|
p_user_id |
Numeric primary key of user account. |
p_user_name |
Alphanumeric name used for login. |
p_first_name |
Informational. |
p_last_name |
Informational. |
p_description |
Informational. |
p_email_address |
E-mail address. |
p_web_address |
Clear text password. |
p_group_ID |
Colon separated list of numeric group IDs. |
p_developer_privs |
Colon separated list of developer privileges (only applies to Oracle HTML DB administrators). |
p_default_schema |
A database schema assigned to user's workspace used by default for browsing. |
p_allow_access_to_schemas |
A list of schemas assigned to user's workspace to which user is restricted. |
p_attribute_01
|
Arbitrary text accessible with 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 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 17-4 describes the parameters available in the CREATE_USER_GROUP procedure.
Table 17-4 CREATE_USER_GROUP Parameters
| Parameter | Description |
|---|---|
p_id |
Primary key of group. |
p_group_name |
Arbitrary name. |
p_security_group_id |
Workspace ID. |
p_group_desc |
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 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 17-5 describes the parameters available in CURRENT_USER_IN_GROUP function.
Table 17-5 CURRENT_USER_IN_GROUP Parameters
| Parameter | Description |
|---|---|
p_group_name |
Identifies the name of an existing group in the workspace. |
p_group_id |
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 17-6 describes the parameters available in EDIT_USER procedure.
Table 17-6 EDIT_USER Parameters
| Parameter | Description |
|---|---|
p_user_id |
Numeric primary key of user account. |
p_user_name |
Alphanumeric name used for login. |
p_first_name |
Informational. |
p_last_name |
Informational. |
p_web_password |
Clear text password, |
p_start_date |
Unused. |
p_end_date |
Unused. |
p_employee_id |
Unused. |
p_allow_access_to_schemas |
A list of schemas assigned to user's workspace to which user is restricted. |
p_person_type |
Unused. |
p_default_schema |
A database schema assigned to user's workspace used by default for browsing. |
p_group_ids |
Colon separated list of numeric group IDs. |
p_developer_privs |
Colon separated list of developer p.rivileges (only ADMIN: has meaning to HTML DB) |
p_description |
Informational. |
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 17-7 describes the parameters available in EXPORT_USERS procedure.
Table 17-7 EXPORT_USERS Parameters
| Parameter | Description |
|---|---|
p_export_format |
Indicates how rows in the export file will be formatted. Specify 'UNIX' to have the resulting file contain rows delimited by line feeds. Specify 'DOS' to have the resulting file contain rows delimited by carriage returns and line feeds. |
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 17-8 describes the parameters available in the FETCH_APP_ITEM function.
Table 17-8 FETCH_APP_ITEM Parameters
| Parameter | Description |
|---|---|
p_item |
The name of an application level item (not a page item) whose current value is to be fetched. |
p_app |
The ID of the application that owns the item (leave null for current application). |
p_session |
The session ID from which to obtain the value (leave null for 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 17-9 describes the parameters available in the FETCH_USER procedure.
Table 17-9 Fetch_User Parameters
| Parameter | Description |
|---|---|
p_user_id |
Numeric primary key of user account. |
p_workspace |
The name of the workspace |
p_user_name |
Alphanumeric name used for login. |
p_first_name |
Informational. |
p_last_name |
Informational. |
p_description |
Informational. |
p_email_address |
E-mail address. |
p_start_date |
Unused. |
p_end_date |
Unused. |
p_employee_id |
Unused. |
p_allow_access_to_schemas |
A list of schemas assigned to user's workspace to which user is restricted. |
p_person_type |
Unused. |
p_default_schema |
A database schema assigned to user's workspace used by default for browsing. |
p_groups |
Unused. |
p_developer_role |
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
p_workspace is 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
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;
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 17-10 describes the parameters available in the GET_ATTRIBUTE function.
Table 17-10 GET_ATTRIBUTE Parameters
| Parameter | Description |
|---|---|
p_username |
User name in the account. |
p_attribute_number |
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;
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;
Example
DECLARE VAL VARCHAR2; BEGIN VAL := HTMLDB_UTIL. GET_DEFAULT_SCHEMA; END;
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;
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 17-11 describes the parameters available in GET_FILE procedure.
Table 17-11 GET_FILE Parameters
| Parameter | Description |
|---|---|
p_file_id |
ID in HTMLDB_APPLICATION_FILES of the file to be downloaded. HTMLDB_APPLICATION_FILES is a view on all files uploaded to your workspace. The following example demonstrates how to use HTMLDB_APPLICATION_FILES:
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;
|
p_mime_type |
Mime type of the file to download. |
p_inline |
Valid values include YES and NO. YES to display inline in a browser. NO to download as attachment. |
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 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;
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;
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;
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;
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;
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;
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;
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 wish 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 17-12 describes the parameters available in the GET_PREFERENCE function.
Table 17-12 GET_PREFERENCE Parameters
| Parameter | Description |
|---|---|
p_preference |
Name of the preference to retrieve the value. |
p_value |
Value of the preference. |
p_user |
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 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 wish to fetch session state.
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
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;
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;
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 17-13 describes the parameters available in the IS_LOGIN_PASSWORD_VALID function.
Table 17-13 IS_LOGIN_PASSWORD_VALID Parameters
| Parameter | Description |
|---|---|
p_username |
User name in account |
p_password |
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
p_username identifies the user name to be tested.
Example
DECLARE VAL BOOLEAN;
BEGIN
VAL := HTMLDB_UTIL.IS_USERNAME_UNIQUE(
p_username=>'SCOTT');
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
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;
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 17-14 describes the parameters available in the REMOVE_PREFERENCE procedure.
Table 17-14 REMOVE_PREFERENCE Parameters
| Parameter | Description |
|---|---|
p_preference |
Name of the preference to remove. |
p_user |
User for whom the preference is for. |
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
p_user 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 17-15 describes the parameters available in the REMOVE_USER procedure.
Table 17-15 REMOVE_USER Parameters
| Parameter | Description |
|---|---|
p_user_id |
The numeric primary key of the user account record. |
p_user_name |
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 to them with a message. 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 17-16describes the parameters available in the RESET_PW procedure.
Table 17-16 RESET_PW Parameters
| Parameter | Description |
|---|---|
p_user |
The user name of the user account |
p_msg |
Message text to be emailed to user. |
Example
BEGIN
HTMLDB_UTIL.REMOVE_USER(
p_user => 'SCOTT',
p_msg => 'Contact help desk at 555-1212 with questions');
END;
To increase performance, Oracle HTML DB caches security checks. You can use this procedure to undo caching thus requiring all security checks be revalidated for the current user. Use this procedure if you wish 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;
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 17-17 describes the parameters available in the SET_EMAIL procedure.
Table 17-17 SET_EMAIL Parameters
| Parameter | Description |
|---|---|
p_userid |
The numeric ID of the user account. |
p_email |
The e-mail 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 17-19 describes the parameters available in the SET_FIRST_NAME procedure.
Table 17-18 SET_FIRST_NAME Parameters
| Parameter | Description |
|---|---|
p_userid |
The numeric ID of the user account. |
p_first_name |
FIRST_NAME value to be saved in 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 17-19 describes the parameters available in the SET_LAST_NAME procedure.
Table 17-19 SET_LAST_NAME Parameters
| Parameter | Description |
|---|---|
p_userid |
The numeric ID of the user account. |
p_last_name |
LAST_NAME value to be saved in 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 17-20 describes the parameters available in the SET_USERNAME procedure.
Table 17-20 SET_USERNAME Parameters
| Parameter | Description |
|---|---|
p_userid |
The numeric ID of the user account. |
p_user_name |
USER_NAME value to be saved in 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 17-21 describes the parameters available in the SET_PREFERENCE procedure.
Table 17-21 SET_PREFERENCE Parameters
| Parameter | Description |
|---|---|
p_preference |
Name of the preference (case-sensitive). |
p_value |
Value of the preference. |
p_user |
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 HTML DB session.
Syntax
HTMLDB_UTIL.SET_SESSION_STATE (
p_name IN VARCHAR2 DEFAULT NULL,
p_value IN VARCHAR2 DEFAULT NULL);
Parameters
Table 17-22 describes the parameters available in the SET_SESSION_STATE procedure.
Table 17-22 SET_SESSION_STATE Parameters
| Parameter | Description |
|---|---|
p_name |
Name of the application or page level item for which you are setting sessions state. |
p_value |
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 17-23 describes the parameters available in the STRING_TO_TABLE function.
Table 17-23 STRING_TO_TABLE Parameters
| Parameter | Description |
|---|---|
p_string |
String to be converted into a PL/SQL table of type HTMLDB_APPLICATION_GLOBAL.VC_ARR2. |
p_separator |
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 17-24 describes the parameters available in the TABLE_TO_STRING function.
Table 17-24 TABLE_TO_STRING Parameters
| Parameter | Description |
|---|---|
p_string |
String separator. Default separator is a colon (:). |
p_table |
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 HEX) all special characters that include spaces, question marks, and ampersands.
Syntax
HTMLDB_UTIL.URL_ENCODE (
p_url IN VARCHAR2)
RETURN VARCHAR2;
Parameters
p_string 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;
You can use the HTMLDB_MAIL package to send an e-mail from an Oracle HTML DB 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.
HTMLDB_MAIL contains two procedures. Use HTMLDB_MAIL.SEND to send an outbound e-mail 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 e-mail is to create a background job (using a DBMS_JOB package) to periodically send all mail messages stored in the active mail queue. |
This procedure sends an outbound e-mail 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 e-mail messages, including partial messages or messages with extraneous exclamation points.
Plain text and HTML e-mail 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 e-mail 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 e-mail 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 e-mail. 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 17-25 describes the parameters available in the SEND procedure.
| Parameter | Description |
|---|---|
p_to |
Valid e-mail address to which the e-mail will be sent (required). For multiple e-mail addresses, use a comma separated list. |
p_from |
E-mail address from which the e-mail will be sent (required). This e-mail address must be a valid address. Otherwise, the message will not be sent. |
p_body |
Body of the e-mail in plain text, not HTML (required). If a value is passed to p_body_html, then this is the only text the recipient sees. If a value is not passed to p_body_html, then this text only displays for e-mail clients that do not support HTML or have HTML disabled. A carriage return or line feed (CRLF) must be included every 1000 characters. |
p_body_html |
Body of the e-mail in HTML format. This must be a full HTML document including the <html> and <body> tags. A single line cannot exceed 1000 characters without a carriage return or line feed (CRLF). |
p_subj |
Subject of the e-mail. |
p_cc |
Valid e-mail addresses to which the e-mail is copied. For multiple e-mail addresses, use a comma separated list. |
p_bcc |
Valid e-mail addresses to which the e-mail is blind copied. For multiple e-mail addresses, use a comma separated list. |
Examples
The following example demonstrates how to use HTMLDB_MAIL.SEND to send a plain text e-mail 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 e-mail 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 HTML DB stores unsent e-mail 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. This procedure requires two input parameters:
p_smtp_hostname defines the hostname of your SMTP gateway
p_smtp_portno defines port number of your SMTP gateway (for example, 25)
Oracle HTML DB 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 e-mail is to create a background job (using a DBMS_JOB package) to periodically send all mail messages stored in the active mail queue.
Syntax
HTMLDB_MAIL.PUSH_QUEUE(
p_smtp_hostname IN VARCHAR2 DEFAULT,
p_smtp_portno IN NUMBER DEFAULT;
Parameters
Table 17-26 describes the parameters available in the HTMLDB_MAIL procedure.
Table 17-26 PUSH_QUEUE Parameters
| Parameters | Description |
|---|---|
| p_smtp_hostname | SMTP gateway hostname. |
| p_smtp_portno | SMTP gateway port number. |
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 hostname is defined as smtp01.oracle.com and the SMTP gateway port number is 25.
SQLPLUS / <<EOF
FLOWS_010600.HTMLDB_MAIL.PUSH_QUEUE('smtp01.oracle.com','25');
DISCONNECT
EXIT
EOF
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_delimitor IN VARCHAR2 DEFAULT)
RETURN VARCHAR2;
Parameters
Table 17-27 describes the parameters available in the CHECKBOX function.
Table 17-27 CHECKBOX Parameters
| Parameter | Description |
|---|---|
p_idx |
Number which determines which HTMLDB_APPLICATION global will be used. Valid range of values is 1 to 50. For example 1 creates F01 and 2 creates F02. |
p_value |
Value of a check box, hidden field, or input form item. |
p_attributes |
Controls HTML tag attributes (such as disabled). |
p_checked_values |
Values to be checked by default. |
p_checked_values_delimitor |
Delimits the values in the previous parameter, p_checked_values. |
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 next 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 next 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. DATE_POPUP dynamically generates a date field that has 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 17-28 describes the parameters available in the DATE_POPUP function.
Table 17-28 DATE_POPUP Parameters
| Parameter | Description |
|---|---|
p_idx |
Number which determines which HTMLDB_APPLICATION global will be used.Valid range of values is 1 to 50. For example 1 creates F01 and 2 creates F02. |
p_row |
p_row is deprecated. Anything specified for this value will be ignored. |
p_value |
Value of a field item. |
p_date_format |
Valid database date format. |
p_size |
Controls HTML tag attributes (such as disabled). |
p_maxlength |
Determine the maximum number of enterable characters. Becomes the maxlength attribute of the <input > HTML tag. |
p_attributes |
Extra HTML parameters you wish 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 17-29 describes the parameters available in the DISPLAY_AND_SAVE.
Table 17-29 DISPLAY_AND_SAVE Parameters
| Parameter | Description |
|---|---|
p_idx |
Number which determines which HTMLDB_APPLICATION global will be used.Valid range of values is 1 to 50. For example 1 creates F01 and 2 creates F02. |
p_value |
Current value. |
p_item_id |
HTML attribute ID for the <input> tag. |
p_item_label |
Label of the text field item. |
Example
The following example demonstrates how to use HTMLDB_ITEM.DISPLAY_AND_SAVE.
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 17-30 describes the parameters available in the HIDDEN function.
| Parameter | Description |
|---|---|
p_idx |
Number to identify the item you wish to generate. The number will determine which G_FXX global is populated.
See Also: "HTMLDB_APPLICATION" |
p_value |
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. Consider the following sample SLQ query:
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 17-32 describes the parameters available in the MD5_CHECKSUM function.
Table 17-31 MD5_HIDDEN Parameters
| Parameter | Description |
|---|---|
p_value01
...
|
Fifty available inputs. Parameters that are not supplied default to null. |
p_col_sep |
String used to separate p_value inputs. Defaults to the pipe symbol (|). |
Example
SELECT HTMLDB_ITEM.MD5_CHECKSUM(ename,job,sal) FROM emp
This function is used for lost update detection which 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 assure 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 17-32 describes the parameters available in the MD5_HIDDEN function.
Table 17-32 MD5_HIDDEN Parameters
| Parameter | Description |
|---|---|
p_idx |
Indicates the form element to be generated. For example, 1 equals F01 and 2 equals F02. Typically the p_idx parameter is constant for a given column. |
p_value01
...
|
Fifty available inputs. Parameters not supplied default to null. |
p_col_sep |
String used to separate p_value inputs. Defaults to the pipe symbol (|). |
Example
p_idx 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 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 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 17-33 describes the parameters available in the SELECT_LIST function.
Table 17-33 SELECT_LIST Parameters
| Parameter | Description |
|---|---|
p_idx |
Form element name. For example, 1 equals F01 and 2 equals F02. Typically the P_IDX parameter is constant for a given column. |
p_value |
Current value. This value should be a value in the P_LIST_VALUES parameter. |
p_list_values |
List of static values separated by commas. Display values and return values are separated by semicolons.
Note that this is only available in the |
p_attributes |
Extra HTML parameters you wish to add. |
p_show_null |
Extra select option to enable the NULL selection. Range of values is YES and NO. |
p_null_value |
Value to be returned when a user selects the null option. Only relevant when P_SHOW_NULL equals YES. |
p_item_id |
HTML attribute ID for the <input> tag. |
p_item_label |
Label of the select list. |
p_show_extra |
Show 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 17-34 describes the parameters available in the SELECT_LIST_FROM_LOV function.
Table 17-34 SELECT_LIST_FROM_LOV Parameters
| Parameter | Description |
|---|---|
p_idx |
Form element name. For example, 1 equals F01 and 2 equals F02. Typically the p_idx parameter is constant for a given column. |
p_value |
Current value. This value should be a value in the p_list_values parameter. |
p_lov |
Text name of a application list of values. This list of values must be defined in your application. This parameter is used only by the select_list_from_lov function. |
p_attributes |
Extra HTML parameters you wish to add. |
p_show_null |
Extra select option to enable the NULL selection. Range of values is YES and NO. |
p_null_value |
Value to be returned when a user selects the null option. Only relevant when p_show_null equals YES. |
p_null_text |
Value to be displayed when a user selects the null option. Only relevant when p_show_null equals YES. |
p_item_id |
HTML attribute ID for the <input> tag. |
p_item_label |
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(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.
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 17-35 describes the parameters available in the SELECT_LIST_FROM_LOV_XL function.
Table 17-35 SELECT_LIST_FROM_LOV_XL Parameters
| Parameter | Description |
|---|---|
p_idx |
Form element name. For example, 1 equals F01 and 2 equals F02. Typically the p_idx parameter is constant for a given column. |
p_value |
Current value. This value should be a value in the p_list_values parameter. |
p_lov |
Text name of a list of values. This list of values must be defined in your application. This parameter is used only by the select_list_from_lov function. |
p_attributes |
Extra HTML parameters you wish to add. |
p_show_null |
Extra select option to enable the NULL selection. Range of values is YES and NO. |
p_null_value |
Value to be returned when a user selects the null option. Only relevant when p_show_null equals YES. |
p_null_text |
Value to be displayed when a user selects the null option. Only relevant when p_show_null equals YES. |
p_item_id |
HTML attribute ID for the <input> tag. |
p_item_label |
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 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 17-36 describes the parameters available in the SELECT_LIST_FROM_QUERY function.
Table 17-36 SELECT_LIST_FROM_QUERY Parameters
| Parameter | Description |
|---|---|
p_idx |
Form element name. For example, 1 equals F01 and 2 equals F02. Typically the p_idx parameter is constant for a given column. |
p_value |
Current value. This value should be a value in the p_list_values parameter. |
p_query |
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 |
p_attributes |
Extra HTML parameters you wish to add. |
p_show_null |
Extra select option to enable the NULL selection. Range of values is YES and NO. |
p_null_value |
Value to be returned when a user selects the null option. Only relevant when p_show_null equals YES. |
p_null_text |
Value to be displayed when a user selects the null option. Only relevant when p_show_null equals YES. |
p_item_id |
HTML attribute ID for the <input> tag. |
p_item_label |
Label of the select list. |
p_show_extra |
Show the current value even if the value of p_value is not located in the select list. |
Example
The following 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 dynamically generates very large select lists (greater than 32K) 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_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 17-37 describes the parameters available in the SELECT_LIST_FROM_QUERY_XL function.
Table 17-37 SELECT_LIST_FROM_QUERY_XL Parameters
| Parameter | Description |
|---|---|
p_idx |
Form element name. For example, 1 equals F01 and 2 equals F02. Typically the p_idx parameter is constant for a given column. |
p_value |
Current value. This value should be a value in the p_list_values parameter. |
p_query |
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 |
p_attributes |
Extra HTML parameters you wish to add. |
p_show_null |
Extra select option to enable the NULL selection. Range of values is YES and NO. |
p_null_value |
Value to be returned when a user selects the null option. Only relevant when p_show_null equals YES. |
p_null_text |
Value to be displayed when a user selects the null option. Only relevant when p_show_null equals YES. |
p_item_id |
HTML attribute ID for the <input> tag. |
p_item_label |
Label of the select list. |
p_show_extra |
Show the current value even if the value of p_value is not located in the select list. |
Example
The following 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 17-39 describes the parameters available in the TEXT function.
Table 17-38 TEXTAREA Parameters
| Parameter | Description |
|---|---|
p_idx |
Number to identify the item you wish to generate. The number will determine which G_FXX global is populated.
See Also: "HTMLDB_APPLICATION" |
p_value |
Value of a textarea item. |
| p_rows | Height of the textarea (HTML rows attribute) |
| p_cols | Width of the textarea (HTML cols attribute). |
p_attributes |
Extra HTML parameters you wish to add. |
p_item_id |
HTML attribute ID for the <input> tag. |
p_item_label |
Label of the text textarea item. |
Example
The following example demonstrates a textarea 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 17-39 describes the parameters available in the TEXT function.
| Parameter | Description |
|---|---|
p_idx |
Number to identify the item you wish to generate. The number will determine which G_FXX global is populated.
See Also: "HTMLDB_APPLICATION" |
p_value |
Value of a text field item. |
p_size |
Controls HTML tag attributes (such as disabled). |
p_maxlength |
Maximum number of characters that can be entered in the text box. |
p_attributes |
Extra HTML parameters you wish to add. |
p_item_id |
HTML attribute ID for the <input> tag. |
p_item_label |
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 an 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 17-40 describes the parameters available in the TEXT_FROM_LOV function.
Table 17-40 TEXT_FROM_LOV Parameters
| Parameter | Description |
|---|---|
p_value |
Value of a field item. |
p_lov |
Text name of a shared list of values. This list of values must be defined in your application. |
p_null_text |
Value to be displayed when the value of the field item is null or a corresponding entry is not located for the value p_value in the list of values. |
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 17-40 describes the parameters available in the TEXT_FROM_LOV_QUERY function.
Table 17-41 TEXT_FROM_LOV_QUERY Parameters
| Parameter | Description |
|---|---|
p_value |
Value of a field item. |
p_query |
SQL query that is expected to select two columns, a display column and a return column. For example:
SELECT dname, deptno FROM dept |
p_null_text |
Value to be displayed when the value of the field item is null or a corresponding entry is not located for the value p_value in the list of values query. |
Example
The following 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
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 17-42 describes the parameters available in the RADIOGROUP function.
Table 17-42 RADIOGROUP Parameters
| Parameter | Description |
|---|---|
p_idx |
Number which determines which HTMLDB_APPLICATION global will be used. Valid range of values is 1 to 50.For example 1 creates F01 and 2 creates F02. |
p_value |
Value of the radio group. |
p_selected_value |
Value that should be "on", or selected. |
p_display |
Text to display next to the radio option. |
p_attributes |
Extra HTML parameters you wish to add. |
p_onblur |
JavaScript to execute in the onBlur event. |
p_onchange |
JavaScript to execute in the onChange event. |
p_onfocus |
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 generates an HTML popup select list from an application list of values (LOV). Like other available functions in the HTMLDB_ITEM package, POPUP_FROM_LOV 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 17-43 describes the some parameters in the POPUP_FROM_LOV function.
Table 17-43 POPUP_FROM_LOV Parameters
| Parameter | Description |
|---|---|
p_idx |
Form element name. For example, 1 equals F01 and 2 equals F02. Typically, p_idx is a constant for a given column. |
p_value |
Form element current value. This value should be one of the values in the p_lov_name parameter. |
p_lov_name |
Named LOV used for this popup. |
p_width |
Width of the text box. |
p_max_length |
Maximum number of characters that can be entered in the text box. |
p_form_index |
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 which posts to a different Web site). If this form comes before the |
p_escape_html |
Replacements for special characters that require an escaped equivalent.
Range of values is YES and NO. If YES, special characters will be escaped. This parameter is useful if you know your query will return illegal HTML. |
p_max_elements |
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 more narrow set of results. |
p_attributes |
Additional HTML attributes to use for the form item. |
p_ok_to_query |
Range of values is YES and NO. If YES, a popup returns first set of rows for the LOV. If NO, a search is initiated to return rows. |
p_item_id |
ID attribute of the form element. |
p_item_label |
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, POPUP_FROM_QUERY 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 17-44 describes the parameters in the POPUP_FROM_QUERY function.
Table 17-44 POPUP_FROM_QUERY Parameters
| Parameter | Description |
|---|---|
p_idx |
Form element name. For example, 1 equals F01 and 2 equals F02. Typically, p_idx is a constant for a given column. |
p_value |
Form element current value. This value should be one of the values in the p_lov_query parameter. |
p_lov_query |
SQL query that is expected to select two columns (a display column and a return column). For example:
SELECT dname, deptno FROM dept |
p_width |
Width of the text box. |
p_max_length |
Maximum number of characters that can be entered in the text box. |
p_form_index |
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 which posts to a different Web site). If this form comes before the |
p_escape_html |
Replacements for special characters that require an escaped equivalent.
Range of values is YES and NO. If YES, special characters will be escaped. This parameter is useful if you know your query will return illegal HTML. |
p_max_elements |
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 more narrow set of results. |
p_attributes |
Additional HTML attributes to use for the form item. |
p_ok_to_query |
Range of values is YES and NO. If YES, a popup returns first set of rows for the LOV. If NO, a search is initiated to return rows. |
p_item_id |
ID attribute of the form element. |
p_item_label |
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). Like other available functions in the HTMLDB_ITEM package, POPUPKEY_FROM_LOV 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 17-45 describes the some parameters in the POPUPKEY_FROM_LOV function.
Table 17-45 POPUPKEY_FROM_LOV Parameters
| Parameter | Description |
|---|---|
p_idx |
Identifies a form element name. For example, 1 equals F01 and 2 equals F02. Typically, p_idx is a constant for a given column
Because of the behavior of SELECT HTMLDB_ITEM.POPUPKEY_FROM_LOV (1,deptno,'DEPT') dt, HTMLDB_ITEM.HIDDEN(3,empno) eno |
p_value |
Indicates the current value. This value should be one of the values in the P_LOV_NAME parameter. |
p_lov_name |
Identifies a named LOV used for this popup. |
p_width |
Width of the text box. |
p_max_length |
Maximum number of characters that can be entered in the text box. |
p_form_index |
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 which posts to a different Web site). If this form comes before the |
p_escape_html |
Replacements for special characters that require an escaped equivalent.
This parameter is useful if you know your query will return illegal HTML. |
p_max_elements |
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 more narrow set of results. |
p_attributes |
Additional HTML attributes to use for the form item. |
p_ok_to_query |
Range of values is YES and NO. If YES, a popup returns first set of rows for the LOV. If NO, a search is initiated to return rows. |
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. Like other available functions in the HTMLDB_ITEM package, POPUPKEY_FROM_QUERY 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 17-46 describes the some parameters in the POPUPKEY_FROM_QUERY function.
Table 17-46 POPUPKEY_FROM_QUERY Parameters
| Parameter | Description |
|---|---|
p_idx |
Form element name. For example, 1 equals F01 and 2 equals F02. Typically, p_idx is a constant for a given column.
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 |
p_value |
Form element current value. This value should be one of the values in the P_LOV_QUERY parameter. |
p_lov_query |
LOV query used for this popup. |
p_width |
Width of the text box. |
p_max_length |
Maximum number of characters that can be entered in the text box. |
p_form_index |
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 which posts to a different Web site). If this form comes before the |
p_escape_html |
Replacements for special characters that require an escaped equivalent.
This parameter is useful if you know your query will return illegal HTML. |
p_max_elements |
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 more narrow set of results. |
p_attributes |
Additional HTML attributes to use for the form item. |
p_ok_to_query |
Range of values is YES and NO. If YES, a popup returns first set of rows for the LOV. If NO, a search is initiated to return rows. |
p_item_id |
ID attribute of the form element. |
p_item_label |
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
The HTMLDB_APPLICATION package is a PL/SQL package that implements the Oracle HTML DB rendering engine. You can use this package to take advantage of a number of global variables. Table 17-47 describes the global variables available in HTMLDB_APPLICATION.
Table 17-47 Global Variables Available in HTMLDB_APPLICATION
| Global Variable | Description |
|---|---|
G_USER |
Specifies the currently logged in user. |
G_FLOW_ID |
Specifies the ID of the currently running application. |
G_FLOW_STEP_ID |
Specifies the ID of the currently running page. |
G_FLOW_OWNER |
Specifies the schema to parse for the currently running application. |
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. Consider the following 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>
Since 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" ALUE="array element 2"> <INPUT TYPE="text" NAME="F01" SIZE="32" MAXLENGTH="32" VALUE="array element 3">
Note that following PL/SQL 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. Since 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;
You can also use Oracle HTML DB public utility functions to convert an array into a single value. For example:
htp.p(HTMLDB_UTIL.TABLE_TO_STRING(HTMLDB_APPLICATION.G_F01));
This function is enables you to reference G_F01 to G_F50 values in an application process that performs actions on data. The following sample process demonstrates the insertion of values into an 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 HTMLDB_CUSTOM_AUTH 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)
Syntax
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 17-48 describes the parameters available in the GET_COOKIE_PROPS procedure.
Table 17-48 GET_COOKIE_PROPS Parameters
| Parameter | Description |
|---|---|
p_app_id |
An application ID in the current workspace. |
p_cookie_name |
The cookie name. |
p_cookie_path |
The cookie path. |
p_cookie_domain |
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 17-49 describes the parameters available in the GET_LDAP_PROPS procedure.
Table 17-49 GET_LDAP_PROPS Parameters
| Parameter | Description |
|---|---|
p_ldap_host |
LDAP host name. |
p_ldap_port |
LDAP port number. |
p_ldap_host |
LDAP DN string. |
p_ldap_host |
LDAP host name. |
p_ldap_edit_function |
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 HTML DB sequence generator. This function returns a number.
Syntax
FUNCTION GET_NEXT_SESSION_ID RETURN NUMBER;
This function returns the Oracle HTML DB 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 HTML DB 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 17-50 describes the parameters available in the LOGIN procedure.
| Parameter | Description |
|---|---|
p_uname |
Login name of the user. |
p_password |
Clear text user password. |
p_session_id |
Current Oracle HTML DB session ID. |
p_app_page |
Current application ID. After login page separated by a colon (:). |
p_entry_point |
Internal use only. |
p_preserve_case |
If true, do not upper p_uname during session registration |
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 17-51 describes the parameters available in the LOGOUT procedure.
| Parameter | Description |
|---|---|
p_this_app |
Current application ID. |
p_next_app_page_sess |
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). |
p_next_url |
URL to redirect to (use this instead of p_next_app_page_sess). |
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 HTML DB 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 17-52 describes the parameters available in the POST_LOGIN procedure.
Table 17-52 POST_LOGIN Parameters
| Parameter | Description |
|---|---|
p_uname |
Login name of user. |
p_session_id |
Current Oracle HTML DB session ID. |
p_app_page |
Current application ID and after login page separated by a colon (:). |
p_preserve_case |
If true, do not include p_uname in upper case during session registration. |
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 HTML DB 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)