{"id":14925,"date":"2022-11-30T09:53:36","date_gmt":"2022-11-30T12:53:36","guid":{"rendered":"https:\/\/dev.dbarj.com.br\/?p=14925"},"modified":"2022-11-30T10:27:31","modified_gmt":"2022-11-30T13:27:31","slug":"ords-pagination-with-pl-sql-source-type","status":"publish","type":"post","link":"https:\/\/dev.dbarj.com.br\/en\/2022\/11\/ords-pagination-with-pl-sql-source-type\/","title":{"rendered":"ORDS pagination with PL\/SQL source type"},"content":{"rendered":"

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.<\/p>\n

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.<\/p>\n

To avoid coding inside ORDS, I created a package with all the logic inside and called this package from my ORDS:<\/p>\n

\"\"<\/p>\n

The package that I created had to add a metadata object to the output with the following entries:<\/p>\n

$ curl -s -S --request GET https:\/\/test.oracle.com\/ords\/test\/test\/test\r\n{\r\n  \"items\" :\r\n  [...],\r\n  \"metadata\" :\r\n  {\r\n    \"offset\" : 0,\r\n    \"limit\" : 25,\r\n    \"count\" : 25,\r\n    \"hasMore\" : true,\r\n    \"links\" :\r\n    {\r\n      \"prev_page\" : null,\r\n      \"next_page\" : \"https:\/\/test.oracle.com\/ords\/test\/test\/test?page_offset=1\"\r\n    }\r\n  }\r\n}<\/pre>\n
I do prefer to work with the page_offset<\/code> and page_size<\/code>, rather than fetch_offset<\/code> and fetch_size<\/code> , but you can adapt the code to support the second.<\/p>\n
Code<\/h4>\n
First, create the package to handle the PL\/SQL output and pagination:<\/p>\n
CREATE OR REPLACE PACKAGE \"X_ORDS_PROCESS\" AS\r\n\r\n  -- Created by Rodrigo Jorge - https:\/\/dev.dbarj.com.br\/ -\r\n\r\n  -- List\r\n  PROCEDURE ORDS_RUN (\r\n    IN_SQL    IN VARCHAR2,\r\n    IN_OFFSET IN INTEGER DEFAULT NULL,\r\n    IN_SIZE   IN INTEGER DEFAULT NULL\r\n  );\r\n\r\nEND X_ORDS_PROCESS;\r\n\/\r\n\r\nCREATE OR REPLACE PACKAGE BODY \"X_ORDS_PROCESS\" AS\r\n\r\n  -- Created by Rodrigo Jorge - https:\/\/dev.dbarj.com.br\/ -\r\n\r\n  -- Due to 32K limit of HTP.PRN, create a LOOP\r\n  PROCEDURE PRINT_CLOB (\r\n    IN_CLOB CLOB\r\n  ) IS\r\n    VAMOUNT INTEGER := 4000;\r\n    VPOS    INTEGER := 1;\r\n    VBUFFER VARCHAR2(32000 BYTE);\r\n  BEGIN\r\n    LOOP\r\n      BEGIN\r\n        DBMS_LOB.READ(IN_CLOB, VAMOUNT, VPOS, VBUFFER);\r\n        VPOS := VPOS + VAMOUNT;\r\n        HTP.PRN(VBUFFER);\r\n      EXCEPTION\r\n        WHEN NO_DATA_FOUND THEN\r\n          RETURN;\r\n        WHEN OTHERS THEN\r\n          IF SQLCODE = -6502 THEN\r\n            RETURN;\r\n          ELSE\r\n            RAISE;\r\n          END IF;\r\n      END;\r\n    END LOOP;\r\n  END PRINT_CLOB;\r\n\r\n  -- Function to beautifier the output JSON\r\n  FUNCTION FORMAT_JSON (\r\n    P_CLOB IN CLOB\r\n  ) RETURN CLOB IS\r\n\r\n    L_BLOB BLOB;\r\n    L_CLOB CLOB;\r\n\r\n    FUNCTION CLOB_TO_BLOB (\r\n      P_CLOB CLOB\r\n    ) RETURN BLOB IS\r\n\r\n      L_BLOB BLOB;\r\n      O1     INTEGER := 1;\r\n      O2     INTEGER := 1;\r\n      C      INTEGER := 0;\r\n      W      INTEGER := 0;\r\n    BEGIN\r\n      SYS.DBMS_LOB.CREATETEMPORARY(L_BLOB, TRUE);\r\n      SYS.DBMS_LOB.CONVERTTOBLOB(L_BLOB, P_CLOB, LENGTH(P_CLOB), O1, O2, 0, C, W);\r\n\r\n      RETURN L_BLOB;\r\n    END CLOB_TO_BLOB;\r\n\r\n  BEGIN\r\n    L_BLOB := CLOB_TO_BLOB(P_CLOB);\r\n    SELECT JSON_SERIALIZE(L_BLOB RETURNING CLOB PRETTY)\r\n      INTO L_CLOB\r\n      FROM DUAL;\r\n\r\n    RETURN L_CLOB;\r\n  END FORMAT_JSON;\r\n\r\n  -- Main function\r\n  PROCEDURE ORDS_RUN (\r\n    IN_SQL    IN VARCHAR2,\r\n    IN_OFFSET IN INTEGER DEFAULT NULL,\r\n    IN_SIZE   IN INTEGER DEFAULT NULL\r\n  ) AS\r\n\r\n    V_SQL          VARCHAR2(1000);\r\n    L_CUR          SYS_REFCURSOR;\r\n\r\n    -- Convert to XML\r\n    L_XML          XMLTYPE;\r\n    L_CTX          DBMS_XMLGEN.CTXHANDLE;\r\n\r\n    -- Pagination Control\r\n    L_FETCH_OFFSET NUMBER;\r\n    L_COUNT        NUMBER;\r\n    L_DEF_OFFSET   CONSTANT NUMBER := 0;\r\n    L_DEF_SIZE     CONSTANT NUMBER := 25;\r\n    L_PAGE_OFFSET  NUMBER;\r\n    L_PAGE_SIZE    NUMBER;\r\n    L_HASMORE      BOOLEAN;\r\n\r\n    -- URL\r\n    V_PREV_URL     VARCHAR2(1000);\r\n    V_NEXT_URL     VARCHAR2(1000);\r\n\r\n    PROCEDURE PREPARE_URL AS\r\n      V_URL VARCHAR2(1000);\r\n    BEGIN\r\n\r\n      -- Prepare URL\r\n      V_URL := OWA_UTIL.GET_CGI_ENV('X-APEX-BASE') || OWA_UTIL.GET_CGI_ENV('X-APEX-PATH');\r\n      V_URL := REGEXP_REPLACE(V_URL, '[&]?page_offset=[0-9]+', '');\r\n      V_URL := REGEXP_REPLACE(V_URL, '[&]?page_size=[0-9]+', '');\r\n      V_URL := REGEXP_REPLACE(V_URL, '\\?$');\r\n      V_URL := REGEXP_REPLACE(V_URL, '\\?&', '?');\r\n      IF INSTR(V_URL, '?') = 0 THEN\r\n        V_URL := V_URL || '?';\r\n      ELSE\r\n        V_URL := V_URL || '&';\r\n      END IF;\r\n\r\n      V_PREV_URL := V_URL;\r\n      V_NEXT_URL := V_URL;\r\n\r\n      -- If has more.\r\n      IF L_HASMORE THEN\r\n        V_NEXT_URL := V_NEXT_URL || 'page_offset=' || ( L_PAGE_OFFSET + 1 ) || '&';\r\n      END IF;\r\n\r\n      IF L_PAGE_OFFSET > 1 THEN\r\n        V_PREV_URL := V_PREV_URL || 'page_offset=' || ( L_PAGE_OFFSET - 1 ) || '&';\r\n      END IF;\r\n\r\n      -- IF L_PAGE_SIZE != L_DEF_SIZE THEN\r\n      IF IN_SIZE IS NOT NULL THEN\r\n        V_PREV_URL := V_PREV_URL || 'page_size=' || L_PAGE_SIZE;\r\n        V_NEXT_URL := V_NEXT_URL || 'page_size=' || L_PAGE_SIZE;\r\n      END IF;\r\n\r\n      -- Clean extra '&'\r\n      V_PREV_URL := REGEXP_REPLACE(V_PREV_URL, '&$');\r\n      V_PREV_URL := REGEXP_REPLACE(V_PREV_URL, '\\?$');\r\n      V_NEXT_URL := REGEXP_REPLACE(V_NEXT_URL, '&$');\r\n      V_NEXT_URL := REGEXP_REPLACE(V_NEXT_URL, '\\?$');\r\n    END PREPARE_URL;\r\n\r\n  BEGIN\r\n\r\n    -- Check for inconsistent inputs.\r\n    IF IN_SIZE < 0 OR IN_OFFSET < 0 THEN\r\n      RAISE INVALID_NUMBER;\r\n    END IF;\r\n\r\n    IF IN_SIZE IS NOT NULL THEN\r\n      L_PAGE_SIZE := IN_SIZE;\r\n    ELSE\r\n      L_PAGE_SIZE := L_DEF_SIZE;\r\n    END IF;\r\n\r\n    IF IN_OFFSET IS NOT NULL THEN\r\n      L_PAGE_OFFSET := IN_OFFSET;\r\n    ELSE\r\n      L_PAGE_OFFSET := L_DEF_OFFSET;\r\n    END IF;\r\n\r\n    -- Check for inconsistent inputs.\r\n    IF L_PAGE_SIZE < 0 OR L_PAGE_OFFSET < 0 THEN\r\n      RAISE INVALID_NUMBER;\r\n    END IF;\r\n\r\n    -- Fetch Offset\r\n    L_FETCH_OFFSET := ( L_PAGE_OFFSET * L_PAGE_SIZE );\r\n\r\n    -- Count SQL\r\n    V_SQL := q'[\r\n    SELECT COUNT(*)\r\n    FROM (\r\n          ]' || IN_SQL || q'[\r\n          OFFSET :L_FETCH_OFFSET ROWS FETCH NEXT :L_PAGE_SIZE + 1 ROWS ONLY\r\n         )\r\n    ]';\r\n\r\n    -- Check total rows.\r\n    EXECUTE IMMEDIATE V_SQL\r\n      INTO L_COUNT\r\n      USING L_FETCH_OFFSET, L_PAGE_SIZE;\r\n\r\n    -- Define if has more.\r\n    IF L_COUNT < L_PAGE_SIZE + 1 THEN\r\n      L_HASMORE := FALSE;\r\n    ELSE\r\n      L_HASMORE := TRUE;\r\n    END IF;\r\n\r\n    -- Original SQL\r\n    V_SQL := IN_SQL || 'OFFSET :L_FETCH_OFFSET ROWS FETCH NEXT :L_PAGE_SIZE ROWS ONLY';\r\n    OPEN L_CUR FOR V_SQL\r\n      USING L_FETCH_OFFSET, L_PAGE_SIZE;\r\n\r\n    -- The reason the output is converted to XML is to handle null attributes.\r\n    \/* converts the results of a SQL query to a canonical XML format *\/\r\n    L_CTX := DBMS_XMLGEN.NEWCONTEXT(L_CUR); \r\n    \/* Sets NULL handling options *\/\r\n    DBMS_XMLGEN.SETNULLHANDLING(L_CTX, DBMS_XMLGEN.EMPTY_TAG);\r\n    -- DBMS_XMLQUERY.SETTAGCASE(L_CTX, 1);\r\n    L_XML := DBMS_XMLGEN.GETXMLTYPE(L_CTX);\r\n    DBMS_XMLGEN.CLOSECONTEXT(L_CTX);\r\n\r\n    -- If has more.\r\n    IF L_HASMORE THEN\r\n      L_COUNT := L_COUNT - 1;\r\n    END IF;\r\n\r\n    -- Prepare previous and next pages URL\r\n    PREPARE_URL;\r\n\r\n    -- Print Output\r\n    OWA_UTIL.MIME_HEADER('application\/json');\r\n    APEX_JSON.INITIALIZE_CLOB_OUTPUT;\r\n    -- Main Open = {\r\n    APEX_JSON.OPEN_OBJECT;\r\n    -- APEX_JSON.WRITE('items', L_CUR);\r\n    APEX_JSON.WRITE('items', L_XML, TRUE);\r\n    -- Metadata Open => metadata: {\r\n    APEX_JSON.OPEN_OBJECT('metadata');\r\n    APEX_JSON.WRITE('offset', L_PAGE_OFFSET);\r\n    APEX_JSON.WRITE('limit', L_PAGE_SIZE);\r\n    APEX_JSON.WRITE('count', L_COUNT);\r\n    APEX_JSON.WRITE('hasMore', L_HASMORE);\r\n    -- Links Open => links: {\r\n    APEX_JSON.OPEN_OBJECT('links');\r\n    IF L_PAGE_OFFSET > 0 THEN\r\n      APEX_JSON.WRITE('prev_page', V_PREV_URL);\r\n    ELSE\r\n      APEX_JSON.WRITE('prev_page', '', TRUE);\r\n    END IF;\r\n\r\n    IF L_HASMORE THEN\r\n      APEX_JSON.WRITE('next_page', V_NEXT_URL);\r\n    ELSE\r\n      APEX_JSON.WRITE('next_page', '', TRUE);\r\n    END IF;\r\n\r\n    -- Links Close = }\r\n    APEX_JSON.CLOSE_OBJECT;\r\n    -- Metadata Close = }\r\n    APEX_JSON.CLOSE_OBJECT;\r\n    -- Main Close = }\r\n    APEX_JSON.CLOSE_OBJECT;\r\n    PRINT_CLOB(FORMAT_JSON(APEX_JSON.GET_CLOB_OUTPUT));\r\n  END ORDS_RUN;\r\n\r\nEND X_ORDS_PROCESS;\r\n\/<\/pre>\n
PS:<\/p>\n
    \n
  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.<\/li>\n
  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.<\/li>\n<\/ol>\n
     <\/p>\n
    Next, create and enable the REST API endpoint. Example:<\/p>\n
    BEGIN\r\n  ORDS.ENABLE_SCHEMA(\r\n      p_enabled             => TRUE,\r\n      p_schema              => USER,\r\n      p_url_mapping_type    => 'BASE_PATH',\r\n      p_url_mapping_pattern => 'test',\r\n      p_auto_rest_auth      => TRUE);\r\n    \r\n  ORDS.DEFINE_MODULE(\r\n      p_module_name    => 'test',\r\n      p_base_path      => '\/test\/',\r\n      p_items_per_page => 25,\r\n      p_status         => 'PUBLISHED',\r\n      p_comments       => NULL);\r\n\r\n  ORDS.DEFINE_TEMPLATE(\r\n      p_module_name    => 'test',\r\n      p_pattern        => 'test',\r\n      p_priority       => 0,\r\n      p_etag_type      => 'HASH',\r\n      p_etag_query     => NULL,\r\n      p_comments       => NULL);\r\n\r\n  ORDS.DEFINE_HANDLER(\r\n      p_module_name    => 'test',\r\n      p_pattern        => 'test',\r\n      p_method         => 'GET',\r\n      p_source_type    => 'plsql\/block',\r\n      p_mimes_allowed  => NULL,\r\n      p_comments       => NULL,\r\n      p_source         => \r\n'DECLARE\r\n  V_SQL VARCHAR2(4000);\r\nBEGIN\r\n  V_SQL := q''(\r\n           SELECT T.*\r\n           FROM   ALL_OBJECTS T\r\n           ORDER BY 1\r\n           )'';\r\n\r\n  X_ORDS_PROCESS.ORDS_RUN(\r\n    IN_SQL          => V_SQL,\r\n    IN_OFFSET       => :page_offset,\r\n    IN_SIZE         => :page_size\r\n  );\r\n\r\nEXCEPTION\r\n  WHEN INVALID_NUMBER THEN\r\n    :string_out := ''Provided page_offset or page_size is invalid.'';\r\n    :status_code := 400;\r\nEND;');\r\n\r\n  ORDS.DEFINE_PARAMETER(\r\n      p_module_name        => 'test',\r\n      p_pattern            => 'test',\r\n      p_method             => 'GET',\r\n      p_name               => 'error',\r\n      p_bind_variable_name => 'string_out',\r\n      p_source_type        => 'RESPONSE',\r\n      p_param_type         => 'STRING',\r\n      p_access_method      => 'OUT',\r\n      p_comments           => 'Return the message if you receive an error.');\r\n\r\n        \r\nCOMMIT;\r\n\r\nEND;\r\n\/<\/pre>\n
    Sample output for 2 entries only:<\/p>\n
    $ curl -s -S --request GET https:\/\/test.oracle.com\/ords\/test\/test\/test?page_size=2\r\n{\r\n  \"items\" :\r\n  [\r\n    {\r\n      \"OWNER\" : \"APEX_220100\",\r\n      \"OBJECT_NAME\" : \"WWV_FLOW_SESSION_SEQ\",\r\n      \"SUBOBJECT_NAME\" : null,\r\n      \"OBJECT_ID\" : 482011,\r\n      \"DATA_OBJECT_ID\" : null,\r\n      \"OBJECT_TYPE\" : \"SEQUENCE\",\r\n      \"CREATED\" : \"22-JUL-22\",\r\n      \"LAST_DDL_TIME\" : \"22-JUL-22\",\r\n      \"TIMESTAMP\" : \"2022-07-22:00:46:07\",\r\n      \"STATUS\" : \"VALID\",\r\n      \"TEMPORARY\" : \"N\",\r\n      \"GENERATED\" : \"N\",\r\n      \"SECONDARY\" : \"N\",\r\n      \"NAMESPACE\" : 1,\r\n      \"EDITION_NAME\" : null,\r\n      \"SHARING\" : \"METADATA LINK\",\r\n      \"EDITIONABLE\" : null,\r\n      \"ORACLE_MAINTAINED\" : \"Y\",\r\n      \"APPLICATION\" : \"N\",\r\n      \"DEFAULT_COLLATION\" : null,\r\n      \"DUPLICATED\" : \"N\",\r\n      \"SHARDED\" : \"N\",\r\n      \"CREATED_APPID\" : 43,\r\n      \"CREATED_VSNID\" : 48,\r\n      \"MODIFIED_APPID\" : 43,\r\n      \"MODIFIED_VSNID\" : 48\r\n    },\r\n    {\r\n      \"OWNER\" : \"APEX_220100\",\r\n      \"OBJECT_NAME\" : \"WWV_SEQ\",\r\n      \"SUBOBJECT_NAME\" : null,\r\n      \"OBJECT_ID\" : 482012,\r\n      \"DATA_OBJECT_ID\" : null,\r\n      \"OBJECT_TYPE\" : \"SEQUENCE\",\r\n      \"CREATED\" : \"22-JUL-22\",\r\n      \"LAST_DDL_TIME\" : \"22-JUL-22\",\r\n      \"TIMESTAMP\" : \"2022-07-22:00:46:07\",\r\n      \"STATUS\" : \"VALID\",\r\n      \"TEMPORARY\" : \"N\",\r\n      \"GENERATED\" : \"N\",\r\n      \"SECONDARY\" : \"N\",\r\n      \"NAMESPACE\" : 1,\r\n      \"EDITION_NAME\" : null,\r\n      \"SHARING\" : \"METADATA LINK\",\r\n      \"EDITIONABLE\" : null,\r\n      \"ORACLE_MAINTAINED\" : \"Y\",\r\n      \"APPLICATION\" : \"N\",\r\n      \"DEFAULT_COLLATION\" : null,\r\n      \"DUPLICATED\" : \"N\",\r\n      \"SHARDED\" : \"N\",\r\n      \"CREATED_APPID\" : 43,\r\n      \"CREATED_VSNID\" : 48,\r\n      \"MODIFIED_APPID\" : 43,\r\n      \"MODIFIED_VSNID\" : 48\r\n    }\r\n  ],\r\n  \"metadata\" :\r\n  {\r\n    \"offset\" : 0,\r\n    \"limit\" : 2,\r\n    \"count\" : 2,\r\n    \"hasMore\" : true,\r\n    \"links\" :\r\n    {\r\n      \"prev_page\" : null,\r\n      \"next_page\" : \"https:\/\/test.oracle.com\/ords\/test\/test\/test?page_offset=1&page_size=2\"\r\n    }\r\n  }\r\n}<\/pre>\n
    If you want to bring all rows at once, just add ?page_size=100000<\/code> or to a big enough number on your call.<\/p>\nHave you enjoyed? Please leave a comment or give a ?!<\/b>\n
    Continue reading<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[15],"tags":[],"class_list":["post-14925","post","type-post","status-publish","format-standard","hentry","category-database-en","item-wrap"],"yoast_head":"\nORDS pagination with PL\/SQL source type - DBA - Rodrigo Jorge - Oracle Tips and Guides<\/title>\n<meta name=\"description\" content=\"How to manually implement the ORDS pagination using PL\/SQL handler as source type.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dev.dbarj.com.br\/en\/2022\/11\/ords-pagination-with-pl-sql-source-type\/\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DBA RJ\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2022\\\/11\\\/ords-pagination-with-pl-sql-source-type\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2022\\\/11\\\/ords-pagination-with-pl-sql-source-type\\\/\"},\"author\":{\"name\":\"DBA RJ\",\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/#\\\/schema\\\/person\\\/28a44ca3a6633fe4156ad1ea209d40a9\"},\"headline\":\"ORDS pagination with PL\\\/SQL source type\",\"datePublished\":\"2022-11-30T12:53:36+00:00\",\"dateModified\":\"2022-11-30T13:27:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2022\\\/11\\\/ords-pagination-with-pl-sql-source-type\\\/\"},\"wordCount\":293,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/#\\\/schema\\\/person\\\/28a44ca3a6633fe4156ad1ea209d40a9\"},\"image\":{\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2022\\\/11\\\/ords-pagination-with-pl-sql-source-type\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/dev.dbarj.com.br\\\/wp-content\\\/uploads\\\/2022\\\/11\\\/img_638759100aa05.png\",\"articleSection\":[\"Oracle Database General\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2022\\\/11\\\/ords-pagination-with-pl-sql-source-type\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2022\\\/11\\\/ords-pagination-with-pl-sql-source-type\\\/\",\"url\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2022\\\/11\\\/ords-pagination-with-pl-sql-source-type\\\/\",\"name\":\"ORDS pagination with PL\\\/SQL source type - DBA - Rodrigo Jorge - Oracle Tips and Guides\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2022\\\/11\\\/ords-pagination-with-pl-sql-source-type\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2022\\\/11\\\/ords-pagination-with-pl-sql-source-type\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/dev.dbarj.com.br\\\/wp-content\\\/uploads\\\/2022\\\/11\\\/img_638759100aa05.png\",\"datePublished\":\"2022-11-30T12:53:36+00:00\",\"dateModified\":\"2022-11-30T13:27:31+00:00\",\"description\":\"How to manually implement the ORDS pagination using PL\\\/SQL handler as source type.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2022\\\/11\\\/ords-pagination-with-pl-sql-source-type\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2022\\\/11\\\/ords-pagination-with-pl-sql-source-type\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2022\\\/11\\\/ords-pagination-with-pl-sql-source-type\\\/#primaryimage\",\"url\":\"https:\\\/\\\/dev.dbarj.com.br\\\/wp-content\\\/uploads\\\/2022\\\/11\\\/img_638759100aa05.png\",\"contentUrl\":\"https:\\\/\\\/dev.dbarj.com.br\\\/wp-content\\\/uploads\\\/2022\\\/11\\\/img_638759100aa05.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/2022\\\/11\\\/ords-pagination-with-pl-sql-source-type\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"ORDS pagination with PL\\\/SQL source type\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/\",\"name\":\"DBA - Rodrigo Jorge - Oracle Tips and Guides\",\"description\":\"Blog about Databases, Security and High Availability\",\"publisher\":{\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/#\\\/schema\\\/person\\\/28a44ca3a6633fe4156ad1ea209d40a9\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/en\\\/#\\\/schema\\\/person\\\/28a44ca3a6633fe4156ad1ea209d40a9\",\"name\":\"DBA RJ\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/RodrigoJorgePOUG19.png\",\"url\":\"https:\\\/\\\/dev.dbarj.com.br\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/RodrigoJorgePOUG19.png\",\"contentUrl\":\"https:\\\/\\\/dev.dbarj.com.br\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/RodrigoJorgePOUG19.png\",\"width\":712,\"height\":712,\"caption\":\"DBA RJ\"},\"logo\":{\"@id\":\"https:\\\/\\\/dev.dbarj.com.br\\\/wp-content\\\/uploads\\\/2019\\\/09\\\/RodrigoJorgePOUG19.png\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"ORDS pagination with PL\/SQL source type - DBA - Rodrigo Jorge - Oracle Tips and Guides","description":"How to manually implement the ORDS pagination using PL\/SQL handler as source type.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dev.dbarj.com.br\/en\/2022\/11\/ords-pagination-with-pl-sql-source-type\/","twitter_misc":{"Written by":"DBA RJ","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dev.dbarj.com.br\/en\/2022\/11\/ords-pagination-with-pl-sql-source-type\/#article","isPartOf":{"@id":"https:\/\/dev.dbarj.com.br\/en\/2022\/11\/ords-pagination-with-pl-sql-source-type\/"},"author":{"name":"DBA RJ","@id":"https:\/\/dev.dbarj.com.br\/en\/#\/schema\/person\/28a44ca3a6633fe4156ad1ea209d40a9"},"headline":"ORDS pagination with PL\/SQL source type","datePublished":"2022-11-30T12:53:36+00:00","dateModified":"2022-11-30T13:27:31+00:00","mainEntityOfPage":{"@id":"https:\/\/dev.dbarj.com.br\/en\/2022\/11\/ords-pagination-with-pl-sql-source-type\/"},"wordCount":293,"commentCount":0,"publisher":{"@id":"https:\/\/dev.dbarj.com.br\/en\/#\/schema\/person\/28a44ca3a6633fe4156ad1ea209d40a9"},"image":{"@id":"https:\/\/dev.dbarj.com.br\/en\/2022\/11\/ords-pagination-with-pl-sql-source-type\/#primaryimage"},"thumbnailUrl":"https:\/\/dev.dbarj.com.br\/wp-content\/uploads\/2022\/11\/img_638759100aa05.png","articleSection":["Oracle Database General"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dev.dbarj.com.br\/en\/2022\/11\/ords-pagination-with-pl-sql-source-type\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dev.dbarj.com.br\/en\/2022\/11\/ords-pagination-with-pl-sql-source-type\/","url":"https:\/\/dev.dbarj.com.br\/en\/2022\/11\/ords-pagination-with-pl-sql-source-type\/","name":"ORDS pagination with PL\/SQL source type - DBA - Rodrigo Jorge - Oracle Tips and Guides","isPartOf":{"@id":"https:\/\/dev.dbarj.com.br\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dev.dbarj.com.br\/en\/2022\/11\/ords-pagination-with-pl-sql-source-type\/#primaryimage"},"image":{"@id":"https:\/\/dev.dbarj.com.br\/en\/2022\/11\/ords-pagination-with-pl-sql-source-type\/#primaryimage"},"thumbnailUrl":"https:\/\/dev.dbarj.com.br\/wp-content\/uploads\/2022\/11\/img_638759100aa05.png","datePublished":"2022-11-30T12:53:36+00:00","dateModified":"2022-11-30T13:27:31+00:00","description":"How to manually implement the ORDS pagination using PL\/SQL handler as source type.","breadcrumb":{"@id":"https:\/\/dev.dbarj.com.br\/en\/2022\/11\/ords-pagination-with-pl-sql-source-type\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dev.dbarj.com.br\/en\/2022\/11\/ords-pagination-with-pl-sql-source-type\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dev.dbarj.com.br\/en\/2022\/11\/ords-pagination-with-pl-sql-source-type\/#primaryimage","url":"https:\/\/dev.dbarj.com.br\/wp-content\/uploads\/2022\/11\/img_638759100aa05.png","contentUrl":"https:\/\/dev.dbarj.com.br\/wp-content\/uploads\/2022\/11\/img_638759100aa05.png"},{"@type":"BreadcrumbList","@id":"https:\/\/dev.dbarj.com.br\/en\/2022\/11\/ords-pagination-with-pl-sql-source-type\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dev.dbarj.com.br\/en\/"},{"@type":"ListItem","position":2,"name":"ORDS pagination with PL\/SQL source type"}]},{"@type":"WebSite","@id":"https:\/\/dev.dbarj.com.br\/en\/#website","url":"https:\/\/dev.dbarj.com.br\/en\/","name":"DBA - Rodrigo Jorge - Oracle Tips and Guides","description":"Blog about Databases, Security and High Availability","publisher":{"@id":"https:\/\/dev.dbarj.com.br\/en\/#\/schema\/person\/28a44ca3a6633fe4156ad1ea209d40a9"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dev.dbarj.com.br\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/dev.dbarj.com.br\/en\/#\/schema\/person\/28a44ca3a6633fe4156ad1ea209d40a9","name":"DBA RJ","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dev.dbarj.com.br\/wp-content\/uploads\/2019\/09\/RodrigoJorgePOUG19.png","url":"https:\/\/dev.dbarj.com.br\/wp-content\/uploads\/2019\/09\/RodrigoJorgePOUG19.png","contentUrl":"https:\/\/dev.dbarj.com.br\/wp-content\/uploads\/2019\/09\/RodrigoJorgePOUG19.png","width":712,"height":712,"caption":"DBA RJ"},"logo":{"@id":"https:\/\/dev.dbarj.com.br\/wp-content\/uploads\/2019\/09\/RodrigoJorgePOUG19.png"}}]}},"_links":{"self":[{"href":"https:\/\/dev.dbarj.com.br\/en\/wp-json\/wp\/v2\/posts\/14925","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dev.dbarj.com.br\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dev.dbarj.com.br\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dev.dbarj.com.br\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dev.dbarj.com.br\/en\/wp-json\/wp\/v2\/comments?post=14925"}],"version-history":[{"count":5,"href":"https:\/\/dev.dbarj.com.br\/en\/wp-json\/wp\/v2\/posts\/14925\/revisions"}],"predecessor-version":[{"id":14938,"href":"https:\/\/dev.dbarj.com.br\/en\/wp-json\/wp\/v2\/posts\/14925\/revisions\/14938"}],"wp:attachment":[{"href":"https:\/\/dev.dbarj.com.br\/en\/wp-json\/wp\/v2\/media?parent=14925"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dev.dbarj.com.br\/en\/wp-json\/wp\/v2\/categories?post=14925"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dev.dbarj.com.br\/en\/wp-json\/wp\/v2\/tags?post=14925"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}