ORDS pagination with PL/SQL source type

Those days, I had a requirement to create an ORDS endpoint that would call a dynamic SQL. Also, my ORDS endpoint was using a GTT and thus first I needed to load this table with some temporary data.

Thus, I was left with no other option than using the PL/SQL handler type in my ORDS. However, one of the biggest issues of using the PL/SQL and returning a REF_CURSOR as an OUT RESULTSET is that pagination is not automatically handled by ORDS (as of version 22.1.6). So I had to create my own solution to avoid spolling a thousand rows, which I'm sharing with you all today.

To avoid coding inside ORDS, I created a package with all the logic inside and called this package from my ORDS:

The package that I created had to add a metadata object to the output with the following entries:

$ curl -s -S --request GET https://test.oracle.com/ords/test/test/test
{
  "items" :
  [...],
  "metadata" :
  {
    "offset" : 0,
    "limit" : 25,
    "count" : 25,
    "hasMore" : true,
    "links" :
    {
      "prev_page" : null,
      "next_page" : "https://test.oracle.com/ords/test/test/test?page_offset=1"
    }
  }
}

I do prefer to work with the page_offset and page_size, rather than fetch_offset and fetch_size , but you can adapt the code to support the second.

Code

First, create the package to handle the PL/SQL output and pagination:

CREATE OR REPLACE PACKAGE "X_ORDS_PROCESS" AS

  -- Created by Rodrigo Jorge - https://www.dbarj.com.br/ -

  -- List
  PROCEDURE ORDS_RUN (
    IN_SQL    IN VARCHAR2,
    IN_OFFSET IN INTEGER DEFAULT NULL,
    IN_SIZE   IN INTEGER DEFAULT NULL
  );

END X_ORDS_PROCESS;
/

