[edit] Last updated: Fri, 28 Jun 2013

oci_bind_by_name

(PHP 5, PECL OCI8 >= 1.1.0)

oci_bind_by_name — Binds a PHP variable to an Oracle placeholder

Description

bool oci_bind_by_name ( resource $statement , string $bv_name , mixed &$variable [, int $maxlength = -1 [, int $type = SQLT_CHR ]] )

Binds a PHP variable variable to the Oracle bind variable placeholder bv_name. Binding is important for Oracle database performance and also as a way to avoid SQL Injection security issues.

Binding allows the database to reuse the statement context and caches from previous executions of the statement, even if another user or process originally executed it. Binding reduces SQL Injection concerns because the data associated with a bind variable is never treated as part of the SQL statement. It does not need quoting or escaping.

PHP variables that have been bound can be changed and the statement re-executed without needing to re-parse the statement or re-bind.

In Oracle, bind variables are commonly divided into IN binds for values that are passed into the database, and OUT binds for values that are returned to PHP. A bind variable may be both IN and OUT. Whether a bind variable will be used for input or output is determined at run-time.

You must specify maxlength when using an OUT bind so that PHP allocates enough memory to hold the returned value.

For IN binds it is recommended to set the maxlength length if the statement is re-executed multiple times with different values for the PHP variable. Otherwise Oracle may truncate data to the length of the initial PHP variable value. If you don't know what the maximum length will be, then re-call oci_bind_by_name() with the current data size prior to each oci_execute() call. Binding an unnecessarily large length will have an impact on process memory in the database.

A bind call tells Oracle which memory address to read data from. For IN binds that address needs to contain valid data when oci_execute() is called. This means that the variable bound must remain in scope until execution. If it doesn't, unexpected results or errors such as "ORA-01460: unimplemented or unreasonable conversion requested" may occur. For OUT binds one symptom is no value being set in the PHP variable.

For a statement that is repeatedly executed, binding values that never change may reduce the ability of the Oracle optimizer to choose the best statement execution plan. Long running statements that are rarely re-executed may not benefit from binding. However in both cases, binding might be safer than joining strings into a SQL statement, as this can be a security risk if unfiltered user text is concatenated.

Parameters

statement

A valid OCI8 statement identifer.

bv_name

The colon-prefixed bind variable placeholder used in the statement. The colon is optional in bv_name. Oracle does not use question marks for placeholders.

variable

The PHP variable to be associated with bv_name

maxlength

Sets the maximum length for the data. If you set it to -1, this function will use the current length of variable to set the maximum length. In this case the variable must exist and contain data when oci_bind_by_name() is called.

type

The datatype that Oracle will treat the data as. The default type used is SQLT_CHR. Oracle will convert the data between this type and the database column (or PL/SQL variable type), when possible.

If you need to bind an abstract datatype (LOB/ROWID/BFILE) you need to allocate it first using the oci_new_descriptor() function. The length is not used for abstract datatypes and should be set to -1.

Possible values for type are:

SQLT_BFILEE or OCI_B_BFILE - for BFILEs;
SQLT_CFILEE or OCI_B_CFILEE - for CFILEs;
SQLT_CLOB or OCI_B_CLOB - for CLOBs;
SQLT_BLOB or OCI_B_BLOB - for BLOBs;
SQLT_RDD or OCI_B_ROWID - for ROWIDs;
SQLT_NTY or OCI_B_NTY - for named datatypes;
SQLT_INT or OCI_B_INT - for integers;
SQLT_CHR - for VARCHARs;
SQLT_BIN or OCI_B_BIN - for RAW columns;
SQLT_LNG - for LONG columns;
SQLT_LBI - for LONG RAW columns;
SQLT_RSET - for cursors created with oci_new_cursor().

Return Values

Returns TRUE on success or FALSE on failure.

Examples

Example #1 Inserting data with oci_bind_by_name()


<?php

// Create the table with:
//   CREATE TABLE mytab (id NUMBER, text VARCHAR2(40));

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $m = oci_error();
    trigger_error(htmlentities($m['message']), E_USER_ERROR);
}

$stid = oci_parse($conn,"INSERT INTO mytab (id, text) VALUES(:id_bv, :text_bv)");

$id = 1;
$text = "Data to insert     ";
oci_bind_by_name($stid, ":id_bv", $id);
oci_bind_by_name($stid, ":text_bv", $text);
oci_execute($stid);

// Table now contains: 1, 'Data to insert     '

?>

Example #2 Binding once for multiple executions


