Jump to content

Oracle Stored Procedure


Recommended Posts

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(:P_id, :P_last_name, :P_birth_date, :P_gender, :P_ret_id, :P_return_status, :P_return_msg); END;';

 

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

 

            //  Bind the input parameter

            oci_bind_by_name($stmt,':P_id',$id,32);

            oci_bind_by_name($stmt,':P_last_name',$lname,32);

            oci_bind_by_name($stmt,':P_birth_date',$birthdate,32);

            oci_bind_by_name($stmt,':P_gender',$gender,32);

 

            // Bind the output parameter

            oci_bind_by_name($stmt,":P_ret_id",$ret_id, 32);

            oci_bind_by_name($stmt,":P_return_status",$ret_stat, 32);

            oci_bind_by_name($stmt,":P_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

          );

 

 

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

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.