SQL*LOADER

di Luca Acrì

Sql*loader è un tool utile per il caricamento di dati in tabelle del DB che possono essere letti sia da file di testo che da nastro, chiaramente precedentemente formattati secondo le direttive decise in fase di analisi.
Come le altre utility anche questa ha un help e si attiva eseguendo il comando sqlldr da prompt in modo da poter visualizzare tutte le possibili opzioni.

ES.

C:\>sqlldr

SQL*Loader: Release 8.1.6.0.0 - Production on Gio Lug 26 23:11:35 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Uso: SQLLOAD parola_chiave=valore [,parola_chiave=valore,...]

Parole chiave valide:

userid -- ORACLE username/password
control -- Control file name
log -- Log file name
bad -- Bad file name
data -- Data file name
discard -- Discard file name
discardmax -- Number of discards to allow (Tutte per default)
skip -- Number of logical records to skip (Per default 0)
load -- Number of logical records to load (Tutte per default)
errors -- Number of errors to allow (Per default 50)
rows -- Number of rows in conventional path bind array or between direct path data saves
(Default: Conventional path 64, Direct path all)
bindsize -- Size of conventional path bind array in bytes (Per default 65536)
silent -- Suppress messages during run (header,feedback,errors,discards,partitions)
direct -- use direct path (Per default FALSE)
parfile -- parameter file: name of file that contains parameter specifications
parallel -- do parallel load (Per default FALSE)
file -- File to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Per default FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Per default FALSE)
commit_discontinued -- commit loaded rows when load is discontinued (Per default FALSE)
readsize -- Size of Read buffer (Per default 1048576)

NOTA: Si possono specificare parametri della riga di comando per posizione o per parola chiave. Esempio del primo caso, 'sqlload scott/tiger foo';
esempio del secondo, 'sqlload control=foo userid=scott/tiger'
Si possono specificare dei parametri per posizione prima, ma non dopo, dei parametri specificati da parole chiavi. Ad esempio, è consentito usare 'sqlload scott/tiger control=foo logfile=log' ma non è consentito invece il comando 'sqlload scott/tiger control=foo log', anche se la posizione del parametro 'log' è esatta.

Come si può vedere dal precedente comando,ci sono molte opzioni, ma alcune si utilizzano in casi particolari …noi analizzeremo alcune di queste :

USERID= richiede l'utente e la password dell'utente proprietario delle strutture da caricare, se questo parametro viene omesso viene richiesto in modo interattivo prima di avviare il caricamento.

CONTROL= è il file di parametri

PARALLEL= per grosse moli di dati migliorano le performance, girano più sessioni caricando i dati simultaneamente nella stessa tabella

DIRECT= vengono copiati in memoria come blocchi oracle, poi fisicamente passati sui datafile

Solitamente direct e parallel insieme garantiscono un miglioramento notevole delle performance.

SKIP= (default 0) dice quanti record saltare durante il caricamento, solitamente si utilizza per caricamenti "incrementali"

LOAD=(default all)

LOG =file di log dove viene messo lo spool del caricamento

BAD= "non buoni"

Di default, se non specificati prendono il nome del ctl e vanno a finire nella directory da dove è stato lanciato.

DISCARD= se ci sono alcune condizioni nel tuo ctl che non sono soddisfatte, vengono messe in questo file.

Lo script di definizione (CTL) per il caricamento è diviso in 4 parti principali:

      1) LOAD DATA parola chiave per l'inizio del caricamento

      2) INFILE seguito da il percorso tra apici del file "TXT"

      3) questo punto si divide in 4 sottoparti

      3.1)INSERT inserisce ex novo le righe nella struttura specificata

      3.2)APPEND va ad aggiungere nuovi record nella struttura

      3.3)REPLACE cancella e inserisce i record nella struttura

      3.4)TRUNCATE uguale al replace tronca la tabella e inserisce i dati

     4) Specificazione dei campi e colonne che può essere di due tipi:
         POSIZIONALE O "TERMINATORE" DETERMINATO

ESEMPI

