There are many developers who use Oracle APEX to develop robust web applications. There also are others who don’t like to use tools, like Oracle APEX. Those developers like to build web applications by writing native PL/SQL stored procedures. This article shows them how to create dynamic web pages. It explains how you pass parameters as sets, ranges, and specific name-value pairs.

The Configure Oracle’s XDB Server for Web Applications article shows you how to configure the XDB Server. It also shows you how to deploy a virtually static web page. The page is static except for the use of the USER built-in. The USER built-in is an environment variable that maps to the user name that owns an active connection to the Oracle database.

This article shows you how to build three types of dynamic web pages:

  • Setup grants and synonyms
  • Using an array for a parameter range
  • Using an array for a series of parameters
  • Using a set of specific name-value parameters

The example programs rely on a new web video store model, which you can download from https://github.com/maclochlainn/web-video-store. If you download the code, the create_web_video_store.sql script runs all the code to create and seed the data model.

These stored procedure examples are complete working code, which you also can run after downloading them from github.com. You can read more about the XDB in the Oracle XML DB Developer’s Guide.

The examples in this article use a stream of parameters, which are the equivalent to a URI (Uniform Resource Identifier). A URI is a more complete form of what many call a URL (Uniform Resource Locator). A URL is simply the hierarchical part of the address minus the user credentials, as shown below (as URI coverage on Wikipedia):

The URI contains the query and a fragment. The fragment contains the call parameters to the server-side program unit. Your program units are stored procedures in the student schema.

Setup grants and synonyms

You need to setup grants to the anonymous schema for any stored procedures in the student schema. You also need to setup synonyms that point to the student schema procedures. The examples use the virtual /db/ path.

You can run the following script as the system user:

SQL> GRANT EXECUTE ON student.html_table_range TO anonymous;
SQL> CREATE SYNONYM anonymous.html_table_range FOR student.html_table_range;
SQL> GRANT EXECUTE ON student.html_table_ids TO anonymous;
SQL> CREATE SYNONYM anonymous.html_table_ids FOR student.html_table_ids;
SQL> GRANT EXECUTE ON student.html_table_values TO anonymous;
SQL> CREATE SYNONYM anonymous.html_table_values FOR student.html_table_values;

You need to create the objects before you grant access to them. However, you can drop and replace objects without revoking the grant. Grants give permission to execute the procedures, but you need synonyms to avoid disclosing ownership of the procedures. Synonyms can exist without the referenced object. The synonym fails when the referenced procedure doesn’t exist.

Using an array for a parameter range

The first example takes two parameters. The parameters are name-value pairs. They use the same name with different values. “ids” is the name, and the values are 1006 and 1014. It is possible to have more than two name-value pairs in the list, as you will see in the Using an array for a series of parameters section later.

You call the stored program with the following URI:

http://localhost:8080/db/html_table_range?ids=1006&ids=1014

The html_table_range is the name of the stored procedure. The two name-value pairs are sent to a single parameter that is a PL/SQL collection. You can find the IDENT_ARR collection in the owa_util package. The IDENT_ARR is a collection of 30-character variable length strings.

You create the html_table_range procedure in the student schema. You grant the execute privilege on the procedure to the anonymous user-schema. Then, you create a synonym for the anonymous user, which hides the ownership of the procedure.

The html_table_range procedure code is:

