参数

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.

// 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]);}

// 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 SQLoci_bind_by_name($stid, ":bv", $city);oci_execute($stid);oci_fetch_all($stid, $res);

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.

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

返回值

成功时返回 TRUE， 或者在失败时返回 FALSE。

注释

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:

User Contributed Notes 12 notes

Example #7 only shows the binding of a small fixed number of values in an IN clause. There is also a way to bind multiple conditions with a variable number of values.

<?php$ids = array(103,104);

$conn = oci_pconnect($user, $pass, $tns);// Using ORACLE table() function to get the ids from the subquery$sql = 'SELECT * FROM employees WHERE employee_id IN (SELECT column_value FROM table(:ids))';$stmt = oci_parse($conn, $sql);// Create collection of numbers. Build in type for strings is ODCIVARCHAR2LIST, but you can also create own types.$idCollection = oci_new_collection($conn, 'ODCINUMBERLIST', 'SYS');

// Maximum length of collections of type ODCINUMBERLIST is 32767, maybe you should check that!foreach ($ids as $id) {$idCollection->append($id);}

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

Please note that in my earlier note about having oci_bind_by_name() in a function, this becomes a little more complicated when returning values like "UPDATE table SET bla='blubb' RETURNING id INTO :id".

The background is that if you don't pass by reference (in which case $var inside the function is a copy of $var outside the function), then oci_bind_by_name() will work just fine at first glance.However, since the oci_fetch statements that you use to actually get the data will reference the $var that has ceased to exist when the function finished. In fact, since the varbind seems to be a pointer, that pointer will point to an invalid location at that point and your variables won't be substitued in the SQL.

All this also means that:

1) You have to pass a variable, and not just a value

This doesn't work:

$stid = sql($conn, $q, array('bla'=>'blubb'));

This is better:

$vars = array('bla'=>'blubb');$stid = sql($conn, $q, $vars);

2) Even when passing as reference to your helper function you cannot use e.g. foreach:

It is very important to set up the maxlength of the returning parameter (:r), even when it is returning a number, otherwise the ORA-01460 exception (unimplemented or unreasonable conversion requested) may be raised.

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.