SQL*Loader, EXTERNAL TABLES e dati delimitati da “TAB”

giovedì 13 novembre 2008 alle 13:54 | Pubblicato su Installation and Configuration, SQL | 10 commenti
Tag: , , ,

Ieri mi è stato passato un file di testo con l’indicazione di caricare i dati su un database. Naturalmente senza indicazione sul tracciato del file, che però era abbastanza piccolo da poter essere aperto con un’editor qualsiasi sul mio PC con Winzoz. Con un certo fastidio ho appurato che si trattava di un file in cui i campi erano delimitato dal caratter TAB (codice ASCII x09). Chiaramente la prima cosa che ho fatto è quella di creare una “External Table” però ho incontrato un problema. Faccio un esempio:

Un file con i seguenti dati (delimitati dal carattere TAB):

12        SARATOGA
10    ACCOUNTING    CLEVELAND
11    ART    SALEM
13    FINANCE    BOSTON
21    SALES    PHILA.
22    SALES    ROCHESTER
42    INT’L    SAN FRAN
TEST    UDINE

E il seguente script

drop table dept;
create table Dept
( deptno number,
dname varchar2(30),
loc varchar2(30)
)
organization external (
type oracle_loader
default directory UNLOAD
access parameters (
records delimited by newline
BADFILE ‘dept.bad’
LOGFILE ‘dept.log’
skip 0
fields terminated by X’9′
OPTIONALLY ENCLOSED BY ‘”‘
(
deptno,
dname,
loc
)
)
location
(
‘dept.txt’
)
);

SELECT COUNT(*) FROM dept;

risultato:

SELECT COUNT(*) FROM dept
*
ERRORE alla riga 1:
ORA-29913: errore nell’esecuzione del callout ODCIEXTTABLEFETCH
ORA-30653: limite di rifiuto raggiunto
ORA-06512: a “SYS.ORACLE_LOADER”, line 52

E nel file di dept.log si trova:

KUP-04021: errore di formattazione campo per il campo LOC
KUP-04023: l’inizio del campo segue la fine del record
KUP-04101: record 1 rifiutato nel file D:\UNLOAD\dept.txt
KUP-04021: errore di formattazione campo per il campo LOC
KUP-04023: l’inizio del campo segue la fine del record
KUP-04101: record 8 rifiutato nel file D:\UNLOAD\dept.txt

La cosa mi ha sconfortato, ho provato a cambiare il delimitatore, usanto “\t”, WHITESPACE ma niente da fare. Per fortuna l’immancabile ricerca su Google mi ha portato da Tom, più precisamente a questa domanda. Dando un’occhiata superficiale mi sembrava proprio il mio problema, ma non capivo perché la soluzione di Tom doveva funzionare e il mio script no. Ebbene, la differenza è proprio la parte ” OPTIONALLY ENCLOSED BY ‘”‘”. Stando al manuale specificando  OPTIONALLY ENCLOSED BY Oracle controlla se c’è il delimitatore di apertura e se lo trova cerca quello di chiusura considerando tutto ciò che vi è compreso come contenuto del campo. Io erroneamente pensavo che se i campi non erano chiusi fra i delimitatori Oracle se ne fregasse e si comportasse bene, invece o mi sfugge qualcosa o si comporta male. Fatto sta che lo script sopra, con quei dati e senza la parte OPTIONALLY ENCLOSED BY funziona perfettamente:

SVILUPPO40@perseo10 > create table Dept
2  ( deptno number,
3  dname varchar2(30),
4  loc varchar2(30)
5  )
6     organization external (
7     type oracle_loader
8     default directory UNLOAD
9     access parameters (
10     records delimited by newline
11     BADFILE ‘dept.bad’
12     LOGFILE ‘dept.log’
13     skip 0
14     fields terminated by X’9′
15     (
16     deptno,
17     dname,
18     loc
19     )
20     )
21     location
22     (
23     ‘dept.txt’
24     )
25  );

Tabella creata.

SVILUPPO40@perseo10 >
SVILUPPO40@perseo10 > SELECT COUNT(*) FROM dept;

COUNT(*)
———-
8

10 commenti »

RSS feed for comments on this post. TrackBack URI

  1. Ciao Cristian,
    su che character set hai operato?

    Alessandro

  2. Che domanda difficile! Il database è in charset AL32UTF8, ma il file è su Win, quindi credo con il cp1252

  3. lo chiedevo perchè bisogna vedere se X’9′ funziona effettuando la cosa su qualsiasi character set

    Alessandro

  4. infatti la tua domanda mi ha fatto venire il dubbio, ma ho provato sia usando ‘\t’ che su un’altro database, versione 9.2.0.8 con charset WE8MSWIN1252 e il comportamento è lo stesso.

  5. buono a sapersi perchè pensavo proprio che non funzionasse a seconda che il character set fosse single oppure multi byte

    Alessandro

  6. ERRORE alla riga 1:
    ORA-29913: errore nell’esecuzione del callout ODCIEXTTABLEFETCH
    ORA-30653: limite di rifiuto raggiunto
    ORA-06512: a “SYS.ORACLE_LOADER”, line 52

    quindi che errore è questo?
    come si risolve

  7. Alessandro,
    quell’errore è generico, indica solo che è stato raggiunto il limite massimo di errori di “parsing” dei dati. Sulla mia istanza di sviluppo, 10.2.0.4 32 bit Winzoz tale limite è 50. Quindi ha trovato 50 righe che non corrispondono al formato specificato nel file di controllo. Gli errori si vedono dal file di log generato che solitamente sta nello stessa direcotory dove si trova il file sorgente

  8. CREATE TABLE TABLE_EXT (
    ooo VARCHAR(8),
    eee VARCHAR2(20),
    bbb VARCHAR(8),
    ccc VARCHAR(4)
    )
    ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_temp
    ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ‘;’
    MISSING FIELD VALUES ARE NULL
    (
    ooo CHAR,
    eee CHAR,
    bbb CHAR,
    ccc CHAR
    )
    )
    LOCATION (‘xxx.txt’)
    )
    REJECT LIMIT UNLIMITED;

    reject limit unlimited quindi non esiste il limite? ma continua a ridarmi lo stesso errore in piu’ non mi crea il file .log

  9. volevo chiederti anche un parere?
    meglio la external table o sqlldr?
    chi ha performance migliori?

  10. Ho risposto troppo in fretta, senza approfondire. La mia risposta si riferiva a Sql*loader. dove c’è la piccola differenza che vi possono essere due tipi di “rigetto“, una da parte di SQL*loader, che quindi comprende eventuali problemi del parsing dei dati sorgente, una da parte di Oracle che è al momento dell’inserimento dei dati nella tabella; questo secondo tipo di errori non credo sia previsto per le external tables. Utilizzando la clausola “REJECT LIMIT UNLIMITED” nel mio caso il count tira fuori 6 record, escludendo occultamente i due che danno errore. Questo secondo me può essere pericoloso.

    Per quanto riguarda le prestazioni di sqllldr e le external table, dipende, il motore di lettura è lo stesso, nell’inserimento può essere con la modalità “DIRECT” sqlldr se la cavi meglio, ma non ho mai approfondito e fatto test al riguardo.


Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

Crea un sito o un blog gratuitamente presso WordPress.com.
Entries e commenti feeds.

%d blogger cliccano Mi Piace per questo: