PLS-00302: Telling me my stored procedure isn't declared

Question

Here is where the error is occurring in the stack:

public static IKSList<DataParameter> Search(int categoryID, int departmentID, string title)
        {
            Database db = new Database(DatabaseConfig.CommonConnString, DatabaseConfig.CommonSchemaOwner, "pkg_data_params_new", "spdata_params_search");
            db.AddParameter("category_id", categoryID);
            db.AddParameter("department_id", departmentID);
            db.AddParameter("title", title, title.Length);

            DataView temp = db.Execute_DataView();

            IKSList<DataParameter> dps = new IKSList<DataParameter>();

            foreach (DataRow dr in temp.Table.Rows)
            {
                DataParameter dp = new DataParameter();
                dp.Load(dr);
                dps.Add(dp);
            }

            return dps;
        }

And here is the error text:

ORA-06550: line 1, column 38:
PLS-00302: component 'SPDATA_PARAMS_SEARCH' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OracleClient.OracleException: ORA-06550: line 1, column 38: PLS-00302: component 'SPDATA_PARAMS_SEARCH' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored

Source Error:

Line 161:            db.AddParameter("title", title, title.Length);
Line 162:
Line 163:            DataView temp = db.Execute_DataView();
Line 164:
Line 165:            IKSList<DataParameter> dps = new IKSList<DataParameter>();

My web.config is pointing to the correct place and everything so I don't know where this is coming from.

Brachylogy

Are you sure that your app is connecting as a user that has access to the proc? – Dietetics

I'm guessing that "spdata_params_search" should be in upper case. – Simoom

Answer 1

firstly make sure that the user that calls the procedure has execute rights on the procedure, secondly make sure that the user that calls the procedure can see the procedure either directly using schemaname.procedurename or synonymname.procedure name, the synonym can be either public or private.

hope it helps

Wideopen

Answer 2

The answer by janbo is spot on, give him an upvote. Here is a script to put into your DB Deployments to make sure this doesn't happen again:

sqlplus @CreateSynonyms.sql

-- CreateSynonyms.sql : Creates synonyms on XYZ_USER for all packages that don't already have synonyms

spool CreateSynonyms.log

DECLARE
  owner        VARCHAR2(20) := 'XYZ';
  currentUser  VARCHAR2(20);
  executeLine  VARCHAR2(200);
BEGIN
  -- Get the user we're currently executing as
  SELECT sys_context('USERENV', 'SESSION_USER') INTO currentUser FROM dual;

  FOR x IN (SELECT p.table_name FROM user_tab_privs p
              WHERE p.owner = owner
                AND p.privilege = 'EXECUTE'
                AND p.table_name NOT IN (
                  SELECT table_name FROM user_synonyms
                    WHERE table_owner = owner
                )
            ) LOOP
    executeLine := 'CREATE OR REPLACE SYNONYM ' || x.table_name || ' FOR ' || owner || '.' || x.table_name;
    DBMS_OUTPUT.PUT_LINE(executeLine);
    EXECUTE IMMEDIATE executeLine;
  END LOOP;
END;
/   
spool off

Extirpate