Pagination in Oracle/PLSQL error

Question

Hey I am trying to add paging to my dynamic sql block in PLSQL but for some reason when I run the test script it errors out:

ORA-00932: inconsistent datatypes: expected - got -

Here is my procedure:

create or replace 
  procedure spm_search_patientmedrecs (
    p_columnsort_in       in varchar2,
    p_column1_in          in varchar2,
    p_column2_in          in varchar2,
    p_column3_in          in varchar2,
    p_column4_in          in varchar2,
    p_ascdesc_in          in varchar2,
    p_return_cur_out      out sys_refcursor
  is
    lv_sql             varchar2(32767);
    lv_startnum        number:= 1;
    lv_incrementby     number:= 20;
  begin
    lv_sql := '';    
        lv_sql := 'select * from (
                   select /*+ first_rows(20) */
                      '||p_column1_in||',
                      '||p_column2_in||',
                      '||p_column3_in||',
                      '||p_column4_in||',
                      row_number() over
                        (order by '||p_columnsort_in||' '||p_ascdesc_in||') rn
                   from membermedicalreconcilationhdr h,
                        membermedicalreconcilationdet d
                   where h.membermedreconciliationhdrskey = 
                         d.membermedreconciliationhdrskey)
                   where rn between :lv_startnum and :lv_incrementby
                   order by rn';

        open p_return_cur_out for lv_sql;
  end spm_search_patientmedrecs;

Here is my test script:

  set serveroutput on
  declare 
    type tempcursor is ref cursor;
    v_cur_result tempcursor;
    p_columnsort_in   varchar2(50);
    p_column1_in      varchar2(50);
    p_column2_in      varchar2(50);
    p_column3_in      varchar2(50);
    p_column4_in      varchar2(50);
    p_ascdesc_in          varchar2(50);
  begin
    spm_search_patientmedrecs
    ('h.PRIMARYMEMBERPLANID',
     'h.PRIMARYMEMBERPLANID',
     'h.ASSIGNEDUSERID',
     'd.MEMBERMEDRECONCILIATIONDETSKEY',
     'd.GENERICNM',
     'ASC',
     v_cur_result
     );
   loop
      fetch v_cur_result into
        p_column1_in,p_column2_in,p_column3_in,p_column4_in;
        dbms_output.put_line('column 1: '||p_column1_in||' column 2: '||p_column2_in||
                             ' column 3: '||p_column3_in||' column 4: '||p_column4_in);
      exit when v_cur_result%notfound;
    end loop;
  end;

The error I posted above doesnt make sense to me, but I've been looking for the cause for awhile. If anyone can point me in the right direction it would be much appreciated, thanks in advance.

Answer 1

create or replace 
  procedure spm_search_patientmedrecs (
    p_columnsort_in       in varchar2,
    p_column1_in          in varchar2,
    p_column2_in          in varchar2,
    p_column3_in          in varchar2,
    p_column4_in          in varchar2,
    p_ascdesc_in          in varchar2,
    p_return_cur_out      out sys_refcursor
  is
    lv_sql             varchar2(32767);
    lv_startnum        number:= 1;
    lv_incrementby     number:= 20;
  begin
        lv_sql := 'select * from (
                   select /*+ first_rows(20) */
                      '||p_column1_in||',
                      '||p_column2_in||',
                      '||p_column3_in||',
                      '||p_column4_in||',
                      row_number() over
                        (order by '||p_columnsort_in||' '||p_ascdesc_in||') rn
                   from membermedicalreconcilationhdr h,
                        membermedicalreconcilationdet d
                   where h.membermedreconciliationhdrskey = 
                         d.membermedreconciliationhdrskey)
                   where rn between :1 and :2
                   order by rn';
        open p_return_cur_out for lv_sql using lv_startnum, lv_incrementby;
  end spm_search_patientmedrecs;

Answer 2

A couple of issues jump out at me.

• The query that you are using to return the cursor returns 5 columns (the 4 you pass in plus the computed rn) while your fetch fetches the data into only 4 variables. You would either need to modify your query to return only 4 columns or modify your test script to fetch the data into 5 variables.
• In your procedure, you have bind variables in your SQL statement but you don't pass in any bind variables when you open the cursor. My guess is that you want something like this

Passing the bind variables with the USING clause

open p_return_cur_out 
     for lv_sql 
   using lv_startnum, lv_incrementby;

There may well be more errors-- if there are, it would be helpful to post the full stack trace including the line number of the error.

A couple of other things to be aware of.

• Unless p_columnsort_in happens to specify a column that is unique, your paging code may well miss rows and/or show rows in multiple pages because the sort order isn't fully specified. If rows 20 and 21 have the same p_columnsort_in value, it would be perfectly legal to sort them one way on the first query and another way on the second query so row 20 might show up on the first and second page and row 21 might not show up anywhere.
• If efficiency is a concern, using rownum will probably end up being more efficient than using the analytic function like this because the optimizer can generally do a better job of optimizing a rownum predicate.