Monday, 18 June 2012

Using HTML in PL/SQL

What is PL/SQL?

PL/SQL stands for Procedural Language/Structured Query Language. It is a programming language that uses detailed sequential instructions to process data. A PL/SQL program combines SQL command (such as Select and Update) with procedural commands for tasks, such as manipulating variable values, evaluating IF/THEN logic structure, and creating loop structures that repeat instructions multiple times until the condition satisfies the defined criteria. PL/SQL was expressly designed for this purpose.

The structure of a PL/SQL program block is:

   Variable declaration
   Program statements
   Error-handling statements

PL/SQL program variables are declared in the program’s declaration section using the data declaration syntax shown earlier. The beginning of the declaration section is marked with the reserved word DECLARE. You can declare multiple variables in the declaration section. The body of a PL/SQL block consists of program statements, which can be assigned statements, conditional statements, looping statements, and so on, that lie between the BEGIN and EXCEPTION statements. The exception section contains program statements for error handling. Finally, PL/SQL programs end with the END; statement.

In a PL/SQL program block, the DECLARE and EXCEPTION sections are optional. If there are no variables to declare, you can omit the DECLARE section and start the program with the BEGIN command.

Using HTML and PL/SQL in Oracle Application Express

Oracle Application Express installs with your Oracle database and is comprised of data in tables and PL/SQL code. Whether you are running the Oracle Application Express development environment or an application you built using Oracle Application Express, the process is the same. Your browser sends a URL request that is translated into the appropriate Oracle Application Express PL/SQL call. After the database processes the PL/SQL, the results are relayed back to your browser as HTML. This cycle happens each time you either request or submit a page.

Specific HTML content not handled by Oracle Application Express forms, reports, and charts are generated using the PL/SQL region type. You can use PL/SQL to have more control over dynamically generated HTML within a region.

htp and htf Packages:

htp  (hypertext procedures) and htf (hypertext functions) packages generate HTML tags. These packages translate PL/SQL into HTML that is understood by a Web browser. For instance, the htp.anchor procedure generates the HTML anchor tag, <A>. The following PL/SQL block generate a simple HTML document:

Create or replace procedure hello AS
    htp.htmlopen;                       -- generates <HTML>
    htp.headopen;                      -- generates <HEAD>
    htp.title('Hello');                   -- generates <TITLE>Hello</TITLE>
    htp.headclose;                     -- generates </HEAD>
    htp.bodyopen;                     -- generates <BODY>
    htp.header(1, 'Hello');          -- generates <H1>Hello</H1>
    htp.bodyclose;                     -- generates </BODY>
    htp.htmlclose;                      -- generates </HTML>

Oracle provided the htp.p tag to allow you to override any PL/SQL-HTML procedure or even a tag that did not exist. If a developer wishes to use a new HTML tag or simply is unaware of the PL/SQL analog to the html tag, he/she can use the htp.p procedure.

For every htp procedure that generates HTML tags, there is a corresponding htf function with identical parameters. The function versions do not directly generate output in your web page. Instead, they pass their output as return values to the statements that invoked them. See the use of these packages on page 113 of the book. 


htp.p / htp.print:

Generates the specified parameter as a string.


Indicates that the text that comes after the tag is to be formatted as a paragraph.


Renders the text they surround in bold.


Escape_sc is a function which replaces characters that have special meaning in HTML with their escape sequence.

converts occurrence of & to &amp
converts occurrence of “ to &quot
converts occurrence of < to &lt
converts occurrence of > to &gt

To prevent XSS (Cross Site Scripting) attacks, you must call SYS.HTF.ESCAPE_SC to prevent embedded javascript code from being executed when you inject the string into the HTML page.


The SYS prefix is used to signify Oracle’s SYS schema. The HTP and HTF packages normally exist in the SYS schema and APEX relies on them.

Cursor FOR LOOP Statement

The cursor FOR LOOP statement implicitly declares its loop index as a record variable of the row type that a specified cursor returns, and then opens a cursor. With each iteration, the cursor FOR LOOP statement fetches a row from the result set into the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor. The cursor also closes if a statement inside the loop transfers control outside the loop or raises an exception.

The cursor FOR LOOP statement lets you run a SELECT statement and then immediately loop through the rows of the result set. This statement can use either an implicit or explicit cursor.