SQL> CREATE OR REPLACE
  2    PROCEDURE html_table_range
  3    ( ids OWA_UTIL.IDENT_ARR ) IS
  4      /* Declare css file. */
  5      css CLOB := '<style>'||CHR(10)
  6               || 'table {background-color:#ffffff;border-style:solid;border-width: 5px;}'||CHR(10)
  7               || 'th.c1 {font-size:18px;font-family:Verdana, Verdana, san-serif;color:#ffffff;font-weight:bold;text-align:center;background-color:#3bbfff;width:50px;}'||CHR(10)
  8               || 'th.c2 {font-size:18px;font-family:Verdana, Verdana, san-serif;color:#ffffff;font-weight:bold;text-align:center;background-color:#3bbfff;width:550px;}'||CHR(10)
  9               || 'th.c3 {font-size:18px;font-family:Verdana, Verdana, san-serif;color:#ffffff;font-weight:bold;text-align:center;background-color:#3bbfff;width:100px;}'||CHR(10)
 10               || 'td.c1 {font-size:16px;font-family:Verdana, Verdana, san-serif;color:#000000;text-align:center;background-color:#eeeeee;}'||CHR(10)
 11               || 'td.c2 {font-size:16px;font-family:Verdana, Verdana, san-serif;color:#000000;text-align:left;background-color:#eeeeee;}'||CHR(10)
 12               || 'td.c3 {font-size:16px;font-family:Verdana, Verdana, san-serif;color:#000000;text-align:right;background-color:#eeeeee;}'||CHR(10)
 13               || '</style>';
 14
 15      /* Declare a range determined list of film items. */
 16      CURSOR get_items
 17      ( start_id NUMBER
 18      , end_id NUMBER ) IS
 19        SELECT   item_id AS item_id
 20        ,        item_title
 21                 || CASE
 22                      WHEN item_subtitle IS NOT NULL THEN
 23                        ': '|| item_subtitle
 24                    END AS item_title
 25        ,        release_date AS release_date
 26        FROM     item
 27        WHERE    item_id BETWEEN start_id AND end_id
 28        ORDER BY item_id;
 29
 30    BEGIN
 31      /* Open HTML page with the PL/SQL toolkit. */
 32      htp.print('<!DOCTYPE html>');
 33      htp.print(css);
 34      htp.htmlopen;
 35      htp.headopen;
 36      htp.htitle('Element Range List');
 37      htp.headclose;
 38      htp.bodyopen;
 39      htp.line;
 40
 41      /* Build HTML table with the PL/SQL toolkit. */
 42      htp.tableopen;
 43      htp.tablerowopen;
 44      htp.tableheader( cvalue => '#'
 45                     , cattributes => 'class="c1"' );
 46      htp.tableheader( cvalue => 'Film Title'
 47                     , cattributes => 'class="c2"' );
 48      htp.tableheader( cvalue => 'Release Date'
 49                     , cattributes => 'class="c3"' );
 50      htp.tablerowclose;
 51
 52      /* Read the cursor values into the HTML table. */
 53      FOR i IN get_items(ids(1),ids(2)) LOOP
 54        htp.tablerowopen;
 55        htp.tabledata( cvalue => i.item_id
 56                     , cattributes => 'class="c1"');
 57        htp.tabledata( cvalue => i.item_title
 58                     , cattributes => 'class="c2"');
 59        htp.tabledata( cvalue => i.release_date
 60                     , cattributes => 'class="c3"');
 61        htp.tablerowclose;
 62      END LOOP;
 63
 64      /* Close HTML table. */
 65      htp.tableclose;
 66
 67      /* Close HTML page. */
 68      htp.line;
 69      htp.bodyclose;
 70      htp.htmlclose;
 71    END;
 72  /

This procedure embeds a cascading style sheet (CSS) inside a local variable on lines 5 through 13. A better solution stores the CSS in a table. The subsequent examples show you how to get and use a stored CSS file.

The get_items cursor on lines 16 through 28 uses a BETWEEN operator to create a range between starting and ending surrogate key column values. Line 32 provides an HTML 5 Document Type Declaration (DTD). The DTD uses the htp package’s print procedure because there isn’t a named procedure to render the document type. That same trick works to include the CSS file on line 33.

The next calls to the htp package procedures open a page, describe a header, and body. Inside the body elements, the procedure calls the get_items cursor to create rows in a table. The calls to the tableheader and tabledata procedures pass the text value and a CSS class attribute. There is a difference between the class values for the row headers, which is their width. The row data elements really could use the same CSS style because the columns inherit their width from the table headers.

It displays the following form when the URI contains two parameters. At least, it does so when the first value is lower than the second values.

This section has shown you how to develop and deploy a dynamic range web page with the PL/SQL Web Toolkit. You can test it with different combinations to see how it works.

Using an array for a series of parameters

The second example builds on the first one. It takes any number of name-value pairs. Like the range example, they use the same name with different values. “ids” is also the name for the name-value pairs in this example.

The values are 1002, 1007, 1015 and 1021. You may provide them in any order inside the URI because the cursor leverages one of Oracle’s adapter patterns and an ORDER BY clause to put them in ascending sequential order.

You call the stored program with the following URI:

http://localhost:8080/db/html_table_ids?ids=1002&ids=1007&ids=1015&ids=1021

