[RhythmAddict]

Hi everyone...I've got an ASP page that has an oracle stored proc in it. The problem is im paging records and since I have to run the entire query in the SP even though I'm only viewing say 15 at a time. This is coming at a very large costs because of the sheer amount of total records (20k +??)
I've found this article:
http://www.15seconds.com/issue/010308.htm

On writing a stored proc to do record paging on SQL server....Can anyone give me an example or a link with further info on settnig up record paging with Oracle instead? This would get me out of a huge jam...

Thank you in advance

[JMartinez]

What you can do is to actually set your paging within the query it self.

Consider the following.. suppose you want to display at most 4 rows out of the EMP table for each Page you are requesting. You would set up a querystring for the pagesize you want (in this case is 4), and another for the pagenumber.

Then, you can take this values out of ASP, and form your query like this:

select *
  from (
select empno, ename, job, mgr, hiredate, sal, comm, deptno
  from emp
 order by empno asc
       )
 where ceil(rownum/pagesize) = pagenumber

So, in the case I described above, your pagesize will be 4 (i.e. how many rows a given pagenumber will have) and you will get the pagenumber out of the query to know which page to display. This will save a lot of work and memory since it will only fetch those rows you want, without sacrifying memory in either, the server or the client.

P.S.: Make sure to make use of bind variables for pagenumber and pagesize there, so you will in fact be gaining a lot of performance!

[RhythmAddict]

Hi, first off thank you for your quick and helpful reply. This sounds like exactly what I'm looking for however im experiencing some complications applying it. I am sure I must've misunderstood something....
My code will explain:

SQL> ed
Wrote file afiedt.buf

  1  select *
  2    from (
  3  select empno, ename, job, mgr, hiredate, sal, comm, deptno
  4    from emp
  5   order by empno asc
  6         )
  7*  where ceil(rownum/5) = 1
SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30


SQL> 

At this point I am all good, I have paged out 5 records...
Am I doing somethin incorrectly by specifying pagenumber = 2?

SQL> ed
Wrote file afiedt.buf

  1  select *
  2    from (
  3  select empno, ename, job, mgr, hiredate, sal, comm, deptno
  4    from emp
  5   order by empno asc
  6         )
  7*  where ceil(rownum/5) = 2
SQL> /

no rows selected

SQL> 

Thank you for your help - it is appreciated.

[JMartinez]

Yes, thank you for spotting that out! I forgot rownum is fetched AFTER the successfull of a row fetch!

Try this work around instead:

select t.*
  from (
select t.*, rownum rn
  from (
select empno, ename, job, mgr, hiredate, sal, comm, deptno
  from emp
 order by empno asc
       ) t
       ) t
 where ceil(t.rn/5) = 2

[andrewst]

An issue with this approach is that it will always process all rows in the inner query. It will be slower than stopping once the upper page bound has been found:

SQL> create table big as
  2  select object_id, object_name
  3  from all_objects
  4  where rownum <= 10000
  5  /

Table created.

SQL> alter table big add constraint big_pk primary key (object_id);

Table altered.
SQL> select t.*
  2    from (
  3  select t.*, rownum rn
  4    from (
  5  select *
  6    from big
  7   order by object_id asc
  8         ) t
  9         ) t
 10   where ceil(t.rn/5) = 1
 11  /

 OBJECT_ID OBJECT_NAME                            RN
---------- ------------------------------ ----------
       195 DUAL                                    1
       228 SYSTEM_PRIVILEGE_MAP                    2
       231 TABLE_PRIVILEGE_MAP                     3
       234 STMT_AUDIT_OPTION_MAP                   4
       435 STANDARD                                5

 real: 406
SQL> /

 OBJECT_ID OBJECT_NAME                            RN
---------- ------------------------------ ----------
       195 DUAL                                    1
       228 SYSTEM_PRIVILEGE_MAP                    2
       231 TABLE_PRIVILEGE_MAP                     3
       234 STMT_AUDIT_OPTION_MAP                   4
       435 STANDARD                                5

 real: 375

Compare timings with:

SQL> select t.* from
  2  ( select t.*, rownum rn from
  3    ( select * from big
  4      order by object_id
  5    ) t
  6    where rownum <= 5
  7  ) t
  8  where rn >= 1
  9  /

 OBJECT_ID OBJECT_NAME                            RN
---------- ------------------------------ ----------
       195 DUAL                                    1
       228 SYSTEM_PRIVILEGE_MAP                    2
       231 TABLE_PRIVILEGE_MAP                     3
       234 STMT_AUDIT_OPTION_MAP                   4
       435 STANDARD                                5

 real: 125
