Last updated: Fri, 13 Nov 2009

oci_bind_by_name

(PHP 5, PECL OCI8 >= 1.1.0)

oci_bind_by_name — Binds the PHP variable to the Oracle placeholder

Descrição

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

Binds the PHP variable variable to the Oracle placeholder ph_name . Whether it will be used for input or output will be determined at run-time and the necessary storage space will be allocated.

Parâmetros

statement

An OCI statement.

ph_name

The placeholder.

variable

The PHP variable.

maxlength

Sets the maximum length for the bind. If you set it to -1, this function will use the current length of variable to set the maximum length.

type

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. The type parameter tells Oracle which descriptor is used. Default to SQLT_CHR. Possible values are:

SQLT_FILE - for BFILEs;
SQLT_CFILE - for CFILEs;
SQLT_CLOB - for CLOBs;
SQLT_BLOB - for BLOBs;
SQLT_RDD - for ROWIDs;
SQLT_NTY - for named datatypes;
SQLT_INT - for integers;
SQLT_CHR - for VARCHARs;
SQLT_BIN - for RAW columns;
SQLT_LNG - for LONG columns;
SQLT_LBI - for LONG RAW columns;
SQLT_RSET - for cursors, that were created before with oci_new_cursor().

Valor Retornado

Retorna TRUE em caso de sucesso ou FALSE em falhas.

Exemplos

Exemplo #1 oci_bind_by_name() example


<?php
/* oci_bind_by_name example thies at thieso dot net (980221)
  inserts 3 records into emp, and uses the ROWID for updating the
  records just after the insert.
*/

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

/* delete our "junk" from the emp table.... */
$stmt = oci_parse($conn, "
                          DELETE FROM
                                     emp
                                WHERE
                                     empno
                                   IN
                                     (1111,2222,3333)
                         ");
oci_execute($stmt);
oci_free_statement($stmt);

oci_close($conn);
?>

Remember, this function strips trailing whitespace. See the following example:

Exemplo #2 oci_bind_by_name() example


<?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);
    /*
     This code will insert into DB string 'trailing spaces follow', without
     trailing spaces
    */
?>

Exemplo #3 oci_bind_by_name() example


<?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);
    /*
     And this code will add 'trailing spaces follow      ', preserving
     trailing whitespaces
    */
?>

Valor Retornado

Retorna TRUE em caso de sucesso ou FALSE em falhas.

Notas

Aviso

Do not use magic_quotes_gpc or addslashes() and oci_bind_by_name() simultaneously as no quoting is needed and any magically applied quotes will be written into your database as oci_bind_by_name() is not able to distinguish magically added quotings from those added intentionally.

Nota: In PHP versions before 5.0.0 you must use ocibindbyname() instead. This name still can be used, it was left as alias of oci_bind_by_name() for downwards compatability. This, however, is deprecated and not recommended.

oci_cancel

oci_bind_array_by_name

Last updated: Fri, 13 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);

?>

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.

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

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;

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, 13 Nov 2009