oci_bind_by_name

PHP 5, PHP 7, PHP 8, PECL OCI8 >= 1.1.0

oci_bind_by_name - Binds a PHP variable to an Oracle placeholder

Manual

Code Examples

Example #1 Inserting data with oci_bind_by_name

Edit/Test in Sandbox

PHP Version:

Example #2 Binding once for multiple executions

Edit/Test in Sandbox

PHP Version:

Example #3 Binding with a foreach loop

Edit/Test in Sandbox

PHP Version:

Example #4 Binding in a WHERE clause

Edit/Test in Sandbox

PHP Version:

Example #5 Binding with a LIKE clause

Edit/Test in Sandbox

PHP Version:

Example #6 Binding with REGEXP_LIKE

Edit/Test in Sandbox

PHP Version:

Example #7 Binding Multiple Values in an IN Clause

Edit/Test in Sandbox

PHP Version:

Example #8 Binding a ROWID returned by a query

Edit/Test in Sandbox

PHP Version:

Example #9 Binding a ROWID on INSERT

<?php
// This example inserts an id & name, and then updates the salary
// Create the table with:
//   CREATE TABLE mytab (id NUMBER, salary NUMBER, name VARCHAR2(40));
//
// Based on original ROWID example by thies at thieso dot net (980221)
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $m = oci_error();
    trigger_error(htmlentities($m['message']), E_USER_ERROR);
}
$sql = "INSERT INTO mytab (id, name) VALUES(:id_bv, :name_bv)
        RETURNING ROWID INTO :rid";
$ins_stid = oci_parse($conn, $sql);
$rowid = oci_new_descriptor($conn, OCI_D_ROWID);
oci_bind_by_name($ins_stid, ":id_bv",   $id,    10);
oci_bind_by_name($ins_stid, ":name_bv", $name,  32);
oci_bind_by_name($ins_stid, ":rid",     $rowid, -1, OCI_B_ROWID);
$sql = "UPDATE mytab SET salary = :salary WHERE ROWID = :rid";
$upd_stid = oci_parse($conn, $sql);
oci_bind_by_name($upd_stid, ":rid", $rowid, -1, OCI_B_ROWID);
oci_bind_by_name($upd_stid, ":salary", $salary,   32);
// ids and names to insert
$data = array(1111 => "Larry",
              2222 => "Bill",
              3333 => "Jim");
// Salary of each person
$salary = 10000;
// Insert and immediately update each row
foreach ($data as $id => $name) {
    oci_execute($ins_stid);
    oci_execute($upd_stid);
}
$rowid->free();
oci_free_statement($upd_stid);
oci_free_statement($ins_stid);
// Show the new rows
$stid = oci_parse($conn, "SELECT * FROM mytab");
oci_execute($stid);
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
    var_dump($row);
}
oci_free_statement($stid);
oci_close($conn);

Edit/Test in Sandbox

PHP Version:

Example #10 Binding for a PL/SQL stored function

Edit/Test in Sandbox

PHP Version:

Example #11 Binding parameters for a PL/SQL stored procedure

Edit/Test in Sandbox

PHP Version:

Example #12 Binding a CLOB column

<?php
// Before running, create the table:
//     CREATE TABLE mytab (mykey NUMBER, myclob CLOB);
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message']), E_USER_ERROR);
}
$mykey = 12343;  // arbitrary key for this example;
$sql = "INSERT INTO mytab (mykey, myclob)
        VALUES (:mykey, EMPTY_CLOB())
        RETURNING myclob INTO :myclob";
$stid = oci_parse($conn, $sql);
$clob = oci_new_descriptor($conn, OCI_D_LOB);
oci_bind_by_name($stid, ":mykey", $mykey, 5);
oci_bind_by_name($stid, ":myclob", $clob, -1, OCI_B_CLOB);
oci_execute($stid, OCI_DEFAULT);
$clob->save("A very long string");
oci_commit($conn);
// Fetching CLOB data
$query = 'SELECT myclob FROM mytab WHERE mykey = :mykey';
$stid = oci_parse ($conn, $query);
oci_bind_by_name($stid, ":mykey", $mykey, 5);
oci_execute($stid);
print '<table border="1">';
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_LOBS)) {
    print '<tr><td>'.$row['MYCLOB'].'</td></tr>';
    // In a loop, freeing the large variable before the 2nd fetch reduces PHP's peak memory usage
    unset($row);  
}
print '</table>';

Edit/Test in Sandbox

PHP Version:

Example #13 Binding a PL/SQL BOOLEAN

Edit/Test in Sandbox

PHP Version:

oci_bind_by_name

Example #1 Inserting data with oci_bind_by_name

Example #2 Binding once for multiple executions

Example #3 Binding with a foreach loop

Example #4 Binding in a WHERE clause

Example #5 Binding with a LIKE clause

Example #6 Binding with REGEXP_LIKE

Example #7 Binding Multiple Values in an IN Clause

Example #8 Binding a ROWID returned by a query

Example #9 Binding a ROWID on INSERT

Example #10 Binding for a PL/SQL stored function

Example #11 Binding parameters for a PL/SQL stored procedure

Example #12 Binding a CLOB column

Example #13 Binding a PL/SQL BOOLEAN

Function oci_bind_by_name:

Oracle OCI8 Functions

Most used PHP functions