17 Oracle HTML DB APIs

CHANGE_CURRENT_USER_PW Procedure

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

Syntax

HTMLDB_UTIL.CHANGE_CURRENT_USER_PW(
    p_new_password IN VARCHAR2);

Parameters

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

Example

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

CLEAR_APP_CACHE Procedure

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

Syntax

HTMLDB_UTIL.CLEAR_APP_CACHE (
    p_app_id    IN    VARCHAR2 DEFAULT NULL);

Parameters

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

Example

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

CLEAR_PAGE_CACHE Procedure

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

Syntax

HTMLDB_UTIL.CLEAR_PAGE_CACHE (
    p_page_id IN NUMBER DEFAULT NULL);

Parameters

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

Example

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

CLEAR_USER_CACHE Procedure

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

Syntax

HTMLDB_UTIL.CLEAR_USER_CACHE;

Example

BEGIN
       HTMLDB_UTIL.CLEAR_USER_CACHE;
END;

COUNT_CLICK Procedure

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

Syntax

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

Parameters

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

Example

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

Where NNN equals your workspace ID.

CREATE_USER Procedure

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

Syntax

HTMLDB_UTIL.CREATE_USER(
    P_USER_ID                      NUMBER                  IN     DEFAULT NULL
    P_USER_NAME                    VARCHAR2                IN
    P_FIRST_NAME                   VARCHAR2                IN     DEFAULT NULL
    P_LAST_NAME                    VARCHAR2                IN     DEFAULT NULL
    P_DESCRIPTION                  VARCHAR2                IN     DEFAULT NULL
    P_EMAIL_ADDRESS                VARCHAR2                IN     DEFAULT NULL
    P_WEB_PASSWORD                 VARCHAR2                IN
    P_WEB_PASSWORD_FORMAT          VARCHAR2                IN     DEFAULT NULL
    P_GROUP_IDS                    VARCHAR2                IN     DEFAULT NULL
    P_DEVELOPER_PRIVS              VARCHAR2                IN     DEFAULT NULL
    P_DEFAULT_SCHEMA               VARCHAR2                IN     DEFAULT NULL
    P_ALLOW_ACCESS_TO_SCHEMAS      VARCHAR2                IN     DEFAULT NULL
    P_ATTRIBUTE_01                 VARCHAR2                IN     DEFAULT NULL
    P_ATTRIBUTE_02                 VARCHAR2                IN     DEFAULT NULL
    P_ATTRIBUTE_03                 VARCHAR2                IN     DEFAULT NULL
    P_ATTRIBUTE_04                 VARCHAR2                IN     DEFAULT NULL
    P_ATTRIBUTE_05                 VARCHAR2                IN     DEFAULT NULL
    P_ATTRIBUTE_06                 VARCHAR2                IN     DEFAULT NULL
    P_ATTRIBUTE_07                 VARCHAR2                IN     DEFAULT NULL
    P_ATTRIBUTE_08                 VARCHAR2                IN     DEFAULT NULL
    P_ATTRIBUTE_09                 VARCHAR2                IN     DEFAULT NULL
    P_ATTRIBUTE_10                 VARCHAR2                IN     DEFAULT NULL)

Parameters

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

Example

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

CREATE_USER_GROUP Procedure

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

Syntax

HTMLDB_UTIL.CREATE_USER_GROUP(
    P_ID                       NUMBER                  IN
    P_GROUP_NAME               VARCHAR2                IN
    P_SECURITY_GROUP_ID        NUMBER                  IN
    P_GROUP_DESC               VARCHAR2                IN);

Parameter

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

Example

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

CURRENT_USER_IN_GROUP Function

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

Syntax

HTMLDB_UTIL.CURRENT_USER_IN_GROUP(
    p_group_name    IN VARCHAR2)
RETURN BOOLEAN;

HTMLDB_UTIL.CURRENT_USER_IN_GROUP(
    p_group_id    IN NUMBER)
RETURN BOOLEAN;

Parameters

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

Example

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

EDIT_USER Procedure

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

Syntax

