Home » Open Source » Programming Interfaces » PHP on Oracle
PHP on Oracle [message #224427] Wed, 14 March 2007 04:31 Go to next message
damian_one
Messages: 21
Registered: March 2007
Junior Member
hello,

I am a MSc student and writing my MSc. Thesis.

I am developing an application using:
1. Oracle 10g R2
2. Windows XP Service Pack 2.
3. PHP 5.1.6
4. Apache Web Server


I am trying to execute a stored procedure (from a package) from a PHP script.

Necessary tables:
1.
create table porchetwork
(
porchetworkid number,
dbuser_id number,
wrk_role varchar2(20),
work_start date,
work_end date,
postingname varchar2(20),
contactid number,
work_mgr varchar2(20),
rate_id number,
constraint pk_porchetwork_01
primary key (porchetworkid),
constraint fk_porchetwork_01
foreign key (dbuser_id) references porchetusers (dbuser_id),
constraint fk_porchetwork_02
foreign key (contactid) references porchetcontact (contactid),
constraint fk_porchet_work_03
foreign key (rate_id) references rate_type (rate_id)
)
/

2.
create table porchetcontact
(
contactid number,
contact_type varchar2(10) not null,
address1 varchar2(50),
address2 varchar2(40),
city_town varchar2(30),
state_county varchar2(25),
postcode varchar2(12),
country varchar2(25),
telephone varchar2(30),
fax varchar2(30),
email varchar2(80),
website varchar2(80),
dbuser_id number,
constraint pk_porchetcontact_01
primary key (contactid),
constraint fk_porchetcontact_01
foreign key (dbuser_id) references porchetusers (dbuser_id),
constraint ck_porchetcontact_01
check (contact_type in ('CONSULTANT','POSTING','MANAGER'))
)
/




STORED PROCEDURE:
/*10th procedure*/
procedure prcRegisterWork(vuser in varchar2,
vcontract in varchar2,
vaddr1 in varchar2,
vaddr2 in varchar2,
vcity in varchar2,
vstate in varchar2,
vpcode in varchar2,
vcountry in varchar2,
vtel in varchar2,
vfax in varchar2,
vemail in varchar2,
vwebsite in varchar2,
vstart in varchar2,
vend in varchar2,
vwrkrole in varchar2,
vworkmgr in varchar2,
v_rate in number,
vp_type in varchar2,
vmessage out varchar2)
as

vstartdate porchet.porchetwork.work_start%type;
venddate porchet.porchetwork.work_end%type;
vuser_id number;
seq_val number;
vrate rate_type.rate_id%type;

begin

vstartdate:= to_date(vstart);
venddate:= to_date(vend);

select a.dbuser_id into vuser_id
from porchetusers a, all_users b
where a.user_id = b.user_id
and b.username = upper(vuser);

select porchetcontact_seq.nextval into seq_val
from dual;

select rate_id into vrate
from rate_type
where rate_value = v_rate
and rate_type = upper(vp_type);

if ((vuser is null)
or (vcontract is null)
or (vaddr1 is null)
or (vaddr2 is null)
or (vcity is null)
or (vstate is null)
or (vpcode is null)
or (vcountry is null)
or (vtel is null)
or (vfax is null)
or (vemail is null)
or (vwebsite is null)
or (vstart is null)
or (vend is null)
or (vwrkrole is null)
or (vworkmgr is null)
or (v_rate is null)
or (vp_type is null)) then
vmessage:='All fields required!';

else

insert into porchetcontact(contactid,contact_type,address1,address2,city_town,state_county,postcode,country,telephone,fax,email,website,dbuser_id )
values(seq_val,'POSTING',upper(vaddr1),upper(vaddr2),upper(vcity),upper(vstate),upper(vpcode),upper(vcountry),upper(vtel),upper(vfax) ,lower(vemail),lower(vwebsite),vuser_id);

insert into porchetwork(porchetworkid,dbuser_id,wrk_role,work_start,work_end,postingname,contactid,work_mgr,rate_id) values(porchetwork_seq.nextval,vuser_id,upper(vwrkrole),vstartdate,venddate,upper(vcontract),seq_val,upper(vworkmgr),vrate);
vmessage:='Details Registered';

end if;

commit;

return;

end prcRegisterWork;


PHP SCRIPT
<?php
session_start();
include('disp_func.php');

$query='BEGIN porchet.porchet_pkg.prcRegisterWork(:vuser,:vcontract,:vaddr1,:vaddr2,:vcity,:vstate,:vpcode,:vcountry,:vtel,:vfax,:vemail,:vweb,:vs_ dt,:ve_dt,:w_role,:w_mgr,:vrate,:vtype,:vmess); END;';

$vcontract=$_POST['postingname'];
$vaddr1=$_POST['addr1'];
$vaddr2=$_POST['addr2'];
$vcty=$_POST['city_town'];
$vst=$_POST['state_county'];
$vpcd=$_POST['postcode'];
$vcnty=$_POST['country'];
$vtel=$_POST['telephone'];
$vfax=$_POST['fax'];
$veml=$_POST['email'];
$vweb=$_POST['website'];
$vstday=$_POST['start_date_day'];
$vstmnth=$_POST['start_date_month'];
$vstyr=$_POST['start_date_year'];
$vstartdate=$vstday.'-'.$vstmnth.'-'.$vstyr;
$vendd=$_POST['end_date_day'];
$vendmnt=$_POST['end_date_month'];
$vendyr=$_POST['end_date_year'];
$vendate=$vendd.'-'.$vendmnt.'-'.$vendyr;
$vwrkr1=$_POST['work_role'];
$vwrkmgr=$_POST['mgr'];
$v_rate=$_POST['pay_rate'];
$vtyp=$_POST['pay_type'];

$vusername=$_SESSION['session_username'];
$vpassword=$_SESSION['session_password'];

$get_all = array(':vuser' => $vusername, ':vcontract' => $vcontract, ':vaddr1' => $vaddr1, ':vaddr2' => $vaddr2, ':vcity' => $vcty, ':vstate' => $vst, ':vpcode' => $vpcd, ':vcountry' => $vcnty, ':vtel' => $vtel, ':vfax' => $vfax, ':vemail' => $veml, ':vweb' => $vweb, ':vs_dt' => $vstartdate, ':ve_dt' => $vendate, ':w_role' => $vwrkrl, ':w_mgr' => $vwrkmgr, ':vrate' => $v_rate, ':vtype' => $vtyp, ':vmess' => $vmess);

$conn = oci_connect($vusername,$vpassword);
$stmt=oci_parse($conn, $query);

foreach ($get_all as $key => $val)
{
OCIBindByName($stmt, $key, $get_all[$key], 32);
}

oci_execute($stmt);


do_html_header($vmess, 1, '');

do_html_footer();

?>




ERROR MESSAGE:

Warning: oci_execute() [function.oci-execute]: OCI_NO_DATA in C:\Program Files\Apache Group\Apache2\test\porchet_rel\reg_work2.php on line 44



Also, I had rewritten the PHP script to call oci_bind_by_name for each stored procedure and using the database table column length as the php value but I was getting:
ERROR: ORA-01460.

Please help as I am stuck and have searched everywhere.

Will REALLY appreciate.

THANKS in advance.


Regards,
Ade.
Re: PHP on Oracle [message #225112 is a reply to message #224427] Sat, 17 March 2007 15:13 Go to previous messageGo to next message
najehas
Messages: 10
Registered: March 2007
Junior Member
hi
it seems that the php could not connect correctly to oracle
did you check that first and ensure your php configuration
Re: PHP on Oracle [message #225113 is a reply to message #224427] Sat, 17 March 2007 15:21 Go to previous messageGo to next message
BlackSwan
Messages: 23189
Registered: January 2009
Senior Member
01460, 00000, "unimplemented or unreasonable conversion requested"

Hmm...
My advice is to "1st make it work, then make it fancy."
Start small WITHOUT passing arguments. Make it work.
Get it to work with a single argument; & then expand as required.
Part of your challenge is to separate PHP problems from Oracle problems.
Re: PHP on Oracle [message #225170 is a reply to message #224427] Sun, 18 March 2007 14:50 Go to previous message
damian_one
Messages: 21
Registered: March 2007
Junior Member
Hi,

Thanks for the response. I eventually got it to work. It had nothing to do with the PHP Database Connection. I had PHP error reporting turned to E_STRICT. I used the function 'error_reporting(E_ERROR | E_WARNING | E_PARSE);' in my script and it works fine.

Thanks.
Previous Topic: PHP on Oracle - oci8 problem
Next Topic: SQL DBI Error
Goto Forum:
  


Current Time: Sun Dec 28 07:46:35 CST 2014

Total time taken to generate the page: 0.10539 seconds