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://dev.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://dev.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:
- 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.
- 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.
