Web Applications and PL/SQL
By Dan Clamage, Clamage Computer Consulting

Here at MSA (Management Science Associates), we’re developing an internal web application using Cold Fusion and PL/SQL. Our design goals are to:

• Security -- The interactive user (the user logging into the web application) only has privileges to execute certain packages – no other schema-level privileges are provided. The interactive user is not the schema owner of the tables being operated upon.
• Move ALL SQL into the database – no SQL statements are allowed in the Cold Fusion application. Instead, packaged procedures encapsulate all database operations.
• Combine DML operations – the web front-end collects a set of data for processing, and then submits it one time to a packaged procedure. This minimizes network roundtrips. We also want to make use of bulk DML operations by passing arrays of records to a PL/SQL packaged routine.

One problem we ran into early on was the lack of support for passing arrays from Cold Fusion to PL/SQL. Our solution (which I also used at zipZap, a now-defunct Internet company) is to build a delimited list of values for each column. A record is therefore represented as a set of delimited lists, one list string per column.

One consideration is what should the delimiter be? For numbers, a comma is OK. But for text fields, we use a non-printable character (0xff) to delimit each field. Commas can appear in the data.

Another consideration is how does Cold Fusion represent NULL values? We discovered when building the delimited string, an empty field still required a single blank character. So when PL/SQL received this value, it would have to convert it into a NULL.

I developed a packaged routine called parse_list to break up the delimited string into separate fields, and store them in an index-by table (starting from offset 1). Here is a snippet from the package Utl’s header.

-- array used to parse char-delimited record with parse_list
TYPE ARR_STR IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;

-- loads an array of string with values parsed from a char-delimited list
-- given a string or record to parse, the single-character delimiter (e.g., ',')
-- first entry is in element 1
-- treats single space as NULL
PROCEDURE parse_list(
    p_str   IN     VARCHAR2
   ,p_delim IN     CHAR
   ,p_arr   IN OUT ARR_STR);

Here is the implementation of procedure parse_list.

-- loads an array of string with values parsed from a char-delimited list
-- given a string or record to parse, the single-character delimiter (e.g., ',')
-- first entry is in element 1
-- treats single space as NULL
PROCEDURE parse_list(
    p_str   IN     VARCHAR2
   ,p_delim IN     CHAR
   ,p_arr   IN OUT ARR_STR)
IS
  c_sp CONSTANT CHAR(1) := ' ';  -- space char
  deststr VARCHAR2(255);   -- capture next token
  nextpos NUMBER(5) := 1;
  i NUMBER(5) := 0;  -- array offset

  -- returns the next token (or NULL) from a delimited string of values
  -- handles leading and embedded NULLs, e.g., ',ab,,c'
  -- NOTE: a test must be made for trailing NULLs (delimiter at end of string)
  PROCEDURE tokenize(
      p_data  IN     VARCHAR2
     ,p_delim IN     CHAR
     ,p_start IN OUT NUMBER
     ,p_dest     OUT VARCHAR2) IS
    Lnext NUMBER(5);
  BEGIN
    Lnext := INSTR(p_data, p_delim, p_start);  -- look for delimiter
    IF (Lnext > 0) THEN  -- found token
      p_dest := SUBSTR(p_data, p_start, Lnext - p_start);
      p_start := Lnext + 1;  -- start past end of token
    ELSE  -- delimiter not found
      p_dest := SUBSTR(p_data, p_start, LENGTH(p_data) - p_start + 1);   -- last token
      p_start := LENGTH(p_data) + 1;
    END IF;  -- found token
  END tokenize;
  -- converts a single space to NULL
  -- otherwise returns the original string
  FUNCTION sp2null(p_str IN VARCHAR2)
  RETURN VARCHAR2
  IS
  BEGIN
    IF (p_str = c_sp) THEN  -- treat single space as NULL
      RETURN (NULL);
    ELSE
      RETURN (p_str);
    END IF;
  END sp2null;

BEGIN  -- parse_list
  p_arr.DELETE;  -- remove any previous entries
  tokenize(p_str, p_delim, nextpos, deststr);  -- initialize first parse
  WHILE (nextpos <= LENGTH(p_str)) LOOP  -- populate array
    i := i + 1;  -- starts from 1
    p_arr(i) := sp2null(deststr);
    tokenize (p_str, p_delim, nextpos, deststr);
  END LOOP;  -- populate array
  i := i + 1;  -- store last token
  p_arr(i) := sp2null(deststr);
  IF (SUBSTR(p_str, LENGTH(p_str), 1) = p_delim) then  -- store trailing null
    i := i + 1;  -- store last token
    p_arr(i) := NULL;
  END IF;

