Programs that write programs are the happiest programs in the world.

So, PL/SQL is kind of a weird language. There are things about it i don't like, and that's ok. There are two kinds of programming languages. The clean, elegant simple well designed languages, and the languages people actually use. So, I guess I make the world slightly uglier in a small corner no one will ever see, but my users are much much happier. So, PL/SQL it is.

Anyway, sometimes users set up a bunch of extra rows in constraint tables on production. For example, you might sell in 25 states, but only a few states are set up in the development database. If you're lucky and you have DBLinks set up, you can take advantage of the mighty left outer join. The idea is, you can look up what's missing on development.

The query looks something like this.

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null

Take a look at this coding horror to get a sense of what i'm talking about.

If we're clever, we can actually just look at the system tables to decide what the primary keys are for a given table, making it far easier to figure out how to compare the two tables. Here we're a little tricky, looking up the keys and then returning a list of column names. I think it's worth spending some time to learn all of the "as table" keywords. Those data structures come in handy all the time.

/* given a table name, return a list of column names that make up the primary key */
   FUNCTION f_constraint_query (v_target_table VARCHAR2)
      RETURN t_values
   IS
      v_query         VARCHAR2 (4000);
      v_constraints   t_values;
   BEGIN
      v_query :=
            'SELECT '
         || 'cols.column_name '
         || 'FROM all_constraints cons, all_cons_columns cols '
         || 'WHERE cols.table_name = '''
         || v_target_table
         || ''' AND cons.constraint_type = ''P'''
         || ' AND cons.constraint_name = cols.constraint_name'
         || ' AND cons.owner = cols.owner';

      EXECUTE IMMEDIATE v_query BULK COLLECT INTO v_constraints;

      RETURN v_constraints;
   END;

Depending on your environment, you could just do the insert's directly. I'm not in that sort of environment, so i just assemble the select statement to pull the data back to development. You should be able to just wrap the select in an insert into v_target_table, run EXECUTE IMMEDIATE and call it a day.

A coworker suggested chasing the foreign key references and recursively inserting everything as we go. I think that's a fantastic idea. It's just a slight mod to the above query. The problem is, our process just isn't clean enough yet. It'll get there.

   FUNCTION f_sel_prod_differences (v_target_table VARCHAR2)
      RETURN VARCHAR2
   IS
      v_query         VARCHAR2 (4000);
      v_constraints   t_values;
   BEGIN
      v_constraints := f_constraint_query (v_target_table);

      IF v_constraints.COUNT = 0
      THEN
         RAISE no_primary_key;
      END IF;

      -- find the stuff on production that's missing on devl
      v_query :=
            'select * from '
         || v_target_table
         || '@production r left outer join '
         || v_target_table
         || ' l ';

      -- add the primary key constraints to the join

      FOR i IN 1 .. v_constraints.COUNT
      LOOP
         IF i = 1
         THEN
            v_query := v_query || ' on l.';
         ELSE
            v_query := v_query || ' and l.';
         END IF;

         v_query :=
            v_query || v_constraints (i) || ' = r.' || v_constraints (i);
      END LOOP;

      v_query := v_query || ' where ';

      -- add the primary key constraints to the null finding part of the where clause
      FOR i IN 1 .. v_constraints.COUNT
      LOOP
         IF i = 1
         THEN
            v_query := v_query || ' l.' || v_constraints (i) || ' is null';
         ELSE
            v_query := v_query || ' and l.' || v_constraints (i) || ' is null';
         END IF;
      END LOOP;

      v_query := v_query || ';';
      -- DBMS_OUTPUT.put_line (v_query);
      RETURN v_query;
   END;

You should be able to just drop the two above queries into some utility package. be sure to update @production to reflect your actual install.