Oracle/PLSQL: Function to get duration in years/months/days/hours/minutes

Below function is ready to use :)

Happy coding... ^^

Note: This should be paste inside the package body.

FUNCTION FUNC_GET_DURATION(VR_FDATE VARCHAR2) RETURN VARCHAR2

  IS

    VR_DURATION VARCHAR2(500);

  BEGIN

  

    SELECT  '~ '

          || TRUNC(MONTH_DIFF/12) || ' years, '

          || TRUNC(MOD(MONTH_DIFF, 12)) || ' months, '

          || TRUNC(31*(MONTH_DIFF - TRUNC(MONTH_DIFF))) || ' days, '

          || TRUNC(24*MOD(VR_TDATE-VR_FDATE,1)) || ' hours, '

          || TRUNC(MOD(MOD(VR_TDATE-VR_FDATE,1)*24,1)*60) || ' minutes'

        INTO VR_DURATION

    FROM (SELECT CAST(TO_TIMESTAMP(VR_FDATE, 'Mon DD, RR HH24:MI:SS') AS DATE) VR_FDATE

                ,SYSDATE VR_TDATE

                ,MONTHS_BETWEEN(SYSDATE,TO_TIMESTAMP(VR_FDATE, 'Mon DD, RR HH24:MI:SS')) as MONTH_DIFF

          FROM DUAL);

          

    RETURN VR_DURATION;

    

  END FUNC_GET_DURATION;

//

Execution:

SELECT PackageName.FUNC_GET_DURATION('JAN 02, 2015 10:00:00 AM');

Output:

~ 2 years, 1 months, 22 days, 2 hours, 56 minutes

Grant SELECT on all tables in Oracle/PLSQL

Execute the anonymous block below:

DECLARE

  STMT VARCHAR2(100);

BEGIN

FOR x IN (SELECT TABLE_NAME FROM user_tables)

LOOP

  STMT := 'GRANT SELECT ON ' || x.TABLE_NAME || ' TO <user>';

  EXECUTE IMMEDIATE STMT;

END LOOP;

END;