<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>Binds a PHP variable to an Oracle placeholder</title>
</head>
<body><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="function.oci-bind-array-by-name.html">oci_bind_array_by_name</a></div>
<div class="next" style="text-align: right; float: right;"><a href="function.oci-cancel.html">oci_cancel</a></div>
<div class="up"><a href="ref.oci8.html">OCI8 Functions</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="function.oci-bind-by-name" class="refentry">
<div class="refnamediv">
<h1 class="refname">oci_bind_by_name</h1>
<p class="verinfo">(PHP 5, PECL OCI8 >= 1.1.0)</p><p class="refpurpose"><span class="refname">oci_bind_by_name</span> — <span class="dc-title">Binds a PHP variable to an Oracle placeholder</span></p>
</div>
<div class="refsect1 description" id="refsect1-function.oci-bind-by-name-description">
<h3 class="title">Description</h3>
<div class="methodsynopsis dc-description">
<span class="type">bool</span> <span class="methodname"><strong>oci_bind_by_name</strong></span>
( <span class="methodparam"><span class="type">resource</span> <code class="parameter">$statement</code></span>
, <span class="methodparam"><span class="type">string</span> <code class="parameter">$bv_name</code></span>
, <span class="methodparam"><span class="type"><a href="language.pseudo-types.html#language.types.mixed" class="type mixed">mixed</a></span> <code class="parameter reference">&$variable</code></span>
[, <span class="methodparam"><span class="type">int</span> <code class="parameter">$maxlength</code><span class="initializer"> = -1</span></span>
[, <span class="methodparam"><span class="type">int</span> <code class="parameter">$type</code><span class="initializer"> = SQLT_CHR</span></span>
]] )</div>
<p class="para rdfs-comment">
Binds a PHP variable <em><code class="parameter">variable</code></em> to the Oracle
bind variable placeholder <em><code class="parameter">bv_name</code></em>. Binding
is important for Oracle database performance and also as a way to
avoid SQL Injection security issues.
</p>
<p class="para">
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.
</p>
<p class="para">
PHP variables that have been bound can be changed and the
statement re-executed without needing to re-parse the statement or
re-bind.
</p>
<p class="para">
In Oracle, bind variables are commonly divided
into <em>IN</em> binds for values that are passed into
the database, and <em>OUT</em> binds for values that are
returned to PHP. A bind variable may be
both <em>IN</em> and <em>OUT</em>. Whether a
bind variable will be used for input or output is determined at
run-time.
</p>
<p class="para">
You must specify <em><code class="parameter">maxlength</code></em> when using
an <em>OUT</em> bind so that PHP allocates enough memory
to hold the returned value.
</p>
<p class="para">
For <em>IN</em> binds it is recommended to set
the <em><code class="parameter">maxlength</code></em> 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 <span class="function"><strong>oci_bind_by_name()</strong></span>
with the current data size prior to
each <span class="function"><a href="function.oci-execute.html" class="function">oci_execute()</a></span> call. Binding an
unnecessarily large length will have an impact on process memory
in the database.
</p>
<p class="para">
A bind call tells Oracle which memory address to read data from.
For <em>IN</em> binds that address needs to contain
valid data when <span class="function"><a href="function.oci-execute.html" class="function">oci_execute()</a></span> 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 <em>OUT</em> binds one symptom is no
value being set in the PHP variable.
</p>
<p class="para">
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.
</p>
</div>
<div class="refsect1 parameters" id="refsect1-function.oci-bind-by-name-parameters">
<h3 class="title">Parameters</h3>
<p class="para">
<dl>
<dt>
<span class="term"><em><code class="parameter">statement</code></em></span>
<dd>
<p class="para">
A valid OCI8 statement identifer.
</p>
</dd>
</dt>
<dt>
<span class="term"><em><code class="parameter">bv_name</code></em></span>
<dd>
<p class="para">
The colon-prefixed bind variable placeholder used in the
statement. The colon is optional
in <em><code class="parameter">bv_name</code></em>. Oracle does not use question
marks for placeholders.
</p>
</dd>
</dt>
<dt>
<span class="term"><em><code class="parameter">variable</code></em></span>
<dd>
<p class="para">
The PHP variable to be associated with <em><code class="parameter">bv_name</code></em>
</p>
</dd>
</dt>
<dt>
<span class="term"><em><code class="parameter">maxlength</code></em></span>
<dd>
<p class="para">
Sets the maximum length for the data. If you set it to -1, this
function will use the current length
of <em><code class="parameter">variable</code></em> to set the maximum
length. In this case the <em><code class="parameter">variable</code></em> must
exist and contain data
when <span class="function"><strong>oci_bind_by_name()</strong></span> is called.
</p>
</dd>
</dt>
<dt>
<span class="term"><em><code class="parameter">type</code></em></span>
<dd>
<p class="para">
The datatype that Oracle will treat the data as. The
default <em><code class="parameter">type</code></em> used
is <strong><code>SQLT_CHR</code></strong>. Oracle will convert the data
between this type and the database column (or PL/SQL variable
type), when possible.
</p>
<p class="para">
If you need to bind an abstract datatype (LOB/ROWID/BFILE) you
need to allocate it first using the
<span class="function"><a href="function.oci-new-descriptor.html" class="function">oci_new_descriptor()</a></span> function. The
<em><code class="parameter">length</code></em> is not used for abstract datatypes
and should be set to -1.
</p>
<p class="para">
Possible values for <em><code class="parameter">type</code></em> are:
<ul class="itemizedlist">
<li class="listitem">
<p class="para">
<strong><code>SQLT_BFILEE</code></strong> or <strong><code>OCI_B_BFILE</code></strong>
- for BFILEs;
</p>
</li>
<li class="listitem">
<p class="para">
<strong><code>SQLT_CFILEE</code></strong> or <strong><code>OCI_B_CFILEE</code></strong>
- for CFILEs;
</p>
</li>
<li class="listitem">
<p class="para">
<strong><code>SQLT_CLOB</code></strong> or <strong><code>OCI_B_CLOB</code></strong>
- for CLOBs;
</p>
</li>
<li class="listitem">
<p class="para">
<strong><code>SQLT_BLOB</code></strong> or <strong><code>OCI_B_BLOB</code></strong>
- for BLOBs;
</p>
</li>
<li class="listitem">
<p class="para">
<strong><code>SQLT_RDD</code></strong> or <strong><code>OCI_B_ROWID</code></strong>
- for ROWIDs;
</p>
</li>
<li class="listitem">
<p class="para">
<strong><code>SQLT_NTY</code></strong> or <strong><code>OCI_B_NTY</code></strong>
- for named datatypes;
</p>
</li>
<li class="listitem">
<p class="para">
<strong><code>SQLT_INT</code></strong> or <strong><code>OCI_B_INT</code></strong> - for integers;
</p>
</li>
<li class="listitem">
<p class="para">
<strong><code>SQLT_CHR</code></strong> - for VARCHARs;
</p>
</li>
<li class="listitem">
<p class="para">
<strong><code>SQLT_BIN</code></strong> or <strong><code>OCI_B_BIN</code></strong>
- for RAW columns;
</p>
</li>
<li class="listitem">
<p class="para">
<strong><code>SQLT_LNG</code></strong> - for LONG columns;
</p>
</li>
<li class="listitem">
<p class="para">
<strong><code>SQLT_LBI</code></strong> - for LONG RAW columns;
</p>
</li>
<li class="listitem">
<p class="para">
<strong><code>SQLT_RSET</code></strong> - for cursors created
with <span class="function"><a href="function.oci-new-cursor.html" class="function">oci_new_cursor()</a></span>.
</p>
</li>
</ul>
</p>
</dd>
</dt>
</dl>
</p>
</div>
<div class="refsect1 returnvalues" id="refsect1-function.oci-bind-by-name-returnvalues">
<h3 class="title">Return Values</h3>
<p class="para">
Returns <strong><code>TRUE</code></strong> on success or <strong><code>FALSE</code></strong> on failure.
</p>
</div>
<div class="refsect1 examples" id="refsect1-function.oci-bind-by-name-examples">
<h3 class="title">Examples</h3>
<p class="para">
<div class="example" id="example-1944">
<p><strong>Example #1 Inserting data with <span class="function"><strong>oci_bind_by_name()</strong></span></strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /><br /></span><span style="color: #FF8000">// Create the table with:<br />// CREATE TABLE mytab (id NUMBER, text VARCHAR2(40));<br /><br /></span><span style="color: #0000BB">$conn </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_connect</span><span style="color: #007700">(</span><span style="color: #DD0000">'hr'</span><span style="color: #007700">, </span><span style="color: #DD0000">'welcome'</span><span style="color: #007700">, </span><span style="color: #DD0000">'localhost/XE'</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$conn</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">$m </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_error</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">trigger_error</span><span style="color: #007700">(</span><span style="color: #0000BB">htmlentities</span><span style="color: #007700">(</span><span style="color: #0000BB">$m</span><span style="color: #007700">[</span><span style="color: #DD0000">'message'</span><span style="color: #007700">]), </span><span style="color: #0000BB">E_USER_ERROR</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">,</span><span style="color: #DD0000">"INSERT INTO mytab (id, text) VALUES(:id_bv, :text_bv)"</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$id </span><span style="color: #007700">= </span><span style="color: #0000BB">1</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$text </span><span style="color: #007700">= </span><span style="color: #DD0000">"Data to insert "</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">":id_bv"</span><span style="color: #007700">, </span><span style="color: #0000BB">$id</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">":text_bv"</span><span style="color: #007700">, </span><span style="color: #0000BB">$text</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">// Table now contains: 1, 'Data to insert '<br /><br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-1945">
<p><strong>Example #2 Binding once for multiple executions</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /><br /></span><span style="color: #FF8000">// Create the table with:<br />// CREATE TABLE mytab (id NUMBER);<br /><br /></span><span style="color: #0000BB">$conn </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_connect</span><span style="color: #007700">(</span><span style="color: #DD0000">'hr'</span><span style="color: #007700">, </span><span style="color: #DD0000">'welcome'</span><span style="color: #007700">, </span><span style="color: #DD0000">'localhost/XE'</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$conn</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">$m </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_error</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">trigger_error</span><span style="color: #007700">(</span><span style="color: #0000BB">htmlentities</span><span style="color: #007700">(</span><span style="color: #0000BB">$m</span><span style="color: #007700">[</span><span style="color: #DD0000">'message'</span><span style="color: #007700">]), </span><span style="color: #0000BB">E_USER_ERROR</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #0000BB">$a </span><span style="color: #007700">= array(</span><span style="color: #0000BB">1</span><span style="color: #007700">,</span><span style="color: #0000BB">3</span><span style="color: #007700">,</span><span style="color: #0000BB">5</span><span style="color: #007700">,</span><span style="color: #0000BB">7</span><span style="color: #007700">,</span><span style="color: #0000BB">11</span><span style="color: #007700">); </span><span style="color: #FF8000">// data to insert<br /><br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #DD0000">'INSERT INTO mytab (id) VALUES (:bv)'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">':bv'</span><span style="color: #007700">, </span><span style="color: #0000BB">$v</span><span style="color: #007700">, </span><span style="color: #0000BB">20</span><span style="color: #007700">);<br />foreach (</span><span style="color: #0000BB">$a </span><span style="color: #007700">as </span><span style="color: #0000BB">$v</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">$r </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #0000BB">OCI_DEFAULT</span><span style="color: #007700">); </span><span style="color: #FF8000">// don't auto commit<br /></span><span style="color: #007700">}<br /></span><span style="color: #0000BB">oci_commit</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">); </span><span style="color: #FF8000">// commit everything at once<br /><br />// Table contains five rows: 1, 3, 5, 7, 11<br /><br /></span><span style="color: #0000BB">oci_free_statement</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_close</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-1946">
<p><strong>Example #3 Binding with a <span class="function"><strong>foreach()</strong></span> loop</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /><br />$conn </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_connect</span><span style="color: #007700">(</span><span style="color: #DD0000">'hr'</span><span style="color: #007700">, </span><span style="color: #DD0000">'welcome'</span><span style="color: #007700">, </span><span style="color: #DD0000">'localhost/XE'</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$conn</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">$m </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_error</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">trigger_error</span><span style="color: #007700">(</span><span style="color: #0000BB">htmlentities</span><span style="color: #007700">(</span><span style="color: #0000BB">$m</span><span style="color: #007700">[</span><span style="color: #DD0000">'message'</span><span style="color: #007700">]), </span><span style="color: #0000BB">E_USER_ERROR</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #0000BB">$sql </span><span style="color: #007700">= </span><span style="color: #DD0000">'SELECT * FROM departments WHERE department_name = :dname AND location_id = :loc'</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #0000BB">$sql</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$ba </span><span style="color: #007700">= array(</span><span style="color: #DD0000">':dname' </span><span style="color: #007700">=> </span><span style="color: #DD0000">'IT Support'</span><span style="color: #007700">, </span><span style="color: #DD0000">':loc' </span><span style="color: #007700">=> </span><span style="color: #0000BB">1700</span><span style="color: #007700">);<br /><br />foreach (</span><span style="color: #0000BB">$ba </span><span style="color: #007700">as </span><span style="color: #0000BB">$key </span><span style="color: #007700">=> </span><span style="color: #0000BB">$val</span><span style="color: #007700">) {<br /><br /> </span><span style="color: #FF8000">// oci_bind_by_name($stid, $key, $val) does not work<br /> // because it binds each placeholder to the same location: $val<br /> // instead use the actual location of the data: $ba[$key]<br /> </span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #0000BB">$key</span><span style="color: #007700">, </span><span style="color: #0000BB">$ba</span><span style="color: #007700">[</span><span style="color: #0000BB">$key</span><span style="color: #007700">]);<br />}<br /><br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$row </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_fetch_array</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #0000BB">OCI_ASSOC</span><span style="color: #007700">+</span><span style="color: #0000BB">OCI_RETURN_NULLS</span><span style="color: #007700">);<br />foreach (</span><span style="color: #0000BB">$row </span><span style="color: #007700">as </span><span style="color: #0000BB">$item</span><span style="color: #007700">) {<br /> print </span><span style="color: #0000BB">$item</span><span style="color: #007700">.</span><span style="color: #DD0000">"<br>\n"</span><span style="color: #007700">;<br />}<br /><br /></span><span style="color: #0000BB">oci_free_statement</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_close</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-1947">
<p><strong>Example #4 Binding in a WHERE clause</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /><br />$conn </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_connect</span><span style="color: #007700">(</span><span style="color: #DD0000">"hr"</span><span style="color: #007700">, </span><span style="color: #DD0000">"hrpwd"</span><span style="color: #007700">, </span><span style="color: #DD0000">"localhost/XE"</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$conn</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">$m </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_error</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">trigger_error</span><span style="color: #007700">(</span><span style="color: #0000BB">htmlentities</span><span style="color: #007700">(</span><span style="color: #0000BB">$m</span><span style="color: #007700">[</span><span style="color: #DD0000">'message'</span><span style="color: #007700">]), </span><span style="color: #0000BB">E_USER_ERROR</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #0000BB">$sql </span><span style="color: #007700">= </span><span style="color: #DD0000">'SELECT last_name FROM employees WHERE employee_id = :eidbv'</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #0000BB">$sql</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$myeid </span><span style="color: #007700">= </span><span style="color: #0000BB">101</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">':eidbv'</span><span style="color: #007700">, </span><span style="color: #0000BB">$myeid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$row </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_fetch_array</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #0000BB">OCI_ASSOC</span><span style="color: #007700">);<br />echo </span><span style="color: #0000BB">$row</span><span style="color: #007700">[</span><span style="color: #DD0000">'LAST_NAME'</span><span style="color: #007700">] .</span><span style="color: #DD0000">"<br>\n"</span><span style="color: #007700">;<br /><br /></span><span style="color: #FF8000">// Output is<br />// Kochhar<br /><br /></span><span style="color: #0000BB">oci_free_statement</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_close</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-1948">
<p><strong>Example #5 Binding with a LIKE clause</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /><br />$conn </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_connect</span><span style="color: #007700">(</span><span style="color: #DD0000">'hr'</span><span style="color: #007700">, </span><span style="color: #DD0000">'welcome'</span><span style="color: #007700">, </span><span style="color: #DD0000">'localhost/XE'</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$conn</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">$m </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_error</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">trigger_error</span><span style="color: #007700">(</span><span style="color: #0000BB">htmlentities</span><span style="color: #007700">(</span><span style="color: #0000BB">$m</span><span style="color: #007700">[</span><span style="color: #DD0000">'message'</span><span style="color: #007700">]), </span><span style="color: #0000BB">E_USER_ERROR</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #FF8000">// Find all cities that begin with 'South'<br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #DD0000">"SELECT city FROM locations WHERE city LIKE :bv"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$city </span><span style="color: #007700">= </span><span style="color: #DD0000">'South%'</span><span style="color: #007700">; </span><span style="color: #FF8000">// '%' is a wildcard in SQL<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">":bv"</span><span style="color: #007700">, </span><span style="color: #0000BB">$city</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_fetch_all</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #0000BB">$res</span><span style="color: #007700">);<br /><br />foreach (</span><span style="color: #0000BB">$res</span><span style="color: #007700">[</span><span style="color: #DD0000">'CITY'</span><span style="color: #007700">] as </span><span style="color: #0000BB">$c</span><span style="color: #007700">) {<br /> print </span><span style="color: #0000BB">$c </span><span style="color: #007700">. </span><span style="color: #DD0000">"<br>\n"</span><span style="color: #007700">;<br />}<br /></span><span style="color: #FF8000">// Output is<br />// South Brunswick<br />// South San Francisco<br />// Southlake<br /><br /></span><span style="color: #0000BB">oci_free_statement</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_close</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-1949">
<p><strong>Example #6 Binding with REGEXP_LIKE</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /><br />$conn </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_connect</span><span style="color: #007700">(</span><span style="color: #DD0000">'hr'</span><span style="color: #007700">, </span><span style="color: #DD0000">'welcome'</span><span style="color: #007700">, </span><span style="color: #DD0000">'localhost/XE'</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$conn</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">$m </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_error</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">trigger_error</span><span style="color: #007700">(</span><span style="color: #0000BB">htmlentities</span><span style="color: #007700">(</span><span style="color: #0000BB">$m</span><span style="color: #007700">[</span><span style="color: #DD0000">'message'</span><span style="color: #007700">]), </span><span style="color: #0000BB">E_USER_ERROR</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #FF8000">// Find all cities that contain 'ing'<br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #DD0000">"SELECT city FROM locations WHERE REGEXP_LIKE(city, :bv)"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$city </span><span style="color: #007700">= </span><span style="color: #DD0000">'.*ing.*'</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">":bv"</span><span style="color: #007700">, </span><span style="color: #0000BB">$city</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_fetch_all</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #0000BB">$res</span><span style="color: #007700">);<br /><br />foreach (</span><span style="color: #0000BB">$res</span><span style="color: #007700">[</span><span style="color: #DD0000">'CITY'</span><span style="color: #007700">] as </span><span style="color: #0000BB">$c</span><span style="color: #007700">) {<br /> print </span><span style="color: #0000BB">$c </span><span style="color: #007700">. </span><span style="color: #DD0000">"<br>\n"</span><span style="color: #007700">;<br />}<br /></span><span style="color: #FF8000">// Output is<br />// Beijing<br />// Singapore<br /><br /></span><span style="color: #0000BB">oci_free_statement</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_close</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
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.
</p>
<p class="para">
<div class="example" id="example-1950">
<p><strong>Example #7 Binding Multiple Values in an IN Clause</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /><br />$conn </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_connect</span><span style="color: #007700">(</span><span style="color: #DD0000">'hr'</span><span style="color: #007700">, </span><span style="color: #DD0000">'welcome'</span><span style="color: #007700">, </span><span style="color: #DD0000">'localhost/XE'</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$conn</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">$m </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_error</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">trigger_error</span><span style="color: #007700">(</span><span style="color: #0000BB">htmlentities</span><span style="color: #007700">(</span><span style="color: #0000BB">$m</span><span style="color: #007700">[</span><span style="color: #DD0000">'message'</span><span style="color: #007700">]), </span><span style="color: #0000BB">E_USER_ERROR</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #0000BB">$sql </span><span style="color: #007700">= </span><span style="color: #DD0000">'SELECT last_name FROM employees WHERE employee_id in (:e1, :e2, :e3)'</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #0000BB">$sql</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$mye1 </span><span style="color: #007700">= </span><span style="color: #0000BB">103</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$mye2 </span><span style="color: #007700">= </span><span style="color: #0000BB">104</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$mye3 </span><span style="color: #007700">= </span><span style="color: #0000BB">NULL</span><span style="color: #007700">; </span><span style="color: #FF8000">// pretend we were not given this value<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">':e1'</span><span style="color: #007700">, </span><span style="color: #0000BB">$mye1</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">':e2'</span><span style="color: #007700">, </span><span style="color: #0000BB">$mye2</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">':e3'</span><span style="color: #007700">, </span><span style="color: #0000BB">$mye3</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_fetch_all</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #0000BB">$res</span><span style="color: #007700">);<br />foreach (</span><span style="color: #0000BB">$res</span><span style="color: #007700">[</span><span style="color: #DD0000">'LAST_NAME'</span><span style="color: #007700">] as </span><span style="color: #0000BB">$name</span><span style="color: #007700">) {<br /> print </span><span style="color: #0000BB">$name </span><span style="color: #007700">.</span><span style="color: #DD0000">"<br>\n"</span><span style="color: #007700">;<br />}<br /><br /></span><span style="color: #FF8000">// Output is<br />// Ernst<br />// Hunold<br /><br /></span><span style="color: #0000BB">oci_free_statement</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_close</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-1951">
<p><strong>Example #8 Binding a ROWID returned by a query</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /><br /></span><span style="color: #FF8000">// Create the table with:<br />// CREATE TABLE mytab (id NUMBER, salary NUMBER, name VARCHAR2(40));<br />// INSERT INTO mytab (id, salary, name) VALUES (1, 100, 'Chris');<br />// COMMIT;<br /><br /></span><span style="color: #0000BB">$conn </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_connect</span><span style="color: #007700">(</span><span style="color: #DD0000">'hr'</span><span style="color: #007700">, </span><span style="color: #DD0000">'welcome'</span><span style="color: #007700">, </span><span style="color: #DD0000">'localhost/XE'</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$conn</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">$m </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_error</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">trigger_error</span><span style="color: #007700">(</span><span style="color: #0000BB">htmlentities</span><span style="color: #007700">(</span><span style="color: #0000BB">$m</span><span style="color: #007700">[</span><span style="color: #DD0000">'message'</span><span style="color: #007700">]), </span><span style="color: #0000BB">E_USER_ERROR</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #DD0000">'SELECT ROWID, name FROM mytab WHERE id = :id_bv FOR UPDATE'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$id </span><span style="color: #007700">= </span><span style="color: #0000BB">1</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">':id_bv'</span><span style="color: #007700">, </span><span style="color: #0000BB">$id</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$row </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_fetch_array</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #0000BB">OCI_ASSOC</span><span style="color: #007700">+</span><span style="color: #0000BB">OCI_RETURN_NULLS</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$rid </span><span style="color: #007700">= </span><span style="color: #0000BB">$row</span><span style="color: #007700">[</span><span style="color: #DD0000">'ROWID'</span><span style="color: #007700">];<br /></span><span style="color: #0000BB">$name </span><span style="color: #007700">= </span><span style="color: #0000BB">$row</span><span style="color: #007700">[</span><span style="color: #DD0000">'NAME'</span><span style="color: #007700">];<br /><br /></span><span style="color: #FF8000">// Change name to upper case & save the changes<br /></span><span style="color: #0000BB">$name </span><span style="color: #007700">= </span><span style="color: #0000BB">strtoupper</span><span style="color: #007700">(</span><span style="color: #0000BB">$name</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #DD0000">'UPDATE mytab SET name = :n_bv WHERE ROWID = :r_bv'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">':n_bv'</span><span style="color: #007700">, </span><span style="color: #0000BB">$name</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">':r_bv'</span><span style="color: #007700">, </span><span style="color: #0000BB">$rid</span><span style="color: #007700">, -</span><span style="color: #0000BB">1</span><span style="color: #007700">, </span><span style="color: #0000BB">OCI_B_ROWID</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">// The table now contains 1, 100, CHRIS<br /><br /></span><span style="color: #0000BB">oci_free_statement</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_close</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-1952">
<p><strong>Example #9 Binding a ROWID on INSERT</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /><br /></span><span style="color: #FF8000">// This example inserts an id & name, and then updates the salary<br />// Create the table with:<br />// CREATE TABLE mytab (id NUMBER, salary NUMBER, name VARCHAR2(40));<br />//<br />// Based on original ROWID example by thies at thieso dot net (980221)<br /><br /></span><span style="color: #0000BB">$conn </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_connect</span><span style="color: #007700">(</span><span style="color: #DD0000">'hr'</span><span style="color: #007700">, </span><span style="color: #DD0000">'welcome'</span><span style="color: #007700">, </span><span style="color: #DD0000">'localhost/XE'</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$conn</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">$m </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_error</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">trigger_error</span><span style="color: #007700">(</span><span style="color: #0000BB">htmlentities</span><span style="color: #007700">(</span><span style="color: #0000BB">$m</span><span style="color: #007700">[</span><span style="color: #DD0000">'message'</span><span style="color: #007700">]), </span><span style="color: #0000BB">E_USER_ERROR</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #0000BB">$sql </span><span style="color: #007700">= </span><span style="color: #DD0000">"INSERT INTO mytab (id, name) VALUES(:id_bv, :name_bv)<br /> RETURNING ROWID INTO :rid"</span><span style="color: #007700">;<br /><br /></span><span style="color: #0000BB">$ins_stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #0000BB">$sql</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$rowid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_new_descriptor</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #0000BB">OCI_D_ROWID</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$ins_stid</span><span style="color: #007700">, </span><span style="color: #DD0000">":id_bv"</span><span style="color: #007700">, </span><span style="color: #0000BB">$id</span><span style="color: #007700">, </span><span style="color: #0000BB">10</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$ins_stid</span><span style="color: #007700">, </span><span style="color: #DD0000">":name_bv"</span><span style="color: #007700">, </span><span style="color: #0000BB">$name</span><span style="color: #007700">, </span><span style="color: #0000BB">32</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$ins_stid</span><span style="color: #007700">, </span><span style="color: #DD0000">":rid"</span><span style="color: #007700">, </span><span style="color: #0000BB">$rowid</span><span style="color: #007700">, -</span><span style="color: #0000BB">1</span><span style="color: #007700">, </span><span style="color: #0000BB">OCI_B_ROWID</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$sql </span><span style="color: #007700">= </span><span style="color: #DD0000">"UPDATE mytab SET salary = :salary WHERE ROWID = :rid"</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$upd_stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #0000BB">$sql</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$upd_stid</span><span style="color: #007700">, </span><span style="color: #DD0000">":rid"</span><span style="color: #007700">, </span><span style="color: #0000BB">$rowid</span><span style="color: #007700">, -</span><span style="color: #0000BB">1</span><span style="color: #007700">, </span><span style="color: #0000BB">OCI_B_ROWID</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$upd_stid</span><span style="color: #007700">, </span><span style="color: #DD0000">":salary"</span><span style="color: #007700">, </span><span style="color: #0000BB">$salary</span><span style="color: #007700">, </span><span style="color: #0000BB">32</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">// ids and names to insert<br /></span><span style="color: #0000BB">$data </span><span style="color: #007700">= array(</span><span style="color: #0000BB">1111 </span><span style="color: #007700">=> </span><span style="color: #DD0000">"Larry"</span><span style="color: #007700">,<br /> </span><span style="color: #0000BB">2222 </span><span style="color: #007700">=> </span><span style="color: #DD0000">"Bill"</span><span style="color: #007700">,<br /> </span><span style="color: #0000BB">3333 </span><span style="color: #007700">=> </span><span style="color: #DD0000">"Jim"</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">// Salary of each person<br /></span><span style="color: #0000BB">$salary </span><span style="color: #007700">= </span><span style="color: #0000BB">10000</span><span style="color: #007700">;<br /><br /></span><span style="color: #FF8000">// Insert and immediately update each row<br /></span><span style="color: #007700">foreach (</span><span style="color: #0000BB">$data </span><span style="color: #007700">as </span><span style="color: #0000BB">$id </span><span style="color: #007700">=> </span><span style="color: #0000BB">$name</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$ins_stid</span><span style="color: #007700">);<br /> </span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$upd_stid</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #0000BB">$rowid</span><span style="color: #007700">-></span><span style="color: #0000BB">free</span><span style="color: #007700">();<br /></span><span style="color: #0000BB">oci_free_statement</span><span style="color: #007700">(</span><span style="color: #0000BB">$upd_stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_free_statement</span><span style="color: #007700">(</span><span style="color: #0000BB">$ins_stid</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">// Show the new rows<br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #DD0000">"SELECT * FROM mytab"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br />while (</span><span style="color: #0000BB">$row </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_fetch_array</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #0000BB">OCI_ASSOC</span><span style="color: #007700">+</span><span style="color: #0000BB">OCI_RETURN_NULLS</span><span style="color: #007700">)) {<br /> </span><span style="color: #0000BB">var_dump</span><span style="color: #007700">(</span><span style="color: #0000BB">$row</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #0000BB">oci_free_statement</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_close</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-1953">
<p><strong>Example #10 Binding for a PL/SQL stored function</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /><br /></span><span style="color: #FF8000">// Before running the PHP program, create a stored function in<br />// SQL*Plus or SQL Developer:<br />//<br />// CREATE OR REPLACE FUNCTION myfunc(p IN NUMBER) RETURN NUMBER AS<br />// BEGIN<br />// RETURN p * 3;<br />// END;<br /><br /></span><span style="color: #0000BB">$conn </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_connect</span><span style="color: #007700">(</span><span style="color: #DD0000">'hr'</span><span style="color: #007700">, </span><span style="color: #DD0000">'welcome'</span><span style="color: #007700">, </span><span style="color: #DD0000">'localhost/XE'</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$conn</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">$e </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_error</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">trigger_error</span><span style="color: #007700">(</span><span style="color: #0000BB">htmlentities</span><span style="color: #007700">(</span><span style="color: #0000BB">$e</span><span style="color: #007700">[</span><span style="color: #DD0000">'message'</span><span style="color: #007700">]), </span><span style="color: #0000BB">E_USER_ERROR</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #0000BB">$p </span><span style="color: #007700">= </span><span style="color: #0000BB">8</span><span style="color: #007700">;<br /><br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #DD0000">'begin :r := myfunc(:p); end;'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">':p'</span><span style="color: #007700">, </span><span style="color: #0000BB">$p</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">// The return value is an OUT bind. The default type will be a string<br />// type so binding a length 40 means that at most 40 digits will be<br />// returned.<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">':r'</span><span style="color: #007700">, </span><span style="color: #0000BB">$r</span><span style="color: #007700">, </span><span style="color: #0000BB">40</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /><br />print </span><span style="color: #DD0000">"</span><span style="color: #0000BB">$r</span><span style="color: #DD0000">\n"</span><span style="color: #007700">; </span><span style="color: #FF8000">// prints 24<br /><br /></span><span style="color: #0000BB">oci_free_statement</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_close</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-1954">
<p><strong>Example #11 Binding parameters for a PL/SQL stored procedure</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /><br /></span><span style="color: #FF8000">// Before running the PHP program, create a stored procedure in<br />// SQL*Plus or SQL Developer:<br />//<br />// CREATE OR REPLACE PROCEDURE myproc(p1 IN NUMBER, p2 OUT NUMBER) AS<br />// BEGIN<br />// p2 := p1 * 2;<br />// END;<br /><br /></span><span style="color: #0000BB">$conn </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_connect</span><span style="color: #007700">(</span><span style="color: #DD0000">'hr'</span><span style="color: #007700">, </span><span style="color: #DD0000">'welcome'</span><span style="color: #007700">, </span><span style="color: #DD0000">'localhost/XE'</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$conn</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">$e </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_error</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">trigger_error</span><span style="color: #007700">(</span><span style="color: #0000BB">htmlentities</span><span style="color: #007700">(</span><span style="color: #0000BB">$e</span><span style="color: #007700">[</span><span style="color: #DD0000">'message'</span><span style="color: #007700">]), </span><span style="color: #0000BB">E_USER_ERROR</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #0000BB">$p1 </span><span style="color: #007700">= </span><span style="color: #0000BB">8</span><span style="color: #007700">;<br /><br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #DD0000">'begin myproc(:p1, :p2); end;'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">':p1'</span><span style="color: #007700">, </span><span style="color: #0000BB">$p1</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">// The second procedure parameter is an OUT bind. The default type<br />// will be a string type so binding a length 40 means that at most 40<br />// digits will be returned.<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">':p2'</span><span style="color: #007700">, </span><span style="color: #0000BB">$p2</span><span style="color: #007700">, </span><span style="color: #0000BB">40</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /><br />print </span><span style="color: #DD0000">"</span><span style="color: #0000BB">$p2</span><span style="color: #DD0000">\n"</span><span style="color: #007700">; </span><span style="color: #FF8000">// prints 16<br /><br /></span><span style="color: #0000BB">oci_free_statement</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_close</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-1955">
<p><strong>Example #12 Binding a CLOB column</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /><br /></span><span style="color: #FF8000">// Before running, create the table:<br />// CREATE TABLE mytab (mykey NUMBER, myclob CLOB);<br /><br /></span><span style="color: #0000BB">$conn </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_connect</span><span style="color: #007700">(</span><span style="color: #DD0000">'hr'</span><span style="color: #007700">, </span><span style="color: #DD0000">'welcome'</span><span style="color: #007700">, </span><span style="color: #DD0000">'localhost/XE'</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$conn</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">$e </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_error</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">trigger_error</span><span style="color: #007700">(</span><span style="color: #0000BB">htmlentities</span><span style="color: #007700">(</span><span style="color: #0000BB">$e</span><span style="color: #007700">[</span><span style="color: #DD0000">'message'</span><span style="color: #007700">]), </span><span style="color: #0000BB">E_USER_ERROR</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #0000BB">$mykey </span><span style="color: #007700">= </span><span style="color: #0000BB">12343</span><span style="color: #007700">; </span><span style="color: #FF8000">// arbitrary key for this example;<br /><br /></span><span style="color: #0000BB">$sql </span><span style="color: #007700">= </span><span style="color: #DD0000">"INSERT INTO mytab (mykey, myclob)<br /> VALUES (:mykey, EMPTY_CLOB())<br /> RETURNING myclob INTO :myclob"</span><span style="color: #007700">;<br /><br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #0000BB">$sql</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$clob </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_new_descriptor</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #0000BB">OCI_D_LOB</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">":mykey"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mykey</span><span style="color: #007700">, </span><span style="color: #0000BB">5</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">":myclob"</span><span style="color: #007700">, </span><span style="color: #0000BB">$clob</span><span style="color: #007700">, -</span><span style="color: #0000BB">1</span><span style="color: #007700">, </span><span style="color: #0000BB">OCI_B_CLOB</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #0000BB">OCI_DEFAULT</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$clob</span><span style="color: #007700">-></span><span style="color: #0000BB">save</span><span style="color: #007700">(</span><span style="color: #DD0000">"A very long string"</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">oci_commit</span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">// Fetching CLOB data<br /><br /></span><span style="color: #0000BB">$query </span><span style="color: #007700">= </span><span style="color: #DD0000">'SELECT myclob FROM mytab WHERE mykey = :mykey'</span><span style="color: #007700">;<br /><br /></span><span style="color: #0000BB">$stid </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_parse </span><span style="color: #007700">(</span><span style="color: #0000BB">$conn</span><span style="color: #007700">, </span><span style="color: #0000BB">$query</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #DD0000">":mykey"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mykey</span><span style="color: #007700">, </span><span style="color: #0000BB">5</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">oci_execute</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">);<br /><br />print </span><span style="color: #DD0000">'<table border="1">'</span><span style="color: #007700">;<br />while (</span><span style="color: #0000BB">$row </span><span style="color: #007700">= </span><span style="color: #0000BB">oci_fetch_array</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #0000BB">OCI_ASSOC</span><span style="color: #007700">)) {<br /> </span><span style="color: #0000BB">$result </span><span style="color: #007700">= </span><span style="color: #0000BB">$row</span><span style="color: #007700">[</span><span style="color: #DD0000">'MYCLOB'</span><span style="color: #007700">]-></span><span style="color: #0000BB">load</span><span style="color: #007700">();<br /> print </span><span style="color: #DD0000">'<tr><td>'</span><span style="color: #007700">.</span><span style="color: #0000BB">$result</span><span style="color: #007700">.</span><span style="color: #DD0000">'</td></tr>'</span><span style="color: #007700">;<br />}<br />print </span><span style="color: #DD0000">'</table>'</span><span style="color: #007700">;<br /><br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
</div>
<div class="refsect1 returnvalues" id="refsect1-function.oci-bind-by-name-returnvalues">
<h3 class="title">Return Values</h3>
<p class="para">
Returns <strong><code>TRUE</code></strong> on success or <strong><code>FALSE</code></strong> on failure.
</p>
</div>
<div class="refsect1 notes" id="refsect1-function.oci-bind-by-name-notes">
<h3 class="title">Notes</h3>
<div class="warning"><strong class="warning">Warning</strong>
<p class="para">
Do not use <a href="info.configuration.html#ini.magic-quotes-gpc" class="link">magic_quotes_gpc</a> or
<span class="function"><a href="function.addslashes.html" class="function">addslashes()</a></span>
and <span class="function"><strong>oci_bind_by_name()</strong></span> simultaneously as no
quoting is needed. Any magically applied quotes will be written
into your database because <span class="function"><strong>oci_bind_by_name()</strong></span>
inserts data verbatim and does not remove quotes or escape
characters.
</p>
</div>
<blockquote class="note"><p><strong class="note">Note</strong>:
<p class="para">
If you bind a string to a <em>CHAR</em> column in
a <em>WHERE</em> clause, remember that Oracle uses
blank-padded comparison semantics for <em>CHAR</em>
columns. Your PHP variable should be blank padded to the same
width as the column for the <em>WHERE</em> clause to
succeed.
</p>
</p></blockquote>
<blockquote class="note"><p><strong class="note">Note</strong>:
<p class="para">
The PHP <em><code class="parameter">variable</code></em> argument is a reference. Some
forms of loops do not work as expected:
</p>
<div class="informalexample">
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /></span><span style="color: #007700">foreach (</span><span style="color: #0000BB">$myarray </span><span style="color: #007700">as </span><span style="color: #0000BB">$key </span><span style="color: #007700">=> </span><span style="color: #0000BB">$value</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #0000BB">$key</span><span style="color: #007700">, </span><span style="color: #0000BB">$value</span><span style="color: #007700">);<br />}<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
<p class="para">
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:
</p>
<div class="informalexample">
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /></span><span style="color: #007700">foreach (</span><span style="color: #0000BB">$myarray </span><span style="color: #007700">as </span><span style="color: #0000BB">$key </span><span style="color: #007700">=> </span><span style="color: #0000BB">$value</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">oci_bind_by_name</span><span style="color: #007700">(</span><span style="color: #0000BB">$stid</span><span style="color: #007700">, </span><span style="color: #0000BB">$key</span><span style="color: #007700">, </span><span style="color: #0000BB">$myarray</span><span style="color: #007700">[</span><span style="color: #0000BB">$key</span><span style="color: #007700">]);<br />}<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p></blockquote>
</div>
<div class="refsect1 seealso" id="refsect1-function.oci-bind-by-name-seealso">
<h3 class="title">See Also</h3>
<p class="para">
<ul class="simplelist">
<li class="member"> <span class="function"><a href="function.oci-bind-array-by-name.html" class="function" rel="rdfs-seeAlso">oci_bind_array_by_name()</a> - Binds a PHP array to an Oracle PL/SQL array parameter</span></li>
<li class="member"> <span class="function"><a href="function.oci-parse.html" class="function" rel="rdfs-seeAlso">oci_parse()</a> - Prepares an Oracle statement for execution</span></li>
</ul>
</p>
</div>
</div><hr /><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="function.oci-bind-array-by-name.html">oci_bind_array_by_name</a></div>
<div class="next" style="text-align: right; float: right;"><a href="function.oci-cancel.html">oci_cancel</a></div>
<div class="up"><a href="ref.oci8.html">OCI8 Functions</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
