Pagination with PHP & ORACLE server side - not working

mensasi

This is more info than question

I had problem with pagination on ORACLE db. It worked only on first page on next pages controler returns result witn no rows.

I examine issue and find bug.

in file OracleQuery.php you have to change row with select query from

        $sql = '
            select *
            from ('.$sql.')
            where rownum > '.$this->_oracle_offset .' AND rownum <= '.($this->_oracle_offset+$this->_oracle_limit);

to

        $sql = '
            SELECT * FROM (
                SELECT ROWNUM AS rn, t.* FROM ( ' . $sql . ' ) t 
            ) WHERE rn > ' . $this->_oracle_offset . ' AND rn <= ' .($this->_oracle_offset+$this->_oracle_limit);

On my ORACLE db ROWNUM doesn't work OK with when it is in WHERE section and condition is ROWNUM>1

allan

What version of Oracle are you using? If I recall correctly off the top of my head, we need 12.c or newer for our server-side processing code to use.

Regards,
Allan

mensasi

Hi, I use 19c.

And mentioned solution works.

hapi

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Solution works also for me, many thanks!

In addtion, the documentation should mention, that PHP PDO Driver for Oracle is NOT needed. I had problems to install id on RedHat and realized only after, that it is not needed anymore.

allan

You are correct - we found that the PDO driver for Oracle was a nightmare to install (you might be able to relate!), so instead we used the oci8 functions. Not as easy to program for, but it does work! I’ll add a note to the docs about that.

Regards,
Allan