Calling Oracle Stored Procedure in PHP

Okay, in this post we will discuss about PHP again. After connecting to Oracle, we can do some query here. Data Manipulation Language (DML) is the basic of SQL (Structured Query Language). But, what about DML in stored procedure? We will create it!

Before continuing, we must create table first. Just make a simple table. Here is the SQL code:

 create table mytable(
id number (3) not null,
name varchar2 (25) not null,
address varchar2 (25) ,
constraint pk_mytable primary key (id)
);

First, create insert procedure to the table. The parameters are the field we want to insert.

create or replace procedure insert_mytable (pid, pname, paddress)
begin
insert into my_table (id, name, address) values (pid, pname, paddress);
end;

Second, create update procedure to update the record of the table. It’s still using the same parameters.

create or replace procedure update_mytable (pid, pname, paddress)
begin
update my_table set id=pid, pname=name, paddress=adress where id=pid;
end;

The last is creating delete procedure. It is used to delete the data from the table. But, we only need one parameter here.

create or replace procedure delete_mytable (pid)
begin
delete from my_table where id=pid;
end;

The next step is calling procedures we’ve created in PHP. Input parameter we can get from a form in html view.

//connect to Oracle
include(“connection.php”);

//get input
$id = $_POST[‘id’];
$name = $_POST[‘name’];
$address = $_POST[‘address’];

//call stored procedured
$query = “BEGIN insert_mytable (:id, :name, :address); END;”;

//parse the query
$s = ociparse($c, $query);

//binding
ocibindbyname($s, ‘:id’, $id, 32 );
ocibindbyname($s, ‘:name’, $name, 32 );
ocibindbyname($s, ‘:address’, $address, 32 );

//execute the query
ociexecute($s);

Before calling the stored procedure, we must connect to Oracle first. It’s done with including connection.php (connection to Oracle has written in the last post). Get the input parameter and then call it. Don’t forget to parse and bind it. And the last step is executing the query. Okay, send me a email if you want to ask about this. Thanks…