<?php

// Create the table with:
//   CREATE TABLE mytab (id NUMBER);

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $m = oci_error();
    trigger_error(htmlentities($m['message']), E_USER_ERROR);
}

$a = array(1,3,5,7,11);  // data to insert

$stid = oci_parse($conn, 'INSERT INTO mytab (id) VALUES (:bv)');
oci_bind_by_name($stid, ':bv', $v, 20);
foreach ($a as $v) {
    $r = oci_execute($stid, OCI_DEFAULT);  // don't auto commit
}
oci_commit($conn); // commit everything at once

// Table contains five rows: 1, 3, 5, 7, 11

oci_free_statement($stid);
oci_close($conn);

?>

Example #3 Binding with a foreach() loop


<?php

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $m = oci_error();
    trigger_error(htmlentities($m['message']), E_USER_ERROR);
}

$sql = 'SELECT * FROM departments WHERE department_name = :dname AND location_id = :loc';
$stid = oci_parse($conn, $sql);

$ba = array(':dname' => 'IT Support', ':loc' => 1700);

foreach ($ba as $key => $val) {

    // oci_bind_by_name($stid, $key, $val) does not work
    // because it binds each placeholder to the same location: $val
    // instead use the actual location of the data: $ba[$key]
    oci_bind_by_name($stid, $key, $ba[$key]);
}

oci_execute($stid);
$row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS);
foreach ($row as $item) {
    print $item."<br>\n";
}

oci_free_statement($stid);
oci_close($conn);

?>

Example #4 Binding in a WHERE clause


<?php

$conn = oci_connect("hr", "hrpwd", "localhost/XE");
if (!$conn) {
    $m = oci_error();
    trigger_error(htmlentities($m['message']), E_USER_ERROR);
}

$sql = 'SELECT last_name FROM employees WHERE employee_id = :eidbv';
$stid = oci_parse($conn, $sql);
$myeid = 101;
oci_bind_by_name($stid, ':eidbv', $myeid);
oci_execute($stid);
$row = oci_fetch_array($stid, OCI_ASSOC);
echo $row['LAST_NAME'] ."<br>\n";

// Output is
//    Kochhar

oci_free_statement($stid);
oci_close($conn);

?>

Example #5 Binding with a LIKE clause


<?php

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $m = oci_error();
    trigger_error(htmlentities($m['message']), E_USER_ERROR);
}

// Find all cities that begin with 'South'
$stid = oci_parse($conn, "SELECT city FROM locations WHERE city LIKE :bv");
$city = 'South%';  // '%' is a wildcard in SQL
oci_bind_by_name($stid, ":bv", $city);
oci_execute($stid);
oci_fetch_all($stid, $res);

foreach ($res['CITY'] as $c) {
    print $c . "<br>\n";
}
// Output is
//   South Brunswick
//   South San Francisco
//   Southlake

oci_free_statement($stid);
oci_close($conn);

?>

Example #6 Binding with REGEXP_LIKE


<?php

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $m = oci_error();
    trigger_error(htmlentities($m['message']), E_USER_ERROR);
}

// Find all cities that contain 'ing'
$stid = oci_parse($conn, "SELECT city FROM locations WHERE REGEXP_LIKE(city, :bv)");
$city = '.*ing.*';
oci_bind_by_name($stid, ":bv", $city);
oci_execute($stid);
oci_fetch_all($stid, $res);

foreach ($res['CITY'] as $c) {
    print $c . "<br>\n";
}
// Output is
//   Beijing
//   Singapore

oci_free_statement($stid);
oci_close($conn);

?>

For a small, fixed number of IN clause conditions, use individual bind variables. Values unknown at run time can be set to NULL. This allows a single statement to be used by all application users, maximizing Oracle DB cache efficiency.

Example #7 Binding Multiple Values in an IN Clause


<?php

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $m = oci_error();
    trigger_error(htmlentities($m['message']), E_USER_ERROR);
}

$sql = 'SELECT last_name FROM employees WHERE employee_id in (:e1, :e2, :e3)';
$stid = oci_parse($conn, $sql);
$mye1 = 103;
$mye2 = 104;
$mye3 = NULL; // pretend we were not given this value
oci_bind_by_name($stid, ':e1', $mye1);
oci_bind_by_name($stid, ':e2', $mye2);
oci_bind_by_name($stid, ':e3', $mye3);
oci_execute($stid);
oci_fetch_all($stid, $res);
foreach ($res['LAST_NAME'] as $name) {
    print $name ."<br>\n";
}

