4 Replies Latest reply on Nov 8, 2019 12:56 PM by thatJeffSmith-Oracle

    PL/SQL Source type : offset issue

    3573277

      When use offset in a PL/SQL block which is used in ords, not allowed to use variables

       

      declare

      begin

          open :hits for

          select

              cursor( select *

                        from rdd_provider

                        order by prvd_ak

                        offset nvl(:p_from,0) rows fetch next nvl(:p_size,5) rows only ) hits

          from dual;

      end;

       

      p_from and p_size are sent as input parameter

       

      The above fails --

      An error was encountered performing the requested operation:

      Validation Information:

      Wed Nov 06 10:24:29 EST 2019

      Module: provider  Template: prov  Handler: GET

      Syntax Error at line 13, column 25

                        offset p_from rows fetch next p_size rows only ) hits

                               ^^^                                            

      Expected: digits,

      Vendor code 0

       

      ** we are able to directly edit ords_metadata.ords_handler directly and it does works