I ran into this one because I was working with some tables in a normalized database and I needed to quickly check to see if an id existed, and if it didn't, I needed to insert and then get the id.
This is what I came up with in the end: inside your PL/SQL block, you can have any number of sub blocks, like this (here I am using 2 PL/SQL variables: thisVehicleFuelTypeId and thisVehicleFuelType):
BEGIN ---------- sub-block begins
SELECT FUEL_TYPEID INTO thisVehicleFuelTypeId FROM TBL_FUEL_TYPE WHERE DESCRIPTION = thisVehicleFuelType;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO TBL_FUEL_TYPE (DESCRIPTION)
VALUES (thisVehicleFuelType) RETURNING FUEL_TYPEID INTO thisVehicleFuelTYpeId;
END; ---------- sub-block ends
First, I am trying to SELECT INTO the ID I need into a PL/SQL variable. If that works, then I win and I move on.
If that value does not exist, then Oracle will throw a NO_DATA_FOUND exception which you catch and then do an INSERT INTO with a RETURNING, so that you get the id that you wanted, maintaining a nicely normalized look up table!