Last updated: Fri, 06 Nov 2009

oci_bind_by_name

(PHP 5, PECL oci8 >= 1.1.0)

oci_bind_by_name — Oracle プレースホルダーに PHP 変数をバインドする

説明

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

ocibindbyname() は、PHP 変数 variable を Oracle プレースホルダー ph_name にバインドします。実行時に入力用、出力用に使用されるによらず、必要な記憶領域が確保されます。

パラメータ

statement

OCI ステートメント

ph_name

プレースホルダー

variable

PHP 変数

maxlength

バインド時の最大長。-1 に設定した場合、 variable の現在の長さを最大長として設定する。

type

抽象データ型 (LOB/ROWID/BFILE) をバインドする必要がある場合、まず oci_new_descriptor() 関数を使用してこれを確保する必要がある。 length は抽象データ型用には使用されず、-1 を設定する必要がある。 type パラメータは、使用されるディスクリプタをOracle に伝える。デフォルトは SQLT_CHR。指定可能な型については以下を参照のこと。

SQLT_FILE - BFILE 用
SQLT_CFILE - CFILE 用
SQLT_CLOB - CLOB 用
SQLT_BLOB - BLOB 用
SQLT_RDD - ROWID 用
SQLT_NTY - 名前付けされたデータ型用
SQLT_INT - integer 用
SQLT_CHR - VARCHAR 用
SQLT_BIN - RAW カラム用
SQLT_LNG - LONG カラム用
SQLT_LBI - LONG RAW カラム用
SQLT_RSET - カーソル用。 oci_new_cursor() により、前もって生成されたもの。

返り値

成功した場合に TRUE を、失敗した場合に FALSE を返します。

例

例1 oci_bind_by_name() の例


<?php
/* oci_bind_by_name  の例 thies at thieso dot net (980221)
  3レコードをempに挿入し、挿入の直後にレコードを更新するために
  ROWIDを使用する。
*/

$conn = oci_connect("scott", "tiger");

