pipelined Date function

The requirement for this function is to
list the months ahead of the month supplied as parameters (both  numbers).
The number of months ahead of the month supplied would also
be supplied as a parameter.


create or replace type dtTable as table of date;
/
create or replace function p (pYear       in number
                             ,pMonth      in number
                             ,pMth_Ahead  in pls_integer) 

  return dtTable pipelined as

 l_In_Date date := to_date(to_char(pYear)||to_char(pMonth), 'yyyymm');

begin

  for i in (select add_months(l_In_Date,(level-1)) dt
            from dual
            connect by level <= pMth_Ahead) loop
    pipe row(i.dt);
  end loop;
  return;
end;
/
SQL> select p.column_Value as Month from  table(p(2008,9,6)) p;

MONTH
---------
01-SEP-08
01-OCT-08
01-NOV-08
01-DEC-08
01-JAN-09
01-FEB-09

6 rows selected.

OUTER Join combined with INSERT ALL

The following is an example of an INSERT ALL. The requirement is that rows that exists in table A be inserted into table B, but only if they exist in the MASTER table. If not, then insert an error message into the LOG_MSG table.

create table a (
col1 number
,col2 varchar2(3)
,col3 varchar2(3)
)
/

create table b (
col1 number
,col2 varchar2(3)
,col3 varchar2(3)
)
/

create table master (
col1 varchar2(3)
,col2 number
)
/

create table log_msg
(msg varchar2(100)
,t timestamp default current_timestamp)
/


insert into a (col1,col2,col3)
  select 1 col1, 'AAA' col2,  'P01' col3 from dual union all
  select 2, 'BBB', 'Q01' from dual union all
  select 3, 'CCC', 'S05' from dual union all
  select 4, 'DDD', 'A02' from dual union all
  select 5, 'EEE', 'B01' from dual union all
  select 6, 'FFF', 'B03' from dual
/

insert into b (col1,col2,col3)
select 1 col1, 'AAA' col2,  'P01' col3 from dual union all
  select 2, 'BBB', 'Q01' from dual union all
  select 3, 'CCC', 'S05' from dual union all
  select 5, 'EEE', 'B01' from dual
/


insert into master (col1,col2)
select 'P01', 100 from dual union all
select 'P02', 400 from dual union all
select 'Q01', 900 from dual union all
select 'Q02', 100 from dual union all
select 'S01', 800 from dual union all
select 'S05', 500 from dual union all
select 'B01', 200 from dual union all
select 'B03', 900 from dual
/

insert all
  when Master_ID is not null then
    into b (col1, col2, col3) values (col1, col2, col3)
  when Master_ID is null then
    into log_msg (msg) values (col1 || ' ' || col2 || ' ' || col3 || ' does not exist on Master')
select m.col1 as Master_ID
      ,a.col1 as Col1
      ,a.col2 as Col2
      ,a.col3 as Col3
from (
      select a.col1, a.col2, a.col3
      from a
      left outer join b on  a.col2 = b.col2
                        and a.col3 = b.col3
      where b.col1 is null
    ) a
left outer join master m on a.col3 = m.col1
/

SQL> select * from b;

      COL1 COL COL
---------- --- ---
         1 AAA P01
         2 BBB Q01
         3 CCC S05
         5 EEE B01
         6 FFF FFF

SQL> select * from log_msg;

MSG                             T
------------------------------- -------------------------
4 DDD A02 does not exist Master 28-SEP-11 11.22.09.221000