Create an ORDS REST Service with Custom JSON In and Out

As I’ve been getting up to speed with ORDS REST services over the last couple weeks, Tim Hall’s Oracle-Base tutorial about REST services basics proved very helpful. I started by experimenting with ORDS’ support for automatic handling of different built-in datatypes to and from JSON. In many cases, this lets you focus on your core PL/SQL logic without having to work directly with JSON.

However, to make sure I was understanding all the moving parts, I set myself the task of creating a service that could process an arbitrary JSON document as the request and return back an arbitrary JSON document in response (possibly created programmatically or by doing a JSON SQL query or some combination of the two). After reading another of Tim’s articles RESTful Web Services Handling Complex JSON Payloads, I set out to try my own example. The Oracle reference docs on PL/SQL JSON datatypes also came in handy while doing this.

In my head, what made the most sense to represent a PL/SQL procedure that accepted a JSON request and returned a JSON response was a procedure specification like this:

procedure generic_service(p_request in json_object_t,
                          p_response out json_object_t)

The simplest version of such a service would be an “echo” service that just returned the JSON document passed in as the response:

procedure generic_service(p_request in json_object_t,
                          p_response out json_object_t) is
begin
    -- Start by just echoing the request as the response
    p_response := p_request;
end;

I knew that once I got this “echo” service working I could modify the internals of the generic_service() procedure to do whatever I needed to do to process the incoming JSON and return a custom output JSON.

Let’s assume the JSON payload we need to process looks like this:

{
  "customer_id" : 1234,
  "items" : [
    {
      "product_number":"978-0262542456",
      "quantity":1
    },
    {
      "product_number":"978-0358446286",
      "quantity":1
    }
  ]
}

In an EXAMPLES schema I’d previously enabled for ORDS access, I ran the following PL/SQL block to define the module, template, and two handlers (one for the POST verb and another for the PATCH verb). Notice that I’m passing the BLOB-valued :body bind variable to the json_object_t() constructor in order to pass the resulting JSON object as the value of the p_request parameter in the call to generic_service(). This results in parsing the incoming JSON payload into a form that’s easy to work with in PL/SQL.

begin
  ords.define_module(
    p_module_name    => 'rest-jsonecho',
    p_base_path      => 'rest/json/',
    p_items_per_page => 0);
  
  ords.define_template(
   p_module_name    => 'rest-jsonecho',
   p_pattern        => 'echo/');

  ords.define_handler(
    p_module_name    => 'rest-jsonecho',
    p_pattern        => 'echo/',
    p_method         => 'POST',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => q'[
    declare
        l_response json_object_t;
    begin
        generic_service(p_request  => json_object_t(:body),
                        p_response => l_response);
        owa_util.mime_header ('application/json', true); 
        -- Show example of modifying the response from the invoked
        -- json-in-json-out method before returning.
        l_response.put('method-used-was','post');
        htp.p(l_response.stringify);
    END;       
    ]',
    p_items_per_page => 0);

  ords.define_handler(
    p_module_name    => 'rest-jsonecho',
    p_pattern        => 'echo/',
    p_method         => 'PATCH',
    p_source_type    => ords.source_type_plsql,
    p_source         => q'[
    declare
        l_response json_object_t;
    begin
        generic_service(p_request  => json_object_t(:body),
                        p_response => l_response);
        owa_util.mime_header ('application/json', true); 
        -- Show example of modifying the response from the invoked
        -- json-in-json-out method before returning.
        l_response.put('method-used-was','patch');
        htp.p(l_response.stringify);
    END;        
    ]',
    p_items_per_page => 0);    
  commit;
end;

When the procedure call to generic_service() returns, the l_response variable contains the JSON object returned in the p_response out parameter. The handler’s PL/SQL code can then perform any post-processing if necessary. In the two example handlers above, notice that I’m simply setting one additional JSON property named method-used-was so you can see a difference between sending the sample payload using the POST or PATCH verb.

In order to write out the JSON response, we start by using owa_util.mime_header() to add the appropriate HTTP response header that signals to the caller that the response is a JSON document. Then we use the venerable htp.p() procedure to write the “stringified” version of the JSON object out to the response. The json_object_t object’s stringify() function prints out the contents of the JSON data structure using the right combination of curly braces, colons, quotes, and brackets as needed to produce the expected text representation of the data.