$stmt = oci_parse($conn, "
                          INSERT INTO
                                     emp (empno, ename)
                               VALUES
                                     (:empno,:ename)
                            RETURNING
                                     ROWID
                                 INTO
                                     :rid
                         ");

$data = array(
              1111 => "Larry",
              2222 => "Bill",
              3333 => "Jim"
             );

$rowid = oci_new_descriptor($conn, OCI_D_ROWID);

oci_bind_by_name($stmt, ":empno", $empno, 32);
oci_bind_by_name($stmt, ":ename", $ename, 32);
oci_bind_by_name($stmt, ":rid",   $rowid, -1, OCI_B_ROWID);

$update = oci_parse($conn, "
                            UPDATE
                                  emp
                               SET
                                  sal = :sal
                             WHERE
                                  ROWID = :rid
                           ");
oci_bind_by_name($update, ":rid", $rowid, -1, OCI_B_ROWID);
oci_bind_by_name($update, ":sal", $sal,   32);

$sal = 10000;

foreach ($data as $empno => $ename) {
    oci_execute($stmt);
    oci_execute($update);
}

$rowid->free();

oci_free_statement($update);
oci_free_statement($stmt);

$stmt = oci_parse($conn, "
                          SELECT
                                *
                            FROM
                                emp
                           WHERE
                                empno
                              IN
                                (1111,2222,3333)
                         ");
oci_execute($stmt);

while ($row = oci_fetch_assoc($stmt)) {
    var_dump($row);
}

oci_free_statement($stmt);

/* テーブル emp から "junk" を削除する.... */
$stmt = oci_parse($conn, "
                          DELETE FROM
                                     emp
                                WHERE
                                     empno
                                   IN
                                     (1111,2222,3333)
                         ");
oci_execute($stmt);
oci_free_statement($stmt);

oci_close($conn);
?>

この関数は余分な空白を除去する事を忘れないでください。次の例を見てください。

例2 oci_bind_by_name() の例


<?php
    $connection = oci_connect('apelsin','kanistra');
    $query = "INSERT INTO test_table VALUES(:id, :text)";

    $statement = oci_parse($query);
    oci_bind_by_name($statement, ":id", 1);
    oci_bind_by_name($statement, ":text", "trailing spaces follow     ");
    oci_execute($statement);
    /*
     このコードは DB に文字列 'trailing spaces follow' を挿入する。
     余分な空白は付加されない。
    */
?>

例3 oci_bind_by_name() の例


<?php
    $connection = oci_connect('apelsin','kanistra');
    $query = "INSERT INTO test_table VALUES(:id, 'trailing spaces follow      ')";

    $statement = oci_parse($query);
    oci_bind_by_name($statement, ":id", 1);
    oci_execute($statement);
    /*
     また、このコードは後方の空白を保持したまま 'trailing spaces follow      '
     を追加する。
    */
?>

返り値

成功した場合に TRUE を、失敗した場合に FALSE を返します。

注意

警告

magic_quotes_gpc や addslashes() と oci_bind_by_name() を同時に使用しないでください。これは、引用符を追加する必要がないためで、また、magic quote により付加された引用符は、 oci_bind_by_name() が magic quote により付加された引用符と意図的に付加されたものを区別できないため、そのままデータベースに書き込まれるためです。

注意: PHP バージョン 5.0.0 以前では、代わりに ocibindbyname() を使用しなければなりません。まだこの名前を使用することができ、下位互換性のため oci_bind_by_name() への別名として残されていますが、推奨されません。

oci_cancel

oci_bind_array_by_name

Last updated: Fri, 06 Nov 2009

add a note User Contributed Notes
oci_bind_by_name

adrian dot crossley at hesa dot ac dot uk
20-Jul-2009 01:14


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



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



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

<?php

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

?>



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

<?php

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

?>

dawid some sign krysiak dotty net dot pl
14-May-2009 03:48


It looks like you don't have to put colon (':') in the beginning of the placeholder name in the second parameter of ocibindbyname(). Of course colon is still needed in sql content.



The following example works just fine under php 4.4.7:

<?php

$sql = "SELECT * FROM schema.table WHERE id = :id";

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

ocibindbyname($stmt, 'id', $id, -1); // <-- note 'id' as a 2nd param - without ':' in the beggining!!!

ociexecute($stmt,OCI_DEFAULT);

// ...

?>

avenger at php dot net
20-Feb-2009 11:54


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



<?php

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

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

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

OCIExecute($stmt);

?>



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



<?php

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

?>



will resloved this problem.



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

php at stock-consulting dot com
21-Jan-2009 11:15


If your database contains CHAR columns, you cannot use blank-padded comparison sematics with bind variables. VARCHAR columns are not affected!



Example:

Table "tab" has a column "col" defined as "CHAR(20)". One row in the table contains the value "hello world" in the column "col".



The query 

SELECT * FROM tab WHERE col = 'hello world'

will return one row, as expected.



The query

SELECT * FROM tab WHERE col = :bindvar

will return zero rows if you bound :bindvar to 'hello world'. Since col is of type CHAR(20), and :bindvar is apparently assumed to be of type VARCHAR2, Oracle will use use NON-blank-padded comparision semantics.

alvaro at demogracia dot com
24-Sep-2008 12:14


"$type [...] The type parameter tells Oracle which descriptor is used. Default to SQLT_CHR"



This is misleading. You don't need to specify a $type to insert numbers. Actually, there isn't any allowed $type constant to insert floating point numbers.

sixd at php dot net
09-May-2008 11:11


For IN string binds (i.e. INSERT or passing a value into a PL/SQL

procedure) where the length is not known when binding, use a length

that is longer than the longest possible string.  If you don't know

what this is in advance, then re-call oci_bind_by_name() with the

actual size prior to each oci_execute() call.



For OUT binds, always specify a length so that PHP allocates enough

memory to hold the returned value.



The cardinal rule for binding is that the memory address used in the

bind call must continue to exist and be usable when the oci_execute

occurs.  This is the common problem with 'foreach' loops.

ajitsingh4u at gmail dot com
09-May-2008 04:39


//Calling Oracle Stored Procedure

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

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

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

<?php

class Users{

    private $connection;

    

    public function __construct()

    {

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

    }



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

    {

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

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



        //Bind in parameter

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

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



        //Bind out parameter

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



        //Bind Cursor

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

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



        // Execute Statement

        oci_execute($stmt);

        oci_execute($p_cursor, OCI_DEFAULT);



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



        echo $result;

        echo '<br>';

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

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

    }



    public function deleteUser($id)

    {

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

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



        // bind in and out variables

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

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



        //Execute the statement

        $check = oci_execute($stmt);



        if($check == true)

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

        else

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



        return $result;

    }

    

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



}

?>

08-May-2007 08:59


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

(PHP 5.1.6 win32)



OCI_B_NTY - SQLT_NTY

OCI_B_BFILE - SQLT_BFILEE

OCI_B_CFILEE - SQLT_CFILEE

OCI_B_CLOB - SQLT_CLOB

OCI_B_BLOB - SQLT_BLOB

OCI_B_ROWID - SQLT_RDD

OCI_B_CURSOR - SQLT_RSET

OCI_B_BIN - SQLT_BIN

OCI_B_INT - SQLT_INT

OCI_B_NUM - SQLT_NUM

tom at bitworks dot de
27-Jan-2007 12:27


Referes to:



Be careful that the variable argument is a reference. So, the following code does not work:

  foreach($some_array as $key => $value)

  {

   OCIBindByName($stmt, $key, $value);

  }



I assume this is because the contents of $value changes, even though the reference remains the same, so all bound variables end up pointing to the last loop iteration's value.



Instead use the following:

  foreach($some_array as $key => $value)

  {

   OCIBindByName($stmt, $key, $some_array[$key]);

  } 



This dues to the foreach statement. $some_array in foreach() is a copy of the origine array. $key would be the "reference" in the copy, but $some_array[$key] points to the original one.

Chris Delcamp
11-Jan-2007 04:48


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



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

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

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

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

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

oci_execute($stmt);

print $rv;

js at mcs dot be
20-Feb-2006 12:48


Be careful that the variable argument is a reference. So, the following code does not work:

  foreach($some_array as $key => $value)

  {

    OCIBindByName($stmt, $key, $value);

  }



I assume this is because the contents of $value changes, even though the reference remains the same, so all bound variables end up pointing to the last loop iteration's value.



Instead use the following:

  foreach($some_array as $key => $value)

  {

    OCIBindByName($stmt, $key, $some_array[$key]);

  }

ehsmeng
16-Feb-2006 08:43


If you do a wrapper for these functions there is a bug I found on php5.1.1 / oracle 9 / windows xp. see the section with oci_bind_by_name.



function db_layer_insert_1_row ($dbquery, $bindvar = array())

{

    global $_db_layer_database;

    global $_db_layer_lasterror;

    

    if (false === ($stid = oci_parse($_db_layer_database, $dbquery))) {

        db_layer_lasterror ();

        return -1;

    }

    

    // Bind variables. NOTE substituting $bindvar[$bcol] with $bval causes

    // all variables to be set to the last value of $bval?!?!

    foreach ($bindvar as $bcol => $bval) {

        oci_bind_by_name($stid, $bcol, $bindvar[$bcol]); //$bval); <- bug?!

    }

    

    ...

hfuecks at nospam dot org
16-Aug-2005 08:12


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



Running the following script;



<?php

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

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

        print "$const\n";

    }

}

?>



Under PHP 4.4.0 I get;



OCI_B_SQLT_NTY < renamed to OCI_B_NTY with PHP5

OCI_B_BFILE

OCI_B_CFILEE

OCI_B_CLOB

OCI_B_BLOB

OCI_B_ROWID

OCI_B_CURSOR

OCI_B_BIN



Under PHP 5.0.4 I get;



OCI_B_NTY

OCI_B_BFILE < docs are wrong right now

OCI_B_CFILEE < docs are wrong right now

OCI_B_CLOB

OCI_B_BLOB

OCI_B_ROWID

OCI_B_CURSOR

OCI_B_BIN < it's a mystery

add a note

oci_cancel

oci_bind_array_by_name

Last updated: Fri, 06 Nov 2009