how to get the first_date and last_date of the months between the given time period

how to get the first_date and last_date of the months between the given time period

We have a start date and end date given as an input .We have to calculate the number of months and start date ,end date of each month.
The following query is used to calculate the above requirement:-

SELECT ADD_MONTHS(TRUNC(TO_DATE('02-Mar-2014', 'DD-MON-YYYY'), 'MON'), ROWNUM - 1) start_date
,last_day(ADD_MONTHS(TRUNC(TO_DATE('02-Mar-2014', 'DD-MON-YYYY'), 'MON'), ROWNUM - 1) ) end_date
FROM   DUAL
CONNECT BY ADD_MONTHS(TRUNC(TO_DATE('02-Mar-2014', 'DD-MON-YYYY'), 'MON'), ROWNUM - 1)
    <= TRUNC(TO_DATE('29-Jun-2014', 'DD-MON-YYYY'), 'MON')


and the output will be like:-
start_date end_date

01/03/2014|31/03/2014
01/04/2014|30/04/2014
01/05/2014|31/05/2014
01/06/2014|30/06/2014

How to display bar-code format value in Oracle reports?

Sometimes some key fields are required to be displayed in a bar-code format. One thing we should have the font bar code 39 .. concatenate the value to be displayed as bar code with '*' .
for example I want to display employee_code as bar code format then in a formula column write '*'||employee_code||'*' and assign this value to the bar code field. Then in the output we can see bar code formatted field value!!

How to calculate working days for an organization , if the organization works for only a particular number of week in a month

How to calculate working days for an organization , if the organization works for only a particular number of week in a month:-


We will create a function which will take current date of calendar as input and check if this input date's week is same as the one which is required.If yes then we will return '1' else
we will return '0'.

we are removing saturday and sunday as weekends and also we are removing the week with minimum number of working days and then again re-numbering the weeks as per number of days .

IS_DEPARTMENT_WORKING(V_DATE NUMBER,wnum number) RETURN NUMBER IS

cursor c_rec is Select CNT,week_of_month,row_number() over (order by count(*) desc,week_of_month)  from
(select cnt,week_of_month,
MIN(week_of_month) KEEP (DENSE_RANK FIRST ORDER BY cnt) OVER () Lowest
from(select count(*) cnt ,week_of_month ,row_number() over (order by count(*) desc,week_of_month)
from (
select x dt,
(to_char( x+1, 'iw') - to_char(to_date('01/'||to_char(x,'mon/')||to_char(x,'yyyy'),'dd/mon/yyyy') +
1,'iw') + 1)
 Week_of_month
from
(select TRUNC(TO_DATE((V_DATE ,'YYYYMMDD'), 'MONTH')+rownum-1 x
from all_objects
where rownum <= 50
)
where to_char(x,'dy','nls_date_language=english') not in ('sat','sun')
and to_char(x,'mon')=to_char(TO_DATE((V_DATE ,'YYYYMMDD'),'mon'))
group by week_of_month) )
where ((week_of_month<>nvl(lowest,0) AND CNT <5) OR (CNT=5))
GROUP BY CNT,WEEK_OF_MONTH;

cursor c_rec2 is select x dt,
(to_char( x+1, 'iw') - to_char(to_date('01/'||to_char(x,'mon/')||to_char(x,'yyyy'),'dd/mon/yyyy') +
1,'iw') + 1)
 Week_of_month
from
(select TRUNC(TO_DATE((V_DATE ,'YYYYMMDD'), 'MONTH')+rownum-1 x
from all_objects
where rownum <= 35
)
where to_char(x,'dy','nls_date_language=english') not in ('sat','sun')
and to_char(x,'mon')=to_char(TO_DATE((V_DATE ,'YYYYMMDD'),'mon');


cnt_wdays number(5);
wnumber number(2);
worgnumber number(2);
date1 DATE;
wnumber1 number(2);
flg number(1);

BEGIN
                       
           
            open c_rec ;
            open c_rec2;
            loop
            fetch c_rec into cnt_wdays,worgnumber,wnumber;
         
           
             if wnum=wnumber then
                loop
                fetch c_rec2 into date1,wnumber1;
               
                if TO_NUMBER(TO_CHAR(date1,'YYYYMMDD'))=trunc((V_DATE ) and wnumber1=worgnumber
                then
               
                flg:=1;
                exit;
                end if;
               exit when c_rec2%notfound;
                end loop;
                     
             end if;
           
           
            exit when c_rec%notfound;
            end loop;
           
            if flg=1
            then
            RETURN 1;
            else
            return 0;
            end if;
           
END;
/



The first cursor of the function is calculating the week number and removing the smallest week and again reorganizing and renumbering the week.

The second cursor is giving all the dates removing saturday and sunday and their respective week numbers.

How to escape message no changes to save in Oracle Forms;

There are various ways to solve this issue:-

KEY-COMMIT  trigger :-

BEGIN
  :System.Message_Level := 25;
  COMMIT;
  :System.Message_Level := 0;
END;
and second is at on-error trigger:-
DECLARE
  V_Error_Code       NUMBER;
  V_Error_Text       VARCHAR2 (2000);
 
BEGIN
  V_Error_Code      := Error_Code;
  V_Error_Text      := Error_Text;


  IF V_Error_Code IN (40401, 40405) THEN
   
    NULL;
 
  ELSE
 
    Show_Error (V_Error_Code);
  END IF;
END;

Enquiring the database block based on a text item:

For example you want to enquire a employee record by employee name .
then set the following value in the database where clause and we can enquire the employee record by employee name.

(REGEXP_LIKE (regexp_replace(employee_name,'[[:space:]]*',''),regexp_replace(:b_parameter.employee_name,'[[:space:]]*',''))
or :b_parameter.employee_name is null)

here the :b_parameter.employee_name is the field entered by user for enquiry and employee_name is the dabase column
for the block employee.While executing query it will match the enquired text with the employee name and will
bring the output based on it.

How to use edit_textitem in Oracle forms

For a LOB or CLOB fields its difficult to display the full content in small text box. So in that case
we can always define an EDITOR under editors section of forms and
attach this editor to the text item which is CLOB or LOB.

To call this editor we can write below code:-

Go_Item('block_name.textitem');
Edit_TextItem;

Calling a loop inside loop for two non-database blocks in form:-

Here the block1 and block2 are non database items and they are not related to each other.
And we have to consider the block1 as master and block2 as detail block. so while reading the data from both the blocks we have to read all the records of block2 for each record of block1 and to achieve that we should write the below loop statement:-

go_block('block1');
first_record;
loop
rec_num:=:system.cursor_record;
if :rnum is not null then
go_block('block2');
first_record;
loop

exit when :block.2from_amt is null;
cnt_case:=GET_TOTAL_COUNT(:block1.rnum,:block2.FROM_AMT,:block2.TO_AMT);

next_record;
end loop;
go_block('block1');
go_record(rec_num);
exit when :system.last_record='TRUE';
next_record;


end if;
end loop;