Show how to access Oracle stored procedure from PHP via OCI8 extension.

Attendees:

Oracle Developer, PHP Developer

Prerequisites:

1. Oracle Database 8.1.7 or higher
2. PHP engine 4.2.x with OCI8 extension

Assumption:

The reader should have basic knowledge on SQL command, PHP and OCI8 extension.

Steps to do:

1. Create table
2. Insert data
3. Create stored procedure/function (single row)
4. Check stored procedure status
5. Call stored procedure/function
6. Pack stored procedure/function to package (multi row)
7. Call package

Details:

1. Create table

CREATE TABLE myfriends(
fid number(2),
fname varchar2(30),
ftel varchar2(20),
fbirthday date,
gid varchar2(3)
);

CREATE TABLE mygroups(
gid number(2),
gname varchar2(30),
gdesc varchar2(50)
);

2. Insert data

insert into myfriends values(1,'teddy','111111',to_date('10-10-1970','DD-MM-YYYY'),1);
insert into myfriends values(2,'nicole','333333',to_date('10-10-1975','DD-MM-YYYY'),1);
insert into myfriends values(3,'tyranno','333333',to_date('10-12-1969','DD-MM-YYYY,'),2);
insert into myfriends values(4,'sefjl','444444',to_date('10-09-1970','DD-MM-YYYY'),2);
insert into myfriends values(5,'fuju','555555',to_date('10-10-1970','DD-MM-YYYY'),1);
insert into myfriends values(6,'kouprex','666666',to_date('10-10-1975','DD-MM-YYYY'),2);
insert into myfriends values(7,'cupid','777777',to_date('10-12-1975','DD-MM-YYYY'),1);
insert into myfriends values(8,'runma','888888',to_date('10-09-1975','DD-MM-YYYY'),1);
insert into myfriends values(9,'spidix','888888',to_date('10-09-1970','DD-MM-YYYY'),2);

insert into mygroups values(1,'SALES','Sales group');
insert into mygroups values(2,'SUPPORTS','Supports group');

commit;

3. Create stored procedure/function (single row)

3.1 Stored procedure

CREATE OR REPLACE PROCEDURE get_single_row_details(var_fname in varchar2, var_ftel out varchar2)
IS
var_temp_tel varchar2(20);
BEGIN
SELECT ftel
INTO var_temp_tel
FROM myfriends
WHERE fname = var_fname;

var_ftel := var_temp_tel;
return;
END;

3.2 Stored function

CREATE OR REPLACE FUNCTION count_friends (in_group_id IN mygroups.gid%type)
RETURN number
IS
var_temp_count NUMBER(2);
BEGIN
select count(*) into var_temp_count from myfriends where gid = in_group_id;
return var_temp_count;
END;

4. Check stored procedure status

select object_name, status from user_objects where object_name like '%GET_%' or object_name like '%COUNT_%';

5. Call stored procedure/function

5.1 By SQL*Plus

COLUMN FID HEADING Number
COLUMN FNAME HEADING Name
COLUMN FTEL HEADING Telephone
COLUMN FBIRTHDAY HEADING Birthday
COLUMN GNAME HEADING Group
SET AUTOPRINT ON

-- call stored procedure
VARIABLE RETVAL VARCHAR2(20)
EXEC get_single_row_details('teddy',:RETVAL );

-- call stored function #1
variable myvar number
exec :myvar := count_friends(1);
print :myvar

-- call stored function #2
select count_friends(1) from dual;

5.2 By PHP

-- call stored procedure
<?php
:
$query = "begin get_single_row_details('$myFriendName', :var_ftel); end;";

echo "Connection is " . $con;
echo '<br>';

$stmt = OCIParse($con, $query) or die ('Can not parse query');
OCIBindByName($stmt,":var_ftel", &$myFriendTel, 20) or die ('Can not bind variable');
OCIExecute($stmt) or die ('Can not Execute statment');
:
?>

-- call stored function
<?php
:
$query = "select count_friends(:group_id) MCOUNT from dual";
echo "Connection is " . $con;
echo '<br>';

