oci_bind_by_name

(PHP 5, PHP 7, PHP 8, PECL OCI8 >= 1.1.0)

oci_bind_by_name Lega una variabile PHP ad un segnaposto Oracle

Descrizione

oci_bind_by_name(
    resource $stmt,
    string $nome_ph,
    mixed $&variabile,
    int $lungmax = ?,
    int $tipo = ?
): bool

oci_bind_by_name() collega la variabile PHP variable al segnaposto Oracle ph_name. L'utilizzo in modalità input o output sarà determinato a run-time, e lo spazio di memoria necessario sarà allocato. Il parametro lungmax imposta la lunghezza massima del collegamento. Se si imposta lungmax a -1 oci_bind_by_name() userà l'attuale lunghezza di variabile per impostare la lunghezza massima.

Se si deve collegare un tipo dato astratto (LOB/ROWID/BFILE) occorre innanzitutto allocarlo usando la funzione oci_new_descriptor(). Il parametro lungmax non è usato con i tipi dati astratti e dovrebbe essere impostato a -1. La variabile tipo informa oracle sul tipo di descrittore che si vuole usare. I valori possibili sono:

Example #1 esempio di ocibindbyname()

<?php
/* esempio di oci_bind_by_name thies at thieso dot net (980221)
inserisce 3 tuple in emp, e usa ROWID per aggiornare le
tuple subito dopo l'inserimento.
*/

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

while (list(
$empno, $ename) = each($data)) {
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);
?>

Ricordarsi che questa funzione elimina gli spazi alla fine della riga. Vedere il seguente esempio:

Example #2 esempio di 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", "Qui ci sono degli spazi ");
oci_execute($statement);
/*
Questo codice inserisce nel DB la stringa 'Qui ci sono degli spazi', senza
gli spazi finali
*/
?>

Example #3 esempio di oci_bind_by_name()

<?php
$connection
= oci_connect('apelsin','kanistra');
$query = "INSERT INTO test_table VALUES(:id, 'Qui ci sono degli spazi ')";

$statement = oci_parse($query);
oci_bind_by_name($statement, ":id", 1);
oci_execute($statement);
/*
Questo codice aggiunge 'Qui ci sono degli spazi ', mantenendo
gli spazi
*/
?>

Avviso

Non utilizzare le magic_quotes_gpc o addslashes() e oci_bind_by_name() simultaneamente in quanto le virgolette non sono necessarie nelle variabili e qualsiasi virgoletta aggiunta automaticamente verrà scritta nel database dal momento che ocibindbyname() non è in grado di distinguere le virgolette aggiunte automaticamente da quelle intenzionali.

Restituisce true in caso di successo, false in caso di fallimento.

Nota:

Nelle versioni di PHP antecedenti la 5.0.0 si deve usare ocibindbyname(). Questo nome può ancora essere utilizzato, è rimasto come alias di oci_bind_by_name() per mantenere la compatibilità. Ciò è comunque deprecato e non raccomandato.

add a note add a note

User Contributed Notes 17 notes

up
6
abiyi2000 at yahoo dot com
12 years ago
I unfortunately spent the whole day trying to make this work as part of OCI bind_by_name insert:

<?php
     
if(is_numeric($v2)){
       
oci_bind_by_name($stmth, $bvar, $v2,  -1, OCI_B_INT);
      }else{
       
$v2 = (string) $v2;
       
oci_bind_by_name($stmth, $bvar, $v2, -1, SQLT_CHR);
      }
?>

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

<?php
     
if(is_numeric($v2)){
       
oci_bind_by_name($stmth, $bvar, $v28, OCI_B_INT);
      }else{
       
$v2 = (string) $v2;
       
oci_bind_by_name($stmth, $bvar, $v2, -1, SQLT_CHR);
      }
?>

Hopefully you'll see this soon before you expend a lot of time repeating the same problem I had.
up
7
martin dot abbrent at ufz dot de
7 years ago
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);
}

oci_bind_by_name($stmt, ':ids', $idCollection, -1, SQLT_NTY);
oci_execute($stmt, OCI_DEFAULT);
oci_fetch_all($stmt, $return);
oci_free_statement($stmt);

oci_close($conn);

?>
up
1
ajitsingh4u at gmail dot com
15 years ago
//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

}
?>
up
2
splintyg at gmail dot com
6 years ago
Guys, i've been looking for long time, how to pass clob to and get from procedure
CREATE OR REPLACE PROCEDURE myproc(p1 IN clob, p2 OUT clob);

Here You are an answer:

<?php
        $conn
