Geeks With Blogs
My lessons learned while developing!

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!

Posted on Tuesday, April 5, 2011 10:12 AM | Back to top


Comments on this post: PL/SQL: get the id if a value exists in a look up table, insert and get the id if it doesn't

# re: PL/SQL: get the id if a value exists in a look up table, insert and get the id if it doesn't
Requesting Gravatar...
Another way to do this is to do an

Insert into table (values) where not exists (criteria)

and then select


The advantage of this method is that it will work across any platform, including those that dont have the exception mechanism.

However, it is slightly more expensive as the insert "where exists" query is executed every time.


Left by Jason Coyne on Apr 05, 2011 12:20 PM

# re: PL/SQL: get the id if a value exists in a look up table, insert and get the id if it doesn't
Requesting Gravatar...
Good point! Thanks for the comment. I will remember that when I have to do a similar thing in a non-oracle environment.
Left by Robert Hyatt on Apr 06, 2011 2:52 PM

Your comment:
 (will show your gravatar)


Copyright © robertphyatt | Powered by: GeeksWithBlogs.net