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.
oci_bind_by_name
(PHP 5, PHP 7, PHP 8, PECL OCI8 >= 1.1.0)
oci_bind_by_name — 绑定 PHP 变量到 Oracle 位置标志符
说明
oci_bind_by_name(
resource
string
mixed
int
int
): bool
resource
$statement,string
$param,mixed
&$var,int
$max_length = -1,int
$type = 0): bool
将 PHP 变量 var 绑定到 Oracle 位置占位符 param。绑定对
Oracle 数据库性能很重要，同时也是避免 SQL 注入安全问题的一种方式。
绑定允许数据库重用语句上下文和以前执行语句的缓存，即使另一个用户或进程最初（originally）执行它也是如此。绑定减少了 SQL 注入问题，因为与绑定变量关联的数据永远不会被视为 SQL 语句的一部分。不需要引用或转义。
已经绑定的 PHP 变量可以改变，语句重新执行，而不需要重新解析语句或重新绑定。
在 Oracle 中，绑定变量通常分为用于传递到数据库的值的 IN 绑定和用于返回给 PHP 的值的 OUT
绑定。绑定变量可以同时为 IN 和 OUT。绑定变量是用于输入还是输出是在运行时确定的。
使用 OUT 绑定时必须指定 max_length 以便 PHP 分配足够的内存来保存返回值。
对于 IN 绑定，如果使用不同的 PHP 变量值多次重新执行该语句，建议设置 max_length 长度。否则
Oracle 可能会将数据截断为初始 PHP 变量值的长度。如果不知道最大长度是多少，则在每次调用 oci_execute() 之前使用当前数据大小重新调用
oci_bind_by_name()。绑定不必要的大长度将对数据库中的进程内存产生影响。
绑定调用告诉 Oracle 从哪个内存地址读取数据。对于 IN 绑定，调用 oci_execute()
时地址需要包含有效数据。这意味着变量绑定必须保留在作用域内直到执行。否则，可能会出现意外结果或错误，例如“ORA-01460：未实现或不合理的转换请求”。对于
OUT 绑定，一个问题是没有在 PHP 变量中设置值。
对于重复执行的语句，永远不变的绑定值可能会降低 Oracle 优化器选择最佳语句执行计划的能力。很少重新执行的长时间运行的语句可能无法从绑定中获益。但是，在这两种情况下，绑定可能比将字符串连接到 SQL 语句中更安全，因为如果连接未过滤的用户文本，这可能会带来安全风险。
参数
-
statement -
有效的 OCI8 语句标识符。
-
param -
语句中使用以冒号为前缀的绑定变量占位符。冒号在
param中是可选的。Oracle 不使用问号作为占位符。 -
var -
与
param关联的 PHP 变量 -
max_length -
设置数据的最大长度。如果将其设置为 -1，则此函数将使用
var的当前长度来设置最大长度。在这种情况下，var必须存在并且在调用 oci_bind_by_name() 时包含数据。 -
type -
Oracle 处理数据的数据类型。使用的默认
type是SQLT_CHR。可能的话，Oracle 将在此类型和数据库列（或 PL/SQL 变量类型）之间转换数据。如果要绑定抽象数据类型（LOB/ROWID/BFILE），需要先用 oci_new_descriptor() 函数分配空间。
length没有用于抽象数据类型，应被设为 -1。type的可能值为：-
SQLT_BFILEE或OCI_B_BFILE对应 BFILE； -
SQLT_CFILEE或OCI_B_CFILEE对应 CFILE； -
SQLT_CLOB或OCI_B_CLOB对应 CLOB； -
SQLT_BLOB或OCI_B_BLOB对应 BLOB； -
SQLT_RDD或OCI_B_ROWID对应 ROWID； -
SQLT_NTY或OCI_B_NTY对应命名数据类型； -
SQLT_INT或OCI_B_INT对应整型； -
SQLT_CHR对应 VARCHAR； -
SQLT_BIN或OCI_B_BIN对应 RAW 列； -
SQLT_LNG对应 LONG 列； -
SQLT_LBI对应 LONG RAW 列； -
SQLT_RSET对应使用 oci_new_cursor() 创建的游标； -
SQLT_BOL或OCI_B_BOL对应 PL/SQL BOOLEAN（需要 Oracle Database 12c）
-
返回值
成功时返回 true， 或者在失败时返回 false。
示例
示例 #1 使用 oci_bind_by_name() 插入数据
<?php
// Create the table with:
// CREATE TABLE mytab (id NUMBER, text VARCHAR2(40));
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$m = oci_error();
trigger_error(htmlentities($m['message']), E_USER_ERROR);
}
$stid = oci_parse($conn,"INSERT INTO mytab (id, text) VALUES(:id_bv, :text_bv)");
$id = 1;
$text = "Data to insert ";
oci_bind_by_name($stid, ":id_bv", $id);
oci_bind_by_name($stid, ":text_bv", $text);
oci_execute($stid);
// Table now contains: 1, 'Data to insert '
?>
示例 #2 一次绑定多次执行
<?php
// Create the table with:
// CREATE TABLE mytab (id NUMBER);
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$m = oci_error();
trigger_error(htmlentities($m['message']), E_USER_ERROR);
}
$a = array(1,3,5,7,11); // data to insert
$stid = oci_parse($conn, 'INSERT INTO mytab (id) VALUES (:bv)');
oci_bind_by_name($stid, ':bv', $v, 20);
foreach ($a as $v) {
$r = oci_execute($stid, OCI_DEFAULT); // don't auto commit
}
oci_commit($conn); // commit everything at once
// Table contains five rows: 1, 3, 5, 7, 11
oci_free_statement($stid);
oci_close($conn);
?>
示例 #3 使用 foreach 循环绑定
<?php
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$m = oci_error();
trigger_error(htmlentities($m['message']), E_USER_ERROR);
}
$sql = 'SELECT * FROM departments WHERE department_name = :dname AND location_id = :loc';
$stid = oci_parse($conn, $sql);
$ba = array(':dname' => 'IT Support', ':loc' => 1700);
foreach ($ba as $key => $val) {
// 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]);
}
oci_execute($stid);
$row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS);
foreach ($row as $item) {
print $item."<br>\n";
}
oci_free_statement($stid);
oci_close($conn);
?>
示例 #4 在 WHERE 子句中绑定
<?php
$conn = oci_connect("hr", "hrpwd", "localhost/XE");
if (!$conn) {
$m = oci_error();
trigger_error(htmlentities($m['message']), E_USER_ERROR);
}
$sql = 'SELECT last_name FROM employees WHERE department_id = :didbv ORDER BY last_name';
$stid = oci_parse($conn, $sql);
$didbv = 60;
oci_bind_by_name($stid, ':didbv', $didbv);
oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_ASSOC)) != false) {
echo $row['LAST_NAME'] ."<br>\n";
}
// Output is
// Austin
// Ernst
// Hunold
// Lorentz
// Pataballa
oci_free_statement($stid);
oci_close($conn);
?>
示例 #5 使用 LIKE 子句绑定
<?php
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$m = oci_error();
trigger_error(htmlentities($m['message']), E_USER_ERROR);
}
// 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 SQL
oci_bind_by_name($stid, ":bv", $city);
oci_execute($stid);
oci_fetch_all($stid, $res);
foreach ($res['CITY'] as $c) {
print $c . "<br>\n";
}
// Output is
// South Brunswick
// South San Francisco
// Southlake
oci_free_statement($stid);
oci_close($conn);
?>
示例 #6 与 REGEXP_LIKE 绑定
<?php
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$m = oci_error();
trigger_error(htmlentities($m['message']), E_USER_ERROR);
}
// Find all cities that contain 'ing'
$stid = oci_parse($conn, "SELECT city FROM locations WHERE REGEXP_LIKE(city, :bv)");
$city = '.*ing.*';
oci_bind_by_name($stid, ":bv", $city);
oci_execute($stid);
oci_fetch_all($stid, $res);
foreach ($res['CITY'] as $c) {
print $c . "<br>\n";
}
// Output is
// Beijing
// Singapore
oci_free_statement($stid);
oci_close($conn);
?>对于少量固定数量的 IN 子句条件，请使用单独的绑定变量。运行时未知的值可以设置为 NULL。这允许所有应用程序用户使用单个语句，从而最大限度地提高 Oracle 数据库缓存效率。
示例 #7 在 IN 子句中绑定多个值
<?php
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$m = oci_error();
trigger_error(htmlentities($m['message']), E_USER_ERROR);
}
$sql = 'SELECT last_name FROM employees WHERE employee_id in (:e1, :e2, :e3)';
$stid = oci_parse($conn, $sql);
$mye1 = 103;
$mye2 = 104;
$mye3 = NULL; // pretend we were not given this value
oci_bind_by_name($stid, ':e1', $mye1);
oci_bind_by_name($stid, ':e2', $mye2);
oci_bind_by_name($stid, ':e3', $mye3);
oci_execute($stid);
oci_fetch_all($stid, $res);
foreach ($res['LAST_NAME'] as $name) {
print $name ."<br>\n";
}
// Output is
// Ernst
// Hunold
oci_free_statement($stid);
oci_close($conn);
?>
示例 #8 Binding a ROWID returned by a query
<?php
// Create the table with:
// CREATE TABLE mytab (id NUMBER, salary NUMBER, name VARCHAR2(40));
// INSERT INTO mytab (id, salary, name) VALUES (1, 100, 'Chris');
// COMMIT;
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$m = oci_error();
trigger_error(htmlentities($m['message']), E_USER_ERROR);
}
$stid = oci_parse($conn, 'SELECT ROWID, name FROM mytab WHERE id = :id_bv FOR UPDATE');
$id = 1;
oci_bind_by_name($stid, ':id_bv', $id);
oci_execute($stid);
$row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS);
$rid = $row['ROWID'];
$name = $row['NAME'];
// Change name to upper case & save the changes
$name = strtoupper($name);
$stid = oci_parse($conn, 'UPDATE mytab SET name = :n_bv WHERE ROWID = :r_bv');
oci_bind_by_name($stid, ':n_bv', $name);
oci_bind_by_name($stid, ':r_bv', $rid, -1, OCI_B_ROWID);
oci_execute($stid);
// The table now contains 1, 100, CHRIS
oci_free_statement($stid);
oci_close($conn);
?>
示例 #9 INSERT 时绑定 ROWID
<?php
// This example inserts an id & name, and then updates the salary
// Create the table with:
// CREATE TABLE mytab (id NUMBER, salary NUMBER, name VARCHAR2(40));
//
// Based on original ROWID example by thies at thieso dot net (980221)
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$m = oci_error();
trigger_error(htmlentities($m['message']), E_USER_ERROR);
}
$sql = "INSERT INTO mytab (id, name) VALUES(:id_bv, :name_bv)
RETURNING ROWID INTO :rid";
$ins_stid = oci_parse($conn, $sql);
$rowid = oci_new_descriptor($conn, OCI_D_ROWID);
oci_bind_by_name($ins_stid, ":id_bv", $id, 10);
oci_bind_by_name($ins_stid, ":name_bv", $name, 32);
oci_bind_by_name($ins_stid, ":rid", $rowid, -1, OCI_B_ROWID);
$sql = "UPDATE mytab SET salary = :salary WHERE ROWID = :rid";
$upd_stid = oci_parse($conn, $sql);
oci_bind_by_name($upd_stid, ":rid", $rowid, -1, OCI_B_ROWID);
oci_bind_by_name($upd_stid, ":salary", $salary, 32);
// ids and names to insert
$data = array(1111 => "Larry",
2222 => "Bill",
3333 => "Jim");
// Salary of each person
$salary = 10000;
// Insert and immediately update each row
foreach ($data as $id => $name) {
oci_execute($ins_stid);
oci_execute($upd_stid);
}
$rowid->free();
oci_free_statement($upd_stid);
oci_free_statement($ins_stid);
// Show the new rows
$stid = oci_parse($conn, "SELECT * FROM mytab");
oci_execute($stid);
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
var_dump($row);
}
oci_free_statement($stid);
oci_close($conn);
?>
示例 #10 为 PL/SQL 存储函数绑定
<?php
// Before running the PHP program, create a stored function in
// SQL*Plus or SQL Developer:
//
// CREATE OR REPLACE FUNCTION myfunc(p IN NUMBER) RETURN NUMBER AS
// BEGIN
// RETURN p * 3;
// END;
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message']), E_USER_ERROR);
}
$p = 8;
$stid = oci_parse($conn, 'begin :r := myfunc(:p); end;');
oci_bind_by_name($stid, ':p', $p);
// The return value 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, ':r', $r, 40);
oci_execute($stid);
print "$r\n"; // prints 24
oci_free_statement($stid);
oci_close($conn);
?>
示例 #11 为 PL/SQL 存储过程绑定参数
<?php
// Before running the PHP program, create a stored procedure in
// SQL*Plus or SQL Developer:
//
// CREATE OR REPLACE PROCEDURE myproc(p1 IN NUMBER, p2 OUT NUMBER) AS
// BEGIN
// p2 := p1 * 2;
// END;
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message']), E_USER_ERROR);
}
$p1 = 8;
$stid = oci_parse($conn, 'begin myproc(:p1, :p2); end;');
oci_bind_by_name($stid, ':p1', $p1);
// 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);
oci_execute($stid);
print "$p2\n"; // prints 16
oci_free_statement($stid);
oci_close($conn);
?>
示例 #12 绑定 CLOB 列
<?php
// Before running, create the table:
// CREATE TABLE mytab (mykey NUMBER, myclob CLOB);
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message']), E_USER_ERROR);
}
$mykey = 12343; // arbitrary key for this example;
$sql = "INSERT INTO mytab (mykey, myclob)
VALUES (:mykey, EMPTY_CLOB())
RETURNING myclob INTO :myclob";
$stid = oci_parse($conn, $sql);
$clob = oci_new_descriptor($conn, OCI_D_LOB);
oci_bind_by_name($stid, ":mykey", $mykey, 5);
oci_bind_by_name($stid, ":myclob", $clob, -1, OCI_B_CLOB);
oci_execute($stid, OCI_DEFAULT);
$clob->save("A very long string");
oci_commit($conn);
// Fetching CLOB data
$query = 'SELECT myclob FROM mytab WHERE mykey = :mykey';
$stid = oci_parse ($conn, $query);
oci_bind_by_name($stid, ":mykey", $mykey, 5);
oci_execute($stid);
print '<table border="1">';
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_LOBS)) {
print '<tr><td>'.$row['MYCLOB'].'</td></tr>';
// In a loop, freeing the large variable before the 2nd fetch reduces PHP's peak memory usage
unset($row);
}
print '</table>';
?>
示例 #13 绑定 PL/SQL BOOLEAN
<?php
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message']), E_USER_ERROR);
}
$plsql =
"begin
:output1 := true;
:output2 := false;
end;";
$s = oci_parse($c, $plsql);
oci_bind_by_name($s, ':output1', $output1, -1, OCI_B_BOL);
oci_bind_by_name($s, ':output2', $output2, -1, OCI_B_BOL);
oci_execute($s);
var_dump($output1); // true
var_dump($output2); // false
?>注释
警告
不要同时使用 addslashes() 和 oci_bind_by_name()，因为不需要引号。任何应用的引号都将写入数据库，因为 oci_bind_by_name() 逐字插入数据并且不会删除引号或转义字符。
注意:
如果将字符串绑定到
WHERE子句中的CHAR列，请记住 Oracle 对CHAR列使用空白填充的比较语义。PHP 变量使用空格填充到与成功的WHERE子句的列相同的宽度。
注意:
PHP
var参数是引用。某些形式的循环无法按预期工作：<?php
foreach ($myarray as $key => $value) {
oci_bind_by_name($stid, $key, $value);
}
?>这会将每个键绑定到 $value 的位置，因此所有绑定变量最终都指向最后一个循环迭代的值。应该使用以下内容：
<?php
foreach ($myarray as $key => $value) {
oci_bind_by_name($stid, $key, $myarray[$key]);
}
?>
参见
- oci_bind_array_by_name() - Binds a PHP array to an Oracle PL/SQL array parameter
- oci_parse() - 预处理用于执行的 Oracle 语句
add a note
User Contributed Notes 18 notes
abiyi2000 at yahoo dot com ¶
13 years ago
martin dot abbrent at ufz dot de ¶
8 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);
?>
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.
splintyg at gmail dot com ¶
7 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
dub357 at gmail dot com ¶
9 months ago
The note about the PHP var argument being a reference and some kinds of loops not working is very important here. However, you can make a foreach loop work if you create a temporary variable, use that in the bind and then unset it. For example:
<?php
foreach ($myarray as $key => $val) {
$value = $val;
oci_bind_by_name($stid, $key, $value);
unset($value);
}
?>
This binds each key to the location of $value, but when you unset it after binding, it can be set and used again.
https://www.php.net/manual/en/function.unset.php
charles dot fisher at arconic dot com ¶
3 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";
asui dot dev dot null at gmail dot com ¶
5 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.
splintyg at gmail dot com ¶
7 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
Anonymous ¶
7 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.
marki at trash-mail dot com ¶
8 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.)
marki at trash-mail dot com ¶
8 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.
xorinox at gmx dot ch ¶
10 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 );
?>
ajitsingh4u at gmail dot com ¶
16 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
}
?>
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;
Anonymous ¶
17 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
hfuecks at nospam dot org ¶
19 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
adrian dot crossley at hesa dot ac dot uk ¶
15 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);
?>
jjeffman at cpovo.net ¶
10 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.