If you use the SELECT statement only in the cursor FOR LOOP statement, then specify the SELECT statement inside the cursor FOR LOOP statement, as in Example A. This form of the cursor FOR LOOP statement uses an implicit cursor, and is called an implicit cursor FOR LOOP statement. Because the implicit cursor is internal to the statement, you cannot reference it with the name SQL.

Example A - Implicit Cursor FOR LOOP Statement

  FOR item IN (
    SELECT last_name, job_id
    FROM employees
    WHERE job_id LIKE '%CLERK%' AND manager_id > 120
    ORDER BY last_name
    DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name || ', Job = ' || item.job_id);


Name = Atkinson, Job = ST_CLERK
Name = Bell, Job = SH_CLERK
Name = Bissot, Job = ST_CLERK
Name = Walsh, Job = SH_CLERK

If you use the SELECT statement multiple times in the same PL/SQL unit, then define an explicit cursor for it and specify that cursor in the cursor FOR LOOP statement, as in Example B. This form of the cursor FOR LOOP statement is called an explicit cursor FOR LOOP statement. You can use the same explicit cursor elsewhere in the same PL/SQL unit.

Example B - Explicit Cursor FOR LOOP Statement

    SELECT last_name, job_id FROM employees
    WHERE job_id LIKE '%CLERK%' AND manager_id > 120
    ORDER BY last_name;
  FOR item IN c1
    DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name || ', Job = ' || item.job_id);


Name = Atkinson, Job = ST_CLERK
Name = Bell, Job = SH_CLERK
Name = Bissot, Job = ST_CLERK
Name = Walsh, Job = SH_CLERK

On page 113 and 114 of my book “Create Rapid Web Applications Using Oracle Application Express”, you can see how to use HTML in PL/SQL code. In this blog post I’ll try to explain that code.

Display Customer Information

This procedure fetches information of the selected customer and presents it in a desirable format (as shown under) using the style named demoCustomerInfo.

This is the parent PL/SQL block. A nested block is also used under Display Current Orders section. Read about PL/SQL block in What is PL/SQL at the beginning of this post.

l_customer_id varchar2(30) := :P11_CUSTOMER_ID;
Assigns customer ID from Page 11 to the variable l_customer_id. This variable is used ahead in a SQL statement to fetch details of the selected customer. In PL/SQL, := is called the assignment operator. The value of the variable that is being assigned the new value is placed on the left side of the assignment operator, and the value is placed on the right side of the operator.

:P11_CUSTOMER_ID is called a bind variable. Bind variables are substituion variables that are used in place of literals. You can use bind variables syntax anywhere in Oracle Application Express where you are using SQL or PL/SQL to reference session state of a specified item. For example:

SELECT * FROM employees WHERE last_name like '%' || :SEARCH_STRING || '%'

In this example, the search string is a page item. If the region type is defined as SQL Query, you can reference the value using standard SQL bind variable syntax. Using bind variables ensures that parsed representations of SQL queries are reused by the database, optimizing memory usage by the server.

The use of bind variables is encouraged in APEX. Bind variables help you protect your Oracle APEX application from SQL injection attacks. Bind variables work in much the same way as passing data to a stored procedure. Bind variables automatically treat all input data as “flat” data and never mistake it for SQL code. Besides the prevention of SQL injection attacks, there are other performance-related benefits to its use.

You declare a field item as a bind variable by prefixing a colon character (:), like this:

When using bind variable syntax, remember the following rules:
  • Bind variable names must correspond to an item name. 
  • Bind variable names are not case-sensitive.
  • Bind variable names cannot be longer than 30 characters (that is, they must be a valid Oracle identifier).
Although page item and application item names can be up to 255 characters, if you intend to use an application item within SQL using bind variable syntax, the item name must be 30 characters or less.

Read What is PL/SQL at the beginning of this post.

l_customer_id := :P18_CUSTOMER_ID;
end if;

P11_CUSTOMER_OPTIONS is a radio group item that is created in section 6.6.6. This item has two values “Existing Customer” and “New Customer” as shown in the following figure. In the above conditional code, if the user opted to create a new customer, then the new customer ID is moved from Page 18 to the variable l_customer_id (new customer record is generated through Page 18).

for x in (select * from demo_customers where customer_id = l_customer_id)
Initiated the FOR loop to locate and fetch record of the selected customer in demo_customers table.

htp.p('<div class="demoCustomerInfo">');
The <div> tag defines a division or a section in an HTML document. This is the opening tag which references demoCustomerInfo class in CSS to format the following elements. Click to see how Cascading Style Sheet is used in Oracle Application Express.

