oracle PLS-00103 程序错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5687400/
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-00103 error for procedure
提问by Exeter
I have 2 servers (one for testing, one for production), both have the following Oracle packages (identical output on both of them for SELECT * FROM V$VERSION;:
我有 2 台服务器（一台用于测试，一台用于生产），都具有以下 Oracle 软件包（它们的输出相同SELECT * FROM V$VERSION;：
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production
The strange thing is that it works just fine on one server and it gives these errors on the other one... Where should I look? It seems it's a server configuration problem.
奇怪的是它在一台服务器上工作得很好，而在另一台服务器上却出现了这些错误......我应该在哪里看？好像是服务器配置问题。
I'm trying to compile this procedure:
我正在尝试编译这个程序：
CREATE OR REPLACE PROCEDURE P_A1 AS
NUMAR INTEGER := 0;
CURSOR A1_C3 IS
SELECT
(SELECT SUM(D1.A_PAY) FROM A1_A D1 WHERE D1.YR_R = D.YR_R AND D1.MON_R = D.MON_R) x_A
FROM APP_COMPANY_ALL CO, A1_A D
WHERE D.YR_R = y.APPL_admin.F$APPL_YEAR
AND D.MON_R = y.APPL_admin.F$APPL_MONTH
AND d.cif=SUBSTR(RTRIM(CO.c_fisc),3);
V_A1 A1_C3%ROWTYPE;
BEGIN
NULL;
END;
I get the folowing errors on one of the servers:
Compilation errors for PROCEDURE P_A1
我在其中一台服务器上遇到以下错误：
PROCEDURE P_A1 的编译错误
Error: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( - + case mod new not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
Text: (SELECT SUM(D1.A_PAY) FROM A1_A D1 WHERE D1.YR_R = D.YR_R AND D1.MON_R = D.MON_R) x_A
Error: PLS-00103: Encountered the symbol ")" when expecting one of the following:
. ( * @ % & - + ; / at for mod rem <an exponent (**)> and or
group having intersect minus order start union where connect
||
Text: (SELECT SUM(D1.A_PAY) FROM A1_A D1 WHERE D1.YR_R = D.YR_R AND D1.MON_R = D.MON_R) x_A
The thing is that, as I said, it works on the test server, but not on the production server. The Oracle versions are identical. I am pretty sure it's a configuration option that's causing this problem. But I don't know where to look for a solution.
问题是，正如我所说，它适用于测试服务器，但不适用于生产服务器。Oracle 版本是相同的。我很确定这是导致此问题的配置选项。但我不知道去哪里寻找解决方案。
The "Select ( Select ..." works just fine if it's not a scalar subquery. It fails when it's inside the cursor. Why doesn't it work on the production server?
如果它不是标量子查询，“Select ( Select ...” 就可以正常工作。当它在游标内时会失败。为什么它不能在生产服务器上工作？
回答by Bob Jarvis - Reinstate Monica
This may just have been a cut-n-paste issue, but in the original post the cursor starts with
这可能只是一个剪切粘贴问题，但在原始帖子中，光标以
SELECT (SELECT...
which I don't think will work. I suggest that you try the following:
我认为这行不通。我建议您尝试以下操作：
CREATE OR REPLACE PROCEDURE P_A1 AS
NUMAR INTEGER := 0;
CURSOR A1_C3 IS
SELECT x_A.PAY_SUM
FROM (SELECT SUM(D1.A_PAY) AS PAY_SUM
FROM A1_A D1
WHERE D1.YR_R = D.YR_R AND
D1.MON_R = D.MON_R) x_A
INNER JOIN A1_A A D
ON (D.YR_R = y.APPL_admin.F$APPL_YEAR AND
D.MON_R = y.APPL_admin.F$APPL_MONTH)
INNER JOIN APP_COMPANY_ALL CO
ON (SUBSTR(RTRIM(CO.c_fisc),3) = D.CIF);
V_A1 A1_C3%ROWTYPE;
BEGIN
NULL;
END P_A1;
Share and enjoy.
分享和享受。
回答by Connor McDonald
Might be worth checking a couple initialization parameters in terms of comparing the two servers:
就比较两个服务器而言，可能值得检查几个初始化参数：
plsql_optimize_level compatible
plsql_optimize_level 兼容
Differences in these could yield different behavior between servers. You can validate the suggestion that it might be the parser issue by changing it to dynamic SQL along the lines of:
这些差异可能会在服务器之间产生不同的行为。您可以通过将其更改为动态 SQL 来验证它可能是解析器问题的建议：
open my_cursor for
'SELECT
(SELECT SUM(D1.A_PAY) FROM A1_A D1 WHERE D1.YR_R = D.YR_R AND D1.MON_R = D.MON_R) x_A
FROM APP_COMPANY_ALL CO, A1_A D
WHERE D.YR_R = :1
AND D.MON_R = :2
AND d.cif=SUBSTR(RTRIM(CO.c_fisc),3)' using y.APPL_admin.F$APPL_YEAR , y.APPL_admin.F$APPL_MONTH