tandrews@IDEV
SQL> /

 OBJECT_ID OBJECT_NAME                            RN
---------- ------------------------------ ----------
       195 DUAL                                    1
       228 SYSTEM_PRIVILEGE_MAP                    2
       231 TABLE_PRIVILEGE_MAP                     3
       234 STMT_AUDIT_OPTION_MAP                   4
       435 STANDARD                                5

 real: 109

[RhythmAddict]

Hi All.
I am using the method mentioned earlier in this thread and it works very well....(the method from JM)

I do have an issue with one of my SP's however....It seems to be because I am using aliases in my stored proc...Check it out
This is the query in my stored proc:

select t.* from
( select t.*, rownum rn from
 ( select to_char(owr.dCreated,''mm/dd/yy'') as dCreated, owr.id, owr.dCreated,
owr.cWhoCreated, owr.cComment, owr.dCompleted, owr.cWhoCompleted, owr.cBillSys, bs.cDesc 
FROM tbl_superbatch owr INNER JOIN
tbl_owr_BillingSystem bs on owr.cbillsys = bs.cid ' || wh || ' ' || srt || ' 
    ) t
    where rownum <= '||PgTo||'
  ) t
 where rn >= '||PgFrom||'';

It compiles fine, however when I try to run it:

SQL> var r refcursor
SQL> EXECUTE getsuperbatches.getsuperbatches('wHERE owr.cArea = ''MW''','order by owr.id DESC','15',
'1',:r);
BEGIN getsuperbatches.getsuperbatches('wHERE owr.cArea = ''MW''','order by owr.id DESC','15','1',:r)

*
ERROR at line 1:
ORA-00918: column ambiguously defined
ORA-06512: at "OWR.GETSUPERBATCHES", line 14
ORA-06512: at line 1


SQL> 

If I just run the query:

SQL> select t.* from
  2   ( select t.*, rownum rn from
  3     ( select to_char(tbl_superbatch.dCreated,'mm/dd/yy') as dCreated, tbl_superbatch.id, tbl_sup
erbatch.dCreated,
  4    tbl_superbatch.cWhoCreated, tbl_superbatch.cComment, tbl_superbatch.dCompleted, tbl_superbatc
h.cWhoCompleted,   tbl_superbatch.cBillSys, tbl_owr_BillingSystem.cDesc 
  5    FROM tbl_superbatch  INNER JOIN
  6    tbl_owr_BillingSystem on tbl_superbatch.cbillsys = tbl_owr_BillingSystem.cid wHERE tbl_superb
atch.cArea =   'MW'
  7       ) t
  8      where rownum <= '15' 
  9  ) t
 10  where rn >= '1' 
 11  ;
 ( select t.*, rownum rn from
            *
ERROR at line 2:
ORA-00918: column ambiguously defined


SQL> 

Seems like an issue with the t.* 's...Any suggestions guys?

[JMartinez]

select to_char(tbl_superbatch.dCreated,'mm/dd/yy') as dCreated, tbl_superbatch.id, tbl_superbatch.dCreated,.... also, I dont know why are you using '' around a number ?

andrewst, strangely enough, they both reproduce the same stats out of SQL*Plus!

SQL> analyze table big compute statistics
  2  for table
  3  for all indexed columns;

Table analyzed.

SQL> set timing on autotrace traceonly explain
SQL> ed
Wrote file afiedt.buf

  1  select t.*
  2      from (
  3    select t.*, rownum rn
  4      from (
  5    select *
  6      from big
  7     order by object_id asc
  8           ) t
  9           ) t
 10*    where ceil(t.rn/5) = 1
SQL> /
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=48 Card=10000 Bytes=430000)
   1    0   VIEW (Cost=48 Card=10000 Bytes=430000)
   2    1     COUNT
   3    2       VIEW (Cost=48 Card=10000 Bytes=300000)
   4    3         SORT (ORDER BY) (Cost=48 Card=10000 Bytes=210000)
   5    4           TABLE ACCESS (FULL) OF 'BIG' (Cost=4 Card=10000 Bytes=210000)

SQL> ed
Wrote file afiedt.buf

  1  select t.* from
  2    ( select t.*, rownum rn from
  3      ( select * from big
  4        order by object_id
  5      ) t
  6      where rownum <= 5
  7    ) t
  8*   where rn >= 1
