How to get Oracle system change number (SCN) ?

The SYSTEM CHANGE NUMBER = A stamp that defines a committed version of a database at a point in time. Oracle assigns every committed transaction a unique SCN.

USER_A@MYDB 01022015 09:24:45> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN

-----------

    2544927

USER_A@MYDB 01022015 09:24:46> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

GET_SYSTEM_CHANGE_NUMBER

------------------------

                 2544927

The User_A should have the privileges execute on DBMS_FLASHBACK and SELECT on V_$DATABASE

Getting the difference between Dates

To get the difference between  two dates;
The below function return the interval time between two dates:

create or replace function 

get_elapsed (pstart in date , pend in date )

 return interval day to second
as
begin
    return (cast(pend as timestamp)- cast(pstart as timestamp))  DAY TO SECOND;
end ;

SELECT GET_ELAPSED(

to_date('19/11/2014 12:10:20', 'DD/MM/YYYY HH24:MI:SS'), to_date('19/11/2014 13:10:20', 'DD/MM/YYYY HH24:MI:SS') 

)  ELAPSED

 FROM DUAL;

ELAPSED:

+000000000 01:00:00

We can extract hour :

SELECT EXTRACT(hour from GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'))) HOUR
  FROM DUAL;

HOUR:

1

We can extract hours,minutes and seconds 

SELECT
   GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:00:00',
                                   'DD/MM/YYYY HH24:MI:SS') ) ELAPSED,
 
   EXTRACT(HOUR FROM GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:00:00',
                                   'DD/MM/YYYY HH24:MI:SS')) ) HOUR,
             EXTRACT(MINUTE FROM GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:00:00',
                                   'DD/MM/YYYY HH24:MI:SS')) ) MINUTE,      
           EXTRACT(SECOND FROM GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:00:00',
                                   'DD/MM/YYYY HH24:MI:SS')) ) SECOND                                          
  FROM DUAL;

    ELAPSED                 HOUR MINUTE SECOND

+000000000 00:49:40 0 49 40

You can refer to Database SQL Language Reference to get more details about Interval type  and extract function; 

Understand Nulls values into Oracle (2) : Nulls in Conditions

This second post, we will talk about how Oracle evaluate conditions :

        A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN.

Table 2-20 shows examples of various evaluations involving nulls in conditions. If the conditions evaluating to UNKNOWN were used in a WHERE clause of a SELECT statement, then no rows would be returned for that query.

Table 2-20 Conditions Containing Nulls

Condition	Value of A		Evaluation
a IS NULL	10	SQL> SELECT 1 FROM DUAL WHERE 10 IS NULL; No rows selected	FALSE
a IS NOT NULL	10	SQL> SELECT 1 FROM DUAL WHERE 10 IS NOT NULL;          1 ----------          1	TRUE
a IS NULL	NULL	SQL> SELECT 1 FROM DUAL WHERE NULL IS  NULL;          1 ----------          1	TRUE
a IS NOT NULL	NULL	SQL> SELECT 1 FROM DUAL WHERE NULL IS NOT  NULL; No rows selected	FALSE
a = NULL	10	SELECT 1 FROM DUAL WHERE NULL=10; No rows selected	UNKNOWN
a != NULL	10	SELECT 1 FROM DUAL WHERE NULL=10; No rows selected	UNKNOWN
a = NULL	NULL	SQL> SELECT 1 FROM DUAL WHERE N!ULL=NULL; No rows selected	UNKNOWN
a != NULL	NULL	SQL> SELECT 1 FROM DUAL WHERE NULL!=NULL; No rows selected	UNKNOWN
a = 10	NULL	SELECT 1 FROM DUAL WHERE NULL=10; No rows selected ucune ligne sÚlectionnÚe	UNKNOWN
a != 10	NULL	SELECT 1 FROM DUAL WHERE NULL!=10; No rows selected	UNKNOWN
NOT(a=NULL)	NULL	SQL>  select * from dual where not(null=null) No rows selected	UNKNOWN

For the truth tables showing the results of logical conditions containing nulls, see Table 7-5, Table 7-6, and Table 7-7.

Nulls with Comparison Conditions

To test for nulls, use only the comparison conditions IS NULL and IS NOT NULL. If you use any other condition with nulls and the result depends on the value of the null, then the result is UNKNOWN. Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null. However, Oracle considers two nulls to be equal when evaluating a DECODE function. Refer toD ECODE for syntax and additional information.

Oracle also considers two nulls to be equal if they appear in compound keys. That is, Oracle considers identical two compound keys containing nulls if all the non-null components of the keys are equal.(Constraints)

In next posts we will continue to explore more behavior related to nulls values .

Last post: Understand Nulls values into Oracle (1)

The Sins of SQL Programming that send the DB to Upgrade Purgatory by Abel Macias

The Sins of SQL Programming that send the DB to Upgrade Purgatory

ORA-30926: unable to get a stable set of rows in the source tables

J'ai eu ce message suite à une requête de merge:

 MERGE INTO events_last a
  USING   events  b
    ON (a.event =b.event)
  WHEN MATCHED THEN
    UPDATE SET a.date_event = b.date_event
  WHEN NOT MATCHED THEN
    INSERT (event, date_event)
    VALUES (b.event, b.date_event);

le contenu de table  events et events_last est le suivant:

select date_event, event from events order by  event;

 select date_event, event from events_last order by  event desc ;

Le problème provient de la jointure entre les deux tables events_last et events qui retourne plus qu'une ligne pour le même event , donc pour résoudre le problème, je propose la solution suivante :

MERGE INTO events_last a  USING ( select max(date_event) date_event,event from  events group by event ) b    ON (a.event =b.event)  WHEN MATCHED THEN    UPDATE SET a.date_event = b.date_event  WHEN NOT MATCHED THEN    INSERT (event, date_event)    VALUES (b.event, b.date_event);

Juste une remarque la solution dépend de votre besoin, pour ne pas avoir cet erreur, il suffit de s'assurer que la jointure entre les deux tables ne retourne pas plus qu'une ligne en appliquant les clauses dans le bloc ON comme dans notre cas (a.event =b.event).

Division par zéro fonctionne ??!!!!!

Deviner le résulat de la requête suivante    :

SELECT 1 FROM DUAL WHERE EXISTS ( SELECT 1/0 FROM DUAL ) ;


On s'attend à :

 

ORA-01476:	divisor is equal to zero
Cause:	An expression attempted to divide by zero.
Action:	Correct the expression, then retry the operation

Mais ça donne bien 1.