EDIT_USER (
    P_USER_ID                      NUMBER                  IN
    P_USER_NAME                    VARCHAR2                IN
    P_FIRST_NAME                   VARCHAR2                IN     DEFAULT
    P_LAST_NAME                    VARCHAR2                IN     DEFAULT
    P_WEB_PASSWORD                 VARCHAR2                IN     DEFAULT
    P_NEW_PASSWORD                 VARCHAR2                IN     DEFAULT
    P_EMAIL_ADDRESS                VARCHAR2                IN     DEFAULT
    P_START_DATE                   VARCHAR2                IN     DEFAULT
    P_END_DATE                     VARCHAR2                IN     DEFAULT
    P_EMPLOYEE_ID                  VARCHAR2                IN     DEFAULT
    P_ALLOW_ACCESS_TO_SCHEMAS      VARCHAR2                IN     DEFAULT
    P_PERSON_TYPE                  VARCHAR2                IN     DEFAULT
    P_DEFAULT_SCHEMA               VARCHAR2                IN     DEFAULT
    P_GROUP_IDS                    VARCHAR2                IN     DEFAULT
    P_DEVELOPER_ROLES              VARCHAR2                IN     DEFAULT
    P_DESCRIPTION                  VARCHAR2                IN     DEFAULTIN);

Parameters

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

EXPORT_USERS Procedure

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

Syntax

HTMLDB_UTIL.EXPORT_USERS(
    p_export_format in varchar2 default 'UNIX');

Parameters

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

Example

BEGIN
  HTMLDB_UTIL.EXPORT_USERS;
END;

FETCH_APP_ITEM Function

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

Syntax

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

Parameters

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

Example

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

FETCH_USER Procedure

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

Syntax

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

Parameters

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

FIND_SECURITY_GROUP_ID Function

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

Syntax

HTMLDB_UTIL.FIND_SECURITY_GROUP_ID(
    p_workspace    IN VARCHAR2
RETURN NUMBER;

Parameters

p_workspace is the name of the workspace.

Example

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

FIND_WORKSPACE Function

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

Syntax

HTMLDB_UTIL.FIND_WORKSPACE(
    p_security_group_id    IN VARCHAR2)
RETURN VARCHAR2;

Parameters

p_security_group_id is the security group ID of a workspace.

Example

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

GET_ATTRIBUTE Function

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

Syntax

HTMLDB_UTIL.GET_ATTRIBUTE(
    P_USERNAME                IN VARCHAR2
    P_ATTRIBUTE_NUMBER        IN NUMBER)
RETURN VARCHAR2;

Parameters

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

Example

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

GET_CURRENT_USER_ID Function

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

Syntax

HTMLDB_UTIL.GET_CURRENT_USER_ID;
RETURN NUMBER;

Example

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

GET_DEFAULT_SCHEMA Function

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

Syntax

HTMLDB_UTIL.GET_DEFAULT_SCHEMA;
RETURN VARCHAR2;

Example

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

GET_EMAIL Function

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

Syntax

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

Parameters

p_username is the user name in the account.

Example

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

GET_FILE Procedure

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

Syntax

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

Parameters

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

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

Example

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

GET_FILE_ID Function

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

Syntax

HTMLDB_UTIL.GET_FILE_ID (
    p_fname    IN   VARCHAR2)
RETURN NUMBER;

Parameters

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

Example

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

GET_FIRST_NAME Function

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

Syntax

HTMLDB_UTIL.GET_FIRST_NAME
   P_USERNAME IN VARCHAR2);
RETURN VARCHAR2;

Parameters

p_username identifies the user name in the account.

Example

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

GET_GROUPS_USER_BELONGS_TO Function

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

Syntax

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

Parameters

p_username identifies the user name in the account.

Example

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

GET_GROUP_ID Function

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

Syntax

HTMLDB_UTIL.GET_GROUP_ID(
   P_GROUP_NAME);
RETURN VARCHAR2;

Parameters

p_group_name identifies the user name in the account.

Example

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

GET_GROUP_NAME Function

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

Syntax

HTMLDB_UTIL.GET_GROUP_NAME(
   P_GROUP_ID);
RETURN NUMBER;

Parameters

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

Example

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

GET_LAST_NAME Function

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

Syntax

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

Parameters

p_username is the user name in the user account record.

Example

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

