|
oci_execute
Executes a statement
Description
bool oci_execute(resource $statement , int $mode = OCI_COMMIT_ON_SUCCESS )
After execution, statements like INSERT will
have data committed to the database by default. For statements
like SELECT , execution performs the logic of the
query. Query results can subsequently be fetched in PHP with
functions like oci_fetch_array.
Each parsed statement may be executed multiple times, saving the
cost of re-parsing. This is commonly used
for INSERT statements when data is bound
with oci_bind_by_name.
Parameters
-
statement
-
A valid OCI statement identifier.
-
mode
-
An optional second parameter can be one of the following constants:
Execution Modes
Constant |
Description |
OCI_COMMIT_ON_SUCCESS |
Automatically commit all outstanding changes for
this connection when the statement has succeeded. This
is the default. |
OCI_DESCRIBE_ONLY |
Make query meta data available to functions
like oci_field_name but do not
create a result set. Any subsequent fetch call such
as oci_fetch_array will
fail. |
OCI_NO_AUTO_COMMIT |
Do not automatically commit changes. |
Using OCI_NO_AUTO_COMMIT mode starts or continues a
transaction. Transactions are automatically rolled back when
the connection is closed, or when the script ends. Explicitly
call oci_commit to commit a transaction,
or oci_rollback to abort it.
When inserting or updating data, using transactions is
recommended for relational data consistency and for performance
reasons.
If OCI_NO_AUTO_COMMIT mode is used for any
statement including queries, and
oci_commit
or oci_rollback is not subsequently
called, then OCI8 will perform a rollback at the end of the
script even if no data was changed. To avoid an unnecessary
rollback, many scripts do not
use OCI_NO_AUTO_COMMIT mode for queries or
PL/SQL. Be careful to ensure the appropriate transactional
consistency for the application when
using oci_execute with different modes in
the same script.
Return Values
Returns true on success or false on failure.
Examples
Example #1 oci_execute for queries
<?php
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
$stid = oci_parse($conn, 'SELECT * FROM employees');
oci_execute($stid);
echo "<table border='1'>\n";
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
echo "<tr>\n";
foreach ($row as $item) {
echo " <td>" . ($item !== null ? htmlentities($item, ENT_QUOTES) : " ") . "</td>\n";
}
echo "</tr>\n";
}
echo "</table>\n";
?>
Example #2 oci_execute without specifying a mode example
<?php
// Before running, create the table:
// CREATE TABLE MYTABLE (col1 NUMBER);
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
$stid = oci_parse($conn, 'INSERT INTO mytab (col1) VALUES (123)');
oci_execute($stid); // The row is committed and immediately visible to other users
?>
Example #3 oci_execute with OCI_NO_AUTO_COMMIT example
<?php
// Before running, create the table:
// CREATE TABLE MYTABLE (col1 NUMBER);
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
$stid = oci_parse($conn, 'INSERT INTO mytab (col1) VALUES (:bv)');
oci_bind_by_name($stid, ':bv', $i, 10);
for ($i = 1; $i <= 5; ++$i) {
oci_execute($stid, OCI_NO_AUTO_COMMIT);
}
oci_commit($conn); // commits all new values: 1, 2, 3, 4, 5
?>
Example #4 oci_execute with different commit modes example
<?php
// Before running, create the table:
// CREATE TABLE MYTABLE (col1 NUMBER);
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
$stid = oci_parse($conn, 'INSERT INTO mytab (col1) VALUES (123)');
oci_execute($stid, OCI_NO_AUTO_COMMIT); // data not committed
$stid = oci_parse($conn, 'INSERT INTO mytab (col1) VALUES (456)');
oci_execute($stid); // commits both 123 and 456 values
?>
Example #5 oci_execute with
OCI_DESCRIBE_ONLY example
<?php
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
$stid = oci_parse($conn, 'SELECT * FROM locations');
oci_execute($s, OCI_DESCRIBE_ONLY);
for ($i = 1; $i <= oci_num_fields($stid); ++$i) {
echo oci_field_name($stid, $i) . "<br>\n";
}
?>
Notes
Note:
Transactions are automatically rolled back when connections are
closed, or when the script ends, whichever is soonest. Explicitly
call oci_commit to commit a transaction.
Any call to oci_execute that uses
OCI_COMMIT_ON_SUCCESS mode explicitly or by
default will commit any previous uncommitted transaction.
Any Oracle DDL statement such as CREATE
or DROP will automatically commit any
uncommitted transaction.
Note:
Because the oci_execute function generally
sends the statement to the
database, oci_execute can identify some
statement syntax errors that the lightweight,
local oci_parse function does not.
|