Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

   

 

  Oracle Database Tips by Donald Burleson

APEX's HTMLDB_ITEM

The HTMLDB_ITEM package can be used to create your own page items.  The normal way, of course, is to create them on the application page using the wizards.  However, if the need ever arises to create them in a web application dynamically at runtime, this is how it is done.

The most common use for the HTMLDB_ITEM package is in a report or tabular form.  The most common of all the procedures is the checkbox procedure.  This was used extensively in the information on Checkboxes.  A few of the HTMLDB_ITEM procedures are outlined below.  With the examples provided, users should have enough information to go the APEX documentation and be able to use the rest of the dynamic page item procedures.

checkbox(
        p_idx in number,
        p_value in varchar2 default,
        p_attributes in varchar2 default,
        p_checked_values in varchar2 default,
        p_checked_values_delimiter in varchar2 default )
        return varchar2;

This procedure can be used to dynamically create checkboxes on a web page during rendering.  This procedure is covered in detail in an earlier chapter.

Parameters:

  • p_idx:  This indicates the ID number of the global htmldb_application variable.  The range of values is one to 50.  A ten will product a page item with the name f10.  It can then be referenced with htmldb_application.g_f10.

  • p_value:  This is the value of the check box.  This is the return value that will be in the g_f10 array if the checkbox is checked when the page is submitted.

  •  p_attributes:  HTML tags can be added here.

  • p_checked_values:  These are the default values to be checked.  A valid value here is 4:5:2:4

  • p_checked_values_delimiter:  This is the character used as the delimiter for the p_checked_values parameter.

Example: 

-- This code is a sample of what would be used to
-- create a checkbox in an APEX report.
select
   class_id, class_name,
   htmldb_item.checkbox( 10, class_id,
      decode( available_flag, 'Y', 'CHECKED', NULL) )
      AS available_flag
from  ...

hidden(
        p_idx in number,
        p_value in varchar2 default )
        return varchar2;
 

This procedure can be used to create a hidden page item at runtime.

Parameters:

  •  p_idx:  This indicates the ID number of the global htmldb_application variable.  The range of values is one to 50.  A ten will product a page item with the name f10.  It can then be referenced with htmldb_application.g_f10.

  • p_value:  This is the value to set or the hidden field.

Example: 

select
   class_id, class_name,
   htmldb_item.checkbox( 10, class_id,
 

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;

This procedure can be used to dynamically build a select list on a web page.  The procedure accepts the name of a defined LOV and displays its contents in an LOV.  The parameters should be familiar, as they are attributes of the select list when they are created using the wizards.

Parameters:

  •  p_idx:  This indicates the ID number of the global htmldb_application variable.

  • p_value:  This is the current value.  When the select list is rendered, this is the default selected item.

  • p_lov:  This is the text name of a defined LOV.

  • p_attributes:  These are the extra HTML parameters to be added.

  • p_show_null:  This will add an extra item to the select list as a NULL selection. Acceptable values are YES and NO.

  • p_null_value:  This is the value of the NULL select list item.  This is the value set in session state if the NULL item is selected.  p_show_null must be set to YES.

  • p_null_text:  This is the value displayed for the NULL select list item.  p_show_null must be set to YES for the NULL text to display in the list.

  • p_item_id:  This is the value to render in the HTML <input> tag for the ID attribute.

  • p_item_label:  This is the text to display for the label of the select list.

Example:

select first_name, last_name,
       htmldb_item.select_list_from_query( 1, trade_id,
          'TRADES', null, 'YES',
          '-1', '- Select Trade '  ) trade
from   union_member
where  member_id = :P110_MEMBER_ID;

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

The select_list_from_lov_xl procedure is the same as the select_list_from_lov except this procedure will populate a select list that contains more than 32K of data.  Attempting to use the select_list_from_lov with data larger than 32K, the following error will result:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

The select_list_from_lov_xl procedure can be used to remedy the error.

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

The select_list_from_query procedure will dynamically render a select list on the web page.  A query for the select list to use for the data can be provided. 

Parameters:

  • Except for p_query, the parameters of the select_list_from_query are the same as the select_list_from_lov above.

  • p_query:  Enter the query to use for the population of the select list.  The query must return two values: one for the display data; and one for the return value. Also, the query must not contain the customary semi-colon on the end or an error will occur.

Example:

select first_name, last_name,
       htmldb_item.select_list_from_lov( 1, trade_id,
          'select desc, id from union_trade order by 1',
          null, 'YES', '-1', '- Select Trade '  ) trade
from   union_member
where  member_id = :P110_MEMBER_ID;
 

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

The select_list_from_query_xl procedure is the same as the select_list_from_query except this procedure will populate a select list that contains more than 32K of data.  Using the select_list_from_query with data larger than 32K will result in the following error:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

The select_list_from_query_xl procedure can be used to remedy the error.


The above book excerpt is from:

Easy HTML-DB Oracle Application Express

Create Dynamic Web Pages with OAE

ISBN 0-9761573-1-4   

Michael Cunningham & Kent Crotty

http://www.rampant-books.com/book_2005_2_html_db.htm

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.