Oracle: function only returning null

Question

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;

TITLE is not a mandatory column. Are you certain you have populated it for the record where the ID = 27? — APC
– APC, Commented May 13, 2013 at 13:02

tbone · Accepted Answer · 2013-05-13 13:39:16Z

2

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.

answered May 13, 2013 at 13:39

tbone

15.5k3 gold badges37 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sebas · Accepted Answer · 2013-05-13 13:45:47Z

1

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

Is much cleaner.

answered May 13, 2013 at 13:45

Sebas

21.6k9 gold badges60 silver badges109 bronze badges

4 Comments

Sportball Over a year ago

This is not PL/SQL. You can't just SELECT. Once you make it friendly to PL/SQL, by SELECTing INTO something it will be obvious why this approach does not work.

Sebas Over a year ago

Your comment amused me. However, I'll kindly ask you further elaboration since the material you provided didn't leave much space for intellectual activity. I'll add that my answer is a suggestion and, given the actual datas of the question and not trying to guess anything, that it represents a good and inexpensive solution to the problem. Please note I appreciated your concern for my friendlyness towards PL/SQL.

Sportball Over a year ago

Your answer will not compile in any PL/SQL block. Thus it is not PL/SQL. It is SQL. If you reworked your SQL into something that would compile, for instance by changing it to: SELECT NVL(TITLE, 'UNKNOWN APPOINTMENT') INTO a_plsql_variable FROM MEETING WHERE MEETING_ID = meetingnumber; then you'd see that when the set (i.e., the number of rows) is empty (i.e., no rows, no_data_found, however you want to look at it) then your NVL() function doesn't have the intended effect.

Sebas Over a year ago

At the end of the OP's question there's: SELECT GetMeeting (27) appointment FROM MEETING; I only reformulated this. This is not pl/sql either. Regarding to the not existing condition, after using my query there would only need one small begin end block around it to solve the problem. But that was not my point. My point was to emphasise on the fact that the main pl/sql function thE OP is using was and is still useless.

Shailesh · Accepted Answer · 2013-05-13 12:56:52Z

0

check below statement:

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

        CLOSE current_meeting;

        RETURN r_meeting.TITLE;
    END;

answered May 13, 2013 at 12:56

Shailesh

9988 silver badges16 bronze badges

Comments

bitmagier · Accepted Answer · 2013-05-13 13:01:07Z

0

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.

answered May 13, 2013 at 13:01

bitmagier

7201 gold badge6 silver badges16 bronze badges

4 Comments

Frank Schmitt Over a year ago

Huh? The cursor has a WHERE clause for the MEETING_ID, so it will only return multiple rows if there are duplicates for the MEETING_ID (which is impossible because of the primary key constraint).

bitmagier Over a year ago

@Frank I didn't spoke about the select inside the function. I meant the final select (SELECT GetMeeting (27) appointment FROM MEETING;)

Frank Schmitt Over a year ago

(correcting myself) I assume you are talking about the second select (SELECT GetMeeting(27) appointment FROM MEETING). You are correct about this one, of course - .. from DUAL would make more sense here.

Frank Schmitt Over a year ago

I already noticed my misinterpretation of your answer - see my other comment.

Collectives™ on Stack Overflow

Oracle: function only returning null

4 Answers 4

Comments

4 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related