Jump to content


Photo

[SOLVED] Calling Oracle Packaged Stored Procedure


  • Please log in to reply
1 reply to this topic

#1 stockton

stockton
  • Members
  • PipPipPip
  • Advanced Member
  • 319 posts
  • LocationMidrand South Africa

Posted 04 December 2005 - 12:58 PM

I am failing in an attempt to access an Oracle database using the following php.

The calls I have tested are as follows:-

$query = "begin spPackage.spMemberDetails(:P_number,:cv_memberdetails); end;";
$stmt = OCIParse($conn, $query )
or die('Cannot parse query');
OCIBindByName($stmt,":P_number",$number, -1)
or die('First cannot bind variable');
OCIBindByName($stmt, ":cv_memberdetails", &$array, -1)
or die('Second cannot bind variable');

OR

$query = "begin spPackage.spMemberDetails(:P_number); end;";
$stmt = OCIParse($conn, $query )
or die('Cannot parse query');
OCIBindByName($stmt,":P_number",$number, -1)
or die('First cannot bind variable');

I get the following error message:-

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

begin spPackage.spMemberDetails(:P_number); end;
^

The 1st part of the stored procedure I am trying to use is as follows:-

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;

PROCEDURE spMemberDetails(
p_number cards.card_number%type, --varchar2
cv_memberdetails out memberdetail_cur_type);

Please tell me what I am not understanding and where I have gone wrong.
BTW I did not write the stored procedure.

Regards,
Alf Stockton   Programmer for rent.

#2 jerry_th

jerry_th
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 27 December 2005 - 01:53 PM

Oracle stored procedures uses strict type checking on parameters
and allow overlayed procedures, so whenever you make a call to a
stored procedure you must have matching argument types for the procedure.
If you try to call "xproc( a varchar(2))" with "xproc( :number_var)"
Oracle thinks this is a different procedure.
So if you are using a call depending on the argument position,
they must match on type and position.
Pocedures may have default values that you may omit if they are placed last
or if you are refering to your args by their names.
/Jerry




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users