Php/docs/function.oci-execute
oci_execute
(PHP 5, PHP 7, PECL OCI8 >= 1.1.0)
oci_execute — Executes a statement
Description
oci_execute
( resource $statement
[, int $mode
= OCI_COMMIT_ON_SUCCESS
] ) : bool
Executes a statement
previously returned
from oci_parse().
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. Prior to PHP 5.3.2 (PECL OCI8 1.4) use
OCI_DEFAULT
which is equivalent toOCI_NO_AUTO_COMMIT
.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 useOCI_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); // use OCI_DEFAULT for PHP <= 5.3.1}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
orDROP
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.