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