To test out the result, I sent a POST and a PATCH request to my local ORDS server with the URL http://localhost:8181/ords/examples/rest/json/echo/

After my first attempts were failing to work, I learned from colleagues that ORDS is very exacting when matching the URL for the defined template handlers. My PL/SQL that defined the template handlers used "rest/json/" for the module base path and used "echo/" for the template pattern, so sending my request to the URL http://localhost:8181/ords/examples/rest/json/echo(without the trailing slash) was not matching any defined template handler. Good lesson learned!

Here’s what my Postman window looked like when testing the POST request:

With this basic skeleton in place, I aim to create a number of custom JSON-in, JSON-out services for a sample app I’m working on that will follow this pattern. If you notice something I could be doing even better, please let me know on Twitter. I’m always happy to learn ways to improve my code, and of course still consider myself an ORDS newbie, so I’m sure there is plenty more to learn.

Mixing Positional & Named Params Can Lead to Trouble

Mixing positional and named parameters can lead to trouble.

A user of the APEX application I built for a local non-profit sent me a screenshot over WhatsApp today saying that she was getting an error trying to save a change to a student record in the system.

I could reproduce the problem, and quickly enabled APEX debug trace to level 9 which gave me a rich amount of information on what was happening.

The trigger on my STUDENTS table contained lines that were conditionally logging audit information for particular column values like the example below and the call stack for the ORA-6502 visible in the APEX debug trace was pointing to the line handling the audit for change in value of the YEAR_OF_BIRTH column.

if (updating) then
    -- etc.
    bambini.handle_audit('STUDENTS','FIRST_NAME',:old.id,
                         p_old_text_value => :old.first_name,
                         p_new_text_value => :new.first_name);
    bambini.handle_audit('STUDENTS','CLASS_YEAR',:old.id,
                         p_old_number_value => :old.class_year,
                         p_new_number_value => :new.class_year);
    bambini.handle_audit('STUDENTS','YEAR_OF_BIRTH',lv_user,:old.id,
                         p_old_number_value => :old.year_of_birth,
                         p_new_number_value => :new.year_of_birth);
    bambini.handle_audit('STUDENTS','DATE_OF_BIRTH',:old.id,
                         p_old_date_value => :old.date_of_birth,
                         p_new_date_value => :new.date_of_birth);
    -- etc.
end if;

Nestled in there among many similar lines of code, my eye wasn’t seeing what could be wrong, but luckily my colleague Patrick spotted an odd disturbance in the repetitive code. What was that extra lv_user variable doing in the argument list? None of the other lines had it! The declaration of the handle_audit() procedure being invoked looked like this:

    PROCEDURE handle_audit(
       p_table_name       VARCHAR2,
       p_column_name      VARCHAR2,
       p_id               NUMBER,
       p_old_text_value   VARCHAR2 := null,
       p_new_text_value   VARCHAR2 := null,
       p_old_date_value   DATE     := null,
       p_new_date_value   DATE     := null,
       p_old_number_value NUMBER   := null,
       p_new_number_value NUMBER   := null    
    );

An accidental copy/paste inserted lv_user at a position in the text that, due to my mixing positional arguments with named arguments, did not cause a compilation error. It resulted in passing the value of lv_user to the numeric p_id parameter and causing PL/SQL to convert the value of the numeric :old.id field to a string as the value of the next p_old_text_value parameter. Since lv_user contained a string with the currently logged-in user name, it was getting an ORA-6502 trying to convert that to a number to pass into p_id.

As a result of this exercise, I learned never to mix positional and named parameter notation when invoking PL/SQL program units, and it reminded me how useful it would be if PL/SQL supported an ANYRECORD datatype so I could have written a single, generic handle_audit() procedure that could accept the :new and :old records from the trigger without stamping out line after line of similar code in all of my table’s triggers where I want value-change auditing. So I also filed an enhancement request in our internal bug database requesting that PL/SQL someday add an ANYRECORD type to make writing generic record-processing code like this less tedious. If that were a thing, my trigger could have been a one-liner:

