Forum Stats

  • 3,728,222 Users
  • 2,245,576 Discussions
  • 7,853,386 Comments

Discussions

ORDS pagination with PL/SQL source type

Vanja Keglević
Vanja Keglević Member Posts: 30 Green Ribbon

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

thatJeffSmith-Oracle

Answers

  • Olafur T
    Olafur T Member Posts: 237 Bronze Badge
    edited February 2019

    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

    thatJeffSmith-Oracle
Sign In or Register to comment.