// Output is
//   Ernst
//   Hunold

oci_free_statement($stid);
oci_close($conn);

?>

Example #8 Binding a ROWID returned by a query


<?php

// Create the table with:
//   CREATE TABLE mytab (id NUMBER, salary NUMBER, name VARCHAR2(40));
//   INSERT INTO mytab (id, salary, name) VALUES (1, 100, 'Chris');
//   COMMIT;

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $m = oci_error();
    trigger_error(htmlentities($m['message']), E_USER_ERROR);
}

$stid = oci_parse($conn, 'SELECT ROWID, name FROM mytab WHERE id = :id_bv FOR UPDATE');
$id = 1;
oci_bind_by_name($stid, ':id_bv', $id);
oci_execute($stid);
$row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS);
$rid = $row['ROWID'];
$name = $row['NAME'];

// Change name to upper case & save the changes
$name = strtoupper($name);
$stid = oci_parse($conn, 'UPDATE mytab SET name = :n_bv WHERE ROWID = :r_bv');
oci_bind_by_name($stid, ':n_bv', $name);
oci_bind_by_name($stid, ':r_bv', $rid, -1, OCI_B_ROWID);
oci_execute($stid);

// The table now contains 1, 100, CHRIS

oci_free_statement($stid);
oci_close($conn);

?>

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);

?>

Example #10 Binding for a PL/SQL stored function


<?php

//  Before running the PHP program, create a stored function in
//  SQL*Plus or SQL Developer:
//
//  CREATE OR REPLACE FUNCTION myfunc(p IN NUMBER) RETURN NUMBER AS
//  BEGIN
//      RETURN p * 3;
//  END;

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message']), E_USER_ERROR);
}

$p = 8;

$stid = oci_parse($conn, 'begin :r := myfunc(:p); end;');
oci_bind_by_name($stid, ':p', $p);

// The return value is an OUT bind. The default type will be a string
// type so binding a length 40 means that at most 40 digits will be
// returned.
oci_bind_by_name($stid, ':r', $r, 40);

oci_execute($stid);

print "$r\n";   // prints 24

oci_free_statement($stid);
oci_close($conn);

?>

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


<?php

//  Before running the PHP program, create a stored procedure in
//  SQL*Plus or SQL Developer:
//
//  CREATE OR REPLACE PROCEDURE myproc(p1 IN NUMBER, p2 OUT NUMBER) AS
//  BEGIN
//      p2 := p1 * 2;
//  END;

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message']), E_USER_ERROR);
}

$p1 = 8;

$stid = oci_parse($conn, 'begin myproc(:p1, :p2); end;');
oci_bind_by_name($stid, ':p1', $p1);

// The second procedure parameter is an OUT bind. The default type
// will be a string type so binding a length 40 means that at most 40
// digits will be returned.
oci_bind_by_name($stid, ':p2', $p2, 40);

oci_execute($stid);

print "$p2\n";   // prints 16

oci_free_statement($stid);
oci_close($conn);

?>

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)) {
  $result = $row['MYCLOB']->load();
  print '<tr><td>'.$result.'</td></tr>';
}
print '</table>';

?>

Return Values

Returns TRUE on success or FALSE on failure.

Notes

Warning

Do not use magic_quotes_gpc or addslashes() and oci_bind_by_name() simultaneously as no quoting is needed. Any magically applied quotes will be written into your database because oci_bind_by_name() inserts data verbatim and does not remove quotes or escape characters.

Note:
If you bind a string to a CHAR column in a WHERE clause, remember that Oracle uses blank-padded comparison semantics for CHAR columns. Your PHP variable should be blank padded to the same width as the column for the WHERE clause to succeed.

Note:
The PHP variable argument is a reference. Some forms of loops do not work as expected:

<?php foreach ($myarray as $key => $value) { oci_bind_by_name($stid, $key, $value); } ?>

This binds each key to the location of $value, so all bound variables end up pointing to the last loop iteration's value. Instead use the following:

<?php foreach ($myarray as $key => $value) { oci_bind_by_name($stid, $key, $myarray[$key]); } ?>

Note:
In PHP versions before 5.0.0 you must use ocibindbyname() instead. The old function name can still be used in current versions, however it is deprecated and not recommended.


Sometimes you get the error "ORA-01461: can bind a LONG value only for insert into a LONG column".  This error is highly misleading especially when you have no LONG columns or LONG values.



From my testing it seems this error can be caused when the value of a bound variable exceeds the length allocated.



