Oracle SP 给出 PLS-00201 ORA-06550 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19524607/
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
Oracle SP giving PLS-00201 ORA-06550 error
提问by TipTop
I have created a SP as below: The purpose is the pass 2 params to xyz. The SP xyz will read records from a table t1 and store in a cursor. xyz in turn will call another SP sp2 in a loop with the records stored in the cursor. When I tried to run this in TOAD, I got the error
我创建了一个 SP，如下所示：目的是将 2 个参数传递给 xyz。SP xyz 将从表 t1 中读取记录并存储在游标中。xyz 依次将循环调用另一个 SP sp2，并将记录存储在游标中。当我尝试在 TOAD 中运行它时，出现错误
ORA-06550: line 2, column 3:
PLS-00201: identifier 'abc.xyz' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
create or replace PROCEDURE abc.xyz(year IN number, ver IN number)
IS
cursor my_cur (year IN number, ver IN number) IS
select p1, p2, p3,
p4, p5, p6
from abc.t1
where p2=year
and p3=ver;
my_row my_cur%rowtype;
params varchar2(1000);
BEGIN
for IN my_cur(year, ver) loop
params := '' || my_row.p1 || '' ||','
|| my_row.p2 || ','
|| my_row.p3 || ','
|| my_row.p4 || ','
|| my_row.p5 || ','
|| '' || my_row.p6 || ''
dbms_output.put_line(params);
exec sp2(params);
params := '';
end loop;
END;
采纳答案by Bjarte Brandt
This one is compiling
这个正在编译
exec within a PLSQL block is not valid syntax. exec is a shortcut for sqlplus begin p; end;
PLSQL 块中的 exec 不是有效的语法。exec 是 sqlplus begin p 的快捷方式；结尾;
1 create or replace PROCEDURE xyz(year IN number, ver IN number)
2 IS
3 cursor my_cur (year IN number, ver IN number) IS
4 select p1, p2, p3,
5 p4, p5, p6
6 from t1
7 where p2=year
8 and p3=ver;
9 --my_row my_cur%rowtype;
10 params varchar2(1000);
11 BEGIN
12 for my_row IN my_cur(year, ver) loop
13 params := '' || my_row.p1 || '' ||','
14 || my_row.p2 || ','
15 || my_row.p3 || ','
16 || my_row.p4 || ','
17 || my_row.p5 || ','
18 || '' || my_row.p6 || '';
19 dbms_output.put_line(params);
20 sp2(params);
21 params := '';
22 end loop;
23* END;
Procedure created.
回答by user2275460
The error is basically telling you there is no VALID (compiled) procedure by that name or you called with incorrect type/number of parameters!
该错误基本上是告诉您没有该名称的有效（已编译）过程，或者您调用的参数类型/数量不正确！
Did your code compile? It had 2 errors and i post here with the corrections.
你的代码编译了吗？它有 2 个错误，我在这里发布更正。
CREATE OR REPLACE PROCEDURE abc.xyz (year IN NUMBER, ver IN NUMBER)
IS
CURSOR my_cur (year IN NUMBER, ver IN NUMBER)
IS
SELECT p1,
p2,
p3,
p4,
p5,
p6
FROM abc.t1
WHERE p2 = year AND p3 = ver;
my_row my_cur%ROWTYPE;
params VARCHAR2 (1000);
BEGIN
FOR my_row IN my_cur (year, ver)
LOOP
params :=
''
|| my_row.p1
|| ''
|| ','
|| my_row.p2
|| ','
|| my_row.p3
|| ','
|| my_row.p4
|| ','
|| my_row.p5
|| ','
|| ''
|| my_row.p6
|| '';
DBMS_OUTPUT.put_line (params);
sp2(params);
params := '';
END LOOP;
END;
