Home » Open Source » Programming Interfaces » php-oracle oci8 issue
php-oracle oci8 issue [message #224452] Wed, 14 March 2007 06:01 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-oracle oci8 issue [message #224577 is a reply to message #224452] Wed, 14 March 2007 12:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
01460, 00000, "unimplemented or unreasonable conversion requested"
// *Cause:
// *Action:
Re: php-oracle oci8 issue [message #224619 is a reply to message #224577] Wed, 14 March 2007 15:26 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://www.oracle.com/technology/tech/php/htdocs/php_troubleshooting_faq.html
Previous Topic: PHP Tablename Issue
Next Topic: PHP on Oracle - oci8 problem
Goto Forum:
  


Current Time: Thu Mar 28 10:48:33 CDT 2024