Skip to main content

Building maintainable REST services in APEX

A

Any developer who has created a RESTful data service in APEX has faced the following issue: you create a service, and it does not work. It is a pain to debug REST services.

This is typical of most APEX examples out there. You write your query or copy and paste your code here and there, and then move on. Low code, right? Never mind that it cannot perform proper code validation. Never mind that it cannot detect broken components in the future. As you might know from my previous blueprint articles or presentations, you should encapsulate everything in packages and views. That way, you can properly control your code and make sure your app continues to work.


RESTful data services

In short, REST services allow you to expose your data (basically SELECT statements) or logic (DML operations or more complex code) to other apps or systems. And it does not have to be a third-party system—you can consume your own services even from the same app. On the service side, you have to define:

  1. module
  2. template
  3. handler

The first step is to create a module. Pick a module name and path—my module will be called "XXABC_IMAGES" and the path will be "/images/". By creating a module, you are also creating a service. It might look a bit redundant, but don't worry about it for now.

Once you have a module, you can create a template. The URI for our first template would be "user_profile/:id". Together with your server address, "/ords," and your module path, it will become the full service address. On OCI, it would look like: "https://INSTANCE.adb.REGION.oraclecloudapps.com/ords/SCHEMA_ALIAS/MODULE/TEMPLATE/:ARGS". The arguments are prefixed with ":", so in this case we are expecting just one argument: "id". That SCHEMA_ALIAS is something you can set up under "ORDS Schema Attributes" when enabling ORDS.

To be able to access the template, you also have to specify the handler. You must pick a method (there are more, but Oracle implemented GET (fetch), PUT (modify), and DELETE). Generally, GET is all you need, so pick that. As the next step, you must define the source type—and you will pick PL/SQL. From the documentation: Executes an anonymous PL/SQL block and transforms any OUT or IN/OUT parameters into a JSON representation. The htp.p function may also be used to emit custom JSON. This allows you to do anything in your package—you don't have to write any core logic here except the procedure call with passed arguments. And you don't have to define the arguments in Parameters! That is awesome—it makes it more flexible and faster to build.


On the backend side, you need to create a package with procedures for each REST handler. You don't have to create your package with the module name and procedure with the template name, but I highly recommend it. If you face any issues in the future with a specific service, you will know where to find the code just from the address.

  1. a package with procedures matching your handlers
  2. possibly some supporting views

So, for our case, you would create an "XXABC_IMAGES" package with a "USER_PROFILE" procedure that has an "ID" argument. In the REST handler, you would then define this as the source:

xxabc_images.user_profile (
    p_id => :id
);

That's really all there is. You don't have to specify the BEGIN-END keywords, and there is no need for exception handling here, since we will do that in the package. This is how you make your code maintainable: you keep it in the package and just call it.


Dictionary

Before we talk about the package content, let's have a look at the data dictionary views and how your modules and templates are stored:

  • user_ords_services
    • user_ords_modules
      • user_ords_templates
      • user_ords_handlers
        • user_ords_parameters
      • user_ords_schemas

From the queries you can see that Services are essentially aggregates of Modules and Templates:

SELECT * FROM user_ords_services;
SELECT * FROM user_ords_modules;
SELECT * FROM user_ords_templates;
SELECT * FROM user_ords_handlers;
SELECT * FROM user_ords_parameters;
SELECT * FROM user_ords_schemas;
--
SELECT *
FROM user_ords_services s
JOIN user_ords_modules m    ON m.id = s.module_id
JOIN user_ords_schemas c    ON c.id = m.schema_id
JOIN user_ords_templates t  ON t.id = s.template_id
JOIN user_ords_handlers h   ON h.id = s.handler_id;

Note that there are no "ALL_" views. It is either "USER_" or "DBA_".


The package

As you might have guessed from the service name and package, we are going to serve images from the database table over REST. Hence, you will need the table containing the images:

CREATE TABLE IF NOT EXISTS xxabc_users (
    id                  NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 10000 NOT NULL,
    profile_image       BLOB,
    updated_at          DATE,
    --
    CONSTRAINT xxabc_users_pk
        PRIMARY KEY (id)
);

