Plsql code template

The following code example can be used as a stub to develop your own plsql data manipulation logic. The base idea is to avoid hardcoding and repeating columnlists. Typical changes, like when the data source gets a new column or even when a column is deleted from the target table, can be considered with minimal codeing effort.


-- prepare demo 
create table testdummy (id number not null, text varchar2(30));

declare 
  cursor c_sourcedata 
  is (select * -- "generic" column list
      from (
         -- begin dummy select - replace with your own data source
         select level as nr , substr( to_char(to_date('1','J') + level - 1,'Jsp'),1,30) as spelling 
         from dual 
         connect by level < 1000 
        -- end of dummy select 
         ) t
      ); 

  type sourcedata_t is table of c_sourcedata%rowtype index by binary_integer; 
  l_sourcedata sourcedata_t; 
  c_source_bulksize constant binary_Integer := 100;

  -- Target dummy table. Replace "testdummy" with real table
  l_targetrow testdummy%rowtype;
  l_targetrow_empty testdummy%rowtype; 
  type targetdata_t is table of testdummy%rowtype index by binary_integer; 
  l_targetdata targetdata_t; 

  i_target binary_integer := 0; -- index for the target collection 
  c_target_bulksize constant binary_integer := 75;

  procedure storeResults(p_targetdata in out nocopy targetdata_t,p_index in out nocopy binary_integer)
  is
  begin
      -- do a bulk insert/update/merge 
      forall f in 1..p_index 
        insert into testdummy 
        values p_targetdata(f); 
        -- if needed handle exceptions here 

        -- unload target collection after it is sucessfully stored 
        p_index := 0; 
        p_targetdata.delete; 
  end storeResults;

begin 

  open c_sourcedata; 
  loop 

      -- fetch in chunks 
      l_sourcedata.delete; 
      fetch c_sourcedata bulk collect into l_sourcedata limit c_source_bulksize; 
      
      -- process data 
      -- do the mapping between data source and data target 
      for i in 1..l_sourcedata.count loop 
        -- reset row 
        l_targetrow := l_targetrow_empty; 

        -- source record to target record 
        -- mapping rules 
        l_targetrow.id  := l_sourcedata(i).nr; 
        l_targetrow.text := l_sourcedata(i).spelling; 

        -- store result in collection 
        -- the target collection needs to use its own index. 
        i_target := i_target + 1; 
        l_targetdata(i_target) := l_targetrow; 

      end loop; 

      -- store data 
      if i_target >= c_target_bulksize then 
        storeResults(l_targetdata,i_target);
      end if;  

      exit when c_sourcedata%notfound; 

  end loop; 
  close c_sourcedata; 
 
  -- finally store remaining data 
  storeResults(l_targetdata,i_target);

end; 
/

-- test result
select * from testdummy;

-- cleanup demo 
drop table testdummy;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s