OCI_BIND_BY_NAME(3)													       OCI_BIND_BY_NAME(3)

oci_bind_by_name - Binds a PHP variable to an Oracle placeholder

SYNOPSIS

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

DESCRIPTION

	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(3) with the current data size prior to each oci_execute(3)  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(3)
       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 vari-
       able.

	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

	      o $statement
		- A valid OCI8 statement identifer.

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

	      o $variable
		- The PHP variable to be associated with $bv_name

	      o $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(3) is called.

	      o $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(3) function. The $length is not used for abstract datatypes and  should
		be set to -1.  Possible values for $type are:

		     o SQLT_BFILEE or OCI_B_BFILE - for BFILEs;

		     o SQLT_CFILEE or OCI_B_CFILEE - for CFILEs;

		     o SQLT_CLOB or OCI_B_CLOB - for CLOBs;

		     o SQLT_BLOB or OCI_B_BLOB - for BLOBs;

		     o SQLT_RDD or OCI_B_ROWID - for ROWIDs;

		     o SQLT_NTY or OCI_B_NTY - for named datatypes;

		     o SQLT_INT or OCI_B_INT - for integers;

		     o SQLT_CHR - for VARCHARs;

		     o SQLT_BIN or OCI_B_BIN - for RAW columns;

		     o SQLT_LNG - for LONG columns;

		     o SQLT_LBI - for LONG RAW columns;

		     o SQLT_RSET - for cursors created with oci_new_cursor(3);

		     o SQLT_BOL or OCI_B_BOL - for PL/SQL BOOLEANs (Requires OCI8 2.0.7 and Oracle Database 12c)

RETURN VALUES

	Returns TRUE on success or FALSE on failure.

EXAMPLES

       Example #1

	      Inserting data with oci_bind_by_name(3)

	      <?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(3) 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>
";
	      }

	      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 department_id = :didbv ORDER BY last_name';
	      $stid = oci_parse($conn, $sql);
	      $didbv = 60;
	      oci_bind_by_name($stid, ':didbv', $didbv);
	      oci_execute($stid);
	      while (($row = oci_fetch_array($stid, OCI_ASSOC)) != false) {
		  echo $row['LAST_NAME'] ."<br>
";
	      }

	      // Output is
	      //    Austin
	      //    Ernst
	      //    Hunold
	      //    Lorentz
	      //    Pataballa

	      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>
";
	      }
	      // 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>
";
	      }
	      // 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>
";
	      }

	      // 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
";   // 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
";   // 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+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>';

	      ?>

       Example #13

	      Binding a PL/SQL BOOLEAN

	      <?php

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

	      $plsql =
		"begin
		  :output1 := true;
		  :output2 := false;
		 end;";

	      $s = oci_parse($c, $plsql);
	      oci_bind_by_name($s, ':output1', $output1, -1, OCI_B_BOL);
	      oci_bind_by_name($s, ':output2', $output2, -1, OCI_B_BOL);
	      oci_execute($s);
	      var_dump($output1);  // true
	      var_dump($output2);  // false

	      ?>

RETURN VALUES

	Returns TRUE on success or FALSE on failure.

NOTES

       Warning

	       Do not use magic_quotes_gpc or addslashes(3) and oci_bind_by_name(3) simultaneously as no quoting is needed. Any magically  applied
	      quotes  will  be	written  into your database because oci_bind_by_name(3) 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  col-
	      umns. 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]);
	      }
	      ?>

SEE ALSO

       oci_bind_array_by_name(3), oci_parse(3).

PHP Documentation Group 												       OCI_BIND_BY_NAME(3)