CODEWITHSUNDEEP
oracle-database
BartWD
BartWD

Reputation: 140

Oracle Function Returning Null?

This is starting to drive me crazy and I cannot find the reason why this ain't working!

The tables have triggers on them so cityID and postcodeID get populated automatically. The purpose of this function is to find a city with the same name and return its ID else add it to the table and return the new ID. 

CREATE OR REPLACE FUNCTION resolveCity(cityNameIn IN VARCHAR2)
RETURN NUMBER AS
    cityIDOut NUMBER;
BEGIN
    SELECT cityID
    INTO cityIDOut
    FROM tblCities
    WHERE cityName = cityNameIn;

    IF cityIDOut IS NULL THEN
        -- Add this city to the list
        INSERT INTO tblCities (cityName)
        VALUES (cityNameIn)
        RETURNING cityID INTO cityIDOut;
    END IF;

    RETURN(cityIDOut);
END;
/

Upvotes: 0

Views: 1488

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

If a SELECT fails in PL/SQL a NO_DATA_FOUND exception is raised. In this case there's no handler in the function so it's raised to an outer handler - and apparently there isn't one, so it gets dropped on the floor and lost.

I suggest that you rewrite your function as:

CREATE OR REPLACE FUNCTION resolveCity(cityNameIn IN VARCHAR2)
RETURN NUMBER AS
    cityIDOut NUMBER;
BEGIN
  BEGIN
    SELECT cityID
      INTO cityIDOut
      FROM tblCities
      WHERE cityName = cityNameIn;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      -- Add this city to the list
      INSERT INTO tblCities (cityName)
        VALUES (cityNameIn)
        RETURNING cityID INTO cityIDOut;
  END;

  RETURN(cityIDOut);
END;

Best of luck.

Upvotes: 2

Related Questions