To avoid this error make sure you specify lengths when binding varchars e.g. 

<?php

oci_bind_by_name($stmt,':string',$string, 256);

?>



And for numerics use the default length (-1) but tell oracle its an integer e.g. 

<?php

oci_bind_by_name($stmt,':num',$num, -1, SQLT_INT);

?>

down

ajitsingh4u at gmail dot com ¶

5 years ago


//Calling Oracle Stored Procedure

//I assume that you have a users table and three columns in users table i.e. id, user, email in oracle

// For example I made connection in constructor, you can modify as per your requirement.

//http://www.devshed.com/c/a/PHP/Understanding-Destructors-in-PHP-5/1/

<?php

class Users{

    private $connection;

    

    public function __construct()

    {

        $this->connection = oci_connect("scott", "tiger", $db); // Establishes a connection to the Oracle server; 

    }



    public function selectUsers($start_index=1, $numbers_of_rows=20)

    {

        $sql ="BEGIN sp_users_select(:p_start_index, :p_numbers_of_rows, :p_cursor, :p_result); END;";

        $stmt = oci_parse($this->connection, $sql);



        //Bind in parameter

        oci_bind_by_name($stmt, ':p_start_index', $start_index, 20);

        oci_bind_by_name($stmt, ':p_numbers_of_rows', $numbers_of_rows, 20);



        //Bind out parameter

        oci_bind_by_name($stmt, ':p_result', $result, 20); // returns 0 if stored procedure succeessfully executed.



        //Bind Cursor

        $p_cursor = oci_new_cursor($this->connection);

        oci_bind_by_name($stmt, ':p_cursor', $p_cursor, -1, OCI_B_CURSOR);



        // Execute Statement

        oci_execute($stmt);

        oci_execute($p_cursor, OCI_DEFAULT);



        oci_fetch_all($p_cursor, $cursor, null, null, OCI_FETCHSTATEMENT_BY_ROW);



        echo $result;

        echo '<br>';

        var_dump($cursor); // $cursor is an associative array so we can use print_r() to print this data.

        // you can return data from this function to use it at your user interface.

    }



    public function deleteUser($id)

    {

        $sql ="BEGIN sp_user_delete(:p_id, :p_result); END;";

        $stmt = oci_parse($this->connection, $sql);



        // bind in and out variables

        oci_bind_by_name($stmt, ':p_id', $id, 20);

        oci_bind_by_name($stmt, ':p_result', $result, 20);



        //Execute the statement

        $check = oci_execute($stmt);



        if($check == true)

        $commit = oci_commit($this->connection);

        else

        $commit = oci_rollback($this->connection);



        return $result;

    }

    

    // You can make function for insert ,update using above two functions



}

?>

down

mail at mkharitonov dot net ¶

1 year ago


If you get error "ORA-22275: invalid LOB locator specified ORA-06512" when using clob try this code:



<?php

$connection = $db->connection;

$statement = oci_parse($connection, 'begin core.clob_test(:p_varchar, :p_clob); end;');



$varchar = 'abc';

oci_bind_by_name($statement, ':p_varchar', $varchar);



$lob = oci_new_descriptor($connection, OCI_D_LOB);

oci_bind_by_name($statement, ':p_clob', $lob, -1, OCI_B_CLOB);



// Differences between original example is method name and methods call order

$lob->writeTemporary('abc abc abc abc abc');

oci_execute($statement);

$lob->close();

?>

down

abiyi2000 at yahoo dot com ¶

1 year ago


I unfortunately spent the whole day trying to make this work as part of OCI bind_by_name insert:





<?php


      if(is_numeric($v2)){


        oci_bind_by_name($stmth, $bvar, $v2,  -1, OCI_B_INT);


      }else{


        $v2 = (string) $v2;


        oci_bind_by_name($stmth, $bvar, $v2, -1, SQLT_CHR);


      }


?>





The string field is always inserting correctly w/o any truncation. The string field is a  varchar2(160) CHAR, but the data used to populate it is 40 chars in length.





