Wednesday, June 20, 2007

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…

3 comments:

Anonymous said...

I see you are using the PHP 4 style "ocibindbyname" and not the PHP 5 "oci_bind_by_name" names. There is nothing wrong with that, since both continue to work in PHP 5.

However, if you are using PHP 4 you really, really, really should upgrade the OCI8 driver at least. The version of OCI8 that is in PHP 4 (even in the latest version of PHp 4) is very old and unstable. Steps to upgrade the extension are given in
the free Underground PHP & Oracle Manual at http://www.oracle.com/technology/tech/php/pdf/underground-php-oracle-manual.pdf

Stu said...

Thank you very much Chis, I'm a beginner not only in PHP but also in Oracle. Thanks for the manual.

Cempluk Story said...

dimarahin km yah ma mr chris jones..hehehe...sabar om, dia temen saya..jgn macem2 loh ma hudan ini.