Fetching data from multiple Oracle databases as a single resultset using PHP oci8 with pagination

Question

1. I have 5 different Oracle databases viz FDB1617, FDB1516,FDB1415, FDB1413 and FDB1312.
2. Their database structures are all the same.
3. I would like to get data from all the databases in a single query using PHP and OCI8 with pagination.

For eg:

SELECT ID,ContactName, Phone FROM FDB1617

should fetch data from all the 5 databases;

The following code can be used for pagination

    public function execFetchPage($sql, $action, $firstrow = 1, $numrows = 1, $bindvars = array()) {
        $query = 'SELECT * FROM (SELECT a.*, ROWNUM AS rnum FROM (' . $sql . ') a WHERE ROWNUM <= :sq_last) WHERE :sq_first <= RNUM';
        array_push($bindvars, array(':sq_first', $firstrow, -1));
        array_push($bindvars, array(':sq_last', $firstrow + $numrows - 1, -1));
        $res = $this->execFetchAll($query, $action, $bindvars);
        return($res);
    }

Please help and thanks in advance

Answer 1

You could use database links and UNION to issue multiple queries while returning a single resultset.

CREATE DATABASE LINK link_name CONNECT TO username IDENTIFIED BY password USING connection_string;
...

And then:

SELECT ID, ContactName, Phone FROM table_name
UNION
SELECT ID, ContactName, Phone FROM table_name@link_name
UNION
...