SQL 甲骨文：收到 ORA-06550 和 PLS-00905
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4654710/
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 : receiving ORA-06550 and PLS-00905
提问by Rajesh Kumar G
i have a holiday table which contains the data are
我有一个包含数据的假日表
HOLIDAYDA DESCRIPTION
--------- --------------------
19-JAN-11 to
17-JAN-11 to
10-JAN-11 new day
Now I want the first business day of the week. IE: If I pass "12-JAN-2011" as input, I want the o/p as 11-JAN-2011 as the 1st business day because 10-JAN-2011 is holiday.
现在我想要一周的第一个工作日。IE：如果我通过“12-JAN-2011”作为输入，我希望o/p 为11-JAN-2011 作为第一个工作日，因为10-JAN-2011 是假期。
here is my code :
这是我的代码：
create or replace procedure sample as
l_dStartDay date;
l_dHolidayDate date;
begin
select trunc(to_date(sysdate),'Day')
into l_dStartday
from dual;
dbms_output.put_line('first day of the week ');
dbms_output.put_line(l_dStartDay);
for i in 2..5 Loop
select holidaydate
from holiday
into l_dHolidayDate
where holidaydate = (l_dStartDay + i);
if(l_dHolidaydate is null) then
dbms_output.put_line(l_dStartDay+i);
end if;
exit;
end loop;
end;
i compiled the above program but with "Procedure created with compilation errors."
我编译了上面的程序，但是“程序创建时有编译错误”。
Newly Added : Compliation errors :
新增：编译错误：
LINE/COL ERROR
-------- -----------------------------------------------------------------
9/1 PL/SQL: SQL Statement ignored
9/33 PL/SQL: ORA-00933: SQL command not properly ended
Error:
错误：
BEGIN sample; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SYSTEM.SAMPLE is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
can any one tell me the reason for the error? if possible tell me the solution?
谁能告诉我错误的原因？如果可能，告诉我解决方案？
回答by APC
"i compiled the above program but with
Procedure created with compilation errors"
“我编译了上面的程序，但是用
Procedure created with compilation errors”
If you are using an IDE such as TOAD or SQL Developer it would show the compilation errors automatically. Otherwise they are accessible in SQL*Plus using this command:
如果您使用的是 TOAD 或 SQL Developer 等 IDE，它会自动显示编译错误。否则，可以使用以下命令在 SQL*Plus 中访问它们：
SQL> show errors
There are also views such as USER_ERRORS which we can query.
还有诸如 USER_ERRORS 之类的视图，我们可以查询。
The problem is most likely the SELECT statement, as the INTO clause should follow immediately after the projection:
问题很可能是 SELECT 语句，因为 INTO 子句应该紧跟在投影之后：
select holidaydate
into l_dHolidayDate
from holiday
where holidaydate = l_dStartDay + i);
Mind you, this also looks wrong:
请注意，这看起来也是错误的：
select trunc(to_date(sysdate),'Day')
SYSDATE is a DATE already, although the more recent versions of Oracle tend to be more forgiving of using TO_DATE on a DATE column. When truncating the time element from a date it is not necessary to include a format mask as this is the default behaviour:
SYSDATE 已经是一个 DATE，尽管最新版本的 Oracle 倾向于更宽容地在 DATE 列上使用 TO_DATE。从日期截断时间元素时，不需要包含格式掩码，因为这是默认行为：
trunc(some_date_variable)
We only need to include a mask if (say) we want the first day of the month:
如果（比如说）我们想要一个月的第一天，我们只需要包含一个掩码：
trunc(some_date_variable, 'MON')
If you want to find the first day of the week, this will do it:
如果你想找到一周的第一天，可以这样做：
SQL> select
2 trunc(to_date('01-DEC-2010', 'DD-MON-YYYY'), 'D') start_of_wk
3 from dual
4 /
START_OF_
---------
29-NOV-10
SQL>
Note that the first day of the week is dependent on the territory setting. In some territories the first day of the week is a working day (for instance Monday in the UK) in others it is not (Sunday is day 1 in the US). So it may be necessary to add an offset.
请注意，一周的第一天取决于地区设置。在某些地区，一周的第一天是工作日（例如英国的星期一），而其他地区则不是（星期日是美国的第 1 天）。因此可能需要添加偏移量。
Once you solve the compilation errors you'll find soem runtime errors, probably relating to unhandled NO_DATA_FOUND exceptions. This is because your lookup query won't return NULL when it doesn't find a matching record, it will fail.
解决编译错误后，您会发现 soem 运行时错误，可能与未处理的 NO_DATA_FOUND 异常有关。这是因为您的查找查询在找不到匹配记录时不会返回 NULL，它会失败。
This is a simple procedure. It uses a SQL solution, because SQL is the most efficient way of doing things. The inner query uses the CONNECT BY trick to generate a result set of dates. This is then reduced by the MINUS set operator, which will filter out any holidays in that week's range. Finally the outer query returns the earliest date from the query.
这是一个简单的程序。它使用 SQL 解决方案，因为 SQL 是最有效的做事方式。内部查询使用 CONNECT BY 技巧生成日期结果集。然后通过 MINUS 集合运算符减少该值，该运算符将过滤掉该周范围内的所有假期。最后，外部查询返回查询中的最早日期。
create or replace procedure get_first_working_day
( p_tgt_date in date )
is
l_st_day date := trunc(p_tgt_date, 'D');
l_working_day date := trunc(p_tgt_date, 'D');
begin
dbms_output.put_line('first day of week = '||l_st_day);
select min(day_of_wk)
into l_working_day
from ( select l_st_day + (level-1) as day_of_wk
from dual
connect by level <= 5
minus
select holidaydate
from hols
where holidaydate between l_st_day and l_st_day + 4 );
dbms_output.put_line('first working day of week = '||l_working_day
||'::'|| to_char(l_working_day, 'DAY'));
end get_first_working_day;
/
Given this test data (which reflects the byzantine state of British bank holidays) ...
鉴于此测试数据（反映了英国银行假期的拜占庭状态）......
SQL> select holidate from hols
2 order by 1
3 /
HOLIDAYDA
---------
25-DEC-10
26-DEC-10
27-DEC-10
28-DEC-10
01-JAN-11
03-JAN-11
6 rows selected.
SQL>
... here's the procedure in action:
......这是行动中的程序：
SQL> set serveroutput on size unlimited
SQL>
SQL> exec get_first_working_day (sysdate)
first day of week = 10-JAN-11
first working day of week = 10-JAN-11::MONDAY
PL/SQL procedure successfully completed.
SQL>
SQL> exec get_first_working_day (to_date( '04-JAN-2011', 'DD-MON-YYYY'))
first day of week = 03-JAN-11
first working day of week = 04-JAN-11::TUESDAY
PL/SQL procedure successfully completed.
SQL>
SQL> exec get_first_working_day (to_date( '01-JAN-2011', 'DD-MON-YYYY'))
first day of week = 27-DEC-10
first working day of week = 29-DEC-10::WEDNESDAY
PL/SQL procedure successfully completed.
SQL>
Incidentally, this is very bad practice:
顺便说一句，这是非常糟糕的做法：
PLS-00905: object SYSTEM.SAMPLE is invalid
Don't use the built-in SYS or SYSTEM accounts for your own work. There is too great a chance of breaking something. Create a new user account instead.
不要将内置的 SYS 或 SYSTEM 帐户用于您自己的工作。破坏某物的可能性太大。而是创建一个新的用户帐户。
回答by Chris Montoro
Aside from the errors already mentioned, try removing the 'EXIT' clause as this loop will iterate a fixed number of times. Also, try specifying the block name when ending the block as in the following:
除了已经提到的错误之外，尝试删除“EXIT”子句，因为此循环将迭代固定次数。此外，尝试在结束块时指定块名称，如下所示：
LOOP
...
END LOOP;
END ObjectName;
Where ObjectName is your top level program. Here, it would be 'sample', so:
其中 ObjectName 是您的顶级程序。在这里，它将是“样本”，因此：
LOOP
...
END LOOP;
END sample;
回答by sjngm
I'm guessing that the line
我猜这条线
where holidaydate = l_dStartDay + i);
is wrong as it has a )where it's not supposed to be.
是错误的，因为它有一个)不应该出现的地方。