The html_table_ids is the name of the stored procedure for this example. The name-value pairs are sent to a single parameter that is a PL/SQL collection. You can find the VC_ARR collection in the owa_util package. The VC_ARR is a collection of 32,000-character variable length strings.

The html_table_ids procedure code follows below. You should note that the CSS file is now stored in a table and read from the table at runtime. This creates an external dependency on data you previously put in the database (check the code on github.com for the insert statement).

SQL> CREATE OR REPLACE
  2    PROCEDURE html_table_ids
  3    ( ids OWA_UTIL.VC_ARR ) IS
  4
  5      /* Declare a variable of the local ADT collection. */
  6      lv_list LIST_IDS := list_ids();
  7
  8      /* Declare a local Cascading Style Sheet. */
  9      lv_css VARCHAR2(4000);
 10
 11      /* Declare a range determined list of film items. */
 12      CURSOR get_items
 13      ( cv_ids LIST_IDS ) IS
 14        SELECT   item_id AS item_id
 15        ,        item_title
 16                 || CASE
 17                      WHEN item_subtitle IS NOT NULL THEN
 18                        ': '|| item_subtitle
 19                    END AS item_title
 20        ,        release_date AS release_date
 21        FROM     item
 22        WHERE    item_id IN (SELECT *
 23                             FROM   TABLE(cv_ids))
 24        ORDER BY item_id;
 25
 26      BEGIN
 27        /* Convert OWA_UTIL PL/SQL collection to SQL collection. */
 28        FOR i IN 1..ids.COUNT LOOP
 29          lv_list.EXTEND;
 30          lv_list(lv_list.COUNT) := ids(i);
 31        END LOOP;
 32
 33        /* Assign the css to a local variable. */
 34        FOR i IN (SELECT css_text
 35                  FROM   css
 36                  WHERE  css_name = 'blue-gray') LOOP
 37          lv_css := i.css_text;
 38        END LOOP;
 39
 40        /* Open HTML page with the PL/SQL toolkit. */
 41        htp.print('<!DOCTYPE html>');
 42        htp.print(lv_css);
 43        htp.htmlopen;
 44        htp.headopen;
 45        htp.htitle('Element Series List');
 46        htp.headclose;
 47        htp.bodyopen;
 48        htp.line;
 49
 50        /* Build HTML table with the PL/SQL toolkit. */
 51        htp.tableopen;
 52        htp.tablerowopen;
 53        htp.tableheader( cvalue => '#'
 54                       , cattributes => 'class="c1"' );
 55        htp.tableheader( cvalue => 'Film Title'
 56                       , cattributes => 'class="c2"' );
 57        htp.tableheader( cvalue => 'Release Date'
 58                       , cattributes => 'class="c3"' );
 59        htp.tablerowclose;
 60
 61        /* Read the cursor values into the HTML table. */
 62        FOR i IN get_items(lv_list) LOOP
 63          htp.tablerowopen;
 64          htp.tabledata( cvalue => i.item_id
 65                       , cattributes => 'class="c1"');
 66          htp.tabledata( cvalue => i.item_title
 67                       , cattributes => 'class="c2"');
 68          htp.tabledata( cvalue => i.release_date
 69                       , cattributes => 'class="c3"');
 70          htp.tablerowclose;
 71        END LOOP;
 72
 73        /* Close HTML table. */
 74        htp.tableclose;
 75
 76        /* Close HTML page. */
 77        htp.line;
 78        htp.bodyclose;
 79        htp.htmlclose;
 80      END;
 81    /

Much of the code follows the prior range example logic. The trick in the code is the use of the adapter pattern on lines 22 and 23. The TABLE function converts the results of the collection into a SQL result set, which acts like a subquery or list of values.

You would re-write lines 22 and 23 like this if you were writing an ad hoc query:

 22  WHERE item_id IN (1002,1007,1015,1021)

The difference between the two approaches is that incoming array is dynamic and the ad hoc query is static. It display the following:

Like the prior section, you can test this with different values to see how it works. This section has shown you how to develop and deploy a dynamic range web page with a series of non-sequential values.

Using a set of name-value parameters

The third and final example builds on the first two. It takes specific name-value pairs. Unlike the prior range or series examples, it uses different name for each of the name-value pairs. The names in the URI must match the parameter names of the html_table_values procedure.

The name-value parameter values differ from the earlier examples too. You can submit them as case insensitive and partial strings. That’s because the internal cursor leverages Oracle’s REGEXP_LIKE and UPPER functions in the WHERE clause.