Displays the label “Customer:” in bold.

The paragraph opening tag.

htp.p(htf.escape_sc(x.cust_first_name) || ' ' || htf.escape_sc(x.cust_last_name) || '<br />');
Concatenates customer’s first and last names using the || symbol. The <br /> is an XHTML tag and inserts a single line break.

htp.p(sys.htf.escape_sc(x.cust_street_address1) || '<br />');
Show customer’s first address on a new line.

if x.cust_street_address2 is not null then
  sys.htp.p(sys.htf.escape_sc(x.cust_street_address2) || '<br />');
end if;
It’s a condition to check whether the customer’s second address is not null. If it’s not, print it on a new line.

htp.p(sys.htf.escape_sc(x.cust_city) || ', ' || x.cust_state || '  ' || htf.escape_sc(x.cust_postal_code));
Displays city, state and postal code data on the same row separating each other with a comma and a blank space.

The paragraph end tag.

end loop;
The loop terminates here after selecting the customer with relevant information.

The div tag terminates here. Following is the output you see for the above code.






Display Products

Here you create a section on your web page to display all products along with their prices and include an option through which users can add products to their cart. 

htp.p('<div class="demoProducts" >');
Creates a division based on the class demoProducts.

htp.p('<strong class="demoTitle">P R O D U C T S</strong>');
Displays the section title in bold on top of column headings using the sub-class demoTitle.