The numeric part is of Type Number in the database which is being used to store unix time (10 digit seconds since 1970/01/01.





The problem, the insert was truncating to 9 digits with some bogus value not even related to the input i.e., it's not just a matter of dropping the leftmost or rightmost digit, it'll just insert a 9 digit bogus number.





The only way I was able to resolve this for the numeric field was to set the maxlength to 8 (not 10 which is the number of digits in the input): 





<?php


      if(is_numeric($v2)){


        oci_bind_by_name($stmth, $bvar, $v2,  8, OCI_B_INT);


      }else{


        $v2 = (string) $v2;


        oci_bind_by_name($stmth, $bvar, $v2, -1, SQLT_CHR);


      }


?>





Hopefully you'll see this soon before you expend a lot of time repeating the same problem I had.

down

avenger at php dot net ¶

4 years ago


Dont forget the 5th parameter: $type. It's will slowly your code some times. Eg:



<?php

$sql = "select * from (select * from b xxx) where rownum < :rnum";

$stmt = OCIParse($conn,$sql);

OCIBindByName($stmt, ":rnum", $NUM, -1);

OCIExecute($stmt);

?>



Below code was slow 5~6 time than not use bind value.Change the 3rd line to:



<?php

OCIBindByName($stmt, ":rnum", $NUM, -1, SQLT_INT);

?>



will resloved this problem.



This issue is also in the ADODB DB class(adodb.sf.net), you will be careful for use the SelectLimit method.

down

Anonymous ¶

6 years ago


This is what the old OCI_B_* constants are now called:

(PHP 5.1.6 win32)



OCI_B_NTY - SQLT_NTY

OCI_B_BFILE - SQLT_BFILEE

OCI_B_CFILEE - SQLT_CFILEE

OCI_B_CLOB - SQLT_CLOB

OCI_B_BLOB - SQLT_BLOB

OCI_B_ROWID - SQLT_RDD

OCI_B_CURSOR - SQLT_RSET

OCI_B_BIN - SQLT_BIN

OCI_B_INT - SQLT_INT

OCI_B_NUM - SQLT_NUM

down

Chris Delcamp ¶

6 years ago


This is an example of returning the primary key from an insert so that you can do inserts on other tables with foreign keys based on that value.  The date is just used to provied semi-unique data to be inserted.



$conn = oci_connect("username", "password")

$stmt = oci_parse($conn, "INSERT INTO test (test_msg) values (:data) RETURN test_id INTO :RV");

$data = date("d-M-Y H:i:s");

oci_bind_by_name($stmt, ":RV", $rv, -1, SQLT_INT);

oci_bind_by_name($stmt, ":data", $data, 24);

oci_execute($stmt);

print $rv;

down

hfuecks at nospam dot org ¶

7 years ago


Note that there have been some changes on the constant identifiers and the documentation is currently not entirely accurate.



Running the following script;



<?php

foreach (array_keys(get_defined_constants()) as $const) {

    if ( preg_match('/^OCI_B_/', $const) ) {

        print "$const\n";

    }

}

?>



Under PHP 4.4.0 I get;



OCI_B_SQLT_NTY < renamed to OCI_B_NTY with PHP5

OCI_B_BFILE

OCI_B_CFILEE

OCI_B_CLOB

OCI_B_BLOB

OCI_B_ROWID

OCI_B_CURSOR

OCI_B_BIN



Under PHP 5.0.4 I get;



OCI_B_NTY

OCI_B_BFILE < docs are wrong right now

OCI_B_CFILEE < docs are wrong right now

OCI_B_CLOB

OCI_B_BLOB

OCI_B_ROWID

OCI_B_CURSOR

OCI_B_BIN < it's a mystery

down

-1

passerbyxp at gmail dot com ¶

1 year ago


I unfortunately used the code below for a data synchronize process and ended up in big trouble:



<?php

/*

cache data from other source

*/

$u=oci_parse($con,"UPDATE....WHERE id=:b_id");

oci_bind_by_name($u,":b_id",$id,-1,SQLT_INT);

//bind value...

$i=oci_parse($con,"INSERT INTO...");

oci_bind_by_name($i,":b_id",$id,-1,SQLT_INT);

//bind value using the same variables

while(/*read data from cache*/)

{

oci_execute($u,OCI_NO_AUTO_COMMIT);

if(oci_num_rows($u)<=0) oci_execute($i,OCI_NO_AUTO_COMMIT);

}

oci_commit($con);

?>



Although in fact the $i statement is never executed, after commit, I found that ALL data are copied as the last row of cached source. I then spent the next hour rebuilding the whole table.



So it seems it's not OK to bind the same variables to two (or more) statements, even if just one statement is actually executed.



I guess this is not a good practice to even think of using more than one statement each time, as no one has said it before, but I think this doesn't clearly violate the "be careful for address binding" rule, so it's still worth to be noted here to prevent some headache.

add a note

oci_bind_by_name

Description

Parameters

Return Values

Examples

Return Values

Notes

See Also