0
votes

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;
/
1
Are the triggers BEFORE insert or AFTER insert?Stilgar
BEFORE, it populates the primary key from sequenceBartWD

1 Answers

2
votes

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.