= oci_connect("TEST", "html", "//hostname", "UTF8");

   
$filename = "./clob.txt";
   
$handle = fopen($filename, "r");
   
$f = fread($handle, filesize($filename));
   
fclose($handle);

   
$stid = oci_parse($conn, "begin myproc(:p1, :p2); end;");
   
$p1 = oci_new_descriptor($conn, OCI_D_LOB);
   
$p2 = oci_new_descriptor($conn, OCI_D_LOB);
   
   
oci_bind_by_name($stid, ":p1", $p1, -1, OCI_B_CLOB);
   
oci_bind_by_name($stid, ":p2", $p2, -1, OCI_B_CLOB);
   
$p1->writeTemporary($f, OCI_TEMP_BLOB);
   
oci_execute($stid); -- Figure out OCI_NO_AUTO_COMMIT
    oci_commit
($conn);
    echo
$p2->load();
   
   
$p1    ->close();
   
$p2    ->close();
   
oci_free_statement($stid);
   
oci_close($conn);
?>

And perfect book about "PHP and Oracle"
http://www.oracle.com/technetwork/topics/php/underground-php-oracle-manual-098250.html
up
0
charles dot fisher at arconic dot com
2 years ago
I am trying to rework ADOdb library calls to OCI, and I wrote this function today which is helping.

function OraQry(&$Results, $Query, $Binds = false) {
  global $xdb;

  $Results = oci_parse($xdb, $Query);

  if($Binds) foreach($Binds as $BindNm => $BindValJunk)
    oci_bind_by_name($Results, $BindNm, $Binds[$BindNm], -1);

  oci_execute($Results, OCI_NO_AUTO_COMMIT);

  return null;
}

This also has similarity to PDO in passing an array of bind variables, with the added benefit that if they are named numerically (starting at zero), then the call to the array() function can be omitted:

OraQry($rs,
  'select status from all_tables where owner=:0 and table_name=:1',
    [$owner, $table_name]);

while($arr = oci_fetch_assoc($rs)) echo $arr['STATUS'] . "\n";
up
0
splintyg at gmail dot com
6 years ago
Guys, i've been looking for long time, how to pass clob to and get from procedure
CREATE OR REPLACE PROCEDURE myproc(p1 IN clob, p2 OUT clob);

Here You are an answer:

<?php
        $conn
= oci_connect("TEST", "html", "//hostname", "UTF8");

   
$filename = "./clob.txt";
   
$handle = fopen($filename, "r");
   
$f = fread($handle, filesize($filename));
   
fclose($handle);

   
$stid = oci_parse($conn, "begin myproc(:p1, :p2); end;");
   
$p1 = oci_new_descriptor($conn, OCI_D_LOB);
   
$p2 = oci_new_descriptor($conn, OCI_D_LOB);
   
   
oci_bind_by_name($stid, ":p1", $p1, -1, OCI_B_CLOB);
   
oci_bind_by_name($stid, ":p2", $p2, -1, OCI_B_CLOB);
   
$p1->writeTemporary($f, OCI_TEMP_BLOB);
   
oci_execute($stid); -- Figure out OCI_NO_AUTO_COMMIT
    oci_commit
($conn);
    echo
$p2->load();
   
   
$p1    ->close();
   
$p2    ->close();
   
oci_free_statement($stid);
   
oci_close($conn);
?>

And perfect book about "PHP and Oracle"
http://www.oracle.com/technetwork/topics/php/underground-php-oracle-manual-098250.html
up
0
Anonymous
6 years ago
Bear in mind that you cannot use reserved words for bind variables. Otherwise you'll get ORA-01745: Invalid host/bind variable name error.
up
0
marki at trash-mail dot com
7 years ago
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".

You can do it as follows:

<?php
function sql($q, &$vars_in=array(), &$vars_out=array()) {
  ...
 
$stid = oci_parse($conn, $q);
  ...
 
reset($vars_in);
  do {
    if (
current($vars_in)===FALSE) {
      break;
    }
   
$b = oci_bind_by_name($stid, key($vars_in), current($vars_in));
   
// insert exception handling here
 
} while (each($vars_in) !== FALSE);

 
// VARS TO RETURN
  // we'll fix this to integer type because for now we need this for index IDs
 
foreach ($vars_out as $k => $v) {
   
$b = oci_bind_by_name($stid, $k, $vars_out[$k], -1, SQLT_INT);
   
// insert exception handling here
 
}

  ...
}
?>

Use like this:

<?php
$blubb
= 'blubb';
$b = array(':bla' => $blubb);
$b_out = array(':id' => ''); // leave value empty
$x = sql($q, $b, $b_out);
$id = $b_out[':id'];
?>