END parse_list;

One assumption I made that appears in the code is that the offset into the list string needs only to be five digits because PL/SQL VARCHAR2 variables can only be 32k in size. Another assumption is that each field can be up to 255 bytes. This can be set higher for use with multibyte character sets.

Procedure tokenize is a local helping function to search the string for the next delimiter, and return the substring between the current position and this delimiter. I had to deal with the situation where the first or last tokens might be NULL. Function sp2null (another local helping routine) provides a simple way to handle converting a single space to NULL.

I took care to clear the index-by table before loading it, to prevent previous executions to leave junk behind. Because of the way tokenize works, I had to store the last token processed. It proved cumbersome to make the logic smart enough to handle the last item inside the loop.

Below is an extracted example using procedure parse_list.

PROCEDURE replace_parameter_values (
     p_program_oid_str     IN     VARCHAR2
    ,p_param_code_str      IN     VARCHAR2
    ,p_param_value_oid_str IN     VARCHAR2
    ,p_string_1_str        IN     VARCHAR2
    ,p_string_2_str        IN     VARCHAR2
    ,p_string_3_str        IN     VARCHAR2
    ,p_number_1_str        IN     VARCHAR2
    ,p_number_2_str        IN     VARCHAR2
    ,p_number_3_str        IN     VARCHAR2
    ,p_number_4_str        IN     VARCHAR2
    ,p_status              IN OUT NUMBER)
IS
  ...
  c_delim CONSTANT CHAR(1) := CHR(255);
  -- these parallel arrays taken together store 1-n records
  a_program_oid utl.ARR_STR;  -- capture tokens
  a_param_code utl.ARR_STR;  -- capture tokens
  a_param_value_oid utl.ARR_STR;  -- capture tokens
  a_string_1 utl.ARR_STR;  -- capture tokens
  a_string_2 utl.ARR_STR;  -- capture tokens
  a_string_3 utl.ARR_STR;  -- capture tokens
  a_number_1 utl.ARR_STR;  -- capture tokens
  a_number_2 utl.ARR_STR;  -- capture tokens
  a_number_3 utl.ARR_STR;  -- capture tokens
  a_number_4 utl.ARR_STR;  -- capture tokens
BEGIN
  p_status := 0;  -- initialize

  -- parse out comma-delimited values from each string in parallel
  utl.parse_list(p_program_oid_str, c_delim, a_program_oid);
  utl.parse_list(p_param_code_str, c_delim, a_param_code);
  utl.parse_list(p_param_value_oid_str, c_delim, a_param_value_oid);
  utl.parse_list(p_string_1_str, c_delim, a_string_1);
  utl.parse_list(p_string_2_str, c_delim, a_string_2);
  utl.parse_list(p_string_3_str, c_delim, a_string_3);
  utl.parse_list(p_number_1_str, c_delim, a_number_1);
  utl.parse_list(p_number_2_str, c_delim, a_number_2);
  utl.parse_list(p_number_3_str, c_delim, a_number_3);
  utl.parse_list(p_number_4_str, c_delim, a_number_4);

  -- check that they all have same number of rows (no incomplete records)
  IF (NOT (a_program_oid.COUNT = a_param_code.COUNT AND
           a_param_code.COUNT = a_param_value_oid.COUNT AND
           a_param_value_oid.COUNT = a_string_1.COUNT AND
           a_string_1.COUNT = a_string_2.COUNT AND
           a_string_2.COUNT = a_string_3.COUNT AND
           a_string_3.COUNT = a_number_1.COUNT AND
           a_number_1.COUNT = a_number_2.COUNT AND
           a_number_2.COUNT = a_number_3.COUNT AND
           a_number_3.COUNT = a_number_4.COUNT)) THEN
    RAISE e_incomplete_record;
  END IF;

  FORALL i IN a_program_oid.FIRST..a_program_oid.LAST -- bulk process changes
      UPDATE temp_param_values_ps
      SET
        string_1 = a_string_1(i)
       ,string_2 = a_string_2(i)
       ,string_3 = a_string_3(i)
       ,number_1 = TO_NUMBER(a_number_1(i))
       ,number_2 = TO_NUMBER(a_number_2(i))
       ,number_3 = TO_NUMBER(a_number_3(i))
       ,number_4 = TO_NUMBER(a_number_4(i))
      WHERE program_oid = TO_NUMBER(a_program_oid(i))
        AND param_code = a_param_code(i)
        AND param_value_oid = TO_NUMBER(a_param_value_oid(i));
  ...

- Dan Clamage