CREATE OR REPLACE PACKAGE BODY "X_ORDS_PROCESS" AS

  -- Created by Rodrigo Jorge - https://www.dbarj.com.br/ -

  -- Due to 32K limit of HTP.PRN, create a LOOP
  PROCEDURE PRINT_CLOB (
    IN_CLOB CLOB
  ) IS
    VAMOUNT INTEGER := 4000;
    VPOS    INTEGER := 1;
    VBUFFER VARCHAR2(32000 BYTE);
  BEGIN
    LOOP
      BEGIN
        DBMS_LOB.READ(IN_CLOB, VAMOUNT, VPOS, VBUFFER);
        VPOS := VPOS + VAMOUNT;
        HTP.PRN(VBUFFER);
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          RETURN;
        WHEN OTHERS THEN
          IF SQLCODE = -6502 THEN
            RETURN;
          ELSE
            RAISE;
          END IF;
      END;
    END LOOP;
  END PRINT_CLOB;

  -- Function to beautifier the output JSON
  FUNCTION FORMAT_JSON (
    P_CLOB IN CLOB
  ) RETURN CLOB IS

    L_BLOB BLOB;
    L_CLOB CLOB;

    FUNCTION CLOB_TO_BLOB (
      P_CLOB CLOB
    ) RETURN BLOB IS

      L_BLOB BLOB;
      O1     INTEGER := 1;
      O2     INTEGER := 1;
      C      INTEGER := 0;
      W      INTEGER := 0;
    BEGIN
      SYS.DBMS_LOB.CREATETEMPORARY(L_BLOB, TRUE);
      SYS.DBMS_LOB.CONVERTTOBLOB(L_BLOB, P_CLOB, LENGTH(P_CLOB), O1, O2, 0, C, W);

      RETURN L_BLOB;
    END CLOB_TO_BLOB;

  BEGIN
    L_BLOB := CLOB_TO_BLOB(P_CLOB);
    SELECT JSON_SERIALIZE(L_BLOB RETURNING CLOB PRETTY)
      INTO L_CLOB
      FROM DUAL;

    RETURN L_CLOB;
  END FORMAT_JSON;

  -- Main function
  PROCEDURE ORDS_RUN (
    IN_SQL    IN VARCHAR2,
    IN_OFFSET IN INTEGER DEFAULT NULL,
    IN_SIZE   IN INTEGER DEFAULT NULL
  ) AS

    V_SQL          VARCHAR2(1000);
    L_CUR          SYS_REFCURSOR;

    -- Convert to XML
    L_XML          XMLTYPE;
    L_CTX          DBMS_XMLGEN.CTXHANDLE;

    -- Pagination Control
    L_FETCH_OFFSET NUMBER;
    L_COUNT        NUMBER;
    L_DEF_OFFSET   CONSTANT NUMBER := 0;
    L_DEF_SIZE     CONSTANT NUMBER := 25;
    L_PAGE_OFFSET  NUMBER;
    L_PAGE_SIZE    NUMBER;
    L_HASMORE      BOOLEAN;

    -- URL
    V_PREV_URL     VARCHAR2(1000);
    V_NEXT_URL     VARCHAR2(1000);

    PROCEDURE PREPARE_URL AS
      V_URL VARCHAR2(1000);
    BEGIN

      -- Prepare URL
      V_URL := OWA_UTIL.GET_CGI_ENV('X-APEX-BASE') || OWA_UTIL.GET_CGI_ENV('X-APEX-PATH');
      V_URL := REGEXP_REPLACE(V_URL, '[&]?page_offset=[0-9]+', '');
      V_URL := REGEXP_REPLACE(V_URL, '[&]?page_size=[0-9]+', '');
      V_URL := REGEXP_REPLACE(V_URL, '\?$');
      V_URL := REGEXP_REPLACE(V_URL, '\?&', '?');
      IF INSTR(V_URL, '?') = 0 THEN
        V_URL := V_URL || '?';
      ELSE
        V_URL := V_URL || '&';
      END IF;

      V_PREV_URL := V_URL;
      V_NEXT_URL := V_URL;

      -- If has more.
      IF L_HASMORE THEN
        V_NEXT_URL := V_NEXT_URL || 'page_offset=' || ( L_PAGE_OFFSET + 1 ) || '&';
      END IF;

      IF L_PAGE_OFFSET > 1 THEN
        V_PREV_URL := V_PREV_URL || 'page_offset=' || ( L_PAGE_OFFSET - 1 ) || '&';
      END IF;

      -- IF L_PAGE_SIZE != L_DEF_SIZE THEN
      IF IN_SIZE IS NOT NULL THEN
        V_PREV_URL := V_PREV_URL || 'page_size=' || L_PAGE_SIZE;
        V_NEXT_URL := V_NEXT_URL || 'page_size=' || L_PAGE_SIZE;
      END IF;

      -- Clean extra '&'
      V_PREV_URL := REGEXP_REPLACE(V_PREV_URL, '&$');
      V_PREV_URL := REGEXP_REPLACE(V_PREV_URL, '\?$');
      V_NEXT_URL := REGEXP_REPLACE(V_NEXT_URL, '&$');
      V_NEXT_URL := REGEXP_REPLACE(V_NEXT_URL, '\?$');
    END PREPARE_URL;

  BEGIN

    -- Check for inconsistent inputs.
    IF IN_SIZE < 0 OR IN_OFFSET < 0 THEN
      RAISE INVALID_NUMBER;
    END IF;

    IF IN_SIZE IS NOT NULL THEN
      L_PAGE_SIZE := IN_SIZE;
    ELSE
      L_PAGE_SIZE := L_DEF_SIZE;
    END IF;

    IF IN_OFFSET IS NOT NULL THEN
      L_PAGE_OFFSET := IN_OFFSET;
    ELSE
      L_PAGE_OFFSET := L_DEF_OFFSET;
    END IF;

    -- Check for inconsistent inputs.
    IF L_PAGE_SIZE < 0 OR L_PAGE_OFFSET < 0 THEN
      RAISE INVALID_NUMBER;
    END IF;

    -- Fetch Offset
    L_FETCH_OFFSET := ( L_PAGE_OFFSET * L_PAGE_SIZE );

    -- Count SQL
    V_SQL := q'[
    SELECT COUNT(*)
    FROM (
          ]' || IN_SQL || q'[
          OFFSET :L_FETCH_OFFSET ROWS FETCH NEXT :L_PAGE_SIZE + 1 ROWS ONLY
         )
    ]';

    -- Check total rows.
    EXECUTE IMMEDIATE V_SQL
      INTO L_COUNT
      USING L_FETCH_OFFSET, L_PAGE_SIZE;

    -- Define if has more.
    IF L_COUNT < L_PAGE_SIZE + 1 THEN
      L_HASMORE := FALSE;
    ELSE
      L_HASMORE := TRUE;
    END IF;

    -- Original SQL
    V_SQL := IN_SQL || 'OFFSET :L_FETCH_OFFSET ROWS FETCH NEXT :L_PAGE_SIZE ROWS ONLY';
    OPEN L_CUR FOR V_SQL
      USING L_FETCH_OFFSET, L_PAGE_SIZE;

    -- The reason the output is converted to XML is to handle null attributes.
    /* converts the results of a SQL query to a canonical XML format */
    L_CTX := DBMS_XMLGEN.NEWCONTEXT(L_CUR); 
    /* Sets NULL handling options */
    DBMS_XMLGEN.SETNULLHANDLING(L_CTX, DBMS_XMLGEN.EMPTY_TAG);
    -- DBMS_XMLQUERY.SETTAGCASE(L_CTX, 1);
    L_XML := DBMS_XMLGEN.GETXMLTYPE(L_CTX);
    DBMS_XMLGEN.CLOSECONTEXT(L_CTX);

    -- If has more.
    IF L_HASMORE THEN
      L_COUNT := L_COUNT - 1;
    END IF;

    -- Prepare previous and next pages URL
    PREPARE_URL;

    -- Print Output
    OWA_UTIL.MIME_HEADER('application/json');
    APEX_JSON.INITIALIZE_CLOB_OUTPUT;
    -- Main Open = {
    APEX_JSON.OPEN_OBJECT;
    -- APEX_JSON.WRITE('items', L_CUR);
    APEX_JSON.WRITE('items', L_XML, TRUE);
    -- Metadata Open => metadata: {
    APEX_JSON.OPEN_OBJECT('metadata');
    APEX_JSON.WRITE('offset', L_PAGE_OFFSET);
    APEX_JSON.WRITE('limit', L_PAGE_SIZE);
    APEX_JSON.WRITE('count', L_COUNT);
    APEX_JSON.WRITE('hasMore', L_HASMORE);
    -- Links Open => links: {
    APEX_JSON.OPEN_OBJECT('links');
    IF L_PAGE_OFFSET > 0 THEN
      APEX_JSON.WRITE('prev_page', V_PREV_URL);
    ELSE
      APEX_JSON.WRITE('prev_page', '', TRUE);
    END IF;

    IF L_HASMORE THEN
      APEX_JSON.WRITE('next_page', V_NEXT_URL);
    ELSE
      APEX_JSON.WRITE('next_page', '', TRUE);
    END IF;

    -- Links Close = }
    APEX_JSON.CLOSE_OBJECT;
    -- Metadata Close = }
    APEX_JSON.CLOSE_OBJECT;
    -- Main Close = }
    APEX_JSON.CLOSE_OBJECT;
    PRINT_CLOB(FORMAT_JSON(APEX_JSON.GET_CLOB_OUTPUT));
  END ORDS_RUN;

