Generic PL/SQL procedure to update target table data with source table data

By | 2009-07-03

Note: I have transferred this blog from my old blog site: http://dcarapic.blogspot.com/ so I am reposting most of the stuff from there

Sometimes you might need to update data inside a table with fresh data from another table which has the same structure (but it might be in another schema). Usually this might occur if you have some sort of a 'master' schema where the 'global' data is located and you want to periodically transfer this data to some the same table in the local user schema.

Although it is relatively easy to create an update procedure/PLSQL block to do this type of thing, it is very tedious to do this every time for each table that you want to synchronize in this way The following procedure takes the name of the table and creates dynamic SQL which it then executes via EXECUTE IMMEDIATE. There are some restrictions in order for this to work:

  • Table must have a unique key
  • User must be able to query 'all_tab_cols' view
  • User must be able to execute selects against the source table (this one is understandable)

Here is the procedure:

procedure copy_from_source
(
      p_table_name in varchar2
)
as
  cursor cur_columns(c_v_table_name varchar2, c_v_source varchar2) is
    select column_name from all_tab_cols 
    where
    table_name = c_v_table_name and owner = c_v_source and column_id is not null;

  v_id_col varchar2(38) := 'ID';
  v_source_schema varchar2(38) := 'SOURCE';
  v_target_schema varchar2(38) := user;
  v_query varchar2(10000);
  v_columns varchar2(10000):= null;
  v_start number := 0;
begin     
  v_query := null;
  for v_col in cur_columns(p_table_name, v_source_schema) loop
      if v_query is null then
         v_query := 'update ' || v_target_schema || '.' || p_table_name || 
            ' l set ' || chr(13) || chr(10);

         v_query := v_query || '  ' || '"' || v_col.column_name || '"' || ' = (';
         v_query := v_query || 'select ' || '"' || v_col.column_name || '"' || 
            ' from ' || v_source_schema || '.' || p_table_name || ' g where g.' || 
            v_id_col || ' = l.' || v_id_col;

         v_query := v_query || ')';
      else
         v_query := v_query || ', ' || chr(13) || chr(10);
         v_query := v_query || '  ' || '"' || v_col.column_name || '"' || ' = (';
         v_query := v_query || 'select ' || '"' || v_col.column_name || '"' ||
            ' from ' || v_source_schema || '.' || p_table_name || ' g where g.' || 
            v_id_col || ' = l.' || v_id_col;

         v_query := v_query || ')';
      end if;
  end loop;      
  v_query := v_query || chr(13) || chr(10) || ' where ' || v_id_col || ' in ' || 
    chr(13) || chr(10);

  v_query := v_query || ' (select ' || v_id_col || ' from ' || v_source_schema || 
    '.' || p_table_name || ')';

  v_query := v_query || chr(13) || chr(10);
  -- dbms_output.putline(v_query);
  execute immediate v_query;

  v_query := null;
  for v_col in cur_columns(p_table_name, v_source_schema) loop
      if v_columns is null then
         v_columns := v_columns || '"' || v_col.column_name || '"';
      else
         v_columns := v_columns || ', ' || '"' || v_col.column_name || '"';
      end if;
  end loop;
  v_query := 'insert into ' || v_target_schema || '.' || p_table_name || 
    ' (' || v_columns || ')' || chr(13) || chr(10);
  v_query := v_query || ' select '  || v_columns  || ' from ' || v_source_schema || 
    '.' || p_table_name || ' where ' || v_id_col || ' not in ' || chr(13) || chr(10);
  v_query := v_query || ' (select ' || v_id_col || ' from ' || v_target_schema || 
    '.' || p_table_name || ')';
  -- dbms_output.putline(v_query);
  execute immediate v_query;
end;

Leave a Reply

Your email address will not be published. Required fields are marked *