If you find yourself writing very similar routines in Oracle that all follow the same "Pattern", consider using some features of the "EXECUTE IMMEDIATE" statement and GLOBAL FUNCTIONS to capture that pattern and make it reusable. You can start your own little personal library of Oracle routines based on this pattern.
Let's look at a concrete problem: I was faced with a task to clone data spread across multiple Oracle tables with key constraints in 3 levels of master-detail relations. The clones should have the same "object graph", that is the same set of relations between them as the originals, with their 'LOCATION_ID' being different. This was part of opening up new locations for the $dayjob business.
As an engineer experienced with certain deployment issues, when I wrote a script to do anything, including this cloning, it was important to me that the script be re-runnable, so if any errors occurred during the running of the script, we could re-run the script and simply not try to migrate objects that had already been migrated. So, I found myself writing almost the same routine to see whether an object at each level of these master-detail relationships existed. I got sick of writing "SELECT some_id INTO result_id FROM some_table WHERE some_column = some_value".
It turns out, by reading the Oracle manual, it is possible to use a small piece of meta-programming to make one single routine that will work for all tables. So the key to this is that the EXECUTE IMMEDIATE statement can be used to build and execute SQL statements, and one can capture results and use bind variables to build generic queries. Here is a routine that can look to see whether a row with a particular value for a field exists, and if it does, provides it's id. This works for single-key, single-id "objects" in Oracle, but can easily be extended for more complex situations.
FUNCTION FIND_OBJECT(in_id_field VARCHAR2, in_table_name VARCHAR2, in_search_field VARCHAR2, in_search_value VARCHAR2 ) RETURN NUMBER
IS v_result NUMBER := Constantsp.int_UNKNOWN_ERROR; BEGIN EXECUTE IMMEDIATE 'SELECT ' || in_id_field || ' FROM ' || in_table_name || ' WHERE ' || in_search_field || ' = :value' INTO v_result USING in_search_value; RETURN NVL(v_result, Constantsp.int_UNKNOWN_ERROR); END FIND_OBJECT;
So if you ever find yourself in the unfortunate position of writing Oracle database scripts using PL/SQL, and want to speed things up a little, use this meta-programming technique and it will save you some time and repetition.