Crea sito

Differenza tra due date in Oracle

Supponiamo di avere una tabella su Oracle (l’esempio che sto trattando qui fa riferimento ad Oracle Database 12c) che presenta dei campi di tipo “date” e desiderate estrarre i dati da queste colonne facendone la differenza. Prima di partire con la classica select è bene verificare in che formato sono visualizzate le date nel nostro database.

set linesize 300
select * from nls_session_parameters where parameter in ('NLS_DATE_FORMAT','NLS_DATE_LANGUAGE');

PARAMETER VALUE
------------------------------ ----------------------------------------------------------------
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN

Se non è possibile modificare i parametri di configurazione relativi alla lingua di sistema e al formato delle date allora possiamo alterare solo la sessione relativa alla nostra query con il seguente comando:

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Verifichiamo che ora il formato sia corretto con la select predente e quindi possiamo andare a fare la differenza tra i due campi data:

set linesize 120
column PROGRESSIVO format 999999
column "EXPORT DIFF (sec)" format 999999
column Export_Diff format a22
column EXPORT_START_DATE format a22
column EXPORT_END_DATE format a22
select PROGRESSIVO,EXPORT_START_DATE,EXPORT_END_DATE,
to_timestamp(EXPORT_END_DATE, 'YYYY-MM-DD hh24:mi:ss') - to_timestamp(EXPORT_START_DATE, 'YYYY-MM-DD hh24:mi:ss') as Export_Diff,
round((EXPORT_END_DATE - EXPORT_START_DATE) * 86400) as "EXPORT DIFF (sec)"
from tabella_export_log;

Questo è l’output che ottengo in sqlplus in cui sono presenti le due colonne con la differenza tra le due date; una presenta la differenza come tipo “date” e l’altra come numero totale di secondi:

PROGRESSIVO EXPORT_START_DATE               EXPORT_END_DATE        EXPORT_DIFF            EXPORT DIFF (sec)
----------- ------------------------------ ---------------------- ---------------------- -----------------
      13010 2010-05-26 12:22:38            2010-05-26 12:30:05    +000000000 00:07:27                  447
      13009 2010-05-26 11:22:36            2010-05-26 11:30:00    +000000000 00:07:24                  444
      12605 2010-05-25 11:23:27            2010-05-25 11:40:53    +000000000 00:17:26                 1046
      12001 2010-05-25 02:00:33            2010-05-25 02:06:33    +000000000 00:06:00                  360
      10608 2010-05-24 15:49:41            2010-05-24 16:02:58    +000000000 00:13:17                  797
       9001 2010-05-23 21:26:59            2010-05-23 21:39:58    +000000000 00:12:59                  779

Read More

Espressioni Regolari in Oracle

Supponiamo di avere un campo varchar e vogliamo verificare se tutti i valori inseriti sono numerici o anche alfanumerici (in pratica voglio simulare una funzione del tipo is_number). La soluzione al problema può essere la creazione di una funzione isnumeric che restituisce true se il campo è un numero. Ma se avete un database oracle versione 10g o superiore allora potete sfruttare le espressioni regolari.

Read More

Oracle options installed

Se avete la necessità di ricavare le options installate con la vostra versione oracle (le options sono estensioni delle funzionalità del database; tipo Oracle Spatial, Oracle OLAP, Partitioning, ecc, ecc) è sufficiente eseguire questa query sql con i privilegi di SYS o DBA.

column parameter format a35
column value format a12
select parameter, value
from v$option
order by parameter;

Qui potete trovare un elenco delle Oracle options disponibili.

Read More

Trasposizione di una colonna in righe

Ritorno ancora una volta sul problema della trasposizione di una colonna in riga applicato al caso di una query sql ma in questo caso voglio limitare il numero di parametri passati nella clausola where per controllare meglio il risultato della procedura e per limitare l’uso delle risorse di sistema. Quindi da un’unica colonna di valori ottengo diverse righe e non un’unica riga come nel precedente articolo. Ad esempio voglio selezionare (o cancellare) una serie di valori da una tabella in base a una lista di oggetti:

[email protected]:~$ cat lista_id.out | head -3
345B8F0740C03437DB55AFA27B1F9475
6180EE3E6DE5C24E09DCA8E40E2F669C
D9018BA57ECECDD83A322BD24EAAA8E1

Per semplicità mi limito a prendere una sequenza di 2 argomenti alla volta usando il parametro -n di xargs:

[email protected]:~$ cat lista_id.out | head -3 | xargs -n2
345B8F0740C03437DB55AFA27B1F9475 6180EE3E6DE5C24E09DCA8E40E2F669C
D9018BA57ECECDD83A322BD24EAAA8E1

Ora invece sostituisco, tramite il comando sed, lo spazio ” ” generato da xargs con la sequenza di caratteri “‘,'” (il comando tr permette di convertire un carattere in un altro mentre con il sed riesco a fare la conversione in un unico passaggio):

[email protected]:~$ cat lista_id.out | head -3 | xargs -n2 | sed 's/ /'\',\''/g'
345B8F0740C03437DB55AFA27B1F9475','6180EE3E6DE5C24E09DCA8E40E2F669C
D9018BA57ECECDD83A322BD24EAAA8E1

Infine posiziono un apice in testa:

[email protected]:~$ cat lista_id.out | head -3 | xargs -n2 | sed 's/ /'\',\''/g' | sed 's/^/'\''/'
'345B8F0740C03437DB55AFA27B1F9475','6180EE3E6DE5C24E09DCA8E40E2F669C
'D9018BA57ECECDD83A322BD24EAAA8E1

e un apice in fondo alla stringa:

[email protected]:~$ cat lista_id.out | head -3 | xargs -n2 | sed 's/ /'\',\''/g' | sed 's/^/'\''/' | sed 's/$/'\''/'
'345B8F0740C03437DB55AFA27B1F9475','6180EE3E6DE5C24E09DCA8E40E2F669C'
'D9018BA57ECECDD83A322BD24EAAA8E1'

Il tutto possiamo inserirlo all’interno di un ciclo while per generare il comando desiderato (in questo caso prendo 20 oggetti per ogni iterazione del ciclo):

TAB=table_a
SQL="select file from $TAB where file_id in"
while read LINE; do
	echo "$SQL $LINE"
done < <(cat lista_id.out | xargs -n20 | sed 's/ /'\',\''/g' | sed 's/^/'\''/' | sed 's/$/'\''/')

Posso miglioare l’output riportando il numero di righe elaborate e/o rimanenti:

NUM=$(wc -l lista_id.out | awk '{print $1}')
TAB=table_a
SQL="select file from $TAB where file_id in"
echo "Totale righe: $NUM"
while read LINE; do
	echo "$SQL $LINE" >> comando.sql
        NUM=$(($NUM - 20))
        if [ $NUM -ge 20 ]; then
                echo "Mancano ancora $NUM righe"
        fi
done < <(cat lista_id.out | xargs -n20 | sed 's/ /'\',\''/g' | sed 's/^/'\''/' | sed 's/$/'\''/')

Precedenti articoli: Prima parte e Seconda parte.

Read More

Trasposizione di una colonna in riga 2: la vendetta

Mi sono ritrovato nel caso in cui la procedura descritta nell’articolo Trasposizione di una colonna in riga non ha funzionato; in particolare il secondo xargs va in errore per un problema di riempimento di buffer (si potrebbe usare l’opzione [-s max-chars] di xargs ma potrebbe non essere sufficiente).

[[email protected]]$ cat elenco | xargs -I {} echo -n ",\'{}\'" | xargs
xargs: argument line too long
xargs: echo: terminated by signal 13

[[email protected]]$ wc -l elenco
9144 elenco

Ho modificato la procedura e ora dovrebbe funzionare anche con un numero elevatissimo di righe (aspetto i vostri commenti). In particolare ho utilizzato il comando sed per inserire all’inizio e alla fine di ogni riga un singolo apice e poi ho semplicemente stampato ogni riga usando l’opzione -n di echo che non stampa il ritorno a capo; quindi la colonna viene trasformata in un’unica riga. Ho riportato il time delle diverse metologie e si vede che il risultato ottenuto con il sed è il migliore.

[[email protected]]$ time for i in $(cat elenco | sed 's/^/'\\\''/' | sed 's/$/'\\\','/'); do echo -n $i; done | sed 's/,$//'
real    m0.858s
user    0m0.470s
sys     0m0.060s

[[email protected]]$ time cat elenco | xargs -I{} echo -n ",#{}#" | tr "#" "'" | cut -c 2-
real    0m24.296s
user    0m2.960s
sys     0m18.007s

[[email protected]]$ time cat elenco | xargs -I {} echo ",\'{}\'" | xargs | cut -c 2- | tr -d ' '
real    0m25.561s
user    0m3.200s
sys     0m22.107s

Read More
Pagina 1 di 212