Oracle & Stored Procedure

[Alf Stockton <alf@xxxxxxxxxxxxxx>]

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

Attachment: 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


-- 
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php