oracle PLS-00302：告诉我我的存储过程没有声明
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2834596/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PLS-00302: Telling me my stored procedure isn't declared
提问by Scott
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
说明：在执行当前 Web 请求期间发生未处理的异常。请查看堆栈跟踪以获取有关错误及其在代码中的来源的更多信息。
异常详细信息：System.Data.OracleClient.OracleException：ORA-06550：第 1 行，第 38 列：PLS-00302：必须声明组件“SPDATA_PARAMS_SEARCH” ORA-06550：第 1 行，第 7 列：PL/SQL：语句被忽略
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.
我的 web.config 指向正确的位置和所有内容，所以我不知道这是从哪里来的。
回答by janbom
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.
首先确保调用该过程的用户对该过程具有执行权限，其次确保调用该过程的用户可以直接使用schemaname.procedurename 或synonymname.procedure name 看到该过程，同义词可以是public 或私人的。
hope it helps
希望能帮助到你
回答by Jeremy Thompson
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:
janbo 的回答很到位，给他一个赞。这是一个脚本，可放入您的数据库部署中，以确保不会再次发生这种情况：
sqlplus @CreateSynonyms.sql
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
