PL/SQL elegant Solution for HTML-User Input

by

Summary

The issue presented involves maintaining state and passing a large number of parameters in an Oracle PL/SQL Web Toolkit (OWA) application. The developer currently relies on explicitly passing T_ARR collections and individual variables via hidden form fields, leading to “parameter bloat” and brittle code. While the developer asked about owa_utils context variables, the core requirement is state management and code modularization.

The robust solution for a “ton of parameters” is not owa_utils (which is generally deprecated or limited for this purpose) but rather the OWA_COOKIE package. This allows the application to store state in HTTP Cookies, which are automatically sent with every request, eliminating the need to manually echo hundreds of hidden input fields. For enterprise-grade applications, migrating to the PL/SQL Gateway (mod_plsql) DAD attributes or APEX is the standard architectural fix.

Root Cause

The root cause of the complexity in the provided code is the stateless nature of HTTP combined with a lack of proper session management mechanisms. In standard PL/SQL Web Toolkit development:

  • No Automatic State: The server does not remember the previous request.
  • Manual Parameter Propagation: To maintain state, every variable (and array) required for the next step must be explicitly serialized into the HTML response (usually as <input type="hidden">) and parsed back on the next request.
  • Array Limitations: HTP procedures cannot directly print PL/SQL collection types (like T_ARR) into HTML. The developer must iterate through the array indices (e.g., a_id.first to a_id.last) and generate name="a_id[1]", name="a_id[2]" syntax, or generate multiple hidden inputs with sequential names. This creates extremely verbose and error-prone code.

Why This Happens in Real Systems

This pattern emerges in legacy or custom-built web systems for several reasons:

  • Legacy Constraints: The application might be built on older Oracle versions where the DBMS_SESSION package was restricted or APEX was not available.
  • Misunderstanding of Web State: Developers coming from desktop GUI backgrounds often try to replicate “global variables” on the web without understanding the request/response cycle.
  • The “owa_utils” Red Herring: As seen in the prompt, developers often discover utility packages like owa_utils (specifically owa_utils.get_cgi_env), but these only allow reading the environment. They do not solve the problem of persisting data from one page generation to the next without using cookies or URL parameters.

Real-World Impact

Relying on massive hidden form fields for state leads to severe production issues:

  • Payload Bloat: If you are passing 1,000 items in arrays, the HTML page size explodes. This consumes massive bandwidth and leads to slow rendering on the client side.
  • URL/Request Limits: Browsers and web servers (Oracle HTTP Server) have limits on the length of a GET request URL and the size of a POST request body. If a_id or a_rating arrays grow too large, the submission will fail with “414 URI Too Long” or “413 Request Entity Too Large.”
  • Security Risks: Exposing internal data structures (like database IDs or sensitive metadata) in hidden form fields allows users to manipulate them easily, leading to IDOR (Insecure Direct Object Reference) vulnerabilities.
  • Maintainability Hell: Adding a new parameter requires changing HTML generation in multiple places (forms, hidden inputs, parsing logic).

Example or Code

The most direct solution to the user’s problem (handling arrays without passing them explicitly in the form) is using OWA_COOKIE. This package simulates session state.

1. The Problem Code (Simplified):
This is what the user is trying to avoid.

-- BAD: Generating massive HTML for arrays
htp.print('');
htp.print('');

2. The Solution: Using OWA_COOKIE
You save the data into a cookie (which the browser stores and sends back), and you read it on the next request.

-- SAVING STATE (In the 'rate' procedure step)
DECLARE
  l_cookie owa_cookie.cookie;
BEGIN
  -- Create a cookie named 'SESSION_DATA'
  -- We can store a delimited string of IDs or specific values
  l_cookie := owa_cookie.cookie('SESSION_DATA', p_user_name || ':' || cur_id || ':' || cur_colour);

  -- The browser automatically remembers this for the next call
  -- No need to print hidden inputs for these values
  htp.print('...'); 
END;


-- READING STATE (In the 'done' procedure step)
DECLARE
  l_vals owa_cookie.cookie;
  l_user VARCHAR2(100);
  l_id   VARCHAR2(100);
  l_col  VARCHAR2(100);
BEGIN
  -- Retrieve the cookie sent by the browser
  l_vals := owa_cookie.get_cookie('SESSION_DATA');

  -- Parse the values (assuming format "User:ID:Colour")
  -- Note: In real scenarios, you might store multiple cookies or JSON
  -- If using arrays, you typically store the count in one cookie 
  -- and loop to read numbered cookies like 'RATE_1', 'RATE_2', etc.

  -- Example for single value retrieval:
  -- htp.print('User: ' || l_vals.vals(1)); 
END;

3. Handling Arrays with Cookies:
To handle the a_id array without passing it, you would iterate and set multiple cookies or one large cookie string, then parse it on the other side. However, the Senior Engineer approach is different (see below).

How Senior Engineers Fix It

Senior engineers avoid owa_utils for state and do not rely on cookies for complex array data (due to size limits). They use Mod_plsql (PL/SQL Gateway) DAD Attributes or PL/SQL Web Agent (AGT) configuration.

The “Silver Bullet”: owa.sec_cookie or Mod_plsql Session Management

In a properly configured environment (mod_plsql), you do not write cookie logic manually. You configure the DAD (Database Access Descriptor) in the Apache configuration to handle sessions automatically.

However, if you must stick to code-level fixes in a raw OWA environment without modifying server configs, the cleanest architectural pattern is JSON Serialization:

  1. JSON in Cookie: Serialize the array of ratings into a JSON string and store it in a single cookie.
  2. JSON in Database: Instead of passing data via the client, pass a p_session_id. Store the transient data in a global temporary table or a staging table keyed by that session ID.

The Ultimate Fix (Modularization):
Refactor the procedure to accept a single p_json_payload CLOB parameter.

-- Refined Procedure Signature
procedure test_page( 
  p_json_payload in CLOB default null
) 
as
  l_data t_json_object; -- A custom object type or use APEX_JSON
begin
  -- 1. Parse JSON into a local PL/SQL object (using APEX_JSON or JSON_OBJECT_T)
  -- 2. No htp.print for hidden inputs needed
  -- 3. Logic branches based on l_data.choice
end;

This reduces parameters to one, regardless of data volume.

Why Juniors Miss It

Junior developers miss these solutions because:

  1. Web Development is Counter-Intuitive: They try to keep a connection “open” or keep variables “live” like a desktop app. They don’t grasp that the browser is a dumb terminal that only remembers what you explicitly tell it to (hidden inputs, cookies, or URLs).
  2. Lack of Server Configuration Knowledge: They often don’t have access to httpd.conf or Oracle Net configuration to enable Mod_plsql session management, so they try to brute-force it in SQL.
  3. Fear of Serialization: Parsing strings (delimited lists or JSON) back into PL/SQL arrays is harder to write than simply printing HTP.PRINT statements. They choose the path of immediate visual gratification (forms look right) over architectural robustness.