ora_bind

描述

bool ora_bind ( resource cursor, string PHP_variable_name, string SQL_parameter_name, int length [, int type] )

该函数将一个 PHP 变量与一个 SQL 参数绑定。 SQL 参数必须使用 ":name" 的形式。在该函数可选的 type 参数中，可以定义 SQL 参数类型。SQL 参数的类型是输入/输出 (0，默认)、输入(1) 、输出 (2) 中的一种。从 PHP 3.0.1 开始，可以使用常量 ORA_BIND_INOUT，ORA_BIND_IN 和 ORA_BIND_OUT 代替数字。

如果成功则返回 TRUE，失败则返回 FALSE。错误的细节能够使用 ora_error() 和 ora_errorcode() 函数取得。

ora_bind() 必须在 ora_parse() 之后和 ora_exec() 之前调用。输入值由绑定的 PHP 变量指派。在调用 ora_exec() 函数之后，如果绑定的 PHP 变量有值输出，则 PHP 变量将等于该值。
例子 1. ora_bind() 函数范例
<?php ora_parse($curs, "declare tmp INTEGER; begin tmp := :in; :out := tmp; :x := 7.77; end;"); ora_bind($curs, "result", ":x", $len, 2); ora_bind($curs, "input", ":in", 5, 1); ora_bind($curs, "output", ":out", 5, 2); $input = 765; ora_exec($curs); echo "Result: $result<br />Out: $output<br />In: $input"; ?>

add a note User Contributed Notes

cjbj at hotmail dot com
20-Oct-2003 08:01


Ahmed's solution is based on the sample given to him in http://forums.oracle.com/forums/thread.jsp?forum=178&thread=206340



Here's a similar example answering the question

"How do I get the return value from a PL/SQL function?"



<?php



/*

  Sample using "oracle" driver to get the value of a PL/SQL function.



  Before running this script create this function using SQL*Plus:



  create or replace function myfunc(myparam IN varchar2) return varchar2 as

  begin

   return myparam;

  end;



  Tested with PHP 4.3.3 against Oracle 9.2



*/



$my_db_conn = ora_logon("scott@mydb", "tiger");



$my_cursor = ora_open($my_db_conn);



$sql = "begin :mybindvar := myfunc('abcde'); end;";



ora_parse($my_cursor, $sql, 0);



$r = ora_bind($my_cursor, "mybindvar", ":mybindvar", 5, 1);



// Set the OUT bind variable to anything (?), else get:

//    Warning: Can't find variable for parameter

//    Notice: Undefined variable: mybindvar

$mybindvar = NULL;



ora_exec($my_cursor);



print 'Return value is: '. $mybindvar;



?>

ahmed dot adaileh at vaillant dot de
17-Oct-2003 02:38


It was hard a little bit to understand the example above. Since it did not work properly :(

I managed to get a similar one running, the whole example is as following:



<?php

 

$connection = ora_logon("user@DBname", "password");

$cursor = ora_open($connection);

ora_commitoff($connection);

 

$cursor=ora_parse($cursor, "begin user.user_create (:name,:surname,:street,:country,:postal,:city,:tel,:mail,:number); end;");

 

$name        = 'a';

$surname    = 'a';

$street        = 'a';

$country      = 'a';

$postal        = 'a';

$city            = 'a';

$tel             = 'a';

$mail           = 'a';



ora_bind($cursor, "name", ":name", 32, 1);

ora_bind($cursor, "surname", ":surname", 32, 1);

ora_bind($cursor, "street", ":street", 32, 1);

ora_bind($cursor, "country", ":country", 32, 1);

ora_bind($cursor, "postal", ":postal", 32, 1);

ora_bind($cursor, "city", ":city", 32, 1);

ora_bind($cursor, "tel", ":tel", 32, 1);

ora_bind($cursor, "mail", ":mail", 32, 1);

ora_bind($cursor, "number", ":number", 32, 2);



// The following step is to charge the output variable with an intial value (Actually still doesn't know why? but it works like this ;-)) ) 



$okdnr = "a";

 

ora_exec($cursor);

 

print "okdnr is $okdnr";

 

?>



The called stored procedure is as following:

CREATE OR REPLACE procedure customer_create

(

iname in varchar2,

insurname in varchar2,

instreet in varchar2,

incountry varchar2,

inpostal in varchar2,

incity in varchar2,

intel in varchar2,

inemail in varchar2,

okdnr out varchar2)

is

begin

.....

..... SOME CODE ....

.....

okdnr:='something_to_output';

commit;

end customer_create;

/



The most important part of all this is the ora_bind function.  Becarefull while writing the syntax regarding the definition wether the variable is an INPUT (1) variable or an OUTPUT variable (2).



Hope this help someone!!

mukund at ziplip dot com
27-Mar-2001 09:23


Inputting integers using ORA_BIND.


   There is a potential problem in binding oracle integers using php. PHP supports only 32 bit integers where as oracle supports 128. so when you  are trying to bind an 128 bit oracle integer, php only interprets the first 32 bits. This results in undesired behaviour. The easiest way around it is not to use bind operations and instead do the whole conversion during the parsing phase. This means you have to parse for each input. A more optimal solution would be to change the oracle inputs to be varchar2 and let oracle deal with the type conversions.

koos at kzdoos dot xs4all dot nl
29-Feb-2000 01:28


The Oracle parser doesn't like DOS line endings (cr/lf). Save as Unix file (lf line ending only).

koos at kzdoos dot xs4all dot nl
08-Jan-2000 03:39


Passing variables of Oracle type 'DATE' will fail. Just convert them to a string with all the relevant details and convert it back when needed in Oracle, using TO_CHAR and TO_DATE calls in Oracle.

castle at ruc dot dk
30-Dec-1999 02:50


ora_bind looks for the bind variables globally. If your are using ora_bind


from within a class you'll have to delcare the bind variables globally and reference them using eg. $GLOBAL["myVar"].

add a note