END X_ORDS_PROCESS;
/

PS:

  1. I use APEX_JSON to create the metadata object tag, but if you don't have it installed in your database, you can adapt to manually creating your JSON by just appending the strings or using other methods.
  2. Even though APEX_JSON does support a REF CURSOR as input, I first convert it to XML so I won't lose the null attributes.

 

Next, create and enable the REST API endpoint. Example:

BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => USER,
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'test',
      p_auto_rest_auth      => TRUE);
    
  ORDS.DEFINE_MODULE(
      p_module_name    => 'test',
      p_base_path      => '/test/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'test',
      p_pattern        => 'test',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'test',
      p_pattern        => 'test',
      p_method         => 'GET',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 
'DECLARE
  V_SQL VARCHAR2(4000);
BEGIN
  V_SQL := q''(
           SELECT T.*
           FROM   ALL_OBJECTS T
           ORDER BY 1
           )'';

  X_ORDS_PROCESS.ORDS_RUN(
    IN_SQL          => V_SQL,
    IN_OFFSET       => :page_offset,
    IN_SIZE         => :page_size
  );

EXCEPTION
  WHEN INVALID_NUMBER THEN
    :string_out := ''Provided page_offset or page_size is invalid.'';
    :status_code := 400;
END;');

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'test',
      p_pattern            => 'test',
      p_method             => 'GET',
      p_name               => 'error',
      p_bind_variable_name => 'string_out',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => 'Return the message if you receive an error.');

        
