Oracle Stored Procedure

[HappyCamper]

I'm currently trying to connect to a oracle database and call a stored procedure through PHP.  I don't manage the oracle database, rather I've supposedly been granted access to certain stored procedures. 

The stored procedure I'm trying to call right now gives me this error:

 

oci_execute(): ORA-06550: line 1, column 7: PLS-00201: identifier 'MCHEKID.CHECK_ID' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored

 

From what I've been reading online it seems that it might be that I have to be explicitly granted access, rather than through a role.  Which implies that the issue lies with the oracle DBAs. I was hoping someone could shed some light on this and could give me some ideas before I ask the DBA's if they've set it up correctly.

 

Thanks for the help!

 

PHP CODE

 

 

          $conn = oci_connect('xxxxxxxxxxxxx','xxxxxxxxxxxxxxxx', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') or die;

 

 

            $sql = 'BEGIN MCHEKID.CHECK_ID(_id, _last_name, _birth_date, _gender, _ret_id, _return_status, _return_msg); END;';

 

            $stmt = oci_parse($conn,$sql);

 

            //  Bind the input parameter

            oci_bind_by_name($stmt,'_id',$id,32);

            oci_bind_by_name($stmt,'_last_name',$lname,32);

            oci_bind_by_name($stmt,'_birth_date',$birthdate,32);

            oci_bind_by_name($stmt,'_gender',$gender,32);

 

            // Bind the output parameter

            oci_bind_by_name($stmt,"_ret_id",$ret_id, 32);

            oci_bind_by_name($stmt,"_return_status",$ret_stat, 32);

            oci_bind_by_name($stmt,"_return_msg",$ret_msg, 32);

 

            // Assign a value to the input

            $id = '1111111111111';

            $lname = 'Connery';

            $birthdate = '25-AUG-1930';

            $gender = 'M';

 

 

            oci_execute($stmt);

 

 

STORED PROCEDURE

 

 

PROCEDURE MCHEKID.CHECK_ID(p_id      IN VARCHAR2, --ID

                    p_last_name    IN VARCHAR2, --Last Name

                    p_birth_date    IN VARCHAR2, --Birthdate format DD-MON-YYYY

                    p_ret_id        OUT VARCHAR2,-- Return alt id

                    P_return_status OUT NUMBER,  --Status 0 = Success

                    p_return_msg    OUT VARCHAR2 --Error reason or SUCCESS

          );

 

 

 

 

[HappyCamper]

So we established my account rights to the stored procedure, now I'm getting the error

 

oci_execute(): ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'CHECK_ID' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

 

But i don't see where I'm calling the incorrect values/ types.

[HappyCamper]

So everything is happy now.

 

1st issue-> Was solved by having the sys admins change my user account to have explicit executable access instead of only role access to the stored procedure.

 

2nd issue-> Was solved by realizing that the sys admins had updated the stored procedure and I was now passing the incorrect amount of parameters.

 

Hope this helps someone else.