Skip Headers

Oracle® HTML DB User's Guide
Release 1.5

Part Number B10992-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

13 Oracle HTML DB APIs

This section describes the APIs available in Oracle HTML DB.

This section contains the following topics:

HTMLDB_UTIL

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:

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

Table 13-1 describes the parameters available in the CLEAR_APP_CACHE procedure.

Table 13-1 CLEAR_APP_CACHE Parameters

Parameter Description
p_app_id 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_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_company     IN    VARCHAR2    DEFAULT NULL);


Parameters

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

Table 13-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_company 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">Click 
here</a>');
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 13-3 describes the parameters available in GET_FILE procedure.

Table 13-3 GET_FILE Parameters

Parameter Description
p_file_id ID in HTMLDB_APPLICATION_FILES of the file to be downloaded.
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;

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

Table 13-4 describes the parameters available in the GET_NUMERIC_SESSION_STATE function.

Table 13-4 GET_SESSION_STATE Parameters

Parameter Description
p_item 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 13-5 describes the parameters available in the GET_PREFERENCE function.

Table 13-5 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;

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

Table 13-6 describes the parameters available in the GET_SESSION_STATE function.

Table 13-6 GET_SESSION_STATE Parameters

Parameter Description
p_item 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;

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

Table 13-7 describes the parameters available in the PUBLIC_CHECK_AUTHORIZATION function.

Table 13-7 PUBLIC_CHECK_AUTHORIZATION Parameters

Parameter Description
p_security_name 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 returns 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 13-8 describes the parameters available in the REMOVE_PREFERENCE procedure.

Table 13-8 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;

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

Table 13-9 describes the parameters available in the REMOVE_SORT_PREFERENCES procedure.

Table 13-9 REMOVE_SORT_PREFERENCES Parameters

Parameter Description
p_user User for whom sorting preference will be removed.


Example
BEGIN
      HTMLDB_UTIL.REMOVE_SORT_PREFERENCES(:APP_USER);
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_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 13-10 describes the parameters available in the SET_PREFERENCE procedure.

Table 13-10 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;

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 13-11 describes the parameters available in the SET_SESSION_STATE procedure.

Table 13-11 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;

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 13-12 describes the parameters available in the STRING_TO_TABLE function.

Table 13-12 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;

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 13-13 describes the parameters available in the TABLE_TO_STRING function.

Table 13-13 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;

URL_ENCODE Function

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


Syntax
HTMLDB_UTIL.URL_ENCODE (
    p_url   IN    VARCHAR2) 
    RETURN VARCHAR2;


Parameters

Table 13-14 describes the parameters available in the URL_ENCODE function.

Table 13-14 URL_ENCODE Parameters

Parameter Description
p_string 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;

HTMLDB_ITEM

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:

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 13-15 describes the parameters available in the CHECKBOX function.

Table 13-15 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 a 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 13-16 describes the parameters available in the DATE_POPUP function.

Table 13-16 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_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.


See Also:

Oracle Database SQL Reference for more information on the TO_CHAR or TO_DATE functions


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

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 13-17 describes the parameters available in the HIDDEN function.

Table 13-17 HIDDEN 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 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.

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 13-19 describes the parameters available in the MD5_CHECKSUM function.

Table 13-18 MD5_HIDDEN Parameters

Parameter Description
p_value01

...

p_value50

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

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 13-19 describes the parameters available in the MD5_HIDDEN function.

Table 13-19 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

...

p_value50

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

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


Parameters

Table 13-20 describes the parameters available in the SELECT_LIST function.

Table 13-20 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 SELECT_LIST 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.


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


Parameters

Table 13-21 describes the parameters available in the SELECT_LIST_FROM_LOV function.

Table 13-21 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 flow list of values. This list of values must be defined in your flow. 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.


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


Parameters

Table 13-22 describes the parameters available in the SELECT_LIST_FROM_LOV_XL function.

Table 13-22 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 flow list of values. This list of values must be defined in your flow. 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.


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


Parameters

Table 13-23 describes the parameters available in the SELECT_LIST_FROM_QUERY function.

