Oracle Queries & Programs

How to Get Number of Days In a Year ?

If you cannot calculate how many days in a year, here is an easy example for you. Write down the bellow code directly in sql*plus or SQL Developer or any others tools and get the result. :)

    SELECT ADD_MONTHS(TRUNC(SYSDATE,'RRRR'),12)-TRUNC(SYSDATE,'RRRR')no_days

    FROM DUAL;

How to Calculate Average of Date Values ?

Do you getting trouble of calculating average time of a filed, where data type is date time ?

I'm calculation of average time of a filed. If there is two time value in a day, i will calculate the first one means minimum one.

SELECT TO_CHAR(TRUNC(SYSDATE)+AVG(VDATE-TRUNC(VDATE)),'HH24:MI:SS')

FROM (SELECT MIN(DDATE) VDATE

FROM DEPT

GROUP BY TRUNC(DDATE))

How to find a table being refferenced ?

You want to know does a TABLE referenced as foreign key in any other table ? Let execute the following code and find out the real truth.

select a.owner,

       a.table_name,

       a.constraint_name,

       a.status

    from   all_constraints a

    where  a.constraint_type = 'R'

    and exists(select 1

               from   all_constraints

               where  constraint_name=a.r_constraint_name

                 and constraint_type in ('P', 'U')

--------name of the table from which you are not able to deleting

                 and table_name = :OBJECT_NAME               

      -------- name of the schema user name

                AND OWNER = :OBJECT_OWNER)

              ORDER BY TABLE_NAME, CONSTRAINT_NAME

How to get year,month and date from two date ?

You are in trouble getting with year, month and date ? Don't worry, just run the following script with values and you will get that.

SELECT    TRUNC (MONTHS_BETWEEN (:P_END_DATE, :P_FROM_DATE) / 12)  YEARS,

 MOD (TRUNC (MONTHS_BETWEEN (:P_END_DATE, :P_FROM_DATE)), 12) MONTHS,

 TO_DATE (:P_END_DATE)- ADD_MONTHS(:P_FROM_DATE,TRUNC (MONTHS_BETWEEN (:P_END_DATE, :P_FROM_DATE)) ) DAYS

FROM DUAL;

:P_FROM_DATE:=11-JUL-1973

:P_END_DATE :=19-JUL-2013

YEARS   MONTHS  DAYS

40      0       8

SELECT    TRUNC (MONTHS_BETWEEN (:P_END_DATE, :P_FROM_DATE) / 12) as YEARS,

MOD(TRUNC (MONTHS_BETWEEN (:P_END_DATE, :P_FROM_DATE)), 12) as MONTHS,

            (  TO_DATE (:P_END_DATE)- ADD_MONTHS (:P_FROM_DATE,TRUNC (MONTHS_BETWEEN (:P_END_DATE, :P_FROM_DATE))))  as Dates

      FROM DUAL;

User access to database within limits of time

Adopted From: Kamran Agayev A. on 28th July 2009

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 28 14:56:58 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> create user kmh identified by kmh;

User created.

SQL> grant connect, resource to kmh;

Grant succeeded.

SQL> conn kmh/kmh

Connected.

SQL> disc

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> conn / as sysdba

Connected.

SQL> CREATE OR REPLACE TRIGGER limit_connection

  2     AFTER LOGON ON DATABASE

  3  BEGIN

  4     IF USER = 'KMH' THEN

  5        IF to_number(TO_CHAR (SYSDATE, 'hh24')) BETWEEN 8 AND 22

  6        THEN

  7           RAISE_APPLICATION_ERROR(-20998,' Dear user '||USER||'! You can''t login between 08 and 22');

  8        END IF;

  9     END IF;

10  END limit_connection;

11  /

Trigger created.

SQL> select to_char(sysdate,'hh24') from dual;

TO

--

23

SQL> conn kmh/kmh

Connected.

SQL> select to_char(sysdate,'hh24') from dual;

TO

--

18

SQL> conn kmh/kmh

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-20998:  Dear user KMH! You can't login between 08 and 22

ORA-06512: at line 5

Warning: You are no longer connected to ORACLE.

SQL>