Tutti i link sottostanti sono gli effettivi file prodotti e utilizzati da SQL*LOADER per ogni specifico caricamento.

ESEMPIO NUMERO 1 di tipo POSIZIONALE:

File case1.ctl:

LOAD DATA
INFILE 'd:\loader\ case1.txt' (l' allineamento del file case1.txt potrebbe non essere corretto)
INSERT
INTO TABLE emp
(empno POSITION(01:04) INTEGER EXTERNAL,
ename POSITION(06:15) CHAR,
job POSITION(17:25) CHAR,
mgr POSITION(27:30) INTEGER EXTERNAL,
sal POSITION(32:39) DECIMAL EXTERNAL,
comm POSITION(41:48) DECIMAL EXTERNAL,
deptno POSITION(50:51) INTEGER EXTERNAL)

ESECUZIONE DEL CARICAMENTO:

Da prompt del DOS eseguire:

C:\>sqlldr control=<percorso>\case1.ctl userid=user/pwd@db log=<percorso>\case1.log
Bad=<percorso>\case1.bad*

Il file di log creato sarà il seguente:

loader\case1.log

ESEMPIO NUMERO 2 di tipo DETERMINATO:

In questo esempio viene utilizzata la clausola APPEND al fine di aggiungere i record del file case2.txt a quelli già esistenti.

File case2.ctl:

LOAD DATA
INFILE 'd:\loader\case2.txt APPEND
INTO TABLE emp
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
(empno     ,
ename      ,
job           ,
mgr          ,
sal           ,
comm       ,
deptno  )

ESECUZIONE DEL CARICAMENTO:

Da prompt del DOS eseguire:

C:\>sqlldr control=<percorso>\case2.ctl userid=user/pwd@db log=<percorso>\case2.log
Bad=<percorso>\case2.bad*

Il file di log creato sarà il seguente:

loader\case2.log

File case2.bad:

IN QUESTO CASO E' STATO FORZATO IL BAD LASCIANDO UNA RIGA VUOTA ALLA FINE DEL .TXT e come si può notare il file case2.bad è vuoto proprio perché il record scartato era una linea bianca.

loader\case2.bad

ESEMPIO NUMERO 3 di tipo POSIZIONALE:

In questo esempio l'inserimento avviene in più tabelle "simultaneamente" emp1,emp2 e emp3 create ad hoc con differenti colonne, ma con in comune, il campo ID che determina in base al suo valore per ogni record, in che tabella inserire i dati in questione.

File case3.ctl:

LOAD DATA
INFILE 'd:\loader\case3.txt' (l' allineamento del file case3.txt potrebbe non essere corretto)
INTO TABLE emp
WHEN id='1'
( id        POSITION(01:01) ,
empno    POSITION(02:05) ,
ename    POSITION(07:16) ,
job         POSITION(18:26) ,
mgr        POSITION(28:31) ,
sal         POSITION(33:40) ,
comm     POSITION(42:49) ,
deptno   POSITION(52:53)
)

INTO TABLE emp2
WHEN id='2'
( id         POSITION(01:01) ,
empno     POSITION(02:05) ,
ename     POSITION(07:16) ,
job         POSITION(18:26) ,
comm      POSITION(42:49) ,
deptno    POSITION(52:53)
)

INTO TABLE emp3
WHEN id='3'
(id   POSITION(01:01) ,
empno   POSITION(02:05)
)

ESECUZIONE DEL CARICAMENTO:

Da prompt del DOS eseguire

C:\>sqlldr control=<percorso>\case3.ctl userid=user/pwd@db log=<percorso>\case3.log
Bad=<percorso>\case3.bad*

Come si può vedere nel file di log, effettivamente non vengono caricati tutti i record simultaneamente, ma al contrario per ogni condizione di when viene scandito tutto il file TXT per poi eseguire ciclicamente tante quante sono le condizioni.
Si può notare che anche in questo caso c'è un record eliminato, cioè la riga finale bianca, ma esprime lo stesso concetto del caso precedente.

loader\case3.log

ESEMPIO NUMERO 4 con SEQUENZA:

In questo esempio viene utilizzata oltre i normali costrutti visti, anche l'utilizzo di una sequenza, che viene associata all'attributo contatore, nel caso specifico il campo MAX cerca il valore massimo per quella colonna e poi per ogni record inserito incrementa di 1.

File case4.ctl:

LOAD DATA
INFILE 'd:\loader\case4.txt'
insert
INTO TABLE dept
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
(deptno,
dname,
loc,
contatore SEQUENCE(MAX,1)
)

Attenzione anche in questo caso il file txt ha delle righe in più vuote.

ESECUZIONE DEL CARICAMENTO:

Da prompt del DOS eseguire

C:\>sqlldr control=<percorso>\case4.ctl userid=user/pwd@db log=<percorso>\case4.log
Bad=<percorso>\case4.bad*

File di case4.log:

loader\case4.log

File case4.bad:

Anche in questo caso e' stato forzato il bad lasciando tre righe vuote alla fine del txt, e come si può notare il file case4.bad è vuoto proprio perché i record scartati erano delle linee bianche.

Con questo, si è voluto sottolineare come è creato il file che contiene i dati, perché come dimostrato viene creato un file di scarto, che implica ugualmente di effettuare dei controlli.

loader\case4.bad

ESEMPIO NUMERO 5 con BLOB:

Questo esempio effettua il caricamento di normali datatype ma anche di formati di tipo LOB,
cioè che possono arrivare a dimensioni di 4 GB(dalle versioni 8i).
In questo caso particolare carichiamo dei formati CLOB e BLOB quindi memorizzati internamente al database.
Dove, per i campi blob, viene assegnata una variabile di appoggio che viene associata al campo,
questa variabile inizia a leggere dall' inizio del file che sta esaminando fino alla fine del file (EOF)

ES: img_fname -à immagine.

Mentre il campo CLOB (ultimo) necessita che i dati devono essere racchiusi da dei caratteri di inizio e fine stringa .

ES: '<startlob>' and '<endlob>'

DROP TABLE VOCABOLI_APPOGGIO2
/

CREATE TABLE VOCABOLI_APPOGGIO2 (
VOCTXT VARCHAR2 (100) NOT NULL,
VOCHTML VARCHAR2 (100),
IMMAGINE   BLOB,
AUDIO        BLOB,
VIDEO        BLOB,
VOCDEF      CLOB)
/

File case5.ctl:

LOAD DATA
INFILE 'd:\loader\stringaok.txt'
INTO TABLE vocaboli_appoggio2
FIELDS TERMINATED BY '|'
(voctxt,
vochtml,
img_fname FILLER char(50000),
immagine LOBFILE(img_fname) terminated by EOF,
aud_fname FILLER char(50000),
audio LOBFILE(aud_fname) terminated by EOF,
vid_fname FILLER char(50000),
video LOBFILE (vid_fname) terminated by EOF,
vocdef char(50000) ENCLOSED BY '<startlob>' and '<endlob>'
)

ESECUZIONE DEL CARICAMENTO:

Da prompt del DOS eseguire

C:\>sqlldr control=<percorso>\case5.ctl userid=user/pwd@db log=<percorso>\case5.log
Bad=<percorso>\case5.bad*

File case5.log:

Guardando il file di log, a primo attrito sembrerebbe un disastro, controllando con calma si nota che tutte le 1350 righe sono state caricate, e gli errori sono dovuti tutti ai casi dove non erano presenti i file di tipo BLOB, e quindi sql*loader ci segnala il warning che sono stati allocate delle variabili di memoria e poi non sono state utilizzate.
Difatti nel primo caso, si può notare, come l'unico warning è dovuto alla variabile VIDEO perchè nel TXT non era specificato; mentre analizzando gli altri dove non era specificato nessun tipo di dato BLOB gli errori sono anche di altro tipo.

loader\case5.log

(*)Solo nel caso che ci siano dei record non corretti saranno messi nel record BAD.