1 Reply Latest reply on Feb 21, 2019 1:07 PM by Olafur T

    ORDS pagination with PL/SQL source type

    Vanja Keglević

      Hello,

       

      is it possible to have pagination supported for a handler which has PL/SQL source type, and has an RESULTSET (ref cursor) output parameter?

       

      Thanks

        • 1. Re: ORDS pagination with PL/SQL source type
          Olafur T

          Sure, you just need to create the pagination in the PL/SQL code.

           

          Example:

          BEGIN
            ORDS.DEFINE_MODULE(
                p_module_name    => '1 test',
                p_base_path      => '/test/',
                p_items_per_page =>  25,
                p_status         => 'PUBLISHED',
                p_comments       => NULL);      
            ORDS.DEFINE_TEMPLATE(
                p_module_name    => '1 test',
                p_pattern        => 'pagination-cursor',
                p_priority       => 0,
                p_etag_type      => 'HASH',
                p_etag_query     => NULL,
                p_comments       => NULL);
            ORDS.DEFINE_HANDLER(
                p_module_name    => '1 test',
                p_pattern        => 'pagination-cursor',
                p_method         => 'GET',
                p_source_type    => 'plsql/block',
                p_items_per_page =>  0,
                p_mimes_allowed  => '',
                p_comments       => NULL,
                p_source         => 
          'declare
            l_cur  sys_refcursor;
            l_page number := (:page * :numrows) - :numrows;
            l_rows number := :numrows;
          begin
            open l_cur for
              select o.owner, o.object_name, o.object_id, o.object_type, o.created, o.last_ddl_time, o.timestamp, o.status
                from all_objects o
               where o.owner = ''ORDS_METADATA''
               order by o.object_type, o.object_name offset l_page rows fetch next l_rows rows only;
            :ret := l_cur;
          end;'
                );
            ORDS.DEFINE_PARAMETER(
                p_module_name        => '1 test',
                p_pattern            => 'pagination-cursor',
                p_method             => 'GET',
                p_name               => 'output',
                p_bind_variable_name => 'ret',
                p_source_type        => 'RESPONSE',
                p_param_type         => 'RESULTSET',
                p_access_method      => 'OUT',
                p_comments           => NULL);      
          
          
            COMMIT; 
          END;
          

           

          then you simply paginate using ..test/pagination-cursor?page=1&numrows=20, ..test/pagination-cursor?page=2&numrows=20 etc..

           

          Regards