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));

  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)
      -- 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; 
  end storeResults;


  open c_sourcedata; 

      -- fetch in chunks 
      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_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 
      end if;  

      exit when c_sourcedata%notfound; 

  end loop; 
  close c_sourcedata; 
  -- finally store remaining data 


-- test result
select * from testdummy;

-- cleanup demo 
drop table testdummy;

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.