Dynamic HTML Generated From PL/SQL Tutorial
Using APEX it’s very easy to display information using forms and reports regions, you could even throw in a chart or dial for a bit of something different. However, it’s also simple to generate HTML pages from PL/SQL code. This opens up a world of choice when it comes to designing and developing your application.
This tutorial is a simple demonstration of creating HTML output from PL/SQL code. It assumes that you have completed the APEX_ITEM Tutorial as it relies on the checkbox and ticked column that you create within this tutorial.
In the APEX_ITEM Tutorial we add a new column to the EMP table called ticked and then added a checkbox to a report to update this column when the checkbox was checked. Now we’ll add a new region to the same page which outputs the employee details, showing those that have been ticked in bold.
In the Application Builder:
- Navigate to the page that you previously used for the APEX_ITEM Tutorial. On this page add a new HTML region. In the wizard accept the default options, except set the name to “PL/SQL Output” and the column to 2, then click the Create Region button.
- Now edit the region that you’ve just created.
- Set the Type to be “PL/SQL (anonymous block)”
- Set the Region Source to :
- Run the page.
DECLARE CURSOR c_emp IS SELECT empno, ename, ticked FROM emp; BEGIN FOR a IN c_emp LOOP IF a.ticked = 'Y' THEN htp.p('<b>Employee '||a.ename||' ('||a.empno||') has been ticked. </b><br/>'); ELSE htp.p('Employee '||a.ename||' ('||a.empno||') has NOT been ticked. <br/>'); END IF; END LOOP; END;
Running the page should display a list of all the employees in the table. Those that have been ticked will be displayed in bold and those that haven’t will be normal. The HTML that is generated on the page is controlled by the htp.p call. There are numerous tags that can be used with the htp function, these are explained in detail in the Oracle documentation. I have used htp.p which just generates the HTML without any surrounding tags. I find that this gives me the most flexibility, especially if I need to come back and make changes.
In this example the PL/SQL generated region is ultimately redundant. It’s not giving us any more information than the reports region next to it. However, this is the easiest example I could think of. PL/SQL generated regions are much more powerful. For example, it can be used to generate a summary page pulling on information from different tables and using functions or calculations in PL/SQL. Or to generate a report style page to replace Oracle Reports style output. I’m sure you’ll agree the possibilities are endless.