-- NOTE: Dream code, not currently supported!
if (updating) then
    bambini.handle_audit('STUDENTS',:new,:old);
end if;

Thanks, Patrick, for the eagle-eye code spotting and the lesson learned on PL/SQL best practice.

Interactive, User-Configurable Card Width #JoelKallmanDay

Create a cards region with interactive card width selector, saving user’s preference across logins.

We miss you, Joel.

Everyone in the Oracle APEX community

Oracle APEX card regions let your users browse and act on a grid of tiles, each representing a row of data. The card region directly taps into your end user’s intuition of browsing their mobile phone’s photo library, especially when the cards feature an image, so it’s a compelling way to present data to users.

The card region’s grid style resizes automatically to the screen space available, but by default end users can’t influence the size of each tile in the grid. Read on to learn how to let your users adjust the card width interactively and remember their choice as a preference across logins. At the end, you’ll find a step-by-step video tutorial and downloadable sample application, but we’ll explore the key ideas behind the technique first.

Overview of Strategy

To implement the feature, you’ll add the following to your page with the card region:

  • A select-list page item showing list of available sizes (e.g. Small, Medium, Large)
    • Having corresponding values of the pixel widths 180px, 220px, 300px
    • Defaulted to the static value for the Medium size (220px)
    • Configured with Maintain Session State setting of Per User (Disk).
  • A dynamic action “trigger” for the select list’s Change event with actions:
    1. Execute JavaScript to update the CSS variable controlling the card size
    2. Execute Server-side Code to save the updated value to the APEX session state

What’s a CSS Variable?

A CSS variable is a custom property whose name is prefixed by a double-hyphen (e.g. --preferred-button-width). It can be associated with any element in a page, either explicitly or implicitly by being associated with a class applied to that element. Any CSS expression can reference the value of a variable by using the syntax var(--variable-name) . The usage can also provide a default value to use in case the variable reference has no value of its own by including a second argument like var(--variable-name, defaultValue) . So, a CSS class named myButton could set the width property to the value of the --preferred-button-width variable (providing a default of 80 pixels) like this:

.myButton {
  width : var(--preferred-button-width,80px);
}

If the same variable exists on multiple elements in the page, the value of the most specific occurrence is used. To provide a global default value for a variable, you can set a value for it on the special :root pseudo-class. If no more specific element in the page provides a value, then the one from the root is used.

As we’ll see below, the Universal Theme style class that defines the card region’s grid layout uses a CSS variable to control the size of the cards in the grid. So setting the right variable to a user-chosen value on the appropriate scope for your needs is the crux of the solution. So let’s explore which variable to set and consider on what context makes sense to set it.

Which Variable Do We Need to Set?

While a page containing a grid region is displayed, using the Chrome developer tools to inspect one of the cards (and clicking to enable the CSS-grid related style properties) we can observe that the card items grid layout is setup by this CSS class:

.a-CardView-items--grid {
    grid-template-columns: 
         repeat(auto-fill,minmax(var(--a-cv-item-width,320px),1fr));
}

At first glance, it’s admittedly cryptic, but let’s unpack what it says. This style property defines the grid-template-columns layout to be a repeating set of columns that auto-fill the horizontal space available with uniform-sized grid cells. The browser computes the width of each grid cell automatically so it falls in the range between the values passed to the minmax() function. The first argument, that is the minimum width value, is given by the value of the CSS variable named --a-cv-item-width (or a default to 320 pixels if the variable is not defined). The second argument providing the maximum card width is one fractional unit (1fr), which represents the width of one column in the layout taking into account a spacing between grid cells, too. In short, if we assign the user-preferred card width value to the CSS variable --a-cv-item-width then the grid will instantly react to layout the grid with cards having that width (or slightly bigger to make each grid cell uniformly sized).

On What Context Do We Set the CSS Variable?

We have at least two sensible choices for the context on which to set the card size CSS variable:

  1. On the card region itself, after assigning it a static id in the App Builder, or
  2. On the “global” root context

Choice 1 imposes the user-preferred card width on just the region on which it’s assigned, whereas choice 2 sets the user-preferred card width so that all card regions in the application will abide by it.

