0

I am doing the following query for pagination. When I run 

$s= oci_parse($conn,"select * from TBL_Name order by D_DATE desc");
$r = oci_execute($s);

then no error shown. When I write the following:

$s= oci_parse($conn,"select * from TBL_Name order by D_DATE desc limit $start,$limit");
$r = oci_execute($s);

error is:  oci_execute(): ORA-00933: SQL command not properly ended .

That means problem is with " limit $start,$limit ", But I need this for pagination. LIMIT is not valid in Oracle perhaps. Now how can I write this query?

user3129375
  • 37
  • 7

2 Answers2

1

limit $start,$limit is for MySQL only, it does not help with Oracle or other databases (although as noted by @Charles in the comments, LIMIT with OFFSET is used elsewhere as well).

With Oracle, it is something like

select * from (
select foo.*, ROWNUM rnum
  from  
( select * from TBL_Name order by D_DATE desc  ) foo
 where ROWNUM <= $end) where rnum >= $start;
eis
  • 45,245
  • 11
  • 129
  • 177
  • ROWNUM > is not possible, as it is a pseudocolumn! – Maheswaran Ravisankar Feb 04 '14 at 06:01
  • FWIW, the `LIMIT` clause [is not just for MySQL](http://en.wikipedia.org/wiki/Select_(SQL)#Result_limits). – Charles Feb 04 '14 at 06:04
  • @Charles but `limit $start,$end` is, AFAIK. – eis Feb 04 '14 at 06:06
  • 1
    @eis, [SQLite as well](http://www.sqlite.org/lang_select.html), and that also made me research [if Postgres allows the comma (no)](http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-LIMIT), wherein I have learned that SQL:2008 *finally* added syntax for result limiting... and of course, it's nothing like what any of the existing implementations do. Glorious. – Charles Feb 04 '14 at 06:11
  • 1
    Ok, seems so. They don't seem to like supporting it, "programmers are strongly encouraged to use the form of the LIMIT clause that uses the "OFFSET" keyword and avoid using a LIMIT clause with a comma-separated offset." :) – eis Feb 04 '14 at 06:14
1

ROWNUM is something genrated with your resultset as. pseduocolumn.. so it can be always less than equal to.. so we first generate the rownums for maximum limit and alias using a different name.. and use the alias referring from the outer query.

select * from 
( select a.*, ROWNUM rnum from 
  (select * from TBL_Name order by D_DATE desc ) a 
  where ROWNUM <= $end )
where rnum  >= $start;

PHP code

// Parse a query containing a bind variable.
$stmt = oci_parse($conn, "    select * from  " +
                                          " ( select a.*, ROWNUM rnum from " +
                                          "   (select * from TBL_Name order by D_DATE desc ) a "+
                                          "    where ROWNUM <= :end) "+
                                          "   where rnum  >= :start) ");

// Bind the value into the parsed statement.
oci_bind_by_name($stmt, ":end", $end);
oci_bind_by_name($stmt, ":start", $start);
Maheswaran Ravisankar
  • 17,195
  • 6
  • 42
  • 68