CODEWITHSUNDEEP
sqlexceptionplsqlassociative-array
Abdul Mubeen
Abdul Mubeen

Reputation: 31

Exception while returning an empty Associative array from PLSQL function

I have PLSQL function which returns an associative array. Here is my code

TYPE ASSOC_ARR IS TABLE OF NUMBER INDEX BY VARCHAR(20)
FUNCTION GET_SAMPLE_MAP(ID IN NUMBER) RETURN ASSOC_ARR IS
  sample_map ASSOC_ARR;
BEGIN
 FOR rec IN (SELECT LOC.STATE, LOC.POPULATION FROM LOCATIONS LOC) LOOP
   sample_map(rec.STATE) := rec.POPULATION;
 END LOOP;

 EXCEPTION
  WHEN NO_DATA_FOUND THEN
  RETURN sample_map;
  WHEN OTHERS THEN
  RETURN sample_map;
END;

When I invoke this function from a procedure and no data is returned by the SQL query then the NO_DATA_FOUND exception is caught which returns sample_map. But I get an error saying

ORA-06503: PL/SQL: Function returned without value *Cause: A call to PL/SQL function completed, but no RETURN statement was executed. *Action: Rewrite PL/SQL function, making sure that it always returns a value of a proper type.

What I should return from this function in this case , to avoid this error?

Upvotes: 0

Views: 712

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

A FOR cursor loop will:

  1. Open and parse the SELECT statement
  2. fetch each row
  3. close the statement when all rows have been fetched (or upon exciting the loop).

Thus, it will never raise a NO_DATA_FOUND exception, even when no row is fetched.

Therefore, your RETURN statement will never be reached, leading to the ORA-06503.

You should instead write:

FUNCTION GET_SAMPLE_MAP(ID IN NUMBER) RETURN ASSOC_ARR IS
  sample_map ASSOC_ARR;
BEGIN
 FOR rec IN (SELECT LOC.STATE, LOC.POPULATION FROM LOCATIONS LOC) LOOP
   sample_map(rec.STATE) := rec.POPULATION;
 END LOOP;
 RETURN sample_map;
END;

Also worth mentioning that you should never catch an unexpected exception and silently ignore it. This leads to hard to diagnose bugs. You may think you're dealing with the exception, but in reality you're just sweeping it under the carpet. The best thing to do with unexpected errors is to let them propagate so that you know they happen.

Upvotes: 2

Related Questions