COMMIT;

END;
/

Sample output for 2 entries only:

$ curl -s -S --request GET https://test.oracle.com/ords/test/test/test?page_size=2
{
  "items" :
  [
    {
      "OWNER" : "APEX_220100",
      "OBJECT_NAME" : "WWV_FLOW_SESSION_SEQ",
      "SUBOBJECT_NAME" : null,
      "OBJECT_ID" : 482011,
      "DATA_OBJECT_ID" : null,
      "OBJECT_TYPE" : "SEQUENCE",
      "CREATED" : "22-JUL-22",
      "LAST_DDL_TIME" : "22-JUL-22",
      "TIMESTAMP" : "2022-07-22:00:46:07",
      "STATUS" : "VALID",
      "TEMPORARY" : "N",
      "GENERATED" : "N",
      "SECONDARY" : "N",
      "NAMESPACE" : 1,
      "EDITION_NAME" : null,
      "SHARING" : "METADATA LINK",
      "EDITIONABLE" : null,
      "ORACLE_MAINTAINED" : "Y",
      "APPLICATION" : "N",
      "DEFAULT_COLLATION" : null,
      "DUPLICATED" : "N",
      "SHARDED" : "N",
      "CREATED_APPID" : 43,
      "CREATED_VSNID" : 48,
      "MODIFIED_APPID" : 43,
      "MODIFIED_VSNID" : 48
    },
    {
      "OWNER" : "APEX_220100",
      "OBJECT_NAME" : "WWV_SEQ",
      "SUBOBJECT_NAME" : null,
      "OBJECT_ID" : 482012,
      "DATA_OBJECT_ID" : null,
      "OBJECT_TYPE" : "SEQUENCE",
      "CREATED" : "22-JUL-22",
      "LAST_DDL_TIME" : "22-JUL-22",
      "TIMESTAMP" : "2022-07-22:00:46:07",
      "STATUS" : "VALID",
      "TEMPORARY" : "N",
      "GENERATED" : "N",
      "SECONDARY" : "N",
      "NAMESPACE" : 1,
      "EDITION_NAME" : null,
      "SHARING" : "METADATA LINK",
      "EDITIONABLE" : null,
      "ORACLE_MAINTAINED" : "Y",
      "APPLICATION" : "N",
      "DEFAULT_COLLATION" : null,
      "DUPLICATED" : "N",
      "SHARDED" : "N",
      "CREATED_APPID" : 43,
      "CREATED_VSNID" : 48,
      "MODIFIED_APPID" : 43,
      "MODIFIED_VSNID" : 48
    }
  ],
  "metadata" :
  {
    "offset" : 0,
    "limit" : 2,
    "count" : 2,
    "hasMore" : true,
    "links" :
    {
      "prev_page" : null,
      "next_page" : "https://test.oracle.com/ords/test/test/test?page_offset=1&page_size=2"
    }
  }
}

If you want to bring all rows at once, just add ?page_size=100000 or to a big enough number on your call.

Have you enjoyed? Please leave a comment or give a 👍!

Leave a Reply

Your email address will not be published.