处于无效状态的 Oracle 包或函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16507889/
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 Package or function in invalid state
提问by user137263
Trying to create a pl/sql cursor based function to return details from an oracle database.
试图创建一个基于 pl/sql 游标的函数来从 oracle 数据库返回详细信息。
The relevant table, MEETING, has the columns
MEETING_ID: number(10), TIME: timestamp(4), TITLE: varchar(20)
相关表MEETING具有以下列
MEETING_ID：number(10) TIME、：timestamp(4) TITLE、：varchar(20)
CREATE OR REPLACE FUNCTION GetMeeting
(meetingnumber MEETING.MEETING_ID%TYPE)
RETURN VARCHAR
IS
CURSOR current_meeting(meetingnumber MEETING.MEETING_ID%TYPE)
IS
SELECT TITLE, TIME
FROM MEETING
WHERE MEETING_ID = meetingnumber;
r_meeting current_meeting%ROWTYPE;
BEGIN
OPEN current_meeting(meetingnumber);
FETCH current_meeting INTO r_meeting;
IF current_meeting%NOTFOUND THEN
r_meeting.TITLE := 'UNKNOWN APPOINTMENT';
END IF;
CLOSE current_meeting;
RETURN r_meeting.TITLE;
END;
SELECT GetMeeting (27) name
FROM MEETING;
The function seems to compile okay - but when called throws
该函数似乎编译正常 - 但调用时抛出
ORA-06575: Package or function GETMEETING is in an invalid state
ORA-06575: 包或函数GETMEETING 处于无效状态
回答by David Aldridge
Perhaps this will work better for you:
也许这对你更有效：
create or replace function
getmeeting(
meeting_id number)
return
varchar
is
meeting_title meeting.title%Type;
begin
select title
into meeting_title
from meeting
where meeting_id = getmeeting.meeting_id;
return meeting_title;
exception
when NO_DATA_FOUND then
return 'UNKNOWN APPOINTMENT';
end;
/
Not syntax checked.
未检查语法。
回答by user137263
Error being generated by column identifier 'TIME' which is an SQL keyword; triggering a runtime error when executed.
由作为 SQL 关键字的列标识符“TIME”生成的错误；执行时触发运行时错误。
Code unfortunately returns NULL when 'TIME' is removed
代码不幸在删除“TIME”时返回 NULL
回答by Michael O'Neill
There aren't enough facts to know but I would look into some form of circular dependency.
没有足够的事实要知道，但我会研究某种形式的循环依赖。
select *
from user_dependencies
where referenced_name = 'GETMEETING'
and referenced_type = 'FUNCTION';
The best way to avoid circular dependencies is to use packages where references to other packages are made in the body only. Avoid standalone function and procedure objects.
避免循环依赖的最佳方法是使用仅在正文中引用其他包的包。避免独立的函数和过程对象。