Finally, we need to create the PL/SQL handler (procedure) for the REST handler. We will also need a procedure to serve the image content. This procedure also allows you to send a cache header, so the images are cached by the browser. If you don't have the CORE package, replace it with your own exception handler.

CREATE OR REPLACE PACKAGE BODY xxabc_images AS

    PROCEDURE download (
        p_payload           IN OUT NOCOPY   BLOB,
        p_file_name         IN              VARCHAR2    := NULL,
        p_file_mime         IN              VARCHAR2    := NULL,
        p_file_updated      IN              DATE        := NULL
    )
    AS
    BEGIN
        -- prepare headers
        OWA_UTIL.MIME_HEADER(NVL(p_file_mime, 'application/octet'), FALSE);
        HTP.P('Content-length:' || DBMS_LOB.GETLENGTH(p_payload));
        HTP.P('Content-Disposition: attachment; filename=' || NVL(p_file_name, 'file'));

        -- cache image for the whole year
        IF p_file_updated IS NOT NULL THEN
            HTP.P('Cache-Control: max-age=31536000');
            HTP.P('ETag: "' || TO_CHAR(p_file_updated, 'YYYYMMDDHH24MISS') || '"');
        END IF;

        -- close headers and serve the image to the browser
        OWA_UTIL.HTTP_HEADER_CLOSE();
        --
        wpg_docload.download_file(p_payload);
        --
    EXCEPTION
    WHEN OTHERS THEN
        core.raise_error();
    END;



    PROCEDURE user_profile (
        p_id                NUMBER
    )
    AS
    BEGIN
        FOR c IN (
            SELECT t.*
            FROM xxabc_users t
            WHERE t.id               = p_id
                AND t.profile_image  IS NOT NULL
        ) LOOP
            download (
                p_payload       => c.profile_image,
                p_file_name     => 'user_profile_' || TO_CHAR(p_id),
                p_file_mime     => '',
                p_file_updated  => c.updated_at
            );
            RETURN;
        END LOOP;

        -- you can serve some generic image here...
        NULL;
        --
    EXCEPTION
    WHEN core.app_exception THEN
        RAISE;
    WHEN OTHERS THEN
        core.raise_error();
    END;

END;
/


More use cases

This is nice, but you probably want more than just serving some images. In your procedures, you can output whatever you want via HTP.P calls. For simple queries, the APEX_JSON package is good enough, but you might want to explore the JSON_OBJECT function for more complex cases. And of course, you can pass all the arguments you need.

    PROCEDURE show_dual
    AS
        v_clob      CLOB;
        v_cursor    SYS_REFCURSOR;
    BEGIN
        OPEN v_cursor FOR
            SELECT 1 AS row_id, 'A' AS column_name FROM DUAL UNION ALL
            SELECT 2 AS row_id, 'B' AS column_name FROM DUAL UNION ALL
            SELECT 3 AS row_id, 'C' AS column_name FROM DUAL;
        --
        /*
        APEX_JSON.INITIALIZE_CLOB_OUTPUT(p_indent => 4);
        APEX_JSON.WRITE(v_cursor);
        v_clob := APEX_JSON.GET_CLOB_OUTPUT;
        APEX_JSON.FREE_OUTPUT;
        HTP.P(v_clob);
        */
        APEX_JSON.OPEN_OBJECT;
        APEX_JSON.WRITE('rowset', v_cursor);
        APEX_JSON.CLOSE_OBJECT;
    EXCEPTION
    WHEN OTHERS THEN
        core.log_error();
        --
        APEX_JSON.OPEN_OBJECT;
        APEX_JSON.WRITE('rowset', '[]');
        APEX_JSON.WRITE('error', SQLERRM);
        APEX_JSON.CLOSE_OBJECT;
    END;

The beauty of this is how easily you can convert a cursor to a JSON object. But if you are just exposing some of your tables or views, it might make more sense to stick with the Collection type instead of custom code.


And that is it

Now, if you have an issue, you will know at compilation time. You will know that your package breaks (is no longer valid) because referenced objects have changed. Every issue will get logged through the exception handler. And you can easily extend it with other logs, so you can, for example, track which images were downloaded and how many times. You can extend it so it can attach to your current APEX session for security checks. You can add support for default images, versions... Whatever you want, without pasting all of this code.


Tags:

Comments

Post a Comment