GET_USERNAME Function

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

Syntax

HTMLDB_UTIL.GET_USERNAME(
   P_USERID);
RETURN NUMBER;

Parameters

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

Example

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

GET_NUMERIC_SESSION_STATE Function

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

Syntax

HTMLDB_UTIL.GET_NUMERIC_SESSION_STATE (
    p_item     IN VARCHAR2) 
    RETURN NUMBER;

Parameters

p_item is the case insensitive name of the item for which you 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;

GET_PREFERENCE Function

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

Syntax

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

Parameters

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

Example

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

GET_SESSION_STATE Function

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

Syntax

HTMLDB_UTIL.GET_SESSION_STATE (
    p_item    IN   VARCHAR2) 
    RETURN VARCHAR2;

Parameters

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

Example

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

GET_USER_ID Function

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

Syntax

HTMLDB_UTIL.GET_USER_ID(
   P_USERNAME);
RETURN VARCHAR2;

Parameters

p_username identifies the name of a user in the workspace.

Example

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

GET_USER_ROLES Function

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

Syntax

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

Parameters

p_username identifies a user name in the account.

Example

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

IS_LOGIN_PASSWORD_VALID Function

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

Syntax

HTMLDB_UTIL.IS_LOGIN_PASSWORD_VALID(
P_USERNAME IN VARCHAR2,
   P_PASSWORD IN VARCHAR2);
RETURN BOOLEAN;

Parameters

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

Example

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

IS_USERNAME_UNIQUE Function

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

Syntax

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

Parameters

p_username identifies the user name to be tested.

Example

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

PUBLIC_CHECK_AUTHORIZATION Function

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

Syntax

HTMLDB_UTIL.PUBLIC_CHECK_AUTHORIZATION (
    p_security_scheme    IN    VARCHAR2) 
    RETURN BOOLEAN;

Parameters

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

Example

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

REMOVE_PREFERENCE Procedure

This function removes the preference for the supplied user.

Syntax

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

Parameters

Table 17-14 describes the parameters available in the REMOVE_PREFERENCE procedure.

Example

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

REMOVE_SORT_PREFERENCES Procedure

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

Syntax

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

Parameters

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

Example

BEGIN
      HTMLDB_UTIL.REMOVE_SORT_PREFERENCES(:APP_USER);
END;

REMOVE_USER Procedure

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

Syntax

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

Parameters

Table 17-15 describes the parameters available in the REMOVE_USER procedure.

Example

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

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

RESET_PW Procedure

This procedure resets the password for a named user and emails it 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.

Example

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

RESET_AUTHORIZATIONS Procedure

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;

SET_EMAIL Procedure

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

Syntax

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

Parameters

Table 17-17 describes the parameters available in the SET_EMAIL procedure.

Example

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

SET_FIRST_NAME Procedure

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

Syntax

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

Parameters

Table 17-19 describes the parameters available in the SET_FIRST_NAME procedure.

Example

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

SET_LAST_NAME Procedure

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

Syntax

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

Parameters

Table 17-19 describes the parameters available in the SET_LAST_NAME procedure.

Example

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

SET_USERNAME Procedure

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

Syntax

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

Parameters

Table 17-20 describes the parameters available in the SET_USERNAME procedure.

Example

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

SET_PREFERENCE Procedure

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

Syntax

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

Parameters

Table 17-21 describes the parameters available in the SET_PREFERENCE procedure.

Example

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

SET_SESSION_STATE Procedure

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

Syntax

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

Parameters

Table 17-22 describes the parameters available in the SET_SESSION_STATE procedure.

Example

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

STRING_TO_TABLE Function

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

Syntax

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

Parameters

Table 17-23 describes the parameters available in the STRING_TO_TABLE function.

Example

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

TABLE_TO_STRING Function

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

Syntax

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

Parameters

Table 17-24 describes the parameters available in the TABLE_TO_STRING function.

Example

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

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

URL_ENCODE Function

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

Syntax

HTMLDB_UTIL.URL_ENCODE (
    p_url   IN    VARCHAR2) 
    RETURN VARCHAR2;

Parameters

p_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;

SEND Procedure

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.

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;
/

PUSH_QUEUE Procedure

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.

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

