Integrazione batch – Oracle SQL Developer – WinSCP – UTL_SMTP

L’obiettivo di questo paragrafo è quello di entrare più nel dettaglio delle procedure e dei bat file che sono stati sviluppati, per fornire una visione anche tecnica a quanto scritto precedentemente. Partiamo dalla procedura di avvio del processo ETL. Questa è utile per dare ordine all’esecuzione degli step, e la sua implementazione è la seguente:

PROCEDURE avvio IS BEGIN estrai_sftp_files(); elenca_files_scaricati(); elabora_files_transato(); invia_email_esito(v_id_associato); EXCEPTION invia_email_esito(v_id_associato); END;

Come spiegato in precedenza, questa procedura chiama per prima cosa la “estrai_sftp_dir”, mostrata di seguito:

PROCEDURE estrai_sftp_files IS v_sftp_log VARCHAR2(4000); BEGIN

SELECT LISTAGG(trim(nome_file), '|') WITHIN GROUP (ORDER BY rownum) INTO v_sftp_log

71 FROM STAGE_BETAX.scarica_files_ags;

EXCEPTION RAISE; END;

La tabella impostata nella clausola FROM è una external table, definita come

CREATE TABLE "STAGE_BETAX"."SCARICA_FILES_AGS" ( "NOME_FILE" VARCHAR2(100 BYTE)

) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER

DEFAULT DIRECTORY "BETAX_STAGE_IN_AGS" ACCESS PARAMETERS(

RECORDS DELIMITED BY NEWLINE

PREPROCESSOR unzip_dir_ags:'scarica_BETAX.bat' )

);

Tramite la clausola PREPROCESSOR richiamiamo l’esecuzione del file bat indicato, il quale procede a definire il settaggio delle informazioni necessarie per l’estrazione dall’area SFTP, proseguendo poi con la chiamata al bat che si occupa della sincronizzazione con WinSCP. Di seguito è possibile visionare i due file in questione:

scarica_BETAX.bat:

set sftp_config=D:\...\sftp_config_betax.txt set log_dir=D:\...

call D:\...\sincronizza_sftp_dir %sftp_config% %log_dir%

sincronizza_sftp_dir:

set yyyymmdd=%date:~6,4%%date:~3,2%%date:~0,2%

C:\"Program Files (x86)"\WinSCP\WinSCP /script=%1 /log=%2\sftp_extract_%yyyymmdd%.log

Una volta terminato questo processo, torniamo alla procedura avvio, che esegue la chiamata successiva a elenca_files_scaricati, definita come segue:

PROCEDURE elenca_files_scaricati IS conta_file NUMBER(4):=0;

72 BEGIN

FOR rec_nome_file IN ( SELECT nome_file

FROM STAGE_BETAX.ELENCA_FILES_AGS

WHERE substr(UPPER(nome_file),1,5)='BETAX' AND SUBSTR(UPPER(nome_file),- 4,4)='.ZIP' ) LOOP conta_file := conta_file+1; END LOOP; END;

La tabella evidenziata nella clausola FROM è anch’essa una external table, usata per il richiamo di un bat file che la popoli con l’elenco dei file che necessitano di essere elaborati. La tabella è così definita

:

CREATE TABLE "STAGE_BETAX"."ELENCA_FILES_AGS" ( "NOME_FILE" VARCHAR2(100 BYTE)

) ORGANIZATION EXTERNAL( TYPE ORACLE_LOADER

DEFAULT DIRECTORY "BETAX_STAGE_IN_AGS" ACCESS PARAMETERS(

RECORDS DELIMITED BY NEWLINE

PREPROCESSOR unzip_dir_ags:'elenca_BETAX.bat' )

);

Il file bat richiamato è così definito

set staging_dir=D:\...\

call D:\...\esegui_dir %staging_dir%

quindi, dopo il set della cartella in cui sono situati i file da elaborare, effettua una chiamato ad un ulteriore bat, il esegui_dir.bat, che esegue semplicemente la seguente istruzione:

dir /B %1

