Ricerche case sensitive in Oracle 11gR2 e 12cR1

venerdì 22 agosto 2014 alle 22:03 | Pubblicato su 11g, 12c, Diario, Performance Tuning | Lascia un commento
Tag: , ,

Questo post è un aggiornamento del post “Database case sensitive su 10.2.0.3“,  aggiornamento necessario, essendo il post risalente ad oltre sette anni fa. In realtà ho fatto qualche test in questi sette anni ma non ben formalizzato, quando poi mi è stato chiesto per l’ennesima volta se e come era possibile fare ricerche case insensitive su database Oracle (versione 11.2) ho prima dato la solita risposta, poi però mi è venuto lo scrupolo di ricontrollare se fosse cambiato qualcosa sulla versione che attualmente usiamo maggiormente (11.2.0.3 o 11.2.0.4) e sulla nuova 12.1 che installai qualche mese fa per i primi test sulle novità della versione. Riporto sotto il test completo che ho eseguito rispettivamente su un Oracle 10.2.0.5, un 11.2.0.3 e un 12.1.0.1, prima però anticipo alcune conclusioni: con la 11.2.0.3 le cose sono migliorate rispetto alla 10.2, mentre fra la 11.2 e la 12.1 non noto differenze rilevanti, neanche confrontanto velocemente lo stesso capitolo della documentazione nelle tre versioni:

SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 22 15:57:31 2014

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

PL/SQL procedure successfully completed.

Session altered.

CRISTIAN@geotutf8 > create table test_ci_search (id number,string1 varchar2(30),string2 varchar2(30) ,numero number,data date, datastamp timestamp);

Table created.

CRISTIAN@geotutf8 > create sequence stest_ci_search;

Sequence created.

CRISTIAN@geotutf8 > insert into test_ci_search
2  select stest_ci_search.nextval, dbms_random.string('A',30), dbms_random.string('P',30), dbms_random.random,sysdate, systimestamp
3  from dual connect by level <= 100000;

100000 rows created.

CRISTIAN@geotutf8 > insert into test_ci_search select stest_ci_search.nextval, upper(string1), upper(String2), numero, data,datastamp from test_ci_search;

100000 rows created.

CRISTIAN@geotutf8 > insert into test_ci_search select stest_ci_search.nextval, lower(string1), lower(String2), numero, data,datastamp from test_ci_search where id<=100000
;

100000 rows created.

