Oracle Function Returning Null?

Question

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;
/

Are the triggers BEFORE insert or AFTER insert?

Stilgar
– Stilgar

2017-05-24 19:16:18 +00:00
Commented May 24, 2017 at 19:16 — Stilgar
– Stilgar, Commented May 24, 2017 at 19:16
BEFORE, it populates the primary key from sequence

BartWD
– BartWD

2017-05-24 19:21:17 +00:00
Commented May 24, 2017 at 19:21 — BartWD
– BartWD, Commented May 24, 2017 at 19:21

Bob Jarvis - Слава Україні · Accepted Answer · 2017-05-24 19:15:47Z

2

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.

answered May 24, 2017 at 19:15

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

50.2k10 gold badges81 silver badges119 bronze badges

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

6 Comments

BartWD Over a year ago

Thank you for the answer, unfortunately this returns me: ORA-14551: cannot perform a DML operation inside a query ORA-06512: at "S.RESOLVECITY", line 13 ORA-01403: no data found

Bob Jarvis - Слава Україні Over a year ago

How are you invoking this function?

Stilgar Over a year ago

Could you use MERGE and return the new id?

BartWD Over a year ago

INSERT INTO tblCustomerAddresses (userID, defaultAddress, addressLine1, addressLine2, addressLine3, cityID, postcodeID, countryCode) VALUES(userIDout, 1, sAddressLine1, sAddressLine2, sAddressLine3, resolveCity(sAddressCity), resolvePostcode(sAddressPostcode), sAddressCountry);

BartWD Over a year ago

I will trying using the MERGE, just need to look it up - thanks for the suggestion!

|

Collectives™ on Stack Overflow

Oracle Function Returning Null?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related