Recursive Oracle query

To do it in SQL, I think you need to incorporate a hierarchical query. Here's a swing at it, but I haven't been able to test it out.

SELECT optionID, NVL( MAX(optionValue), MAX(optionDefaultValue) ) optionValue
FROM (SELECT optionID, optionDefaultValue, subscriptionID, inherits
        FROM options CROSS JOIN subscriptions
        WHERE optionID IN (a,b,c,d,e,f)
     )
     LEFT JOIN subscriptionOptions USING (optionId, subscriptionID)
START WITH subscriptionID = x
CONNECT BY PRIOR optionValue IS NULL
       AND subscriptionID = PRIOR inherits
       AND optionID = PRIOR optionID
GROUP BY optionID

Another approach would be to write a function that implements the recursive logic for a single subscriptionID and optionID, then call it like this:

SELECT optionID, NVL( getSubscriptionOption( x, optionID), optionDefaultValue )
  FROM options
  WHERE optionID IN (a,b,c,d,e,f)

The function could be something like:

FUNCTION getSubscriptionOption( pSubID NUMBER, pOptID NUMBER )
  RETURN subscriptionOptions.optionValue%TYPE
  IS
    l_optionValue subscriptionOptions.optionValue%TYPE;
    l_inherits    subscriptionOptions.inherits%TYPE;
  BEGIN
    SELECT optionValue
      INTO l_optionValue
      FROM subscriptionOptions
      WHERE subscriptionID = pSubID
        AND optionID = pOptID;
    RETURN l_optionValue;
  EXCEPTION
    WHEN no_data_found THEN
      SELECT inherits
        INTO l_inherits
        FROM subscriptions
        WHERE subscriptionID = pSubID;
      IF inherits IS NULL THEN
        RETURN NULL;
      ELSE
        RETURN getSubscriptionOption( l_inherits, pOptID );
      END IF;
  END;

or could be written to use a loop instead of recursion.