Leap year function PLSQL

PL SQL Leap year function

Hi all, I didn't have time to write cause I had no time on my schedule :)
so I decided to refresh my blog with leap year calculator....

Some exceptions to this rule are required since the duration of a solar year is slightly less than 365.25 days. Over a period of four centuries, the accumulated error of adding a leap day every four years amounts to about three extra days. The Gregorian Calendar therefore omits 3 leap days every 400 years, omitting February 29 in the 3 century years (integer multiples of 100) that are not also integer multiples of 400.
For example, 1600 was a leap year, but 1700, 1800 and 1900 were not. Similarly, 2000 was a leap year, but 2100, 2200, and 2300 will not be. By this rule, the average number of days per year is 365 + 1/4 − 1/100 + 1/400 = 365.2425.

Why do we need Leap Years?

Leap Years are needed to keep our calendar in alignment with the Earth's revolutions around the sun. It takes the Earth approximately 365.242199 days – or 365 days, 5 hours, 48 minutes, and 46 seconds (a tropical year) – to circle once around the Sun.

However, the Gregorian calendar has only 365 days in a year, so if we didn't add a day on February 29 nearly every 4 years, we would lose almost six hours off our calendar every year. After only 100 years, our calendar would be off by approximately 24 days!
Which
The year is evenly divisible by 4;
If the year can be evenly divided by 100, it is NOT a leap year, unless;
The year is also evenly divisible by 400. Then it is a leap year.

Years are Leap Years?

In the Gregorian calendar 3 criteria must be taken into account to identify leap years:

This means that 2000 and 2400 are leap years, while 1800,1900,2100,2200,2300 and 2500 are NOT leap years.

The year 2000 was somewhat special as it was the first instance when the third criterion was used in most parts of the world since the transition from the Julian to the Gregorian Calendar

Why should I care about Leap Years?

The 29th day added to February  (leap day ) brings a smidgen of a financial boost for some consumers and slight adjustments for businesses that rely on daily sales and workers who are paid by the hour.
So if February is leaping then you must add +1 day to the monthly salary if you are accountant or to calculate extra profit for February....

Little function for leaping year

 -- function returns 1 for leaping year
 

FUNCTION f_leap_year (i_year VARCHAR2) RETURN NUMBER IS
v_remainder1 number(5,2);
v_remainder2 number(5,2);
v_remainder3 number(5,2);
o_value pls_integer;
begin
   v_remainder1 := mod(i_year,4);
   v_remainder2 := mod(i_year,100);
   v_remainder3 := mod(i_year,400);
   if ((v_remainder1 = 0 and v_remainder2 <> 0 ) or v_remainder3 = 0) then
      o_value := 1;
      return o_value;  
   else
      o_value := 0;
      return o_value;  
   end if;
end f_leap_year;

 enjoy
....................................................

Read More

PLSQL backup time...

PL SQL package backup tutorial

I started this project because i needed program that will easily back up my files from developer without clicking on package and exporting it to save my work...
Second i wrote it because there is Bug in SQL DEVELOPER that causes lines to be messed up  when you have large code...
Example:

You have large code and export it with right click on package name and select save specs and body

Real code is written like this...

1 line - BEGIN
2 LINE - SELECT RTRIM(SUBSTR(INSTANCE_NAME,1,8))||'-'||RTRIM(SUBSTR(HOST_NAME,1,15))
3 line - INTO L_INPUTNAME
4 line - FROM V$INSTANCE ;
5 line - EXCEPTION WHEN OTHERS THEN
6 LINE - NULL;
7 line - END;

but sometimes export does the weird thing and saves it messed up
switching the line order....

1 line - BEGIN
2 line - SELECT RTRIM(SUBSTR(INSTANCE_NAME,1,8))||'-'||RTRIM(SUBSTR(HOST_NAME,1,15))
4 line - FROM V$INSTANCE ;
3 line - INTO L_INPUTNAME
5 line - EXCEPTION WHEN OTHERS THEN
6 line - NULL;
7 line - END;

I googled this program somewhere on the web two years ago and manage to rewrite it for my needs...
So without any chit chat let's see the code

CREATE OR REPLACE
PROCEDURE BACKUP_OF_PL_FILES
    (I_TIPE_OF_OBJECT    VARCHAR2
    ,I_NAME_OF_OBJECT    VARCHAR2 DEFAULT '%')
IS
    PL_OUTPUT    UTL_FILE.FILE_TYPE;
    L_READ UTL_FILE.FILE_TYPE;
    L_BUFFER    VARCHAR2(4000);
    G_SESSONID    VARCHAR2(80);
    L_LINE        VARCHAR2(80);
    L_COUNTER        NUMBER(30) :=1 ;
    L_SAVE VARCHAR2(32767);
    L_INPUTNAME    VARCHAR2(20);
    L_ERRNUM    NUMBER := 0;
    L_DIRNAME VARCHAR2(100) := ' ';
