CODEWITHSUNDEEP
phporacle-databaseplsqloracle-sqldeveloper
user3129375
user3129375

Reputation: 37

Oracle db pagination difficulties with query, error is : "command not properly ended"

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?

Upvotes: 0

Views: 712

Answers (2)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

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);

Upvotes: 1

eis
eis

Reputation: 53502

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;

Upvotes: 1

Related Questions