CRISTIAN@geotutf8 > insert into test_ci_search values (stest_ci_search.nextval,'Cristian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@geotutf8 > insert into test_ci_search values (stest_ci_search.nextval,'CriStian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@geotutf8 > insert into test_ci_search values (stest_ci_search.nextval,'CRISTIAN','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@geotutf8 > insert into test_ci_search values (stest_ci_search.nextval,'cristian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@geotutf8 > commit;

Commit complete.

CRISTIAN@geotutf8 > select * from test_ci_search where string1 ='cristian';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300004 cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.832366 PM

CRISTIAN@geotutf8 > ALTER SESSION SET NLS_COMP=LINGUISTIC;

Session altered.

CRISTIAN@geotutf8 > ALTER SESSION SET NLS_SORT=BINARY_CI;

Session altered.

CRISTIAN@geotutf8 > select * from test_ci_search where string1 ='cristian';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.012672 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.031840 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.051397 PM

300004 cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.832366 PM

CRISTIAN@geotutf8 > select * from test_ci_search where string1 like 'cris%';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.012672 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.031840 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.051397 PM

300004 cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.832366 PM

CRISTIAN@geotutf8 > create index test_ci_search_idx on test_ci_search (nlssort(string1,'nls_sort=binary_ci'));

Index created.

CRISTIAN@geotutf8 >  set autotrace on
CRISTIAN@geotutf8 > select * from test_ci_search where string1 like 'cris%';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.012672 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.031840 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.051397 PM

300004 cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.832366 PM

Execution Plan
----------------------------------------------------------
Plan hash value: 18365057

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |    46 |  3772 |   913   (1)| 00:00:11 |
|*  1 |  TABLE ACCESS FULL| TEST_CI_SEARCH |    46 |  3772 |   913   (1)| 00:00:11 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("STRING1" LIKE 'cris%')

Note
-----
- dynamic sampling used for this statement

&nbsp;


C:\tmp>sqlplus cristian/cristian@//10.110.3.52/salespdb

SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 22 13:08:07 2014

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

ERROR:
ORA-28002: the password will expire within 7 days

Last Successful login time: Wed Apr 09 2014 15:21:53 +02:00

Connected to:
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

PL/SQL procedure successfully completed.

Session altered.

CRISTIAN@//10.110.3.52/salespdb > create table test_ci_search (id number,string1 varchar2(30),string2 varchar2(30) ,numero number,data date, datastamp timestamp);

Table created.

CRISTIAN@//10.110.3.52/salespdb > create sequence stest_ci_search;

Sequence created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search
2  select stest_ci_search.nextval, dbms_random.string('A',30), dbms_random.string('P',30), dbms_random.random,sysdate, systimestamp
3  from dual connect by level <= 100000;

100000 rows created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search select stest_ci_search.nextval, upper(string1), upper(String2), numero, data,datastamp from test_ci_search;

100000 rows created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search select stest_ci_search.nextval, lower(string1), lower(String2), numero, data,datastamp from test_ci_search wh
ere id<=100000;

100000 rows created.

CRISTIAN@//10.110.3.52/salespdb >
CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search values (stest_ci_search.nextval,'Cristian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search values (stest_ci_search.nextval,'CriStian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search values (stest_ci_search.nextval,'CRISTIAN','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search values (stest_ci_search.nextval,'cristian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@//10.110.3.52/salespdb >
CRISTIAN@//10.110.3.52/salespdb > commit;

Commit complete.

CRISTIAN@//10.110.3.52/salespdb > select * from test_ci_search where string1 ='cristian';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300004 cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.484698 PM

CRISTIAN@//10.110.3.52/salespdb > ALTER SESSION SET NLS_COMP=LINGUISTIC;

Session altered.

CRISTIAN@//10.110.3.52/salespdb > ALTER SESSION SET NLS_SORT=BINARY_CI;

Session altered.

CRISTIAN@//10.110.3.52/salespdb > select * from test_ci_search where string1 ='cristian';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.457087 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.466520 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.475596 PM

300004 cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.484698 PM

CRISTIAN@//10.110.3.52/salespdb > select * from test_ci_search where string1 like 'cris%';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.457087 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.466520 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.475596 PM

300004 cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.484698 PM

CRISTIAN@//10.110.3.52/salespdb > create index test_ci_search_idx on test_ci_search (nlssort(string1,'nls_sort=binary_ci'));

Index created.

CRISTIAN@//10.110.3.52/salespdb > set autotrace on
ERROR:
ORA-28002: the password will expire within 7 days

SP2-0619: Error while connecting
SP2-0611: Error enabling STATISTICS report
CRISTIAN@//10.110.3.52/salespdb > select * from test_ci_search where string1 like 'cris%';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.457087 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.466520 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.475596 PM

300004 cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.484698 PM

Execution Plan
----------------------------------------------------------
Plan hash value: 1977262958

----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Rows  | Bytes | Cost (%CPU)| Time  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |   780 |   159K|  1125   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_CI_SEARCH     |   780 |   159K|  1125   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | TEST_CI_SEARCH_IDX |  1404 |       |    11   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(NLSSORT("STRING1",'nls_sort=''BINARY_CI''')>=HEXTORAW('6372697300') AND
NLSSORT("STRING1",'nls_sort=''BINARY_CI''')<HEXTORAW('6372697400'))

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

In sintesi, fino a Oracle 10.2.0.5 era possibile abilitare ricerche su stringhe in modalità case insensitive, il problema era però che dall’uso dell’indice linguistico erano escluso l’operatore LIKE, dalla versione 11.2 in poi invece anche con l’operatore LIKE viene usato l’indice. Ho riportato solo i test per 10.2.0.5 e 12.1.0.1, per 11.2.0.3 il risultato è uguale a quello di 12.1.0.1

Database server con più “Time Zone”

lunedì 19 maggio 2014 alle 19:52 | Pubblicato su Diario | Lascia un commento
Tag: ,

Recentemente mi è stata commissionata un’indagine su come è possibile gestire più “Time Zone” su un database Oracle o più database Oracle sulla stessa macchina, siccome il risultato delle mie indagini mi è parso interessante e ho voglia di organizzare tale risultato al fine di trovare più rapidamente le informazioni la prossima volta che mi serviranno (se mai capiterà) ho deciso di scrivere questo post riepilogativo.

Quando mi è stato sottoposto il problema ho deciso di partire subito dal supporto Oracle, partendo dalla presunzione che per una singola istanza Oracle non fosse possibile “vedere” più time zone contemporaneamente. Quindi ho iniziato con una ricerca per i termini “multiple instances different timezones” ed ho cominciato con il primo documento rilevante che è:

How to use multiple timezones with one Oracle RAC database (Doc ID 1531653.1)

Nel mio caso non si usa RAC, quindi sono passato subito al primo documento indicato dopo poche righe:

How To setup TNS listener to Show More Than one Timezone (Doc ID 399448.1)

Questo è stato il primo documento che mi ha fatto pensare: “questo è interessante e me lo devo segnare”. In esso viene spiegato come, ad esclusione di ambienti Windows, dove a causa dell’architettura monoprocesso/multithread la soluzione non funziona, è possibile avviare per una singola istanza Oracle più listener, ciascuno dei quali fa vedere ai client collegati il database su una specifica “time zone” specificata sotto forma di variabile d’ambiente TZ nella configurazione del listener sul file listener.ora. La variabile TZ viene passata i processi server che vengono creati su richiesta del listener per soddisfare la richiesta di connessione. Questo funziona solo se non si usano “Shared Server” (perché in quel caso non c’è la creazione del nuovo processo, do’ per scontata la conoscenza dei meccanismi Oracle per la gestione delle richieste di connessione in caso di “dedicated server” e “shared server”). Altra condizione indicata nel documento è che la connessione avvenga tramite SID, su questo non ho indagato in realtà, ma non essendo un problema l’ho preso per buono.

Per fare qualche controllo mi sono ripassato e riporto altri interessanti documenti:

Dates & Calendars – Frequently Asked Questions (Doc ID 227334.1)

Timestamps & time zones – Frequently Asked Questions (Doc ID 340512.1)

Database Timezone Configuration (Doc ID 745854.1)

Ci sono cose che ho gia riassunto in un mio vecchio post e altro. Alla fine pare che la Time Zone del server influisca solo sulle funzioni SYSDATE e SYSTIMESTAMP, nel senso che l’ora del server verrà presentata al client nella time zone impostata; per il resto al client che si collega al database cambia poco (direi nulla, ma metto in conto possa essermi sfuggito qualcosa).

Standby database per Oracle standard edition 11gR2

mercoledì 15 gennaio 2014 alle 15:36 | Pubblicato su 11g, Installation and Configuration | Lascia un commento
Tag: , ,

Sono passati oltre quatto anni da quando scrissi questo post in cui parlai dell’implementazione del sistema di disaster recovery basato sullo standby database su Oracle standard edition. Qualche tempo fa mi è stato chiesto se eravamo in grado di mettere in piedi questa soluzione, siccome però sono passati gli anni  però, ora la nostra versione di riferimento è oracle 11.2 e non più 10.2 con la quale feci i test a suo tempo. C’è da dire che a suo tempo ho anche messo in piedi una implementazione completa, con tutta la gestione automatizzata del trasferimento degli archived log e della loro applicazione sullo standby database su una installazione con Oracle 9.2.

Con un po’ di pazienza sono riuscito a crearmi un ambiente virtualizzato (con virtual box, che per questi test sembra funzionare molto bene). Ho creato quindi una macchina virtuale con Oracle Linux 6.3 64 bit, vi ho installato Oracle Database 11.2.0.3. A questo punto ho clonato la macchina e in qualche modo ho sistemato il nome e l’indirizzo IP. Ho creato un database sulla prima macchina, poi ho seguito i miei appunti usati a suo tempo per Oracle 9.2 per creare lo standby database e fare qualche prova: il risultato è che funziona tutto allo stesso modo in cui funzionava su Oracle 9.2 e Oracle 10.2. L’unico inghippo l’ho riscontrato quando ho fatto un test che forse a suo tempo non avevo fatto: l’aggiunta di una nuova tablespace con relativo database; in questo caso mi sono imbattuto nel problema ben descritto qui, così ho scoperto l’esistenza del parametro STANDBY_FILE_MANAGEMENT. Per rendermi la vita facile quello che ho fatto è stato di modificare il parametro e ricreare da zero lo standby database.

Ieri sono riuscito a fare un ulteriore test, il cui esito al momento pare positivo, cioè applicare l’aggiornamento alla versione 11.2.0.4.  Mi sono trovato un po’ spiazzato con questo aggiornamento, per due motivi: il primo è che non ho trovato al suo interno le solite istruzioni (ho trovato la guida “Upgrade” fra la documentazione ufficiale ma questa, a voler essere pignoli, si riferisce alla versione 11.2.0.3; il secondo motivo di spiazzamento è che fare l’aggiornamento “in-place” è stato reso complicato da Oracle. Ho trovato comunque lo spazio per fare l’aggiornamento “out-of-place” come richiesto, quindi creando una nuova Oracle Home. Alla fine della parte di aggiornamento della parte “software” l’installer lancia automaticamente DB Upgrade Assistant (DBUA) che nonostante i miei timori ha eseguito la parte di aggiornamento del database senza errori.

Il bello però viene nell’aggiornamento dello standby database. La mia idea era quella di aggiornare il software e sperare che la sincronizzazione tramite l’applicazione degli archived logs funzionasse aggiornando anche lo standby database. Ciò che è accaduto è che lanciando anche sullo standby l’installer di 11.2.0.4 è andato tutto bene fino alla parte di aggiornamento del software, poi è partito DBUA il quale però manco trovava le informazioni sul database da aggiornare, in quanto in effetti non aveva le informazioni registrate sul file /etc/oratab. Poco male, anzi stavo per aggiungerle quando mi sono reso conto che comunque DBUA non avrebbe potuto fare nulla perché lo standby database non è apribile in scrittura (se non con una switch-over) . Io qui ho interrotto la procedura ed ho provveduto manualmente ad applicare gli archived logs generati sul primary e tutto pare aver funzionato bene, ho fatto nuovi test e pare funzionare come atteso (l’unica cosa che non ho detto è che ho copiato il contenuto della directory “dbs” dalla vecchia alla nuova Oracle Home.

Prossimamente conto  di fare lo stesso test con Oracle 12.1

Ancora Character Set, ORA-00600 [kafspa:columnBuffer2]

giovedì 1 agosto 2013 alle 01:55 | Pubblicato su 11g, Diario, Installation and Configuration, Varie | Lascia un commento
Tag: , ,

Non c’è dubbio che la gestione del Character Set sui database Oracle sia una cosa tutt’altro che banale e poco chiara ai più. Ieri controllando i log di un database interno utilizzato per i test ho trovato il seguente errore:

ORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [kafspa:columnBuffer2], [4053], [4000], [], [], [], [], []

Il database è un 10.2.0.5 su linux 64 bit, recentemente migrato da una vecchia macchina dove c’era però una versione 10.2.0.3. Ho passato i dati dal vecchio database al nuovo tramite expdp/impdp dei vari schemi (approfittando per fare un po’ di pulizia). L’errore veniva generato durante il calcolo delle statistiche notturno e nell’alert.log c’era rimando a un file di trace su cui ho trovato anche dettagli sulla tabella che provocava l’errore. Il secondo e terzo argomento dell’errore ORA-00600 mi ha fatto subito sospettare il tipo di problema (che fra l’altro in forme diverse ho gia incontrato altre volte. In effetti ho individuato nella tabella un campo definito come VARCHAR2(4000) facendo una query simile a questa:


SELECT NOMECAMPO FROM NOMETABELLA WHERE LENGTH(NOMECAMPO)>4000

ottenevo sempre l’errore  ORA-00600.

Facendo una ricerca sul supporto Oracle ho trovato poco, facendone una Google sono arrivato qui, come dire: a volte ritornano. Infatti in coda al post che non ricordavo di aver gia letto ho trovato dei miei commenti. Avendo anche cambiato il titolo del blog non mi ricordavo neanche chi fosse l’autrice 🙂

In realtà ho scoperto con un po’ di sorpresa che lo schema proveniva in effetti da un dump fornitoci dal cliente, che però io credevo avesse database con character set UTF-8 solo di recente sono riuscito a scoprire che in realtà il cliente utilizza charset ISO8859-15 e che quindi ero incappato sullo stesso problema. Il bello è che per trovare una soluzione, dopo alcuni banali tentativi per isolare i record con stringhe lunghe più di 4000 byte all’interno del campo incriminato, ho provato a fare un export con data pump e importare la tabella su un’altro schema dello stesso database, con una certa sorpresa mi sono trovato sul nuovo schema una copia della tabella con lo stesso problema.

Dopo vari esperimenti, alla fine, sfruttando la chiave primaria della tabella, che è un id generato da sequence,  ho isolato un record con il problema; in realtà ho trovato anche altri 6 record che su un campo varchar2(600) avevano stringhe che occupavano ben più di 600 byte ma questi record creavano problemi solo nei tentativi di CREATE TABLE AS …

In teoria ho risolto facendo un update sul campo per chiave:


UPDATE NOMETABELLA SET NOMECAMPO=SUBSTR(NOMECAMPO,1,4000) WHERE CAMPOCHIAVE=XXX

che con un certo mio sconcerto ha funzionato (apparentemente), in seguito ho fatto alcune prove, compreso il ricalcolo delle statistiche, senza riscontrare problemi, stamattina però nell’alert.log ho ritrovato l’errore sulla stessa tabella, in realtà mi aspettavo di trovare l’errore sulla copia che ho mantenuto con l’errore per fare ulteriori verifiche è così è stato anche se in un primo momento non era chiaro guardando il trace.

Stamattina ho avuto modo di fare ricerche più approfondite e mirate sul problema, leggendo nota del supporto Oracle intitolata:

“Changing the NLS_CHARACTERSET From AL32UTF8 / UTF8 (Unicode) to another NLS_CHARACTERSET (Doc ID 1283764.1)”

al paragrafo 1.d ho trovato la seguente frase:

Do NOT use Expdp/Impdp when going from (AL32)UTF8 to a single byte NLS_CHARACTERSET on ALL 10g versions lower then 10.2.0.4 (including 10.1.0.5). Also 11.1.0.6 is affected.
It will provoke data corruption unless Patch 5874989 is applied on the Impdp side. Expdp is not affected, hence the data in the dump file is correct.Also the “old” exp/imp tools are not affected.
This problem is fixed in the 10.2.0.4 and 11.1.0.7 patch set.
Fixed in 11.2.0.1 and up

 

che mi ha un po’ illuminato. In realtà ho dovuto un po’ ricostruire la storia. Sul nostro nuovo database di test 10.2.0.5 (che dovrebbe essere esente dal bug) ho due schemi dello stesso cliente, uno importato direttamente dal dump fornito dal cliente e su cui non si presenta il problema, infatti nei log dell’import trovo:

ORA-02374: conversion error loading table “SCHEMA2″.”NOMETABELLA”
ORA-12899: value too large for column NOMECAMPO (actual: 3995, maximum: 4000)
ORA-02372: data for row:

L’altro schema, sui ho trovato il problema, proveniva dallo stesso dump, ma era passato prima dal vecchio database 10.2.0.3, quindi era stato importato sul vecchio db, dove si era generata la corruzione (senza che io me ne accorgessi), poi da li è stato esportato  e importato sul nuovo database, senza errori nei log di data pump. Mi pare quindi che Oracle abbia corretto parzialmente il problema, perché impdp non genera più la corruzione sulla tabella ma scarta i dati in importazione, però, se la tabella è gia corrotta essa viene esportata e importata da datapump tale e quale, corruzione compresa. Prossimamente conto di provare a fare dei test su un Oracle 11.2.0.3, magari se ne trovo uno con character set ISO8859 posso fare dei test più completi.

Riguardo la gestione del character set su Oracle riporto qui un paio di riferimenti a note Oracle che vale la pena leggere:

  • AL32UTF8 / UTF8 (Unicode) Database Character Set Implications (Doc ID 788156.1)
  • Changing the NLS_CHARACTERSET From AL32UTF8 / UTF8 (Unicode) to another NLS_CHARACTERSET (Doc ID 1283764.1)
  • NLS_LANG Explained (How does Client-Server Character Conversion Work?) (Doc ID 158577.1)

Il comando MERGE

lunedì 3 dicembre 2012 alle 03:36 | Pubblicato su SQL | 6 commenti
Tag: , ,

La prima volta che ho visto il comando SQL MERGE con un esempio di utilizzo è stato quando ho letto il libro “Expert Oracle Database Architecture. 9i and 10g Programming Techniques and Solutions”; si tratta di  qualche anno fa, non ricordo esattamente quando, perché sul libro, contrariamente a ciò che a volte faccio non ho segnato le date in cui ho iniziato a leggere i vari capitoli. Quello che però ricordo è che mi colpì e mi segnai questo comando come promemoria. Poi credo di averlo studiato un po’, credo anche che all’epoca il database prevalentemente in uso da me fosse il 9i (quindi è decisamente qualche anno fa) perché una delle cose per cui non l0 avevo trovato utile per me era il fatto che sezione “WHEN NOT MATCHED” era obbligatoria. Siccome però ultimamente ho riscoperto l’utilità di questo comando ho deciso di dedicarci  un bel post per decriverlo.

Il comando MERGE, come fa un po’ intuire il nome, serve a unire il risultato di una query su una tabella o una vista in un’altra tabella, ad esempio Tom Kyte fa vedere, a pagina 118 del libro che sopra ho citato, come utilizza il comando per mantere aggiornata una tabellina con delle statistiche. Quindi la situazione tipica di utilizzo di MERGE è quando si hanno due tabelle collegate fra loro da una chiave e si vuole aggiornare un attributo su una prendendo il dato sull’altra tabella. Riporto un caso di test banale:


create table test_a (aid number, atext varchar2(50));
create table test_b (bid number, btext varchar2(50));

insert into test_a values (1,'testoa1');
insert into test_a values (2,'testoa2');

insert into test_b values (1,'testb1');
insert into test_b values (3,'testb3');

SVILUPPO40@perseo10 > select * from test_a;

AID ATEXT
---------- --------------------------------
 1 testoa1
 2 testoa2

SVILUPPO40@perseo10 > select * from test_b;

BID BTEXT
---------- --------------------------------
 1 testb1
 3 testb3

Per dare un’idea, la tabella TEST_A potrebbe essere una tabella di anagrafiche di articoli di produzione, la tabella TEST_B una tabella con aggiornamenti che possono essere nuovi articoli o articoli modificati. Ecco un esempio di MERGE sulle due tabelle:


SVILUPPO40@perseo10 > merge into test_a ta using
 2 (select bid,btext from test_b) tb
 3 on (tb.bid=ta.aid)
 4 when matched then update set ta.atext=tb.btext
 5 when not matched then insert (aid,atext) values (tb.bid,tb.btext)
 6 ;

2 di righe unite.

SVILUPPO40@perseo10 > select * from test_a;

AID ATEXT
---------- --------------------------------------------------
 1 testb1
 2 testoa2
 3 testb3

Ora, dalla versione 10 (non ho verificato sulla 10.1 ma dalla documentazione parrebbe gia uguale alla 10.2), la sezione “WHEN NOT MATCHED” non è più obbligatoria come lo era sulla versione 9.2, quindi è possibile fare questa cosa:


SVILUPPO40@perseo10 > rollback;

Rollback completato.

SVILUPPO40@perseo10 > merge into test_a ta using
 2 (select bid,btext from test_b) tb
 3 on (tb.bid=ta.aid)
 4 when matched then update set ta.atext=tb.btext
 5 ;

Unita 1 riga.

SVILUPPO40@perseo10 > select * from test_a;

AID ATEXT
---------- -------------------------------------------
 1 testb1
 2 testoa2

Io in alternativa al MERGE per questi casi sono sempre riuscito a utilizzare un  update semplice, che però non va sempre bene, come in questo esempio:


SVILUPPO40@perseo10 > select * from test_a;

AID ATEXT
---------- --------------------------------------------------
 1 testoa1
 2 testoa2

SVILUPPO40@perseo10 > update test_a ta set atext=(select btext from test_b tb where ta.aid=tb.bid);

Aggiornate 2 righe.

SVILUPPO40@perseo10 > select * from test_a;

AID ATEXT
---------- --------------------------------------------------
 1 testb1
 2

Per evitare questo caso che raramente è quanto desiderato occorre una clausola WHERE sull’UPDATE, ad esempio:


SVILUPPO40@perseo10 > rollback;

Rollback completato.

SVILUPPO40@perseo10 > select * from test_a;

AID ATEXT
---------- --------------------------------------------------
 1 testoa1
 2 testoa2

SVILUPPO40@perseo10 > update test_a ta set
 2 atext=(select btext from test_b tb where ta.aid=tb.bid)
 3 where atext is null;

Aggiornate 0 righe.

SVILUPPO40@perseo10 > select * from test_a;

AID ATEXT
---------- --------------------------------------------------
 1 testoa1
 2 testoa2

Siccome recentemente mi sono imbattuto in un caso in cui mi era comodo il merge (che come prestazioni è superiore all’UPDATE) , non avevo una condizione di WHERE applicabil all’UPDATE, ma non mi serviva la parte “WHEN NOT MATCHED” sono ricorso all’unico trucco che mi è venuto in mente, ovvero l’inserimento di record con dati fasulli e a seguire una DELETE:


SVILUPPO40@perseo10 > rollback;

Rollback completato.

SVILUPPO40@perseo10 > select * from test_a;

AID ATEXT
---------- --------------------------------------------------
 1 testoa1
 2 testoa2

SVILUPPO40@perseo10 > merge into test_a ta using
 2 (select bid,btext from test_b) tb
 3 on (tb.bid=ta.aid)
 4 when matched then update set ta.atext=tb.btext
 5 when not matched then insert (aid,atext) values (-1,'X-X')
 6 ;

2 di righe unite.

SVILUPPO40@perseo10 > delete from test_a where aid=-1 and atext='X-X';

Eliminata 1 riga.

SVILUPPO40@perseo10 > select * from test_a;

AID ATEXT
---------- --------------------------------------------------
 1 testb1
 2 testoa2

Come ho appena accennato, un buon motivo per ricorrere a questo espediente è che comunque le prestazioni del comando MERGE sono nettamente superiori a quelle dell’UPDATE con la sotto-query.

Operatori Spaziali

mercoledì 12 settembre 2012 alle 12:37 | Pubblicato su 11g, Oracle Locator, Oracle Spatial | Lascia un commento
Tag: , , ,

Facendo riferimento al paragrafo 5.2 del manuale “Oracle Spatial user’s guide and reference” versione 11.2, cerco di parlare un po’ degli operatori spaziali. Prima di tutto dico che gli operatori fanno ancora parte della componente Locator (Licensing Information) il che significa che il loro utilizzo è gia possibile con la Standard Edition senza sovrapprezzo.

Gli operatori spaziali sono delle particolari funzioni che si utilizzano nelle clausole WHERE degli statemente per filtrare i dati in base a logiche geometriche; il formato di questi operatori è generalmente questo:


<nome  operatore>(
geometria_tabella SDO_GEOMETRY,
geometria_riferimento SDO_GEOMETRY,
[,stringa_parametri IN VARCHAR2
[,tag IN NUMBER]]
)='TRUE'

Gli operatori funzionano solo su campi SDO_GEOMETRY gia indicizzati, in mancanza di un indice spaziale valido sul campo usato come primo argomento dell’operatore viene restituito un errore.

Il manuale spiega che le query spaziali vengono normalmente eseguite con una logica a due livelli, prima viene fatta una selezione approssimata e poi sul risultato di questa prima selezione viene fatta una selezione esatta. Cosa si intende con selezione approssimata lo si può capire andando a vedere il primo operatore spaziale: SDO_FILTER, questo operatore controlla non se due geometrie interagiscano ma se i loro MBR interagiscono. L’MBR, Minimum Bounding Rectangle di una geometria è il reattangolo più piccolo possibile che contiene la geometria. Per un punto l’MBR è il punto stesso, per un segmento è il segmento stesso, per poligoni più complessi è facile immaginare cosa sia l’MBR. Gli indici spaziali lavorano proprio su MBR e questo permette di fare una prima selezione in modo molto efficente. Riporto paro paro dal manuale un esempio di uso di questo operatore:


SELECT A.Feature_ID FROM TARGET A WHERE sdo_filter(A.shape, SDO_geometry(2003,NULL,NULL,
 SDO_elem_info_array(1,1003,3),
 SDO_ordinate_array(x1,y1, x2,y2))
 ) = 'TRUE';

Gli operatori spaziali vengono usati sempre così, dentro la clausola WHERE ponendo l’uguaglianza alla stringa ‘TRUE’. Quindi SDO_FILTER fa solo una selezione “primaria”, approssimata quindi nell’esempio vengono restituiti tutti i record per cui l’MBR del campo  SHAPE è adiacente o contenuto nel rettangolo i cui vertici estremi sono x1,y1,x2,y2.

Per interrogazioni esatte vi sono altri operatori, il primo citato nella documentazione è SDO_RELATE che serve a trovare geometrie che si “sovrappongono” in qualche maniera; il terzo parametro serve a specificare il tipo di sovrapposizione che si vuole ricercare, un esempio è ‘mask=anyinteract’ ma al posto di anyinteract (il cui significato mi sembra intuitivo) si può specificare contains, covers, inside, equal, ecc. per le combinazioni diverse da anyinteract il significato può essere poco intuitivo ma qui non voglio approfondire questo aspetto e mi limito a una veloce carrellata degli operatori, precisando che esistono per ciascuna delle possibilità degli operatori dedicati, ad esempio SDO_CONTAINS, SDO_ANYINTERACT, ecc.

Per trovare oggetti entro una certa distanza da un riferimento si può utilizzare l’operatore SDO_WITHIN_DISTANCE, specificando come stringa per il terzo parametro qualcosa come ‘distance=5’ dove l’unità di misura nel caso di sistema di riferimento geodetico dovrebbe essere sempre metri. Faccio notare che nella documentazione c’è qualcosa che non va, si dice che l’operatore non è efficente per fare join fra due tabelle, però l’esempio per una soluzione alternativa usa la funzione SDO_BUFFER senza speficare il package (SDO_GEOM) e usando la parola UNRECOVERABLE che stando alla documentazione è deprecata.

L’ultimo operatore di cui parlo è SDO_NN, NN sta per Nearest Neighbor, quindi questo operatore serve a trovare gli oggetti più vicini a un certo oggetto (quello specificato nel secondo argomento). Senza specificare nulla per il terzo parametro usando questo operatore si trovano tutti gli oggetti in ordine di distanza crescente, altrimenti specificando come terzo parametro ‘sdo_num_res=n’ dove n è un numero intero si trovano i primi n oggetti  più vicini (però non più in ordine di distanza crescente).

Sia per l’operatore SDO_WITHIN_DISTANCE che per l’operatore SDO_NN viene specificato che non sono supportati per fare join spaziali, quindi non sono supportate query del tipo:


select * from tab1 a, tab2 b where sdo_nn(a.geom,b.geom,'sdo_num_res=1')='TRUE'

Infatti io ho provato e può accadere che funzioni come che dia errore tipo questo:

ERROR at line 1:
ORA-13249: SDO_NN cannot be evaluated without using index
ORA-06512: at “MDSYS.MD”, line 1723
ORA-06512: at “MDSYS.MDERR”, line 17
ORA-06512: at “MDSYS.PRVT_IDX”, line 9

anche se entrambi i campi sono indicizzati. Di anomalie sul funzionamento se ne parla anche qui, ma io ad esempio sono riuscito a farlo funzionare solo con l’hint INDEX, ma visto l’avviso di non supporto dato sulla documentazione non so quanto sia affidabile.

Assieme all’operatore SDO_NN è possibile usare l’operatore discendente (ancillary) SDO_NN_DISTANCE che messo nella sezione SELECT ritorna anche la distanza fra gli oggetti selezionati e quello di riferimento.

Per eseguire vere e proprie join spaziali occorre usare l’opertore SDO_JOIN, che però come si può vedere dalla documentazione non è un vero operatore ma una funzione (una table function). Non ho ancora fatto molti test per poter dire se SDO_JOIN sopperisce completamente ai problemi che ho avuto sopratutto con SDO_NN nel caso di join fra tabelle, spero di dedicarci un post apposito.

 

Il costrutto SQL CASE

mercoledì 12 settembre 2012 alle 12:57 | Pubblicato su 11g, PL/SQL, SQL | Lascia un commento
Tag: , ,

Pur essendo il mio strumento di uso quotidiano da oltre 10 anni l’SQL è un linguaggio di cui ancora non ho piena padronanza, anche perché per le mie esigenze in realtà molti costrutti li ho usati poche volte. Un po’ alla volta però i nodi vengono al pettine e mi scontro con la necessità di approfondire l’uso di espressioni che in precedenza sono riuscito ad evitare. Fra queste ci oggi parlo delle espressioni “CASE“. Comincio con il dire, come apprendo dal libro “Oracle Database 11g SQL” di Jason Price che la CASE espression serve a implementare la classica logica IF-Then-else dentro statement SQL, è supportata da Oracle 9i in su (anche se sul sito di Tim Hall si parla di prima apparizione in Oracle 8i, evidentemente con limitazioni). Le espressioni CASE funzionano in modo simile alla funzione DECODE, ma la espressione CASE oltre ad essere più leggibile ha il vantaggio di essere conformo allo standard ANSI SQL/92, cosa che pare confermata da questa bozza di tale standard.

Per un’ottima descrizione di questo tipo di espressioni non mi posso esimere da riportare come primo riferimento il manuale ufficiale oracle: “Oracle Database SQL language reference“. E’ molto sintetico ma credo dica tutto e riporta anche due semplici esempi. La cosa che a me ha sempre confuso è il fatto che esistono due versioni di espressioni CASE:

  1. le Simple case expressions: che funzionano esattamente come la DECODE
  2. le Searched case expressions: che sono un po’ più complesse

A parte il vincolo sulla omogeneità dei tipi dato, non ci sono vincoli particolari sul tipo dato su cui possono lavorare le condizioni. In realtà noto la mancanza sul manuale a riferimenti a tipi data, però facendo un paio di prove a me pare funzioni:


SVILUPPO40@perseo10 > select case trunc(Sysdate) when to_date('12-09-2012','dd-mm-yyyy') then 1 else 2 end case1 from dual;

CASE1
----------
 1

SVILUPPO40@perseo10 > select case when sysdate>to_date('12-09-2012','dd-mm-yyyy') then 1 else 2 end case1 from dual;

CASE1
----------
 1

SVILUPPO40@perseo10 > alter session set nls_date_format='yyyy-mm-dd';

Modificata sessione.

SVILUPPO40@perseo10 > select case when sysdate>to_date('12-09-2012','dd-mm-yyyy') then 1 else 2 end case1 from dual;

CASE1
----------
 1

SVILUPPO40@perseo10 > select case when sysdate>to_date('12-09-2012','dd-mm-yyyy') then to_date('01-02-2010','dd-mm-yyyy') else 2 end case1 from dual;
select case when sysdate>to_date('12-09-2012','dd-mm-yyyy') then to_date('01-02-2010','dd-mm-yyyy') else 2 end case1 from dual
 *
ERRORE alla riga 1:
ORA-00932: tipi di dati incoerenti: previsto DATE, ottenuto NUMBER
SVILUPPO40@perseo10 > select case when sysdate>to_date('12-09-2012','dd-mm-yyyy') then to_date('01-02-2010','dd-mm-yyyy') else to_date('07-02-2010','d
d-mm-yyyy') end case1 from dual;

CASE1
----------
2010-02-01

L’errore che ho ricevuto nel penultimo esempio mi fa supporre che non sia una questione di conversione implicita ma una vera e propria gestione del tipo DATE.

Un bell’articoletto che spiega il CASE si può trovare sul sito databasejournal.com, anche se noto che parla solo delle “searched case expressions”. Invece Tim Hall fa degli esempi di entrambe le versioni, sia in SQL che in PL/SQL. Mi sento di dire che mentre le “simple case expressions” gestiscono gli stessi casi gestiti dalla decode, quindi solo uguaglianze su un campo, le “searched case expressions” permettono, sempre nel rispetto dell’omogeneità del tipo dato, costruzioni molto complesse usando anche operatori di comparazione quali “>” e “<“.

Appunti su Oracle Spatial: introduzione

lunedì 3 settembre 2012 alle 03:17 | Pubblicato su 11g, Documentazione, Oracle Locator, Oracle Spatial | 1 commento
Tag: , , ,

Da qualche tempo sto cercando di studiare e approfondire una componente del database Oracle per me nuova: Oracle Spatial.

In realtà Oracle Spatial è una opzione del database che comprende un insieme abbastanza grande di funzionalità,  questo insieme estende una serie di funzionalità più ristrette che sono però incluse in una componente denominata Oracle Locator e parte della Standard Edition del database, quindi senza costi extra sulla licenza del database, a differenza di Spatial. Per quanto mi riguarda, a parte qualche isolata funzione, sono ancora allo studio delle funzionalità offerte gia con Oracle Locator e per assimilare meglio i concetti proverò a scrivere una serie di post in cui riporterò quello che ho appresso.

Oracle Locator comprende innanzi tutto la definizione di un tipo dato, chiamato SDO_GEOMETRY che serve per immagazzinare sul database, su normali tabelle HEAP, assieme ad altri dati di tipo semplice (numeri, date e stringhe) delle forme geometriche, partendo da cose semplici come punti, passando per linee spezzate (line strings) e arrivando a poligoni e collezioni di poligoni. Le tipologie di oggetti geometrici modellabili all’interno del tipo dato SDO_GEOMETRY pur non essendo tutte quelle possibili a questo mondo sono secondo me molte.

SDO_GEOMETRY è definito sul database come un tipo oggetto, qui c’è il riferimento alla documentazione ufficiale, come si può vedere SDO_GEOMETRY è un record composto a sua volta da numeri e altri oggetti che non sono altro che array ordinati di numeri (varray) in cui l’ordine segue delle regole ben precise. Riporto dal manuale la definizione di SDO_GEOMETRY:

CREATE TYPE sdo_geometry AS OBJECT (
SDO_GTYPE NUMBER,
SDO_SRID NUMBER,
SDO_POINT SDO_POINT_TYPE,
SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY,
SDO_ORDINATES SDO_ORDINATE_ARRAY);

CREATE TYPE sdo_point_type AS OBJECT (
   X NUMBER,
   Y NUMBER,
   Z NUMBER);
CREATE TYPE sdo_elem_info_array AS VARRAY (1048576) of NUMBER;
CREATE TYPE sdo_ordinate_array AS VARRAY (1048576) of NUMBER;

Come ricordato sul manuale c’è un limite subito da notare dato dalla dimensione massima degli array che si riflette sul numero massime di coordinate per un oggetto memorizzabili. Tale limite sarà più chiaro dopo aver visto cosa devono contenere tali array.

Prima di passare alla descrizione dettagliata dell’oggetto SDO_GEOMETRY darò qualche altra indicazione. Innanzitutto, la definizione dell’oggetto SDO_GEOMETRY, assieme ad alcuni package contenenti programmi di utilità per la gestione di geometrie si trovano tutti su uno schema dedicato chiamato MDSYS. Oracle Locator viene installata con la componente denominata in ORacle 11gR2 “Multimedia”, in precedenza chiamata “InterMedia”, quindi installando la componente standard Multimedia viene installato anche locator, quindi viene creato lo schema MDSYS con la definizione di SDO_GEOMETRY e altri  package PL/SQL.  Lo schema MDSYS si chiama così perché alla sua introduzione, in Oracle 7.2, questa componente di chiamava Oracle MultiDimension, a sua volta il prefisso SDO usato per il nome dell’oggetto SDO_GEOMETRY deriva da nome dato alla componente: Oracle Spatial Data Option.

Vediamo ora nel dettaglio la definizione di SDO_GEOMETRY:

SDO_GTYPE

il primo elemento è un numero, con un struttura precisa che definisce il tipo  di oggetto geometrico, SDO_GTYPE ha la forma dltt dove:

  • d è un numero da 1 e 4 e indica quante dimensioni ha l’oggetto
  • l indica la dimensione che indica la misura in un sistema di riferimento lineare (LRS), vale quindi quando  è 3 o 4, spero che la mia traduzione sia corretta perché in realtà non ho ancora affrontato l’argomento LRS e quindi fin’ora ho analizzato solo geometrie con l=0
  • tt indica il tipo di geometria, sono stati definiti valori da 00 a 07, tutti gli altri sono riservati per usi futuri.

Quindi riassumento la tabellina riportata sul manuale e facendo l’ipotesi di lavorare con sole due dimensioni questi sono i possibili valori di SDO_GTYPE con il loro significato:

  1. 2000: UNKNOWN_GEOMETRY, geometria non considerata da Oracle Spatial
  2. 2001: POINT, punto semplice, quindi composto normalmente da due coordinate x,y
  3. 2002: LINE or CURVE, un segmento di retta o un arco di cerchio
  4. 2003: POLYGON, un poligono senza “buchi”, può essere un poligono come un quadrato, un pentagono o un poligono irregolare
  5. 2004:  COLLECTION,  un insiemete eterogeneo di oggetti gemetrici
  6. 2005: MULTIPOINT,  un insieme di punti
  7. 2006: MULTILINE or MULTICURVE, un insieme di segmenti di retta o di archi
  8. 2007: MULTIPOLYGON, insieme di poligoni disgiunti

Essendo io ancora agli inizi e avendo avuto qualche esempio pratico escludo mentalmente le tipologie oggetto dalla 4 in su, considerando quindi solo punti, linee e poligoni.

SDO_SRID

Questo numero indica il sistema spaziale di riferimento, l’argomento non è banale e non me la sento di parlarne qui, in sostanza però serve  a indicare il significato che hanno le coordinate (la misura di un angolo, com’è per le classiche coordinate geografiche, piuttosto che la distanza da un punto di riferimento)

SDO_POINT

è una struttura semplice, un record di 4 elementi numerici, usato nel caso la geometria da memorizzare  sia un punto, in questo caso è consigliabile usarlo, altrimenti viene lasciato a null

SDO_ELEMENT_INFO

Si tratta di un record di numeri che hanno il compito di indicare come interpretare le coordinate che vengono memorizzate nell’elemento successivo che sarà SDO_COORDINATES. SDO_ELEMENT_INFO contiene delle triplette (quindi minimo tre numeri) in cui ciascun numero ha il seguente significato:

  1. SDO_STARTING_OFFSET, indice del primo elemento dell’array delle coordinate (l’elemento SDO_COORDINATES) cui si riferisce
  2. SDO_ETYPE, indica il tipo dell’elemento, quindi fornisce informazioni di dettaglio che si aggiungono a quelle che gia abbiamo dall’elemento SDO_GTYPE; SDO_ETYPE assume valori 1,2,1003 o 2003 per elementi semplici, altri valori per elementi più complessi
  3. SDO_INTERPRETATION, in coppia con SDO_ETYPE contribuisce a descrivere l’elemento geometrico

Spiegare bene il significato di SDO_ETYPE e SDO_INTERPRETATION è complicato, conviene fare alcuni esempi di triplette, considero ora per semplicita SDO_STARTING_OFFSET sempre 1

  • (1,0,n): con n qualunque è un tipo non supportato da Oracle Spatial
  • (1,1,1) punto
  • (1,1,n) con n>1 cluster di punti
  • (1,2,1) line string connessa da segmenti di retta
  • (1,2,2) line string connessa da archi di cerchio
  • (1,1003,1)   poligono semplice i cui vertici sono connessi da segmenti di retta
  • (1,1003,2)  poligono i cui vertici sono connessi da archi di cerchio
  • (1,1003,3) rettangolo
  • (1,1003,4) cerchio
  • (1,4,n) n>1 “compound line string”, quindi direi una linea spezzata composta da un misto di segmenti di retta e archi di cerchio)

Vi sono anche altri casi, ma prima di approfondire conviene continuare e fare qualche esempio pratico.

SDO_COORDINATES

un semplice array di numero che contiene una sequenza di coordinate, da interpretare in base al contenuto di SDO_ELEM_INFO. Nel caso di due dimensioni le coordinate sono sempre coppie x,y, quindi nel caso di un segmento di retta potrebbe essere SDO_COORDINATES=(1,1,1,2) oppure SDO_COORDINATES=12.9805953, 46.2552841, 12.980599, 46.2553036) in questo caso ho usato coordinate geografiche con longitudine e latitudine.

Le prove che ho fatto io fin’ora per farmi un po’ un’idea sono in ambito geografico, quindi con oggetti geometrici che possono rappresentare limiti amministrativi di una regione, un comune o strade o fiumi e così vai, quindi ciò che ho visto sono prevalentemente punti, poligoni (pochi) ma soprattutto linee spezzate (chiamate line strings, non so se la mia traduzione in linee spezzate sia corretta). L’uso di archi di cerchio è complesso e nella maggior parte dei casi superfluo perché approssimabile bene con segmenti di retta.

Vediamo se con il prossimo post riesco a preparare qualche esempio pratico.

External Tables, possibili errori

mercoledì 29 agosto 2012 alle 29:01 | Pubblicato su 11g | 1 commento
Tag: , ,

Ieri ho incontranto uno dei tanti casi in cui una “external table” oracle può non funzionare, nel mio caso dando errore durante le interrogazioni. Voglio quindi descrivere il caso . Questa è la DDL della mia external table utilizzata per accedera a un file CSV:


create table letturearduino_csv
 (
 id number,
 tman number,
 trit number,
 text number,
 tboll number,
 pump_status number,
 dataora date,
 acspump number,
 znpump number,
 zntman number,
 zntrit number,
 acscaldaia number,
 tsalatermica number
)
 organization external (
 type oracle_loader
 default directory LOAD_DATA
 access parameters (
 records delimited by newline
 TERRITORY AMERICA
 BADFILE 'letturearduino.bad'
 LOGFILE 'letturearduino.log'
 skip 1
 fields terminated by ";" optionally enclosed by "'"
 MISSING FIELD VALUES ARE NULL
 (
 id ,
 tman ,
 trit ,
 text ,
 tboll ,
 pump_status ,
 dataora char(20) date_format DATE mask 'yyyy-mm-dd hh24:mi:ss',
 acspump ,
 znpump ,
 zntman ,
 zntrit ,
 acscaldaia ,
 tsalatermica
 )
 )
 location
 (
 'letturearduino2.csv'
 )
);

Il file CVS è stato generato tramite phpMyAdmin da un database MySQL su cui registro i dati del mio impianto solare termico tramite il microprocessore Arduino (ne parlo dettagliatamente qui), per essere più dettagliato riporto anche la struttura della tabella di partenza:


CREATE TABLE `letturearduino2` (
 `lecture_id` bigint(20) NOT NULL AUTO_INCREMENT,
 `s_temp_rit` decimal(7,4) DEFAULT NULL,
 `s_temp_man` decimal(7,4) DEFAULT NULL,
 `s_temp_ext` decimal(7,4) DEFAULT NULL,
 `s_temp_amb` decimal(7,4) DEFAULT NULL,
 `pump_status` decimal(2,0) DEFAULT NULL,
 `dataora` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `acspump` decimal(2,0) DEFAULT NULL,
 `znpump` decimal(2,0) DEFAULT NULL,
 `zntman` decimal(7,4) DEFAULT NULL,
 `zntrit` decimal(7,4) DEFAULT NULL,
 `acscaldaia` decimal(2,0) DEFAULT NULL,
 `tca` decimal(7,4) DEFAULT NULL)

Caricando il file sul mio database server, un Oracle 11.2.0.2 32 bit su linux e facendo un count sulla tabella riscontravo il seguente errore:

error processing column TSALATERMICA in row 44551 for datafile /usr/backup/UNLOAD/letturearduino2.csv
ORA-01722: invalid number

 

E questo è l’estratto incriminato del file csv (la prima colonna riporta il numero di riga)


44548 ...
44549 65098;16.3000;15.6000;6.6000;28.5000;0;2012-04-10 09:31:38;0;0;16.3000;16.5000;0;
44550 65099;16.3000;15.6000;6.6000;28.5000;0;2012-04-10 09:32:39;0;0;16.3000;16.5000;0;
44551 65100;16.3000;15.6000;6.6000;28.5000;0;2012-04-10 09:33:40;0;0;16.3000;16.5000;0;15.7000
44552 65101;16.3000;15.6000;6.6000;28.5000;0;2012-04-10 09:34:42;0;0;16.3000;16.5000;0;15.8000
44553 ...

Quindi nella mia tabella fino a un certo punto l’ultima colonna non è valorizzata (è stata aggiunata a posteriori), il problema è che la mia external table cominciava a dare errore nel momento in cui la colonna era valorizzata. Non era un problema di numero perché  altrimenti avrebbero dato errore anche tutti gli altri. Ho provato ad aggiungere all’ultimo campo anche la clausola

NULL if (TSALATERMICA = BLANKS)

ma in realtà il problema sembrava contrario e la parte “MISSING FIELD VALUES ARE NULL” dovrebbe gia risolvere questo caso. Poi ho avuto un’illuminazione ed ho fatto un controllo sul file csv:


[oracle@methone ~]$ file letturearduino2.csv
letturearduino2.csv: ASCII text, with CRLF line terminators

Dunque, inspiegabilmente il file generato da phpMyAdmin  è in formato DOS, quindi eseguendo un semplice dos2unix:


[oracle@methone ~]$ dos2unix letturearduino2.csv
dos2unix: converting file letturearduino2.csv to UNIX format ...

il problema è magicamente scomparso.

External Tables, un’introduzione

venerdì 24 agosto 2012 alle 24:30 | Pubblicato su 11g, Documentazione | 1 commento
Tag: , ,

Le External Tables sono una caratteristica introdotta per la prima volta con la versione 9i del database Oracle (quindi se non erro con la versione 9.0.1 ). Si tratta secondo me di uno strumento molto potente, fessibile e utile; esse sono molto simili a normali tabelle definite sul database, accessibili solo il modalità lettura (read-only) che  una volta create permettono di accedere a dati presenti su file esterni al database in modo trasparente. Fino alla versione 9i i file esterni potevano solo essere file di testo, poi con la versione 10g è stato aggiunto un nuovo access driver (chiamato ORACLE_DATAPUMP) che permette di accedere, sia in lettura che scrittura a file (con delle restrizioni) binari. Non ho mai usato questa soluzione, quindi non ne so molto. Il nome del driver mi aveva fatto pensare che il formato dei file binari fosse lo stesso dei dump creati con l’utility Oracle DataPump export, ma nella documentazione non ne parlano il che mi fa pensare che le due tipologie di file non siano interscambiabili.

Con la versione 11.2 è stata introdotta un’ulteriore funzionalità, il preprocessamento dei file esterni cioè la possibilità di aggiungere alla definizione della tabella esterna un clausola in cui si indica un programma esterno per preprocessare I file da leggere, l’esempio tipico è quello della decompressione di un file di testo compresso.

Le tabelle esterne forniscono funzionalità complementari (e in parte sovrapponibili)  a quelle fornite da SQL*Loader. Esse permettono di accedere a dati esterni come se fossero in tabelle del database con la limitazione che i dati sono accessibili in sola lettura. Il numero di righe che possono essere ritornate da una query dipende da che colonne vengono richieste perché le colonne di alcuni record possono dare errori di conversione e quindi causare il rigetto del record da parte del driver di accesso ai dati.

Il driver di accesso ai dati viene eseguito all’interno del database server a differenza di quanto accade per SQL*Loader che è un programma client a se stante. Questo ha  due implicazioni:

  1. il server deve avere accesso ai file da cui caricare i dati
  2. il server deve creare e scrivere il file creati dal file di accesso ai dati, ad esempio il file di log , il bad file e il discard file.

Non è possibile quindi indicare al server nomi di file qualunque, è necessario invece definire oggetti directory come locazioni da cui leggere e scrivere file. Ad esempio:

create directory load_src as ‘/usr/apps/datafiles’;

crea l’oggetto direcotory load_src.

Per creare un oggetto directory bisogna avere il ruolo di DBA oppure avere il privilegio “CREATE ANY DIRECTORY”. A questo punto chi crea l’oggetto directory deve anche concedere i privilegi READ e/o WRITE sulla directory ad eventuali altri utenti. Ad esempio:

grant read on directory load_src to scott;

L’utente SYS è l’unico utente che può possedere oggetti directory, ma l’utente SYS può concedere ad altri utenti il privilegio di creare oggetti directory.

Una volta definito un oggetto directory è possibile creare una tabella esterna ad esempio:

CREATE TABLE nome_tabella ( …)
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER
DEFAULT DIRECTORY load_src
ACCESS PARAMETERS (
RECORD DELIMITED BY NEWLINE
FIELDS TERMINATED BY ‘,’
(firs_name CHAR(7),
last_name CHAR(8),
year_of_birth CHAR(4))
)
LOCATION (‘file.dat’));

La parola chiave TYPE serve a indicare l’access driver per la tabella esterna. L’access driver è il modulo che si occupa per il database di interpretare i dati esterni, se viene omessa la specifica TYPE viene considerato come default il driver ORACLE_LOADER (quindi quello di accesso ai file di testo derivato direttamente da SQL*Loader).

Vengono poi speficiati gli ACCESS PARAMETERS che come indicato sulla documentazione sono opachi al database perché servono al driver. Quindi, mentre la prima lista di colonne definisce come viene vista lato database la tabella esterna (a tutti gli effetti analoga a una classica tabella HEAP), mentre la lista campi definita all’interno della sezione ACCESS PARAMETERS serve a indicare al driver come interpretare i dati sul file esterno e come mapparli sulla tabella oracle.

Per capire bene come definire una tabella esterna occorre quindi approfondire la definizione degli ACCESS PARAMETERS, qui mi occupo del driver ORACLE_LOADER, quindi la documentazione pertinente è questa : http://docs.oracle.com/cd/E11882_01/server.112/e22490/et_params.htm#g1031955

ACCESS_PARAMETERS per ORACLE_LOADER

commenti

Come prima parte e solo all’inizio è possibile inserire dei commenti, preceduti dal doppio trattino, poi seguono le altre parti.

record_format_info

Questa parte serve a indicare il formato dei record sul file di testo, per quanto mi riguarda il formato che uso sempre è quello delimitato da « accapo » quindi la clausola è :

RECORDS DELIMITED BY NEWLINE

come per sqlloader sono possibili anche altri formati di cui non parlo qui

poi sono definibili altri parametri come :

  • CHARACTERSET per indicare il charset del file
  • LANGUAGE/TERRITORY, qui la documentazione non è chiara e non ho ancora fatto delle prove, parrebero mutualmente esclusivi, comunque mi sembra possa tornare utile soprauttuo la TERRITORY per un caso neanche tanto particolare, quello dei numeri, infatti come indicato in seguito sulla documenazione per il parametro NLS_NUMERIC_CHARACTERS viene considerato il valore settato sul database server e non quello definito a livello di sessione.
  • BADFILE, file su cui vengono scritti record rigettati per errori
  • DISCARFILE file su cui vengo scritti record rigettati in base alla condizione speficata con la clausola LOAD WHEN
  • LOGFILE file di log
  • SKIP indica il numero di record da saltare dall’inizio del file. Su questa clausola occorre fare attenzione, perché a me in particolari casi non ha funzionato

field_definitions

Questa parte credo sia la più complessa e delicata. Se viene omessa vengono fatte le seguenti assunzioni da Oracle :

  • i campi sono delimitati dalla virgola « , »
  • i campi sono di tipo CHAR
  • la lunghezza massima del campo è 255
  • l’ordine dei campi nel file è nello stesso ordine in cui sono definiti i campi sulla tabella
  • gli spazi vuoti alla fine non vengono rimossi (no trim)

Prima della definizione dei singoli campi è possibile inserire alcune clausole :

  • delim_spec per questa riporto direttamente il grafo dalla documentazione :
  • trim_spec, può essere LRTRIM, NOTRIM, LTRIM, RTRIM, LDRTRIM
  • MISSING FIELD VALUES ARE NULL

Dopo le clausole precedenti si può mettere l’elenco dei campi, altrimenti valgono le seguenti regole :

  • se non viene specificato il tipo viene assounto CHAR(255) per campi delimitatati e CHAR(1) per campi non delimitati
  • se non vine specificata una lista campi allora oracle assume che i campi siano nello stesso ordine in cui sono definiti sulla tabella. I tipo dato viene assunto CHAR(255) se sulla tabella non è di tipo CHAR o VARCHAR2, altrimenti per la lunghezza viene preso il massimo fra 255 e la lunghezza definita per il campo sulla tabella.
  • Se non viene speficata ne’ la lista dei campi ne’ la clausola di specifica sulla delimitazione dei campi allora vale come sopra ma con tipo dato CHAR(255) per tutti e delimitatati da virgola.

Se invece viene specificato l’elenco dei campi il formato è sinteticamente (come riportato dalla documentazione ufficiale :

(field_name [pos_spec] [datatype_spec] [init_spec]) [,  (field_name [pos_spec] [datatype_spec] [init_spec])]…

Il « field_name » viene maiuscolizzato e confrontato  con in nomi dei campi nella tabella, se non ci sono corrispondenze il campo non viene caricato ma può essere usato in altre clausole di valutazione (ad esempio WHEN o NULLIF)

Per quanto riguarda il tipo, personalmente uso praticamente sempre il tipo CHAR, gli altri sono tipi dato in formato interno Oracle, quindi poco usati da me.

Nel caso di date è possibile usare la specifica « date_format », associata al tipo dato CHAR, ad esempio, sempre dal manuale :

employee_hire_date CHAR(10) date_format DATE mask « mm/dd/yyyy »

Qui è possibile vedere il grafo della specifica del date_format

Infine è possibile specificare quella che sul manuale chiama « init_spec Clause » che può essere :

  • DEFAULTIF condition_spec
  • NULLIF condition_spec

ad esempio : NULLIF campox=BLANKS per ulteriori dettagli  rimando sempre al manuale.

Dopo la definizione dei campi (field_definitions) è possibile aggiungere una ulteriore parte opzionale definta « column_trasforms » (http://docs.oracle.com/cd/E11882_01/server.112/e22490/et_params.htm#i1012621)

che permette di specificare come caricare colonne sulla tabella esterna che non mappano direttamente sui campi del file esterno ad esempio :

COLUMN TRANSFORMS (
campoy FROM CONSTANT 'NOT DEF',
campoz FROM NULL,
campog FROM CONCAT(campoa,CONSTANT 'stringa')
)

Un esempio :

drop table exttable1;
create table exttable1
   (
  pk_id	number(5),
  localita VARCHAR2(100),
  xcoord	number(10,8),
  ycoord    number(10,8),
  data_field	date,
  time_field date,
  test_number number(10),
  dummy_field varchar2(20)
)
   organization external (
   type oracle_loader
   default directory UNLOAD
   access parameters (
   records delimited by newline
   BADFILE 'exttable1.bad'
   LOGFILE 'exttable1.log'
   skip 0
   fields terminated by ";"  optionally enclosed by "'"
   MISSING FIELD VALUES ARE NULL
   (
	pk_id char(5),
	data_field char(10) date_format DATE mask 'dd/mm/yyyy',
	time_field char(8) date_format DATE mask 'hh24:mi:ss',
	localita char(100),
	ycoord,
	xcoord,
	test_number
   )
   COLUMN TRANSFORMS (
    dummy_field FROM CONSTANT 'NOT DEF'
   )
   )
   location
   (
   'exttable1.csv'
   )
);
select * from exttable1;

il contenuto di exttable1.csv è :

82;’25/12/2009′;’15:25:31′;’Udine’;13,230234;46,068812;’99’

80;’23/12/2009′;’15:19:19′;’Udine’;13,230134;46,119412;’99’

81;’24/12/2009′;’15:20:16′;’Udine’;13,240134;46,068812;’99’

82;’25/12/2009′;’15:21:12′;’Udine’;13,230234;46,068812;’99’

83;’26/12/2009′;’15:22:52′;’Udine’;13,231134;46,059412;’99’

84;’27/12/2009′;’15:24:22′;’Udine’;13,222134;46,068412;’99’

85;’28/12/2009′;’15:25:31′;’Udine’;13,211134;46,079412;’99’

L’output è :


PK_ID LOCALITA XCOORD YCOORD DATA_FIELD TIME_FIELD TEST_NUMBER DUMMY_FIELD
---------- -------------------- ---------- ---------- ------------------- ------------------- ----------- ------------
 82 Udine 46,068812 13,230234 25-12-2009 00:00:00 01-08-2012 15:25:31 99 NOT DEF
 NOT DEF
 80 Udine 46,119412 13,230134 23-12-2009 00:00:00 01-08-2012 15:19:19 99 NOT DEF
 81 Udine 46,068812 13,240134 24-12-2009 00:00:00 01-08-2012 15:20:16 99 NOT DEF
 82 Udine 46,068812 13,230234 25-12-2009 00:00:00 01-08-2012 15:21:12 99 NOT DEF
 83 Udine 46,059412 13,231134 26-12-2009 00:00:00 01-08-2012 15:22:52 99 NOT DEF
 84 Udine 46,068412 13,222134 27-12-2009 00:00:00 01-08-2012 15:24:22 99 NOT DEF
 85 Udine 46,079412 13,211134 28-12-2009 00:00:00 01-08-2012 15:25:31 99 NOT DEF

Da notare che nel file i numeri hanno come separatore decimale, in questo caso il mio database è configurato in italiano, territorio italiano, se però nel file ho come separatore decimale il punto l’unica soluzione è aggiungere la specifica TERRITORY :

DROP TABLE EXTTABLE2;
create table exttable2
   (
  pk_id	number(5),
  localita VARCHAR2(100),
  xcoord	number(10,8),
  ycoord    number(10,8),
  data_field	date,
  time_field date,
  test_number number(10),
  dummy_field varchar2(20)
)
   organization external (
   type oracle_loader
   default directory UNLOAD
   access parameters (
   records delimited by newline
   TERRITORY AMERICA
   BADFILE 'exttable2.bad'
   LOGFILE 'exttable2.log'
   skip 0
   fields terminated by ";"  optionally enclosed by "'"
   MISSING FIELD VALUES ARE NULL
   (
	pk_id char(5),
	data_field char(10) date_format DATE mask 'dd/mm/yyyy',
	time_field char(8) date_format DATE mask 'hh24:mi:ss',
	localita char(100),
	ycoord,
	xcoord,
	test_number
   )
   COLUMN TRANSFORMS (
    dummy_field FROM CONSTANT 'NOT DEF'
   )
   )
   location
   (
   'exttable2.csv'
   )
);
select * from exttable2;

Perché anche settando a livello di session il parametro NLS_TERRITORY non funziona.

Riferimenti:

Oracle 9i Utilities, Cap. 11;

Oracle 11gR2 Administrator’s Guide (http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables013.htm#i1007424 );

Oracle 11gR2 Utilities (http://docs.oracle.com/cd/E11882_01/server.112/e22490/part_et.htm#i436567);

Pagina successiva »

Blog su WordPress.com.
Entries e commenti feeds.