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;

working with editions – part 1

This post will list commands that are used in the scope of edition based redefinition feature of the 11g Oracle database. There will be no additional explanation of the commands. That is what the oracle documentation is good for.
Additionally see this article http://www.oracle.com/technetwork/testcontent/o10asktom-172777.html

-- create a new edition (DBA)
create edition Release0 AS CHILD OF ORA$BASE;

-- switch current session to a different edition
ALTER SESSION SET EDITION = Release0;

or

-- in PL/SQL (inside a db logon trigger)
dbms_session.SET_EDITION_DEFERRED('Release0') 
 
--show the current edition of the session
select sys_context('userenv','current_edition_name') from dual;
 
-- which users are allowed to work with editions? (DBA)
select username, editions_enabled
from dba_users;

-- switch user to allow working with editions (DBA)
ALTER USER MYSCHEMA ENABLE EDITIONS;

-- switch user to disallow working with editions 
not possible!

-- give access to a specific edition
GRANT USE ON EDITION Release0 to APEX_PUBLIC_USER;
GRANT USE ON EDITION Release0 to MYSCHEMA;
--GRANT USE ON EDITION Release0 to APEX_040100;
--GRANT USE ON EDITION Release0 to ANONYMOUS;
--GRANT USE ON EDITION Release0 to PUBLIC;

-- disallow access to a specific edition
REVOKE USE on EDITION Release0 from APEX_PUBLIC_USER;

-- set the default edition (DBA)
alter database default edition = Release0;

-- show the current default edition 
select * from database_properties where property_name like 'DEFAULT_EDITION';

-- show editions (DBA)
select * from dba_editions;
select * from dba_edition_comments;

-- remove edition (DBA)
drop edition Release0;

/* sometimes the edition can not be dropped, because there are still sessions open that use this edition.
Then we will get an
"SQL-ERROR: ORA-38805: Edition in use"

additionally the default edition can not be dropped
*/

-- double check own session
select sys_context('userenv','current_edition_name') from dual;

-- search for other sessions, incl. APEX
select s.sid, s.serial#, s.session_edition_id , o.object_name as edition_name, s.username, s.machine, s.program
from v$session s
left join all_objects o on o.object_id = s.session_edition_id;


-- remove edition including objects in that edition (DBA)
drop edition Release0 cascade;

The next post will focus on building a connection to a specific edition.