BEGIN
  
   G_SESSONID := USERENV('sessionid');
   L_DIRNAME := 'BACKUP_PL/'||I_NAME_OF_OBJECT||'/'||TO_CHAR(TO_DATE(SYSDATE,'dd.mm.yy'),'yyyymmdd')||'/';
   -----------------------------------------------------------------------------
  
    BEGIN
        SELECT    RTRIM(SUBSTR(INSTANCE_NAME,1,8))||'-'||RTRIM(SUBSTR(HOST_NAME,1,15))
        INTO    L_INPUTNAME
        FROM    V$INSTANCE ;
    EXCEPTION WHEN OTHERS THEN
        NULL;
    END;

    IF I_NAME_OF_OBJECT='%' THEN
        G_SESSONID    := 'BAK_'||INITCAP(I_TIPE_OF_OBJECT)||'_'||L_INPUTNAME||'_'||TO_CHAR(SYSDATE, 'DDMMYY_HH24MI') || '.pls';
    ELSE
        G_SESSONID    := 'BAK_'||INITCAP(I_NAME_OF_OBJECT)||'_'||L_INPUTNAME||'_'||TO_CHAR(SYSDATE, 'DDMMYY_HH24MI') || '.pls';
    END IF;   
  
   PL_OUTPUT := UTL_FILE.FOPEN('c:\BACKUP', G_SESSONID,'A',4000);      
   UTL_FILE.PUT_LINE(PL_OUTPUT, 'c:\BACKUP' || L_DIRNAME || G_SESSONID);

    FOR RS_TYPE IN (SELECT    DISTINCT TYPE, NAME
            FROM    USER_SOURCE
            WHERE    TYPE LIKE UPPER(I_TIPE_OF_OBJECT)||'%'
            AND    NAME LIKE UPPER(I_NAME_OF_OBJECT)
            ORDER BY 2,1 )
    LOOP
        FOR RS_NAME IN (SELECT    TEXT
                FROM    USER_SOURCE
                WHERE    TYPE = RS_TYPE.TYPE
                AND    NAME = RS_TYPE.NAME )
        LOOP
            BEGIN
                IF (L_COUNTER = 1 OR UPPER(RS_NAME.TEXT) LIKE '%PACKAGE BODY%') THEN
                    L_BUFFER := 'CREATE OR REPLACE '||RS_NAME.TEXT ;
                ELSE
                    L_BUFFER := RTRIM(RS_NAME.TEXT,' ');
                END IF;
                IF SUBSTR(L_BUFFER,-1,1) = ';' THEN
                    UTL_FILE.PUT_LINE(PL_OUTPUT,RTRIM(L_BUFFER));
                ELSE
                    UTL_FILE.PUT_LINE(PL_OUTPUT,RTRIM(L_BUFFER,SUBSTR(L_BUFFER,-1,1)));
                END IF;
                L_COUNTER := L_COUNTER + 1 ;
           
            EXCEPTION WHEN OTHERS THEN
                L_ERRNUM :=1 ;
                DBMS_OUTPUT.PUT_LINE('error 2nd loop...'||SQLERRM);
            END ;
        END LOOP;
        L_COUNTER := 1;
        UTL_FILE.PUT_LINE(PL_OUTPUT,'/');              
        IF RS_TYPE.TYPE NOT LIKE '%PACKAGE%' OR RS_TYPE.TYPE LIKE '%PACKAGE BODY%' THEN
          UTL_FILE.PUT_LINE(PL_OUTPUT,L_LINE);   
        END IF;
    END LOOP;
    UTL_FILE.FCLOSE(PL_OUTPUT);
   L_READ := UTL_FILE.FOPEN('PL_OUT', G_SESSONID,'A',4000);
   ----------------------
EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error... '||SQLCODE);
    UTL_FILE.FCLOSE(PL_OUTPUT);
END BACKUP_OF_PL_FILES;

 
Basically this procedure is searching database same as export and write package in your local directory c:\BACKUP

Calling is easy. All you have to do is call it from anonymous block with specification(package,procedure,function...) and name of the Package/Procedure... like this

BEGIN
   BACKUP_OF_PL_FILES('package','MY_PACKAGE_NAME_PKG');
   BACKUP_OF_PL_FILES('package','MY_PACKAGE_NAME_PKG');
   BACKUP_OF_PL_FILES('procedure','MY_PROCEDURE_NAME);
   BACKUP_OF_PL_FILES('function','MY_FUNCTION_NAME');
END;

Read More

PLSQL what day is it?

PLSQL what day is it

Say that for some reason you need name of the day for certain reason...
You can create function that will return name of the day of specified date....

function f_name_of_the day (i_date) return varchar2 as
l_day varchar2(50):=null;
begin
   SELECT TO_CHAR( trunc(to_date( to_char(to_date(i_date,'YYYYMMDD')) )),'day') into l_day FROM DUAL;
   return(day);
end f_name_of_the day ;

So what does this function do?

You enter number in date format like 20130306 which is 2013 year 03 month 06 day and function should return Wednesday value....
You can manipulate with desired input value by changing format of date
 (to_date(i_date,'YYYYMMDD')) into lets say (to_date(i_date,'YYYYDDMM')) so that input now must be in year day month shape.

CODE in SQL developer

Here is  code for simple developer test that will display name of the day

declare
l_day varchar2(50):='20130306';
begin
   select to_char( trunc(to_date( to_char(to_date(l_day,'YYYYMMDD')) )),'day') into l_day from dual;
   dbms_output.put_line(l_day);
end;

Read More