htp.p('<table width="100%" cellspacing="0" cellpadding="0" border="0">
Here you are initiating to draw a table. The <table> tag defines an HTML table. An HTML table consists of the <table> element and one or more <tr>, <th>, and <td> elements. The <tr> element defines a table row, the <th> element defines a table header, and the <td> element defines a table cell.

The Width attribute specifies the width of a table. In this instance, we’re setting the width in percent of the surrounding element. In the style sheet, we set the area for demoProducts to 58% and for demoCurrentOrder to 38% div.demoProducts{clear:both;float:left;width:58%;margin:16px 0 0 0;padding:0 8px 0 0;}. This division meant that the elements in the demoProducts class would consume 58% of the overall region whereas demoCurrentOrder elements would share 38%. If you set the table’s width to 50%, the product section would look like the following figure. Setting 50% width instructs the browser to use half of the demoProducts area we specified in the CSS rule. 

<tr><th class="left">Product</th><th>Price</th><th>Add to Cart</th></tr>
The <thead> tag is used to group header content in an HTML table. The <thead> element is used in conjunction with the <tbody> and <tfoot> elements to specify each part of a table (header, body, footer). The <tr> tag creates a row for column heading. The three <th> tags specify the headings i.e. Product, Price, and Add to Cart. The class=”left” declaration is included that points towards the CSS rule div.demoProducts .left{text-align:left;} to align the title “Product” to the left. 

The <tbody> tag is used to group the body content in an HTML table.

for c1 in (select product_id, '<a href="javascript:productPopup(''' || product_id || ''');">' || sys.htf.escape_sc(product_name) || '</a>' product_name,  list_price, 'Add to Cart' add_to_order
from demo_product_info
where product_avail = 'Y'
order by product_name) loop
This FOR loop fetches Product ID, Product Name, and List Price columns from the products table. To display Add > link in the table, we appended a column aliased add_to_order and populated all rows with a constant value 'Add to Cart'. For information on FOR LOOP see Cursor FOR LOOP Statement section earlier in this post.

The <a href="javascript:productPopup(''' || product_id || ''');">' || sys.htf.escape_sc(product_name) || '</a> tag defines an anchor. An anchor can be used in two ways:

1.      To create a link to another document, by using the href attribute
2.      To create a bookmark inside a document, by using the name attribute

The <a> element is usually referred to as a link or a hyperlink. The most important attribute of the <a> element is the href attribute, which specifies the URL of the page the link goes to. In the above scenario, the first portion <a href="javascript:productPopup(''' || product_id || ''');"> calls a JavaScript function named productPopup by passing the product_id value. The second portion, || sys.htf.escape_sc(product_name) || '</a> concatenates product name to the former one to display product name as a link. The function, in turn, opens a popup to display details based on the product ID for the clicked product.

sys.htp.p('<tr><td class="left">'||c1.product_name||'</td><td>'||trim(to_char(c1.list_price,'999G999G990D00'))||
        '</td><td><a href="f?p=&APP_ID.:12:'||:app_session||':ADD:::P12_PRODUCT_ID:'||
        c1.product_id||'" class="demoAddtoCart">Add &gt;</a></td></tr>');
The c1 prefix in front of column names, points to the FOR LOOP cursor. The TRIM function in the expression trim(to_char(c1.list_price,'999G999G990D00')) takes a character expression and returns that expression with leading and/or trailing pad characters removed. This expression initially formats the list price column to add thousand separators and decimal place. Next, it converts the numeric price value to text expression using the TO_CHAR function and finally applies the TRIM function. The TO_CHAR function converts a DATETIME, number, or NTEXT expression to a TEXT expression in a specified format. The tables that follow list the elements of a number format model that we used above and provide some examples.

Returns leading zeros.

Returns trailing zeros.
Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number.
Returns in the specified position the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER parameter. The default is a period (.).
Returns in the specified position the group separator (the current value of the NLS_NUMERIC_CHARACTER parameter. The default is comma [,]). You can specify multiple group separators in a number format model.

Use the following SQL statement to check the current value for decimal and group separator characters:

select value from v$nls_parameters where parameter='NLS_NUMERIC_CHARACTERS';

The expression <a href="f?p=&APP_ID.:12:'||:app_session||':ADD:::P12_PRODUCT_ID:'||c1.product_id||'" class="demoAddtoCart">Add &gt;</a> creates a link with an ADD request. The value of REQUEST is the name of the button the user clicks, or the name of the tab the user selects. For example, suppose you have a button with a name of CHANGE, and a label Apply Change. When a user clicks the button, the value of REQUEST is CHANGE. In section 6.10.7 on page number 117 of the book, we created the following process named Add product to the order collection.

for x in (select * from demo_product_info where product_id = :P12_PRODUCT_ID)
  apex_collection.add_member(p_collection_name => 'ORDER',
    p_c001 => x.product_id,
    p_c002 => x.product_name,
    p_c003 => x.list_price,
    p_c004 => 1);
end loop;

During the process creation, we selected Request=Expression 1 in Condition Type and entered ADD in Expression 1. The ADD request in the above <a> tag is referencing the same expression. When a user clicks the ADD> link on the web page, the url sends the ADD request to the above process along with the product’s ID. In return, the process adds the product to the selected item list. The &gt is used to show the” >” symbol after the ADD label.

end loop;
End of FOR loop.

Table and table body closing tags.

The Closing div tag.

Display Current Order

htp.p('<div class="demoCurrentOrder" >');
Defines the <div> tag and utilizes the demoCurrentOrder class for this section.

htp.p('<strong class="demoTitle">Current Order</strong>');
Displays section heading as follows.

This is the nested or child block. To nest a block means to embed one or more PL/SQL block inside another PL/SQL block to allow you have a better control over program’s execution.

c number := 0; t number := 0;
Declared two numeric counter variables and initialized them with zero. The variable c is used to evaluate whether any product is selected in the current order while the variable t stores total value.

-- loop over cart values
for c1 in (select c001 pid, c002 i, to_number(c003) p, count(c002) q, sum(c003) ep,  'Remove' remove
from apex_collections
where collection_name = 'ORDER'
group by c001, c002, c003
order by c001)
Collection enables you to temporarily capture one or more non-scalar values. You can use collections to store rows and columns currently in session state so they can be accessed, manipulated, or processed during a use’s specific session. You can think of a collection as a bucket in which you temporarily store and name rows of information.

Every collection contains a named list of data elements (or members) which can have up to 50 character attributes (varchar2 (4000)), 5 number, 5 date, 1 XML type, 1 BLOB, and 1 CLOB attribute. You insert, update, and delete collection information using the PL/SQL API APEX_COLLECTION.

When you create a new collection, you must give it a name that cannot exceed 255 characters. Note that collection names are not case-sensitive and will be converted to uppercase. Once the collection is named, you can access the values (members of a collection) in the collection by running a SQL query against the database view APEX_COLLECTIONS.

The APEX_COLLECTIONS view has the following definition:

SEQ_ID                                  NOT NULL NUMBER
C001                                       VARCHAR2(4000)
C002                                       VARCHAR2(4000)
C003                                       VARCHAR2(4000)  
C004                                       VARCHAR2(4000)  
C005                                       VARCHAR2(4000) 
C050                                       VARCHAR2(4000)
N001                                       NUMBER
N002                                       NUMBER
N003                                       NUMBER
N004                                       NUMBER
N005                                       NUMBER    
CLOB001                               CLOB
BLOB001                               BLOB 
XMLTYPE001                        XMLTYPE
MD5_ORIGINAL                  VARCHAR2(4000) 

Use the APEX_COLLECTIONS view in an application just as you would use any other table or view in an application, for example:

Select c001, c002, c003, n001, clob001 
From APEX_collections 
Where collection_name = 'DEPARTMENTS'

Note that you can't read apex_collection using external tools. A collection is related to an APEX session and not available outside of it. However, using the following statement you can query WWV_FLOW_COLLECTION_MEMBERS$. It is into this table that APEX stores its collection data.

Select c001,c002,c003,c004 from APEX_040000.wwv_flow_collection_members$;

The CREATE_OR_TRUNCATE_COLLECTION method creates a new collection if the named collection does not exist. If the named collection already exists, this method truncates it. Truncating a collection empties it, but leaves it in place.

On page number 102 - section 6.6.7, we created a process named Create or Truncate Order Collection under page rendering section and used the following statement to create a collection named ORDER:

apex_collection.create_or_truncate_collection (p_collection_name => 'ORDER');

In the above loop, we’re selecting records from the same ORDER collection. Columns from apex_collections in the select statement correspond to:

Corresponds To
C001 – pid
Product ID
C002 – i
Product Name
C003 – p
List Price

htp.p('<div class="sideCartItem"><a href="'||
    '"><img src="#IMAGE_PREFIX#delete.gif" alt="Remove from cart" title="Remove from cart" /></a>&nbsp;&nbsp;
The above <a> tag creates a link with a REMOVE request. This time it uses product ID from the collection. In section 6.10.7 on page number 117, there is a process named Remove product from the order collection (as shown below) where the request expression is set to REMOVE.

for x in (select seq_id, c001 from apex_collections
where collection_name = 'ORDER' and c001 = :P12_PRODUCT_ID)
   apex_collection.delete_member(p_collection_name => 'ORDER', p_seq => x.seq_id);
end loop;

In HTML, images are defined with the <img> tag. The <img> tag has no closing tag. To display an image on a page, you need to use the src attribute. Src stands for "source". The value of the src attribute is the URL of the image you want to display.

Syntax for defining an image:
<img src="url" alt="some_text"/>

The URL points to the location where the image is stored. The value of IMAGE_PREFIX determines the virtual path the Web server uses to point to the images directory distributed with Oracle Application Express. We used “delete.gif” that is displayed in front of product name. The required alt attribute specifies an alternate text for an image, if the image cannot be displayed.

When a user clicks the remove link [X] in the Current Order section, the above url sends the REMOVE request to the above process along with product ID. The DELETE_MEMBER procedure deletes a specified member from a given named collection using the p_seq => x.seq_id parameter which is the sequence ID of the collection member to be deleted.

<a href="javascript:productPopup('''||||''');">'||c1.i||'</a>
The above link allows you to see product details in the Current Order section as well and calls the popup through the JavaScript function. is the product ID passed on to the function. c1.i is concatenated in the url to display product name as a link.  

<span>Quantity: '||c1.q||'</span>
<span class="subtotal">Subtotal: '||trim(to_char(c1.ep,'$999G999G999D00'))||'</span>
The above three lines of code displays price, quantity, and sub-total of the selected product in the Current Order section as shown below:

The ending div tag.

c := c + 1;
This counter increments the value of c with 1 at the end of each loop. The variable c is used to calculate number of items selected in the current order.

t := t + c1.ep;
Similar to the variable c, t is also incremented with the sum of list price (c1.ep) to calculate total order value.

end loop;

if c > 0 then
    sys.htp.p('<div class="sideCartTotal">
    <p>Items: <span>'||c||'</span></p>
    <p class="sideCartTotal">Total: <span>'||trim(to_char(t,'$999G999G999D00'))||'</span></p>
    sys.htp.p('<p class="sideCartTotal">You have no items in your current order.</p>');
end if;
The if c > 0 condition evaluates whether a product is selected in the current order. A value other than zero in this variable indicates addition of product(s). If the current order has some items added, show the label Total: along with the value, which is stored in the variable t. If no items are selected, show the message defined in the else section.

Mark the end of child PL/SQL block.

Div tag closed.

Parent PL/SQL block ended.


 Tutorial Books

1 comment: