Home » Open Source » Programming Interfaces » PHP on Oracle - oci8 problem
PHP on Oracle - oci8 problem [message #224428] Wed, 14 March 2007 04:35 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 - oci8 problem [message #224478 is a reply to message #224428] Wed, 14 March 2007 07:19 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

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


Might give you a no_data_found exception.

Also I found

Quote:
If you get ora-01460 you might want to check whether the OCIBindByName was done with -1 on a date field, which on bind time (not execute time) was bound to a php variable with size 0. (e.g.: $var=""; bind var to date with length -1; loop; $var=realdate; execute will give ora-01460 -> unreasonable or unimplemented conversion). Changing the initial $var to a good length, or do the bind with the right size of your date in string format representation instead of -1 will solve it.

[Updated on: Wed, 14 March 2007 07:23]

Report message to a moderator

Re: PHP on Oracle - oci8 problem [message #224494 is a reply to message #224478] Wed, 14 March 2007 07:51 Go to previous messageGo to next message
damian_one
Messages: 21
Registered: March 2007
Junior Member
Thanks for the response.

I use the vrate code internally in the stored procedure to input a foreign key (for 1 to many) relationship.

$v_rate=$_POST['pay_rate'];
//this is the value passed from the form and used by the 'select into' statement in the where clause passed as v_rate in the procedure, and
$vtyp=$_POST['pay_type'];
//is passed from the form used in the 'select into as vp_type.

I have prepopulated rows inputs in the rate_type table. This are exactly what I pass into the PHP script from the html form and I still come up with the error.


SQL> select * from rate_type;

RATE_ID RATE_NAME RATE_T RATE_VALUE
---------- --------- ------ ----------
1 DAILY DAILY 575


Desc rate_type

Name Null? Type
----------------------------------------- -------- -------------
RATE_ID NOT NULL NUMBER
RATE_NAME VARCHAR2(9)
RATE_TYPE VARCHAR2(6)
RATE_VALUE NUMBER(6,2)

I saw the reference you sent as regards the ORA-01460 but had already changed my oci_bind_by_name value to the appropriate column length but still get an OCI_NO_DATA.

Thanks.
Re: PHP on Oracle - oci8 problem [message #224738 is a reply to message #224494] Thu, 15 March 2007 05:08 Go to previous messageGo to next message
damian_one
Messages: 21
Registered: March 2007
Junior Member
Hi,

This is to inform you that I have solved the OCI8 problem.

It was my php error_reporting configuration that was on 'E_STRICT' I used this is "error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE);" in my php script to define errors to be reported. The script works fine now.

Thanks.

Ade.
icon14.gif  Re: PHP on Oracle - oci8 problem [message #224824 is a reply to message #224738] Thu, 15 March 2007 10:54 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

No Message Body
Previous Topic: php-oracle oci8 issue
Next Topic: PHP on Oracle
Goto Forum:
  


Current Time: Thu Mar 28 13:26:06 CDT 2024