$stmt = OCIParse($con, $query) or die ('Can not parse query');
OCIBindByName($stmt,":group_id", &$gid, 20) or die ('Can not bind variable');

$gid=1; // for gid =1 or 2
OCIExecute($stmt) or die ('Can not Execute statment');

while (ocifetch($stmt)){
echo '<br>'.$con." <".ociresult($stmt,"MCOUNT").">\n\n";
$_friends=ociresult($stmt,"MCOUNT");
echo '<br>'.$con."----done\n\n";
}
:
?>

6. Pack stored procedure/function to package (multi rows)

CREATE OR REPLACE PACKAGE myfriends_data AS
TYPE MyFriendsRecTyp IS RECORD (
fid myfriends.fid%type,
fname myfriends.fname%type,
ftel myfriends.ftel%type,
fbirthday myfriends.fbirthday%type,
gid myfriends.gid%type
);

TYPE MyFriendsCurTyp IS REF CURSOR RETURN MyFriendsRecTyp;

PROCEDURE get_single_row_details(var_fname in varchar2,var_ftel out varchar2);
PROCEDURE get_multi_rows_details(var_gid IN NUMBER,cv_myfriends IN OUT MyFriendsCurTyp);
FUNCTION count_friends(in_group_id IN mygroups.gid%type) Return Number;

END myfriends_data;

CREATE OR REPLACE PACKAGE BODY myfriends_data AS
PROCEDURE get_single_row_details(var_fname in varchar2, var_ftel out varchar2)
IS
var_temp_tel varchar2(20);
BEGIN
SELECT ftel
INTO var_temp_tel
FROM myfriends
WHERE fname = var_fname;

var_ftel := var_temp_tel;
return;
END get_single_row_details;

PROCEDURE get_multi_rows_details(var_gid IN NUMBER, cv_myfriends IN OUT MyFriendsCurTyp)
IS
BEGIN
OPEN cv_myfriends FOR
SELECT fid, fname, ftel, fbirthday,mf.gid FROM myfriends mf, mygroups mg
WHERE mf.gid = var_gid AND mf.gid = mg.gid
ORDER BY fid;
END get_multi_rows_details;

FUNCTION count_friends (in_group_id IN mygroups.gid%type)
RETURN number
IS
var_temp_count NUMBER(2);
BEGIN
select count(*) into var_temp_count from myfriends where gid = in_group_id;
return var_temp_count;
END count_friends;

END myfriends_data;

7. Call package

7.1 By SQL*Plus

COLUMN FID HEADING Number
COLUMN FNAME HEADING Name
COLUMN FTEL HEADING Telephone
COLUMN FBIRTHDAY HEADING Birthday
COLUMN GNAME HEADING Group
SET AUTOPRINT ON

-- call stored procedure
VARIABLE RETVAL VARCHAR2(20)
VARIABLE cv REFCURSOR

EXEC myfriends_data.get_single_row_details('teddy',:RETVAL );
EXEC myfriends_data.get_multi_rows_details(1,:cv );

-- call stored function #1
variable myvar number
exec :myvar := myfriends_data.count_friends(1);
print :myvar

-- call stored function #2
select myfriends_data.count_friends(1) from dual;

7.2 By PHP

<?php
:
$stmt = OCIParse($conn,"begin myfriends_data.get_multi_rows_details(:group_id, :friend_cv); end;");

OCIBindByName($stmt,":group_id",&$gid,32);
OCIBindByName($stmt,":friend_cv",&$curs,-1,OCI_B_CURSOR);

$gid=1; // for gid = 1 only
ociexecute($stmt);
ociexecute($curs);

while (OCIFetchInto($curs,&$friend_cv )) {
echo "<pre>";
print_r($friend_cv );
echo "</pre>";
}
:
?>

Complete sample code:

References:

More Information:

N/A

Keywords:

PHP, OCI8, Extenstion, OCI, Oracle, database, call, stored procedure, stored function, single row, multi rows, package