--- Begin Message ---
With the attached php program I keep getting:-
Warning: ociexecute() [function.ociexecute]: OCIStmtExecute: ORA-06550:
line 1, column 7: PLS-00306: wrong number or types of arguments in call
to 'SPMEMBERDETAILS' ORA-06550: line 1, column 7: PL/SQL: Statement
ignored in C:\Program Files\Apache
Group\Apache2\htdocs\KioskCurrent\TI\GetMember.php on line 37
and in case it might help I have also attached the source from which the
stored procedure was created.
Please tell me what I have done wrong?
--
Regards,
Alf Stockton www.stockton.co.za
He draweth out the thread of his verbosity finer than the staple of his
argument.
-- William Shakespeare, "Love's Labour's Lost"
My email disclaimer is available at www.stockton.co.za/disclaimer.html
GetMember.php
Description: application/php
CREATE OR REPLACE PACKAGE "BIG"."SPPACKAGE" AS
type memberdetail_type is record (
r_mem_number members.mem_number%type,
r_mem_sname members.mem_sname%type,
r_mem_fname members.mem_fname%type,
r_mem_title members.mem_title%type);
type memberdetail_cur_type is ref cursor return memberdetail_type;
type tablesplayed_type is record (
r_trans_code transact.trans_code%type,
r_trans_table_name transact.trans_table_name%type);
type tablesplayed_cur_type is ref cursor return tablesplayed_type;
PROCEDURE spMemberDetails(
p_number cards.card_number%type, --varchar2
cv_memberdetails out memberdetail_cur_type);
PROCEDURE spTablesPlayed(
p_number cards.card_number%type, --varchar2
p_startdate varchar2,
p_enddate varchar2,
cv_tablesplayed out tablesplayed_cur_type);
PROCEDURE spGetMemberNumber(
p_number cards.card_number%type,
p_membernumber OUT members.mem_number%type);
PROCEDURE spGetSlotsHandle (
p_handle OUT transact.trans_turnover%type,
p_number cards.card_number%type,
p_startdate varchar2,
p_enddate varchar2);
PROCEDURE spGetTableHandle (
p_handle OUT transact.trans_turnover%type,
p_number cards.card_number%type,
p_tablegameprefix varchar2,
p_startdate varchar2,
p_enddate varchar2);
END sppackage;
/
show err
CREATE OR REPLACE PACKAGE BODY "BIG"."SPPACKAGE" as
PROCEDURE spMemberDetails(
p_number cards.card_number%type, --varchar2
cv_memberdetails out memberdetail_cur_type)
AS
v_membernumber members.mem_number%type; --varchar2(12)
BEGIN
spGetMemberNumber(p_number,v_membernumber);
open cv_memberdetails for
SELECT MEM_NUMBER, MEM_SNAME, MEM_FNAME, MEM_TITLE
FROM MEMBERS
WHERE MEM_NUMBER= v_membernumber;
END spMemberDetails;
PROCEDURE spTablesPlayed(
p_number cards.card_number%type, --varchar2
p_startdate varchar2,
p_enddate varchar2,
cv_tablesplayed out tablesplayed_cur_type)
AS
v_startdate varchar2(12);
v_starttime varchar2(12);
v_enddate varchar2(12);
v_endtime varchar2(12);
v_membernumber varchar2(12);
BEGIN
v_startdate := to_char(to_date(substr(p_startdate,0,8), 'DD-MM-RRRR'),'DD-MON
-RRRR');
v_enddate := to_char(to_date(substr(p_enddate,0,8), 'DD-MM-RRRR'),'DD-MON
-RRRR');
spGetMemberNumber(p_number, v_membernumber);
open cv_tablesplayed for
SELECT TRANS_CODE, TRANS_TABLE_NAME
FROM
(
select trans_code, trans_date, trans_time, trans_table_name
from TRANSACT
where TRANS_CODE = v_membernumber
and (trans_date >= v_startdate
and trans_date <= v_enddate)
)
-- WHERE TRANS_CODE = v_membernumber
where nvl(TRANS_TABLE_NAME,'X') != 'X'
and (
( to_date(to_char(TRANS_DATE, 'DDMMRRRR')||' '||
to_char(TRANS_TIME, 'HH24MISS'), 'DD-MM-RRRR HH24MISS'))
>= to_date(p_startdate,'DD-MM-RRRR HH24MISS')
AND
( to_date(to_char(TRANS_DATE, 'DDMMRRRR')||' '||
to_char(TRANS_TIME, 'HH24MISS'), 'DD-MM-RRRR HH24MISS'))
<= to_date(p_enddate,'DD-MM-RRRR HH24MISS')
);
END spTablesPlayed;
PROCEDURE spGetMemberNumber(
p_number cards.card_number%type,
p_membernumber OUT members.mem_number%type)
AS
BEGIN
SELECT CARD_MEMBER
into p_membernumber
FROM CARDS
WHERE CARD_NUMBER = p_number
AND trunc(CARD_DTO) >= trunc(sysdate);
exception
when no_data_found
then
SELECT MEM_NUMBER
into p_membernumber
FROM MEMBERS
WHERE MEM_NUMBER = p_number;
END spGetMemberNumber;
PROCEDURE spGetSlotsHandle (
p_handle OUT transact.trans_turnover%type,
p_number cards.card_number%type,
p_startdate varchar2,
p_enddate varchar2)
AS
v_startdate varchar2(12);
v_starttime varchar2(12);
v_enddate varchar2(12);
v_endtime varchar2(12);
v_membernumber members.mem_number%type := null;
BEGIN
v_startdate := to_char(to_date(substr(p_startdate,0,8), 'DD-MM-RRRR'),'DD-MON
-RRRR');
v_enddate := to_char(to_date(substr(p_enddate,0,8), 'DD-MM-RRRR'),'DD-MON
-RRRR');
spGetMemberNumber(p_number, v_membernumber);
SELECT SUM(TRANS_TURNOVER)
into p_handle
FROM
(
select TRANS_CODE, TRANS_DATE, TRANS_TIME, TRANS_TURNOVER, TRANS_TABLE_NA
ME
from TRANSACT
WHERE TRANS_CODE = v_membernumber
and (trans_date >= v_startdate
and trans_date <= v_enddate)
)
-- WHERE TRANS_CODE = v_membernumber
where nvl(TRANS_TABLE_NAME,'X') = 'X'
and (
( to_date(to_char(TRANS_DATE, 'DDMMRRRR')||' '||
to_char(TRANS_TIME, 'HH24MISS'), 'DD-MM-RRRR HH24MISS'))
>= to_date(p_startdate,'DD-MM-RRRR HH24MISS')
AND
( to_date(to_char(TRANS_DATE, 'DDMMRRRR')||' '||
to_char(TRANS_TIME, 'HH24MISS'), 'DD-MM-RRRR HH24MISS'))
<= to_date(p_enddate,'DD-MM-RRRR HH24MISS')
);
END spGetSlotsHandle;
PROCEDURE spGetTableHandle (
p_handle OUT transact.trans_turnover%type,
p_number cards.card_number%type,
p_tablegameprefix varchar2,
p_startdate varchar2,
p_enddate varchar2)
AS
v_startdate varchar2(12);
v_starttime varchar2(12);
v_enddate varchar2(12);
v_endtime varchar2(12);
v_membernumber members.mem_number%type;
BEGIN
v_startdate := to_char(to_date(substr(p_startdate,0,8), 'DD-MM-RRRR'),'DD-MON
-RRRR');
v_enddate := to_char(to_date(substr(p_enddate,0,8), 'DD-MM-RRRR'),'DD-MON
-RRRR');
spGetMemberNumber(p_number, v_membernumber);
SELECT SUM(TRANS_TURNOVER)
into p_handle
FROM
(
select trans_code, trans_date, trans_time, trans_turnover, trans_table_na
me
from TRANSACT
WHERE TRANS_CODE = v_membernumber
and ( trans_date >= v_startdate
and trans_date <= v_enddate)
)
-- WHERE TRANS_CODE = v_membernumber
where TRANS_TABLE_NAME LIKE p_tablegameprefix||'%'
and (
( to_date(to_char(TRANS_DATE, 'DDMMRRRR')||' '||
to_char(TRANS_TIME, 'HH24MISS'), 'DD-MM-RRRR HH24MISS'))
>=
to_date(p_startdate,'DD-MM-RRRR HH24MISS')
AND
( to_date(to_char(TRANS_DATE, 'DDMMRRRR')||' '||
to_char(TRANS_TIME, 'HH24MISS'), 'DD-MM-RRRR HH24MISS'))
<= to_date(p_enddate,'DD-MM-RRRR HH24MISS')
);
END spGetTableHandle;
END spPackage;
/
show err
--- End Message ---
