oci_bind_array_by_name
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);
?>
oci_bind_by_name
(PHP 5, PECL OCI8 >= 1.1.0)
oci_bind_by_name — Lega una variabile PHP ad un segnaposto Oracle
Descrizione
bool oci_bind_by_name
( resource
$stmt
, string $nome_ph
, mixed $&variabile
[, int $lungmax
[, int $tipo
]] )
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:
-
OCI_B_FILE- per i BFILE; -
OCI_B_CFILE- per i CFILE; -
OCI_B_CLOB- per i CLOB; -
OCI_B_BLOB- per i BLOB; -
OCI_B_ROWID- per i ROWID; -
OCI_B_NTY- per i named datatype; -
OCI_B_CURSOR- per i cursori precedentemente creati con oci_new_cursor().
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
User Contributed Notes
oci_bind_by_name - [9 notes]
adrian dot crossley at hesa dot ac dot uk ¶
3 years ago
ajitsingh4u at gmail dot com ¶
5 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
}
?>
mail at mkharitonov dot net ¶
10 months ago
If you get error "ORA-22275: invalid LOB locator specified ORA-06512" when using clob try this code:
<?php
$connection = $db->connection;
$statement = oci_parse($connection, 'begin core.clob_test(:p_varchar, :p_clob); end;');
$varchar = 'abc';
oci_bind_by_name($statement, ':p_varchar', $varchar);
$lob = oci_new_descriptor($connection, OCI_D_LOB);
oci_bind_by_name($statement, ':p_clob', $lob, -1, OCI_B_CLOB);
// Differences between original example is method name and methods call order
$lob->writeTemporary('abc abc abc abc abc');
oci_execute($statement);
$lob->close();
?>
abiyi2000 at yahoo dot com ¶
1 year 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, $v2, 8, 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.
avenger at php dot net ¶
4 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.
Anonymous ¶
6 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
Chris Delcamp ¶
6 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;
hfuecks at nospam dot org ¶
7 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
passerbyxp at gmail dot com ¶
1 year ago
I unfortunately used the code below for a data synchronize process and ended up in big trouble:
<?php
/*
cache data from other source
*/
$u=oci_parse($con,"UPDATE....WHERE id=:b_id");
oci_bind_by_name($u,":b_id",$id,-1,SQLT_INT);
//bind value...
$i=oci_parse($con,"INSERT INTO...");
oci_bind_by_name($i,":b_id",$id,-1,SQLT_INT);
//bind value using the same variables
while(/*read data from cache*/)
{
oci_execute($u,OCI_NO_AUTO_COMMIT);
if(oci_num_rows($u)<=0) oci_execute($i,OCI_NO_AUTO_COMMIT);
}
oci_commit($con);
?>
Although in fact the $i statement is never executed, after commit, I found that ALL data are copied as the last row of cached source. I then spent the next hour rebuilding the whole table.
So it seems it's not OK to bind the same variables to two (or more) statements, even if just one statement is actually executed.
I guess this is not a good practice to even think of using more than one statement each time, as no one has said it before, but I think this doesn't clearly violate the "be careful for address binding" rule, so it's still worth to be noted here to prevent some headache.