(The point is: you would not be able to return anything into $b[':bla'] because $b[':bla'] becomes current($vars_in) inside sql() and cannot be written to.)
up
0
marki at trash-mail dot com
7 years ago
I had a query that was working properly at first sight, no errors on execute, nothing, but there were simply no results returned at runtime.

Be careful when putting the database commands into a function and binding your variables there while using oci_fetch_xxx() outside the function.

function sql($conn, $stmt, $var) {
  $stid = oci_parse($conn, $stmt);
  ...
  oci_bind_by_name($stid, ':val', $var);
  ...
}
sql($conn, $q, $var);
$row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS);

As you see from the definition of oci_bind_by_name(), $var needs to be passed as reference, so your function has to have this reference ready like this:

function sql($conn, $stmt, &$var) {
  $stid = oci_parse($conn, $stmt);
  ...
  oci_bind_by_name($stid, ':val', $var);
  ...
}

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:

This doesn't work:

function sql($conn, $q, $vars) {
  ...
  foreach ($vars as $k => $v) {
    oci_bind_by_name($stid, $k, $v);
  }
  ...
}

Again, because $k and $v are local variables that will have disappeared once you perform an oci_fetch outside the function.

Instead you have to work the array in a more low-level way like this:

function sql($conn, $q, &$vars) {
  ...
  $stid = oci_parse($conn, $q);
  ...
  reset($vars);
  do {
    if (current($vars)===FALSE) { // end of array
      break;
    }
    $b = oci_bind_by_name($stid, key($vars), current($vars));
    if ($b === FALSE) {
      DIE('Could not bind var');
    }
  } while (each($vars) !== FALSE);
}
$binds = array(':bla1' => 'blubb1',
               ':bla2' => 'blubb2');
$stid = sql($conn, $q, $binds);
$row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS);

Wherever you oci_bind_by_name(), the pointer to the initial data has to exist from beginning to end.
up
0
xorinox at gmx dot ch
9 years ago
Working with Oracle and raw types in and out worked like the following for me.

<?php
 
/*oracle procedure
  procedure open_session(
    i_instance_id in raw,
    o_session_id out raw,
    o_errcode out number,
    o_errmsg out varchar2
  ); 
  */

  //open database
 
$conn = DBOpen( DB_DEV_USER );

 
//get session id
 
$sql = "begin p_loader.open_session( hextoraw( :instance_id ), :session_id, :errcode, :errmsg ); end;";
 
$stmt = oci_parse( $conn, $sql );
 
$instanceId = DB_INSTANCE_ID;
 
oci_bind_by_name( $stmt, ":instance_id", $instanceId, 1, SQLT_CHR );
 
oci_bind_by_name( $stmt, ":session_id", $sessionId, 16, SQLT_BIN );
 
oci_bind_by_name( $stmt, ":errcode", $errcode, 12, SQLT_INT );
 
oci_bind_by_name( $stmt, ":errmsg", $errmsg, 4000, SQLT_CHR );
 
 
oci_execute( $stmt );
 
$sessionId = bin2hex( $sessionId ); //now this is a hex string
 
  //close database
 
DBClose( $conn );
?>
up
0
jjeffman at cpovo.net
9 years ago
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.
up
0
avenger at php dot net
15 years ago
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.
up
0
Chris Delcamp
17 years ago
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;
up
-1
asui dot dev dot null at gmail dot com
4 years ago
If you are getting "ORA-01722: invalid number error" while inserting/updating a FLOAT value into a NUMBER column, please check the correctness of a binded value format according to the current locale settings.

Default "american" locale assumes that value send to oracle will be a dot decimal separator (just like 4127.5). But with setlocale('pl_PL.UTF-8') your float number would be represented as 4127,5 and that form will be used while sending data do oracle causing a problem...
That was my case (8 hours of debugging).

You can check your current locale with setlocale(LC_ALL, 0).

What I can recommend as a solutions:
a) do not set locale, or set it to 'C' for a time of sending data;
b) convert float to a string format compatible with current oracle session NLS_NUMERIC_CHARACTERS parameter value.
For example: when NLS_NUMERIC_CHARACTERS = '.,' float value 4127.5 should be converted to '4127.5'.  Then oracle will catch it correctly even if current locale are set differently.
up
-1
Anonymous
16 years ago
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
up
-2
hfuecks at nospam dot org
18 years ago
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
up
-1
adrian dot crossley at hesa dot ac dot uk
14 years ago
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);
?>
To Top