CODEWITHSUNDEEP
sqloracle-databaseplsqlcursor
user137263
user137263

Reputation: 747

Oracle Package or function in invalid state

Trying to create a pl/sql cursor based function to return details from an oracle database.

The relevant table, MEETING, has the columns 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

Upvotes: 3

Views: 16450

Answers (3)

Michael O'Neill
Michael O'Neill

Reputation: 954

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.

Upvotes: 0

user137263
user137263

Reputation: 747

Error being generated by column identifier 'TIME' which is an SQL keyword; triggering a runtime error when executed.

Code unfortunately returns NULL when 'TIME' is removed

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52396

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.

Upvotes: 1

Related Questions