CHECKBOX Function

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.

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;

DATE_POPUP Function

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.

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

DISPLAY_AND_SAVE Function

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.

Example

The following example demonstrates how to use HTMLDB_ITEM.DISPLAY_AND_SAVE.

SELECT HTMLDB_ITEM.DISPLAY_AND_SAVE(10,empno) c FROM emp

HIDDEN Function

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.

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.

MD5_CHECKSUM Function

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.

Example

SELECT HTMLDB_ITEM.MD5_CHECKSUM(ename,job,sal)
FROM emp

MD5_HIDDEN Function

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.

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

MULTI_ROW_UPDATE Procedure

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:,:,:,:,

SELECT_LIST Function

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.

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

SELECT_LIST_FROM_LOV Function

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.

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

SELECT_LIST_FROM_LOV_XL Function

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.

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

SELECT_LIST_FROM_QUERY Function

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.

SELECT dname, deptno FROM dept

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

SELECT_LIST_FROM_QUERY_XL Function

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.

SELECT dname, deptno FROM dept

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

TEXTAREA

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.

Example

The following example demonstrates a textarea based on a SQL query.

SELECT HTMLDB_ITEM.TEXTAREA(3,ename,5,80) a
FROM emp

TEXT Function

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.

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

TEXT_FROM_LOV Function

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.

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

TEXT_FROM_LOV_QUERY Function

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.

SELECT dname, deptno FROM dept

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

RADIOGROUP Function

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.

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

POPUP_FROM_LOV Function

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.

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

POPUP_FROM_QUERY Function

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.

SELECT dname, deptno FROM dept

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

POPUPKEY_FROM_LOV Function

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.

SELECT HTMLDB_ITEM.POPUPKEY_FROM_LOV (1,deptno,'DEPT') dt,
HTMLDB_ITEM.HIDDEN(3,empno) eno

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

POPUPKEY_FROM_QUERY Function

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.

SELECT HTMLDB_ITEM.POPUPKEY_FROM_QUERY (1,deptno,'SELECT dname, deptno FROM dept') dt,
HTMLDB_ITEM.HIDDEN(3,empno) eno

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

Referencing Arrays

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;

Referencing Values Within an On Submit Process

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;

Converting an Array to a Single Value

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;

APPLICATION_PAGE_ITEM_EXISTS Function

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;

CURRENT_PAGE_IS_PUBLIC Function

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;

DEFINE_USER_SESSION Procedure

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);

GET_COOKIE_PROPS Procedure

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.

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;

GET_LDAP_PROPS Procedure

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.

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;

GET_NEXT_SESSION_ID Function

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;

GET_SESSION_ID_FROM_COOKIE Function

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;

GET_USERNAME Function

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;

GET_SECURITY_GROUP_ID Function

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;

GET_SESSION_ID Function

This function returns HTMLDB_APPLICATION.G_INSTANCE global variable. GET_SESSION_ID returns a number.

Syntax

PROCEDURE GET_SESSION_ID 
RETURN NUMBER;

GET_USER Function

This function returns the HTMLDB_APPLICATION.G_USER global variable (VARCHAR2).

Syntax

FUNCTION GET_USER 
RETURN VARCHAR2;

IS_SESSION_VALID Function

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;

LOGIN Procedure

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.

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;

LOGOUT Procedure

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.

Example

BEGIN
HTMLDB_CUSTOM_AUTH.LOGOUT (
    p_this_app            => '1000',
    p_next_app_page_sess  => '1000:99');
END;

POST_LOGIN Procedure

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.

Example

BEGIN
HTMLDB_CUSTOM_AUTH.POST_LOGIN (
    p_uname       => 'SCOTT',
    p_session_id  => V('APP_SESSION'),
    p_app_page    => :APP_ID||':1');
END;

SESSION_ID_EXISTS Function

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;

SET_USER Procedure

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)

SET_SESSION_ID Procedure

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)

SET_SESSION_ID_TO_NEXT_VALUE Procedure

This procedure combines the operation of GET_NEXT_SESSION_ID and SET_SESSION_ID in one call.

Syntax

PROCEDURE SETsN_ID_TO_NEXT_VALUE;