Table 13-23 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 SELECT_LIST_FROM_QUERY 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.


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


Parameters

Table 13-24 describes the parameters available in the SELECT_LIST_FROM_QUERY_XL function.

Table 13-24 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 SELECT_LIST_FROM_QUERY_XL 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.


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

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 13-25 describes the parameters available in the TEXT function.

Table 13-25 TEXT 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 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

TEXT_FROM_LOV Function

This function returns the display value of a LOV given its value.


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 13-26 describes the parameters available in the TEXT_FROM_LOV function.

Table 13-26 TEXT_FROM_LOV Parameters

Parameter Description
p_value Display value of the LOV you are retrieving.
p_lov Name of the LOV in your application.
p_null_text Text to display if the value is null.


Example

Suppose you have an LOV called DEPARTMENTS_LOV as shown in the following example:

SELECT dname, deptno FROM dept;

Next, assume you have a SQL Query region and you wish to query the emp table. However, instead of displaying the deptno column (which contains numbers), you wish to show the department name. You can accomplish this by using HTMLDB_ITEM.TEXT_FROM_LOV function. For example:

SELECT ename, job, sal, comm, HTMLDB_ITEM.TEXT_FROM_LOV(deptno,'DEPARTMENTS_LOV') d 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 13-27 describes the parameters available in the RADIOGROUP function.

Table 13-27 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

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 13-28 describes the some parameters in the POPUP_FROM_LOV function.

Table 13-28 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 #FORM_OPEN# substitution string, then its index is zero and the form opened automatically by Oracle HTML DB must be referenced as form 1. This functionality supports the JavaScript used in the popup LOV which passes a value back to a form element.

p_escape_html Replacements for special characters that require an escaped equivalent.
  • &lt; for <

  • &gt; for >

  • &amp; for &

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

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 13-29 describes the parameters in the POPUP_FROM_QUERY function.

Table 13-29 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 #FORM_OPEN# substitution string, then its index is zero and the form opened automatically by Oracle HTML DB must be referenced as form 1. This functionality supports the JavaScript used in the popup LOV which passes a value back to a form element.

p_escape_html Replacements for special characters that require an escaped equivalent.
  • &lt; for <

  • &gt; for >

  • &amp; for &

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

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 13-30 describes the some parameters in the POPUPKEY_FROM_LOV function.

Table 13-30 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 POPUPKEY_FROM_QUERY, the next index value should be p_idx + 1. For example:

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 #FORM_OPEN# substitution string, then its index is zero and the form opened automatically by Oracle HTML DB must be referenced as form 1. This functionality supports the JavaScript used in the popup LOV which passes a value back to a form element.

p_escape_html Replacements for special characters that require an escaped equivalent.
  • &lt; for <

  • &gt; for >

  • &amp; for &

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

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 13-31 describes the some parameters in the POPUPKEY_FROM_QUERY function.

Table 13-31 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 POPUPKEY_FROM_QUERY, the next index value should be p_idx + 1. For example:

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 #FORM_OPEN# substitution string, then its index is zero and the form opened automatically by Oracle HTML DB must be referenced as form 1. This functionality supports the JavaScript used in the popup LOV which passes a value back to a form element.

p_escape_html Replacements for special characters that require an escaped equivalent.
  • &lt; for <

  • &gt; for >

  • &amp; for &

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

HTMLDB_APPLICATION

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 13-32 describes the global variables available in HTMLDB_APPLICATION.

Table 13-32 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:

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

HTMLDB_CUSTOM_AUTH

You can use HTMLDB_CUSTOM_AUTH to perform various operations related to authentication and session management.

Topics in this section include:

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)


See Also:

"Editing Page Attributes" and "About Security" for more information on setting this page attribute


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

SESSION_ID_EXISTS Function

This function determines whether HTMLDB_APPLICATION.G_INSTANCE is set. SESSION_ID_EXISTS returns a BOOLEAN value (true or false).


Syntax
FUNCTION SESSION_ID_EXISTS 
RETURN BOOLEAN;

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;