The code examples that follow assume you’ve created a page item named P3_CARD_SIZE on the page with the card region, and that the values for the P3_CARD_SIZE select list page item are one of 180px for Small, 220px for Medium, and 300px for Large.

JavaScript to Set the CSS Variable on a Region

After configuring a static id on your card region (e.g. BooksCardRegion), your dynamic action on the P3_CARD_SIZE page item’s Change event can use the following line of JavaScript to change the CSS variable --a-cv-item-width to the new value of the P3_CARD_SIZE page item:

$('#BooksCardRegion').css('--a-cv-item-width',$v('P3_CARD_SIZE'));
JavaScript to Set the CSS Variable Globally

Your dynamic action on the P3_CARD_SIZE page item’s Change event can use the following line of JavaScript to change the CSS variable --a-cv-item-width on the global “root” context to the new value of the P3_CARD_SIZE page item:

$(':root').css('--a-cv-item-width',$v('P3_CARD_SIZE'));

Pushing the Interactive Card Size Change Immediately to the Server

The change to the CSS variable is made in the browser when the dynamic action reacts to the user’s change of the select list page item, and immediately takes visual effect for the end user in their browser. However, to immediately force the value change to be saved in the APEX session, add an additional dynamic action step to Execute server-side Code and which specifies the P3_CARD_SIZE as one of the page items to send to the server. Since it doesn’t need to perform any other server-side logic beyond pushing the values, you can simply use the “do nothing” PL/SQL instruction NULL; to enter into the required PL/SQL script property. If the page item’s Maintain Session State setting is configured to “Per Session (Disk)” then the user’s preference will persist for the duration of the session. If instead it’s set to “Per User (Disk)“, the setting will survive across subsequent user logins.

Step by Step Tutorial Video

NOTE: The video illustrates setting the CSS variable on the :root context, while the downloadable example app illustrates setting the variable on the card region. See above for a consideration on which is appropriate for your use case .

Sample Application

You can download an Oracle APEX 21.1 example application (containing a supporting objects installation script for two sample tables PNL_THEROUX_BOOKS and PNL_PUBLISHERS) from here.

Old Dog, New Tricks

Over lockdown I fell in love with Oracle APEX, while using it to build a donation tracking application for Progetto Dogon, a local non-profit here in Italy. My end users immediately noticed the lightning speed, attractive UI, and many handy new features I was able to deliver in the new system. What they didn’t know, is that APEX did most of the work, while I got to take the credit. It was the ace up my sleeve.

Friends and colleagues know that when something inspires me, I go all in, so I kept learning more about APEX over the past year with the goal of finding a way to join the development team in time. The Harvard CS50x online course I was taking to refresh my knowledge of C and Python let us use any technology to implement our final project, so I used APEX to build an art collection tracking application that I hope to continue enhancing and make available as open source on GitHub eventually in case others might find it useful. If you’re curious to check out what I built, here’s a short demo I had to submit along with the source code for my final grade in the course.

Many colleagues helped me on my APEX learning journey by answering my newbie questions about the tool, but I gradually began to feel confident enough to start answering a few questions about APEX myself on our internal discussion forums. It was eye-opening to witness how many Oracle employees and teams are using APEX on a daily basis to build themselves applications they and their teams use to get their jobs done.

By listening to all the back episodes of The Oracle APEX Talkshow podcast on my daily morning walks, I got to “meet” many of the APEX dev team members including Joel, Mike, Carsten, Patrick, Marc, and others, and became aware of the scale, reach, and passion of the APEX community by listening to numerous other guests on the show. It was a group of like-minded folks across the globe looking out for each other, helping each other succeed. I felt compelled to join them.

Long story short, I started a new chapter in my Oracle career on the APEX dev team on October 1st, and look forward to using this blog to chronicle the new tricks that this old dog learns along the road. We’ve come a long way together since EXEMACRO EXETRG in Forms 2.3 in 1990, but a consistent thread through my Oracle career has been helping the community make the best use of Oracle’s tools and helping make our tools the easiest way to build database-backed business applications. That journey continues in my new role…