CODEWITHSUNDEEP
sqloracle-databasefunctionplsql
user137263
user137263

Reputation: 747

Oracle: function only returning null

The following function is supposed to return information relating to a particular meeting, stored in a meeting table:

CREATE TABLE  "MEETING" 
   (    "MEETING_ID" NUMBER(10,0) NOT NULL ENABLE, 
    "TIME" TIMESTAMP (4) NOT NULL ENABLE, 
    "LOCATION" VARCHAR2(40), 
    "MAP_HREF" VARCHAR2(140), 
    "FK_INTEREST_ID" CHAR(4) NOT NULL ENABLE, 
    "ADDITIONAL_INFO" CLOB, 
    "PASSED" NUMBER(1,0), 
    "TITLE" VARCHAR2(20), 
     CONSTRAINT "MEETING_PK" PRIMARY KEY ("MEETING_ID") ENABLE
   ) ;

The code compiles just fine, and runs fine as well.

However, if the meeting exists, only null is returned. If the meeting doesn't exist the exception prints 'UNKNOWN APPOINTMENT' correctly.

CREATE OR REPLACE FUNCTION GetMeeting
                    (meetingnumber MEETING.MEETING_ID%TYPE)
RETURN VARCHAR
IS
    CURSOR current_meeting(meetingnumber MEETING.MEETING_ID%TYPE)
    IS
        SELECT TITLE
        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) appointment
FROM MEETING;

Upvotes: 0

Views: 4660

Answers (4)

Sebas
Sebas

Reputation: 21542

SELECT NVL(TITLE, 'UNKNOWN APPOINTMENT') FROM MEETING WHERE MEETING_ID = meetingnumber;

Is much cleaner.

Upvotes: 1

tbone
tbone

Reputation: 15493

Seems this is an exercise in using cursors? Its much more complicated than it needs to be. Try something like (untested):

create or replace function get_meeting(i_meetingnumber MEETING.MEETING_ID%TYPE)
RETURN VARCHAR2
IS
  l_title MEETING.TITLE%TYPE;
BEGIN

  select title
  into l_title
  FROM MEETING
  WHERE MEETING_ID = i_meetingnumber;

  return l_title;
EXCEPTION
  when no_data_found then
    return 'UNKNOWN APPOINTMENT';
  when others then raise;
END;

This is also a bit unnecessary to put this small logic in a function, I would simply select it as needed (via a join of a larger SQL or individually in a larger pl/sql procedure)

Also, I notice that your original function returns VARCHAR where title is VARCHAR2. Not sure off hand if the conversion is done implicitly by Oracle, but something worth mentioning.

Upvotes: 2

bitmagier
bitmagier

Reputation: 720

PL/SQL Function works fine. It returns the desired result, but your select returns as much datasets, as are present in MEETING. You should select from dual instead.

Upvotes: 0

Shailesh
Shailesh

Reputation: 990

check below statement:

IF r_meeting%NOTFOUND THEN
        r_meeting.TITLE := 'UNKNOWN APPOINTMENT';
        END IF;

        CLOSE current_meeting;

        RETURN r_meeting.TITLE;
    END;

Upvotes: 0

Related Questions