Call Oracle Stored Function using php

Concider the following function code :

create or replace function notify ( pname in varchar2 ) 

return varchar2 

is 

massage varchar2(100); 

begin

     massage := 'hello '||pname||' time is '||sysdate ; 

     return massage ; 

end; 

/

Now write php code to execute the function and catch the return value .

1.  At first connect to oracle server using the following lines

$targetAddress = "localhost/XE"; // ipAddress / servicename or sid   

$username = "hello" ;   

$password = "hello" ;   

//this line of code connects you to desired database   

$conn = oci_connect($username , $password , $targetAddress ); 

 (change the connect parameters according to yours.)

2.  Then prepare a statement using oci_parse() 

$query = oci_parse($conn , "select notify(:pname) message from dual");

3.  Lets declare a php variable which we will use as the function parameter .

$person = "Mike" ;

 

4.  after that bind the php variable with the prepared statement   $query  

using oci_bind_by_name() function 

oci_bind_by_name($query ,":pname",$person , 30 , SQLT_CHR );

5.  now execute the statement using oci_execute()

oci_execute($query);

6.  Now it's time to retrieve the value returned by the function .Like most other result retrieving process its time to fetch and find the value of a particular column  ( in this case it will be "MESSAGE" , we aliased it in the oci_parse() function )

using functions oci_fetch_array() and oci_result() we get our desired result .

while( oci_fetch_array($query))   

{        

  //retrives the value of the specified field from the row fetched by oci_fetch_array()        

  echo "
Notifier: " . oci_result($query,"MESSAGE");   

} 

Alternative way:

before step 5 do the following :

define a variable to get the return value by oci_define_by_name().

oci_define_by_name($query ,"MESSAGE",$message);

Then do step 5 .

after that instead of doing step 6 do the following . oci_fetch() is used instead of oci_fetch_array()

while( oci_fetch($query))  

 {        

      echo "Notifier: " . $message ;  

 }