You call the html_table_ids stored program with the following URI:

http://localhost:8080/db/html_table_ids?film_title=star&film_rating=pg&film_media=blu-ray

The html_table_values is the name of the stored procedure for this example. The name-value pairs all use variable length strings, which may be up to 32,768 bytes in length.

The html_table_values procedure is:

SQL> CREATE OR REPLACE
  2    PROCEDURE html_table_values
  3    ( film_title VARCHAR2
  4    , film_rating VARCHAR2
  5    , film_media VARCHAR2 ) IS
  6
  7      /* Declare a local CSS variable. */
  8      lv_css VARCHAR2(4000);
  9
 10      /* Declare a range determined list of film items. */
 11      CURSOR get_items
 12      ( cv_title VARCHAR2
 13      , cv_rating VARCHAR2
 14      , cv_media VARCHAR2 ) IS
 15        SELECT   i.item_id AS item_id
 16        ,        i.item_title
 17                 || CASE
 18                      WHEN i.item_subtitle IS NOT NULL THEN
 19                        ': '|| i.item_subtitle
 20                    END AS item_title
 21        ,        ra.rating
 22        ,        i.release_date AS release_date
 23        FROM     item i INNER JOIN rating_agency ra
 24        ON       i.item_rating_id = ra.rating_agency_id INNER JOIN common_lookup cl
 25        ON       i.item_type = cl.common_lookup_id
 26        WHERE    REGEXP_LIKE(UPPER(i.item_title),UPPER(cv_title))
 27        AND      REGEXP_LIKE(UPPER(ra.rating),UPPER(cv_rating))
 28        AND      REGEXP_LIKE(UPPER(cl.common_lookup_type),UPPER(cv_media))
 29        ORDER BY item_title;
 30
 31    BEGIN
 32
 33      /* Assign the css to a local variable. */
 34      FOR i IN (SELECT css_text
 35                FROM   css
 36                WHERE  css_name = 'blue-gray') LOOP
 37        lv_css := i.css_text;
 38      END LOOP;
 39
 40      /* Open HTML page with the PL/SQL toolkit. */
 41      htp.print('<!DOCTYPE html>');
 42      htp.print(lv_css);
 43      htp.htmlopen;
 44      htp.headopen;
 45      htp.htitle('Element Value List');
 46      htp.headclose;
 47      htp.bodyopen;
 48      htp.line;
 49
 50      /* Open the HTML table. */
 51      htp.tableopen;
 52      htp.tablerowopen;
 53      htp.tableheader( cvalue => '#'
 54                     , cattributes => 'class="c1"' );
 55      htp.tableheader( cvalue => 'Film Title'
 56                     , cattributes => 'class="c2"' );
 57      htp.tableheader( cvalue => 'Release Date'
 58                     , cattributes => 'class="c3"' );
 59      htp.tablerowclose;
 60
 61      /* Read the cursor values into the HTML table. */
 62      FOR i IN get_items(film_title, film_rating, film_media) LOOP
 63        htp.tablerowopen;
 64        htp.tabledata( cvalue => i.item_id
 65                     , cattributes => 'class="c1"');
 66        htp.tabledata( cvalue => i.item_title
 67                     , cattributes => 'class="c2"');
 68        htp.tabledata( cvalue => i.release_date
 69                     , cattributes => 'class="c3"');
 70        htp.tablerowclose;
 71      END LOOP; 
 72
 73      /* Close HTML table. */
 74      htp.tableclose;
 75
 76      /* Close HTML page. */
 77      htp.line;
 78      htp.bodyclose;
 79      htp.htmlclose;
 80    END;
 81  /

Lines 26 through 28 hold parameter comparisons that use the REGEXP_LIKE function to validate partial strings and the UPPER function to make the comparisons case insensitive. This technique allows for the comparison of partial lookup strings.

Other than the joins and nuances of the cursor, the code is more or less the same as the prior examples. It does show you how to leverage elements from different tables to discover a set of unique rows in the ITEM table. More importantly, it shows you how to use specific names in the name-value paris.

It displays the following:

This section has shown you how to use specific names in name-value pairs and how to leverage information from a set of tables to find a set of rows in one table. You can find all the code at this URL

As always, I hope this helps you learn another facet of writing PL/SQL.