SQL> /
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=48 Card=10000 Bytes=430000)
   1    0   VIEW (Cost=48 Card=10000 Bytes=430000)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=48 Card=10000 Bytes=300000)
   4    3         SORT (ORDER BY STOPKEY) (Cost=48 Card=10000 Bytes=210000)
   5    4           TABLE ACCESS (FULL) OF 'BIG' (Cost=4 Card=10000 Bytes=210000)

... but, TKPROF really tell us the truth!

select /* the good*/ t.* from
  ( select t.*, rownum rn from
    ( select * from big
      order by object_id
    ) t
    where rownum <= 5
  ) t
  where rn >= 1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.02       0.02          0         53         12           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.04          0         53         12           5


select /* the bad */ t.*
    from (
  select t.*, rownum rn
    from (
  select *
    from big
   order by object_id asc
         ) t
         ) t
   where ceil(t.rn/5) = 1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.03       0.03          0          0          1           0
Fetch        2      0.08       0.17         47         53         48           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.11       0.20         47         53         49           5

[RhythmAddict]

Hi again.. You're absolutley right about the two dcreated's being my issue....I changed it and I am fine now....As far as the quotes around a number....this is what happens when I do not have quotes around my number:

SQL> EXECUTE getsuperbatches.getsuperbatches('wHERE owr.cArea = ''MW''','order by owr.id DESC','15',
'1',:r);
BEGIN getsuperbatches.getsuperbatches('wHERE owr.cArea = ''MW''','order by owr.id DESC','15','1',:r)

*
ERROR at line 1:
ORA-00936: missing expression
ORA-06512: at "OWR.GETSUPERBATCHES", line 14
ORA-06512: at line 1

my the body..

 AS
 PROCEDURE getsuperbatches
 (

  WH    IN  VARCHAR2,
  SRT    IN  VARCHAR2,
  PGTO  IN  NUMBER,
  PGFROM  IN  NUMBER,
  RC1                     IN  OUT  REF_CUR
 )
 AS
 BEGIN

  OPEN RC1 FOR '
select t.* from
 ( select t.*, rownum rn from
    (select to_char(owr.dCreated,''mm/dd/yy'') as dCreated_sa, 
    owr.id, owr.dCreated,
    owr.cWhoCreated, 
    owr.cComment, 
    owr.dCompleted, 
    owr.cWhoCompleted, 
    owr.cBillSys, 
    bs.cDesc FROM 
    tbl_superbatch owr INNER JOIN
    tbl_owr_BillingSystem bs on owr.cbillsys = bs.cid 
    '|| wh ||' '|| srt ||'
 ) t
    where rownum <= ||PgTo||
  ) t
  where rn >= ||PgFrom||';
END;


END;

More than happy to take any advice on improving this....Also, please excuse my ignorance here as I am very new to PL/SQL but...What is the diff. between a normal variable and a bind variable...Can i utilize a bind variable here to improve performance as was suggested earlier in this thread?
Thank to you to both of you this thread has been a large help improving my overall application performance dramatically....!

[JMartinez]

It is because this two lines

   where rownum <= ||PgTo||
 ...
  where rn >= ||PgFrom||';

If you change them to

   where rownum <= ' || PgTo || ' 
 ...
  where rn >= ' || PgFrom;

