Jump to content

Archived

This topic is now archived and is closed to further replies.

stockton

[SOLVED] Calling Oracle Packaged Stored Procedure

Recommended Posts

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.

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.