Oracle (plsql) scripts

Calculate first and last day of each month


Use the below script to select the last and first day for a number of months.

select trunc(add_months((sysdate),-level),'MM') date_from,
        trunc(add_months(last_day(sysdate),-level)) date_to
   from dual
connect by level < to_char(sysdate,'MM')+12 /*count since first month add +12 for 1 year back etc*/

Two more different approaches for calculation of last day of each month.

select last_day(to_date(t2.yy||'-'||t1.mon||'-01', 'YYYY-MM-DD')) foo
from (select level mon from dual connect by level < 13) t1,
     ( select distinct extract (year from date_period) yy from date_to) t2
order by 1 desc

SELECT LAST_DAY(ADD_MONTHS(TO_DATE('2010-01-01', 'YYYY-MM-DD'), LEVEL-1)) FOO
  from dual m
      CONNECT BY LEVEL<=12*( EXTRACT(YEAR FROM SYSDATE)-2009 )
order by 1 desc

Function to convert rows in separated-string

create or replace function append_field(sqlstr in varchar2, sep in varchar2 )
return varchar2 is
  ret varchar2(4000) := '';
  type cur_typ is ref cursor;
  rec cur_typ;
  field varchar2(4000);
begin
     OPEN rec FOR sqlstr;
     LOOP
         FETCH rec INTO field;
         EXIT WHEN rec%NOTFOUND;
         ret := ret || field || sep;
     END LOOP;
     if length(ret) = 0 then
          RETURN '';
     else
          RETURN substr(ret,1,length(ret)-length(sep));
     end if;
end;

Function of oracle to convert rows in separated-string

select comp_emp.department , 
       listagg(user_login, ',') within group (order by department) as employees
  from comp_emp, employees, supplier_addresses
 where comp_emp.emp_code = employees.emp_code 
   and comp_emp.company = supplier_addresses.recno 
   and comp_emp.company in ( to_date('13-05-1926 15:02:34','DD-MM-YYYY HH24:MI:SS') )
  group by  comp_emp.department