in your code, they should work. (Avoid sending the parameters as strings -- with '' around them -- as well, and if this being executed from ASP, you shouldn't be hardcoding these, use the Command and Parameters objects, they are for this kind of stuff -- bind).

Well, just to show you off how you would use Bind variables in the package of yours on the rownum stop keys, if you change your code to:

create or replace package body getsuperbatches
AS
 PROCEDURE getsuperbatches
 (

  WH    IN  VARCHAR2,
  SRT    IN  VARCHAR2,
  PGTO  IN  NUMBER,
  PGFROM  IN  NUMBER,
  RC1                     IN  OUT  REF_CUR
 )
 AS
 BEGIN

  OPEN RC1 FOR '
select t.* from
 ( select t.*, rownum rn from
    (select to_char(owr.dCreated,''mm/dd/yy'') as dCreated_sa, 
    owr.id, owr.dCreated,
    owr.cWhoCreated, 
    owr.cComment, 
    owr.dCompleted, 
    owr.cWhoCompleted, 
    owr.cBillSys, 
    bs.cDesc FROM 
    tbl_superbatch owr INNER JOIN
    tbl_owr_BillingSystem bs on owr.cbillsys = bs.cid 
    '|| wh ||' '|| srt ||'
 ) t
    where rownum <= :1
  ) t
  where rn >= :2'
  using PgTo, PgFrom;
END;
END;

It would have used it if the WHERE (wh) construct and the ORDER BY (str) would not be there. But since they are there, and you are actually adding them from the procedure as a paremeter, it will blow up the use of Bind variables here at all. You should be thinking about changing that to a more 'elegant' and efficient way therefore.

[RhythmAddict]

Hey JM. Thanks again for all your help.
Just out of curiousity, what would be your suggestion for a more elegant solution on having the where and order by clauses in my SP?

Also, hypothetically, if I were using bind variables - could I not pass one IN to my SP?

Thanks!

[JMartinez]

The more efficient and elegant solution is to use bind variables. Like you are coding it right now, you are causing Oracle to trash the shared pool each time you execute this query. Every time you send a statement, Oracle must check syntax, semantics, plans, etc.. all of those that can take sometime if you are sending a different statement each time, incurring this in a hard parse. When you use bind variables, Oracle already has this statement in the shared pool, and it doesn't have to make the checks it did when the statement was first parsed. It will only replace those variable predicates with the new ones you are parsing, incurring this in a soft parse. The key is to: parse one, execute many.

[ss659]

One suggestion I have to make your code more logical to others (in case another developer needs to take over your code) is to put the where and order statement in the actual package.

Of course I dont have any idea how many where statements you could use, but a better way to do it would be to have the where statement coded in there, and then put in code to determine what the value should be.

Ex: (taken from your code)

AS
 PROCEDURE getsuperbatches
 (

  P_AREA_IN   IN  VARCHAR2,
  SRT_COL    IN  VARCHAR2,
  PGTO  IN  NUMBER,
  PGFROM  IN  NUMBER,
  RC1                     IN  OUT  REF_CUR
 )
 AS
 BEGIN

  OPEN RC1 FOR '
select t.* from
 ( select t.*, rownum rn from
    (select to_char(owr.dCreated,''mm/dd/yy'') as dCreated_sa, 
    owr.id, owr.dCreated,
    owr.cWhoCreated, 
    owr.cComment, 
    owr.dCompleted, 
    owr.cWhoCompleted, 
    owr.cBillSys, 
    bs.cDesc FROM 
    tbl_superbatch owr INNER JOIN
    tbl_owr_BillingSystem bs on owr.cbillsys = bs.cid 
    where owc.area = p_area_in
    order by srt_col
 ) t
    where rownum <= ||PgTo||
  ) t
  where rn >= ||PgFrom||';
END;
END;

Now this assumes you will always have a "static" where clause, and just change the value of owc.area. You could also define different where clauses based on parameters within the code. This is the key here - Not a good idea to be passing entire where statements to the procedure like that. So heres another example for what its worth.

EX2

PROCEDURE getsuperbatches
 (

  P_AREA_IN   IN  VARCHAR2,
  P_SRT_COL    IN  VARCHAR2,
  PGTO  IN  NUMBER,
  PGFROM  IN  NUMBER,
  RC1                     IN  OUT  REF_CUR
 )
 AS
v_where    varchar2(1000);
v_order_by varchar2(1000);
 BEGIN

-- Handle the where clause based on P_AREA_IN

IF P_AREA_IN = 'VALUE A' then
v_where = ' WHERE ocw.area = 'A';

ELSIF P_AREA_IN = 'VALUE B' then
v_where = ' WHERE table1.col2 = 'B';

ELSE -- Handle all other cases then
v_where = ' WHERE 1 = 1 ';

END IF;

-- Handle order by clause
IF P_SRT_COL = 'ID' then
v_order_by = ' Order by ID ';

ELSIF P_SRT_COL = 'CREATED_BY' then
v_order_by = ' Order by CREATED_BY ' ;

ELSE -- Handle default case
v_order_by = ' Order by 1 ' -- or whatever you want to default to

  OPEN RC1 FOR '
select t.* from
 ( select t.*, rownum rn from
    (select to_char(owr.dCreated,''mm/dd/yy'') as dCreated_sa, 
    owr.id, owr.dCreated,
    owr.cWhoCreated, 
    owr.cComment, 
    owr.dCompleted, 
    owr.cWhoCompleted, 
    owr.cBillSys, 
    bs.cDesc FROM 
    tbl_superbatch owr INNER JOIN
    tbl_owr_BillingSystem bs on owr.cbillsys = bs.cid 
    ||v_where
    ||v_order_by
 ) t
    where rownum <= ||PgTo||
  ) t
  where rn >= ||PgFrom||';
END;
END;

DISCLAIMER : I know this last method does work with ref cursors. I cannot guarantee it will work out of the box, but I have done it with using variable statements like so.

Just some ideas - make of it what you will

[RhythmAddict]

SS - I was actually debating doing that, although I feel like there was some factor preventing me from doing so (or at least making it too difficult due to time constraints) However, I will set that up when this program gets re-written (I feel like that will happen very soon - this program was not written recently at all)

I do have a question for all though - I realize now Im going to have to pass a value back to my ASP - not a problem, but I do have a question regarding it. Basically, I need to see if there is a next record. Simply put; I'm paging 15 rows at a time (this is working well, and speedy now thank you everyone) Okay thats no problem...However, certain queries will have less than 15 results, and certain queries will have considerably more. I need to pass a value back to ASP to see if the next record exists so I can make a "Next" button appear, if "the next record does not exist" then the "next" button will be grayed out....So simply, what is the best/easiest/most efficient way for me to check if the next record exists within the query?

I can grasp this logically, but the subquery I'm using coupled with the use of the ref cursor completley throws off what little I know about PL/SQL syntax
:-/

Thank you for your help in advance!

[WilliamR]

It is dangerous to accept arbitrary SQL strings in a dynamic procedure, unless you are very sure that you can trust the source.

For example,

SQL> CREATE OR REPLACE PROCEDURE get_employees
  2     ( p_order_by VARCHAR2
  3     , p_results_out OUT SYS_REFCURSOR )
  4  AS
  5  BEGIN
  6     OPEN p_results_out FOR '
  7             SELECT e.ename, d.dname
  8             FROM   emp e, dept d
  9             WHERE  d.deptno = e.deptno
 10             AND    d.dname = ''SALES'' '
 11             || p_order_by;
 12* END;
SQL> /

Procedure created.

SQL> exec get_employees('ORDER BY sal', :results)

PL/SQL procedure successfully completed.


ENAME      DNAME
---------- --------------
JAMES      SALES
WARD       SALES
MARTIN     SALES
TURNER     SALES
ALLEN      SALES
BLAKE      SALES

6 rows selected.

So far so good. But what if I call the procedure like this:

SQL> EXEC get_employees -
> ('AND 1=2 UNION SELECT grantee, granted_role FROM dba_role_privs', :results)

PL/SQL procedure successfully completed.


ENAME                          DNAME
------------------------------ ------------------------------
DBA                            DELETE_CATALOG_ROLE
DBA                            EXECUTE_CATALOG_ROLE
DBA                            EXP_FULL_DATABASE
DBA                            GATHER_SYSTEM_STATISTICS
DBA                            IMP_FULL_DATABASE
DBA                            SELECT_CATALOG_ROLE
DBSNMP                         CONNECT
DEVELOPER                      AQ_ADMINISTRATOR_ROLE
DEVELOPER                      AQ_USER_ROLE
DEVELOPER                      EXECUTE_CATALOG_ROLE
DEVELOPER                      GATHER_SYSTEM_STATISTICS
DEVELOPER                      HS_ADMIN_ROLE
DEVELOPER                      OEM_MONITOR
DEVELOPER                      SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE           HS_ADMIN_ROLE
EXP_FULL_DATABASE              EXECUTE_CATALOG_ROLE
EXP_FULL_DATABASE              SELECT_CATALOG_ROLE
IMP_FULL_DATABASE              EXECUTE_CATALOG_ROLE
IMP_FULL_DATABASE              SELECT_CATALOG_ROLE
JAVASYSPRIV                    JAVAUSERPRIV
OEM_MONITOR                    CONNECT
OEM_MONITOR                    RESOURCE
OUTLN                          CONNECT
OUTLN                          RESOURCE
SCOTT                          CONNECT
SCOTT                          RESOURCE
SELECT_CATALOG_ROLE            HS_ADMIN_ROLE
SYS                            AQ_ADMINISTRATOR_ROLE
SYS                            AQ_USER_ROLE
SYS                            CONNECT
SYS                            DBA
SYS                            DELETE_CATALOG_ROLE
SYS                            EJBCLIENT
SYS                            EXECUTE_CATALOG_ROLE
SYS                            EXP_FULL_DATABASE
SYS                            GATHER_SYSTEM_STATISTICS
SYS                            HS_ADMIN_ROLE
SYS                            IMP_FULL_DATABASE
SYS                            JAVADEBUGPRIV
SYS                            JAVAIDPRIV
SYS                            JAVASYSPRIV
SYS                            JAVAUSERPRIV
SYS                            LOGSTDBY_ADMINISTRATOR
SYS                            OEM_MONITOR
SYS                            RECOVERY_CATALOG_OWNER
SYS                            RESOURCE
SYS                            SELECT_CATALOG_ROLE
SYSTEM                         AQ_ADMINISTRATOR_ROLE
SYSTEM                         DBA
SYSTEM                         DEVELOPER
WILLIAMR                       DBA
WILLIAMR                       DEVELOPER

52 rows selected.

SQL>

[RhythmAddict]

Fortunately, this is intranet based so I am not too concerned about security in this regard (at the moment)

Currently, I'm still trying to figure out how to/what is the best way to return a value from my stored proc to inform me if there are more results....Really, I can just run the query I have in the package, and then run the query again adding 1 to the PgTo value. If a record exsits, I'll just pass a value out of the package say, 1..if not pass a 0.

So far I have gotten this far, but it does not work...

 AS


        PROCEDURE GETREQSTEST
        (
  
              WH                               IN              VARCHAR2,
              SRT                              IN              VARCHAR2,
              PgTo                             IN              NUMBER,
              PgFrom                           IN              NUMBER,
              RC1                              IN  OUT         REF_CUR,
              MrRecords                        OUT             VARCHAR2
        )
        AS
        BEGIN
 
                OPEN RC1 FOR 'select t.* from
( select t.*, rownum rn from
 ( select ID, 
 CREQID, 
 CREQNAME, 
 CREQEMAIL, 
 CREGION, 
 CMARKET, 
 CCHANNEL,
 CBILLSYS,
 CACCOUNT,
 CMOBILE, 
 CPRICEPLAN, 
 CPROMOCODE, 
 CDISCOUNTCODE, 
 CFEATURECODE, 
 DBACKDATE,
 IREASON, 
 CREASON, 
 DSUBMIT, 
 DSUBMITTIME,
 DAPPRREJ, 
 DAPPRREJTIME,
 CAPPRNAME,
 CAPPR2ID,
 IAPPRSTATUS,
 IAPPR2STATUS,
 DAPPR2REJ, 
 DAPPR2REJTIME,
 CISGID,
 IRESUBMIT, 
 DISGPROCESSED, 
 IISGREASON, 
 CAPPRCOMMENTS,
 IBATCH, 
 IFINRVWREQUIRED,
 IFINSTATUS,DFINANCE,
 CFEATURECODE2,
 CFEATURECODE3,
 CFEATURECODE4,
 CFEATURECODE5,            
 CBILLACCT, 
 CBGSA
  from tbl_OpenWindowRequests  '||wh||' '||srt||'
    ) t
    where rownum <= '||PgTo||'  
  ) t
 where rn >= '||PgFrom||'';  

  
Select count (*) as Cnt from (
select t.* from
( select t.*, rownum rn from
 ( select ID, 
 CREQID, 
 CREQNAME, 
 CREQEMAIL, 
 CREGION, 
 CMARKET, 
 CCHANNEL,
 CBILLSYS,
 CACCOUNT,
 CMOBILE, 
 CPRICEPLAN, 
 CPROMOCODE, 
 CDISCOUNTCODE, 
 CFEATURECODE, 
 DBACKDATE,
 IREASON, 
 CREASON, 
 DSUBMIT, 
 DSUBMITTIME,
 DAPPRREJ, 
 DAPPRREJTIME,
 CAPPRNAME,
 CAPPR2ID,
 IAPPRSTATUS,
 IAPPR2STATUS,
 DAPPR2REJ, 
 DAPPR2REJTIME,
 CISGID,
 IRESUBMIT, 
 DISGPROCESSED, 
 IISGREASON, 
 CAPPRCOMMENTS,
 IBATCH, 
 IFINRVWREQUIRED,
 IFINSTATUS,DFINANCE,
 CFEATURECODE2,
 CFEATURECODE3,
 CFEATURECODE4,
 CFEATURECODE5,            
 CBILLACCT, 
 CBGSA
  from tbl_OpenWindowRequests  '||wh||' '||srt||'
    ) t
    where rownum <= '||PgTo||' + 1  
  ) t
 where rn >= '||PgFrom||' 

);
Returning Cnt Into MrRecords;
 
END;

END;

Could anyone help me on this?