PHP and Oracle Function and Procedures

By Sergey Skudaev

In this example I show how to create Oracle procedure or function and how to call them in PHP

The difference between functions and procedures is that Oracle functions returns only one value, while procedures can have multiple OUT parameters.

Let's say, we have a table:

CREATE TABLE instructors (
  instructorid NUMBER (11),
  last_name VARCHAR2 (20),
  first_name VARCHAR2 (20),
  email VARCHAR2 (50),
  phone VARCHAR2 (20),
  hiredate DATE,
  rate NUMBER (3),
CONSTRAINT pk_instructors PRIMARY KEY (instructorid)
);

To make instructorid auto incremental we create a sequence:

create sequence instructor _seq
start with 1
increment by 1
nomaxvalue;

Oracle Function example

This oracle function inserts a new record into the instructors table and returns the id of the inserted record. It takes in 10 parameters an returns one.

CREATE OR REPLACE FUNCTION insert_instructor (
pLast_name     IN  VARCHAR2,
pFirst_name  IN    VARCHAR2,
pEmail    IN     VARCHAR2,
pPhone IN   VARCHAR2,
pHiredate IN DATE,
pRate IN NUMBER,
pStreet IN VARCHAR2,
pCity IN VARCHAR2,
pState IN VARCHAR2,
pZip IN VARCHAR2
)
 return number is
Result number;

BEGIN

INSERT INTO instructors (instructorid, last_name, first_name, email, phone, hiredate, rate, street, city, state, zip))
	VALUES (
instructors_seq.nextval,
pLast_name,
pFirst_name,
pEmail, pPhone,
pHiredate, pRate,
pStreet, pCity,
pState, pZip)

RETURNING instructorid into Result;

RETURN (Result);

END insert_instructor;

Call insert_instructor function in PHP

<?php
$myuser="smithjohn ";
$mypassword="Practice2 ";
$mydb="(DESCRIPTION = (ADDRESS =
(PROTOCOL = TCP)(HOST = my_host)(PORT myport))
(CONNECT_DATA = (SERVICE_NAME = my_oracle_user.my_host)))";

if(!$myconn=oci_connect($myuser, $mypassword, $mydb))
{
    $e = oci_error();

echo "if not connection<br>";

  echo htmlentities($e['message']);
}
else
{

$query="BEGIN :result := INSERT_INSTRUCTOR(:last_name, :first_name, :email, :phone, :hiredate, :rate, :street, :city, :state, :zip); end;";

$stmt= oci_parse($myconn, $query);

$lastname='Willams';
$firstname='Bob';
$email="bobw@yahoo.com";
$phone="222-123-1234";
$hiredate="01-JAN-15";
$rate=80;
$street="423 Druid Rd."
$city="Clearwater";
$state="FL";
$zip="33256";

oci_bind_by_name($stmt, ":result", $result, 10);
oci_bind_by_name($stmt, ":lastname", $lastname);
oci_bind_by_name($stmt, ":firstname", $firstname);
oci_bind_by_name($stmt, ":phone", $phone);
oci_bind_by_name($stmt, ":hiredate", $hiredate);
oci_bind_by_name($stmt, ":rate", $rate);
oci_bind_by_name($stmt, ":street", $street);
oci_bind_by_name($stmt, ":city", $city);
oci_bind_by_name($stmt, ": state ", $ state);
oci_bind_by_name($stmt, ":zip", $zip);

$er=oci_execute($stmt);

if(!$er)
{
$e = oci_error($stmt);
echo $e['message'];
}

$ret=oci_commit ($myconn);

echo "Instructor ID=". $result."<br>";

} //if connected
?>

Oracle procedure example

The delete_instructor procedure takes IN 2 parameters: firstname and lastname and takes OUT 2 parameters: instructorid and zip of the deleted record.

CREATE OR REPLACE PROCEDURE delete_instructor(
plastname IN VARCHAR2, pfirstname IN VARCHAR2, pid OUT NUMBER, pzip OUT VARCHAR2)
IS

-- declare variable for OUT parameters
lv_id NUMBER;
lv_zip VARCHAR2(10);

BEGIN
DELETE
FROM instructors
WHERE lastname=plastname
AND firstname=pfirstname
returning instructorid, zip INTO lv_id, lv_zip;

pid:=lv_id;
pzip:=lv_zip;

RETURN;

END delete_instructor;

Call delete_instructor procedure with PHP

<?php

$sql2="BEGIN delete_instructor(:last_name, :first_name, :id, :zip); end;";

$istmt2 = oci_parse($myconn, $sql2);

$last_name='Barry';
$first_name='John';

oci_bind_by_name($istmt2, ":zip", $zip);
oci_bind_by_name($istmt2, ":id", $id);
oci_bind_by_name($istmt2, ":last_name", $last_name);
oci_bind_by_name($istmt2, ":first_name", $first_name);

$er2=oci_execute($istmt2);
   	 if(!$er2)
  	{
	$e = oci_error($stmt2);
	echo $e['message'];
	}

$ret2=oci_commit($myconn);

echo "Deleted instructorid=".  $id.", zip=".$zip."<br>";

?>

More examples of Calling Oracle function and procedures, you can find in my eBook "Learn PHP Programming by Example" on Amazon.com