Jump to content

[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.

 

Link to comment
Share on other sites

  • 4 weeks later...

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.