I am using PDO in the following code to run multiple statements in one excetute and fetchall. This works for Microsoft SQL Server but is not working for Oracle.
<?php
$conn = new PDO("odbc:oratest", "SYSTEM", "password");
$result = $conn->prepare("select * from all_objects; select * from all_users;");
$result->execute();
echo "<html><body>";
do
{
echo "<table border=\"0\">";
$row = $result->fetchAll(PDO::FETCH_ASSOC);
if($row && count($row) > 0) {
$keys = array_keys($row[0]);
echo "<tr>";
for($j = 0; $j < count($keys); $j++) {
echo "<td>" . $keys[$j] . "</td>";
}
echo "</tr>";
for($i = 0; $i < count($row); $i++) {
echo "<tr>";
for($j = 0; $j < count($keys); $j++) {
echo "<td>" . $row[$i][$keys[$j]] . "</td>";
}
echo "</tr>";
}
}
echo "</table>";
} while($result->nextRowset());
echo "</body></html>";
?>
The other code using the OCI PHP way is also not working with multiple statements:
$stid = oci_parse($conn, 'select * from all_objects; select * from all_users;');
The oci_parse just fails saying it doesnt understand the semicolon character.
Is there some way to run multiple statements in one batch simultaneously in PHP connecting to latest Oracle XE.
This question is different from the existing MySQL question as this question is about Oracle which is a different driver with different capabilities and limitations.
I'm actually surprised that it works with Microsoft SQL Server. PDO::prepare first parameter is a SQL statement as per the doc, not several ones.
The ability to put several statements in a prepare is a security concern for me. Even if it should be avoided as much as possible, it can happen that the string for the SQL query is dynamically built in PHP. In case of malicious attack and unescaped input, people could add a second statement after your select and delete data in your DB.
I'm also concerned about how PHP would handle the result of two SQL statements on a single call if the tables don't have the same structure. What should the result array look like?
TL;DR : Use multiple execute and fetchall or retrieve all your data in a single SQL query, using UNION ALL for instance.