73 NOME_FILE STATO File1.zip DA MAPPARE File2.zip DA MAPPARE File3.zip DA MAPPARE File4.zip DA MAPPARE Tabella 8: External table ELENCA_FILE_AGS

A questo punto, tornati nella procedura avvio, viene eseguita la chiamata al terzo step, elabora_files_transato, il quale è strutturato nel seguente modo:

PROCEDURE elabora_files_transato IS nome_file VARCHAR2(200):= '' ; BEGIN

FOR rec_file IN (SELECT * FROM STAGE_BETAX.ELENCA_FILE_AGS) LOOP

BEGIN

nome_file:= rec_file.nome_file ; IF rec_file.stato='DA MAPPARE' THEN

EXECUTE IMMEDIATE 'ALTER TABLE STAGE_BETAX.UNZIP_TRANSATO_AGS LOCATION (rec_file.nome_file)'; carica_transato_alog_temp_ags(nome_file); carica_dwh(rec_file.nome_file); END IF; END LOOP; END;

In pratica, per ogni file zip presente nella tabella ELENCA_FILE_AGS, popolata allo step precedente, che presenta lo stato ‘DA MAPPARE’, si esegue un alter table della external table UNZIP_TRANSATO_AGS, modificando la clausola “LOCATION”, in modo tale da indirizzare l’esecuzione sul prossimo file da elaborare, utile per la procedura successiva.

Subito dopo viene eseguita la procedura ‘carica_transato_alog_temp_ags’, che esegue il caricamento dei dati riportati all’interno dei file .dat estratti per mezzo della external table. Tale procedura è così definita:

PROCEDURE carica_transato_alog_temp_ags(nome_file varchar2) IS trunc_tab varchar2(255):= '';

nome_file_interno varchar2(255):= '' ; BEGIN

trunc_tab:= 'TRUNCATE TABLE STAGE_BETAX.TRANSATO_ALOG_TEMP_AGS';

EXECUTE IMMEDIATE trunc_tab; /* serve a ripulire la cartella temporanea */ FOR rec_file_interno IN (SELECT NOME_FILE FROM UNZIP_TRANSATO_AGS)

74 LOOP

BEGIN

nome_file_interno:= rec_file_interno.nome_file ;

/* alter della location external table */

EXECUTE IMMEDIATE 'ALTER TABLE STAGE_BETAX.TRANSATO_AGS LOCATION (nome_file_interno)';

/* eseguo il parse del file interno per popolare la temp */ STAGE_BETAX.PARSER_SCONTRINI(...);

END; END LOOP; END;

Mediante la SELECT sulla external table possiamo eseguire il parsing di ogni file .dat presente negli zip-file. La external table è così definita:

CREATE TABLE "STAGE_BETAX"."UNZIP_TRANSATO_AGS"( "RECORD_LINE" VARCHAR2(250 BYTE)

) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER

DEFAULT DIRECTORY "BETAX_STAGE_IN_AGS" ACCESS PARAMETERS(

RECORDS DELIMITED BY NEWLINE CHARACTERSET UTF8

PREPROCESSOR unzip_dir_ags:'unzip_BETAX.bat' )

LOCATION('FileN.ZIP') );

Così facendo, si avvia il bat file ‘unzip_BETAX.bat’ sul file puntato nella LOCATION. Il bat file esegue il solo comando

C:\...\unzip.exe -p %1

al termine del quale la tabella UNZIP_TRANSATO_AGS risulterà essere strutturata come riportato di seguito:

75 NOME_FILE STATO File_dati1.dat DA MAPPARE File_dati2.dat DA MAPPARE File_dati3.dat DA MAPPARE File_dati4.dat DA MAPPARE Tabella 9: External table UNZIP_TRANSATO_AGS

Terminate le attività di queste procedure, si passa alla ‘carica_dwh’, che non sarà riportata poiché non utile ai fini del capitolo che si sta trattando. Terminato il caricamento nel DWH, si continua con la procedura ‘avvio’ per concluderla con l’invio della email di resoconto attività.