La CSP è formata da tante persone, se tutte collaborassero apportando il proprio spicchio di visuale...
da Lezioni di... - indice

nota: la citazione precedente e l'introduzione fanno riferimento
alla mia attuale(?) attività lavorativa

prefazione
introduzione Oracle nel mercato dei db Piano iniziale
Ma.. parte 1 (introduzione al PL/SQL) Pratica
MOOM introduzione
come si usa?
pdf2html
specifiche del protocollo
esempi es. 5 e nota perchè on mail e non on line?
implementazione
schema di MOOM e sorgente php
sicurezza
possibili sviluppi futuri note
outro

Prefazione
(aggiornamento al 21-2-03)

Questo documento è a dir poco antico!! eppure continua ad essere consultato decine di volte al giorno (non so da chi.. eppure è così; v. qui); quindi lo lascio, facendo presente che

c'è una (nuova) sezione Oracle, non semplice come questa, ma in compenso molto più completa
PostgreSQL (di cui si parla in seguito) è un database che ha caratteristiche strutturali ben superiori ad Oracle (ad es. Rules, Aggregates, Operators ecc.), e pur non avendo gli strumenti di management e suites varie che ha Oracle, presenta ottime prestazioni e può essere utilizzato in un ambiente di produzione (v. es. HTML links graph visualizer per un confronto).
(...dimenticavo: è free, cioè gratis;)

Introduzione

Tutto è cominciato alcuni mesi fa (nell'ottobre 2000), prima ancora che vedessero la luce le famigerate Lezioni di...: ad un tratto mi ero reso conto di poter condividere le mie conoscenze dell'ambiente Oracle con i miei colleghi, e non solo loro;
in fin dei conti, era anche nel mio interesse diffondere le conoscenze che permettono di apprenderne rapidamente le basi, a cominciare dal PL/SQL...

Oracle nel mercato dei DB

Pur nella sua posizione di attuale dominanza⁵ del mercato RDBMS, Oracle è comunque "minacciata" dai free database come PostgreSQL, MySQL, ecc... (ad es., nel mese di marzo 2001, Oracle ha licenziato 3000 persone¹);
la stessa rapida successione di major release non fa presagire un futuro molto positivo: Oracle 9i presenta principalmente soltanto una maggiore integrazione del db con internet;

il "nuovo" Oracle Internet Application Server ⁶ (iAS), basato sul server Web Apache (1.3.x tree²), è un prodotto che ha come caratteristica fondamentale solo una maggiore attrattiva per le Fortune 500: una bella confezione, ma praticamente vuota;
dei 36 moduli che lo compongono, 35 sono moduli che fanno parte del server Apache; solo il mod_plsql (che è comunque free software) è specificamente dedicato all'interfacciamento col database engine, e le funzioni che svolge sono le stesse ottenibili con un qualsiasi scripting language, dal Perl, al php, a java, ecc..;

Piano iniziale

L'idea, all'inizio, è stata di scrivere un'introduzione a Oracle molto concisa, che ne prendesse in considerazione i punti che ritenevo fondamentali;
leggerne l'indice, riportato sotto, è il modo migliore per iniziare a conoscere Oracle:
(i link sono ai manuali on line su OTN, Oracle Tecnology Network; la registrazione per l'accesso è gratuita)

parte 1: PL/SQL
anonymous blocks
procedure
function
trigger
SQL, funzioni standard
tipi dati
strutture di controllo: for, while
packages
scoping, ricorsione
exceptions, debug e trace
ottimizzazione
plan

parte 2: DB
dictionary
views
constraints
dependency
synonyms
dblink
parallel query
snapshots e DB distribuiti

parte 3: packages e utilities oracle
import
export
SQL*Loader
utl_file
dbms_output
dbms_debug
dbms_application_info
dbms_sql
dbms_pipe
dbms_alert
dbms_lock

parte 4: DBA
configurazione, startup e shutdown
Sql*Net
backup e recovery
sicurezza: users, privileges, rules, auditing
data blocks, extents e segments
segmenti di rollback e temporanei
tablespaces e datafiles
gestione della memoria e dei processi
SGA e PGA

L'organizzazione in 4 sezioni non corrisponde a quella presente nei manuali Oracle: l'importanza di ottimizzazione e utl_file è evidenziata dalla loro presentazione, nei manuali, in capitoli appositi.

Ma... (Arkesis)³

but life goes on...⁷

Delle 4 parti, ho portato (quasi) a termine solo la parte 1, prima di rendermi conto di star facendo un lavoro abbastanza inutile:
non dubitavo che esistessero già, su internet, delle buone guide introduttive a Oracle, ma non credevo anche in italiano; e purtroppo il problema della lingua per molti è sufficiente a rendere eccessivo l'impegno necessario.

I manuali Oracle (anche questi solo in inglese) sono estremamente dettagliati, ma presentano lo svantaggio, per chi non ha già delle conoscenze di base, di disorientare il lettore e di non presentare gli argomenti in ordine di importanza, o di ripresentarli più volte da diversi punti vista..

Da ricerche su siti in lingua italiana sembrava che gli unici "corsi oracle" fossero pubblicità di corsi a pagamento (per giunta, anche ridicolmente costosi);

ma poi ho trovato, sul sito della Archesis, dei discreti documenti, abbastanza approfonditi e precisi (anche nei pochi casi in cui non è così, è evidente che le imprecisioni sono dovute solo alla fretta degli autori, non a incompetenza), forse soltanto un poco più dispersivi di come li avrei strutturati io.

I punti principali per cominciare sono contenuti nel documento sql_ora, che può essere suddiviso in questo modo:

Tipi di dati ORACLE
DML - Data Manipulation Language
DDL - Data Definition Language
DCL - Data Control Language
Comandi di Amministrazione
Sintassi SQL (operatori e funzioni)
Sql*Plus
PL/SQL Nell'ultimo paragrafo (PL/SQL) vengono ripresi gli argomenti che ho trattato in parte 1: PL/SQL;
purtroppo, nel complesso, anche questi documenti sono "sbilanciati", riservando troppa poca importanza ad alcuni argomenti e troppa ad altri; ma per ora dovrete arrangiarvi...

Pratica (praxis)⁴

Diciamo che a questo punto avete una documentazione sufficiente (e organizzata) per cominciare a fare pratica col db.
Ma pochi avranno voglia di

procurarsi un server Oracle (si può richiedere un CD, gratis, on-line sul sito Oracle, ma a me non è mai arrivato...)
installarlo
farlo funzionare

cose che richiedono già delle discrete conoscenze, un buon PC e una mezza giornata di tempo (per chi non ha mai fatto un'installazione);

eppure, un corso che non permetta di mettere in pratica e provare quello che si è imparato, evidentemente non serve a molto...

MOOM (my oracle on mail)

introduzione
come si usa?
pdf2html
specifiche del protocollo
esempi
perchè on mail e non on line?
implementazione
schema di MOOM
sicurezza
possibili sviluppi futuri

introduzione

Nasce in questo modo MOOM, uno script php che permette di interfacciare un db oracle (ma lo stesso vale per qualsiasi db per il quale si possa creare una connessione via ODBC) con internet, avendo semplicemente

una connessione al db via ODBC (una DSN definita)
un interprete php locale in grado usare quella DSN
un server POP (o IMAP) da cui scaricare le mail, che raccoglierà le richieste da inviare al db
un server SMTP (o ESMTP) con cui inviare le mail, che spedirà indietro i risultati a chi li aveva richiesti

ad alcuni ciò potrà sembrare complicato, ma non lo è; chi è interessato può trovare dopo i dettagli tecnici dell'implementazione;
quello che è importante per il momento è come usarlo:

come si usa?

chi vorrà mi potrà richiedere una password (l'approvazione è a mia discrezione, ma non vale solo per i colleghi..)
una volta ricevuta la password, potrà eseguire qualsiasi operazione sul db (in un ambito ristretto da esigenze di sicurezza), inviando una mail il cui soggetto e body rispettino determinate regole
riceverà il risultato al suo indirizzo email in formato HTML
il tempo di risposta non sarà ovviamente paragonabile a quello di una connessione LAN, ma chi conosce TSO, MVS e amenità del genere non si meraviglierà troppo...

pdf2html

Il principio è lo stesso usato dalla Adobe per permettere la conversione dei file .pdf (portable document format) in testo o HTML (ma ciò, per la precisione, l'ho scoperto dopo...); ad esempio, è possibile, spedendo all'indirizzo pdf2html@adobe.com un documento PDF, ricevere un equivalente HTML, oppure (spedendolo all'indirizzo pdf2txt@adobe.com) l'equivalente in semplice testo (plain text);
ciò è utile ad es. nel caso si voglia "copiare e incollare" una parte del documento, ecc...
(qui troverete informazioni dettagliate sul metodo da seguire);

il metodo può essere chiaramente utilizzato in generale come mezzo di comunicazione automatizzato alternativo all'HTTP, ma rispetto a questo presenta il grosso limite del tempo di risposta e della tecnica push/pull che adotta (il client invia la richiesta al mail server; il convertitore pdf2html la scarica ad intervalli regolari, la processa e la reinvia; dal lato opposto, il client controlla periodicamente l'arrivo di nuova posta, tra la quale può trovare la risposta);

specifiche del protocollo

Le richieste da inviare via mail dovranno rispettare i seguenti criteri:

soggetto (subject) della mail:
è composto da 4 parametri, separati da virgole (",")

"coracle" : costante, case-sensitive, indica che la mail è da elaborare
indirizzo email a cui rispondere, nel formato classico "you@site.com"
la password che vi fornirò
un parametro numerico opzionale, che specifica il timeout in secondi, cioè ogni quanto tempo MOOM deve controllare l'arrivo di richieste; il default è inizialmente 30 sec., e aumenta esponenzialmente del 110% ad ogni ciclo;

body:
la sintassi da usare per gli statement è esattamente la stessa specificata nei manuali Oracle, utilizzabile ad es. da Sql*Plus

statement di select, insert, update, delete, create, drop, alter, ecc., terminato da una riga contenente il carattere "/" (slash) in prima posizione;
non occorrono ";" per terminare lo statement

le mail devono essere inviate in formato plain text, non HTML;
per chi usa M$ Outlook: dalla voce di menu Formato di Nuovo messaggio selezionare Testo normale;
per gli altri: saprete già come fare..

I risultati verranno rispediti indietro in questo formato:
per ogni statement:

i primi 80 caratteri del comando inviato;
il risultato (nel caso si tratti di una select)
l'errore relativo allo statement (se presente)
tutti gli errori dell'utente (se presenti)

Probabilmente sarà necessaria una fase di tuning iniziale, per migliorare e rendere più efficace l'interfaccia utente: attendo suggerimenti..

esempi

Alcuni esempi di mail corretti dal punto di vista del protocollo di comunicazione (ma non necessariamente per la sintassi PL/SQL) sono:

(es. 1)

subject:
body:	create table tabella1 (col1 integer, col2 date) /

il risultato che verrà inviato come output sarà semplicemente:

create table tabella1 (col1 integer, col2 date)

(es. 2):

body:

create or replace procedure prova1 is
begin
 insert into tabella1 values (1,sysdate);
end;
/

risultato:

create or replace procedure prova1 is begin insert into tabella1 values (1,sys...

(es. 3)

body:

create tabella2 (col1 integer, col2 date)

risultato:

create tabella2 (col1 integer, col2 date)

SQL error: [Oracle][ODBC Oracle Driver][Oracle OCI]ORA-00901: invalid CREATE command., SQL state 37000 in SQLExecDirect

(es. 4)

body:

create or replace procedure prova2 is
begin
  insert into tabella1 (2,sysdate);
end;
/

risultato:

create or replace procedure prova2 is begin insert into tabella1 values (2,sysda...

errori:

NAME	TYPE	SEQUENCE	LINE	POSITION	TEXT
PROVA2	PROCEDURE	1.	3.	24.	PLS-00103: Encountered the symbol "2" when expecting one of the following: ( select <an identifier> <a double-quoted delimited-identifier>

(es. 5 e nota) (creazione di 2 funzioni e select finale)

perchè on mail e non on line?

Qualcuno si chiederà: non era più facile creare una normale interfaccia Web (chiamata di solito - impropriamente - CGI) che permettesse di eseguire un comando Oracle e vederne subito il risultato? (un anno fa me lo sarei chiesto anch'io..)

la risposta è: tecnicamente più facile, ma irrealizzabile poi in pratica:

per farlo sarebbe necessario un server Web, avente un indirizzo IP su internet, in grado di connettersi al db;
in genere (e nel mio caso particolare) la situazione invece è diversa:

l'accesso a internet è via LAN;
server Web e server Oracle fanno parte della stessa LAN, ma nessuno dei 2 ha un indirizzo IP visibile da internet;
la LAN è protetta da un firewall (che costituisce l'unico punto di contatto con internet) che permette le connessioni in uscita⁸ ma maschera quelle in ingresso.

implementazione

I requisiti essenziali per il funzionamento di MOOM sono i 4 prima citati:

una DSN ODBC per il collegamento al db
un interprete php
un server SMTP per spedire le mail (risposte)
un server POP3/IMAP da cui scaricare le mail (richieste)

altri componenti utili per il debug e per un funzionamento stabile sono:

un server Web (apache)
un browser da command-line

tutti questi componenti sono indipendenti dal sistema operativo, ma nel seguito mi riferirò alla creazione dell'ambiente su Windows;

la creazione di una DSN (Data Source Name, che si effettua da pannello di controllo / origine dati ODBC) richiede la presenza di

un client per il collegamento al db (installato generalmente, nel caso di Oracle, in una directory ORANT o ORAWIN)
le dll specifiche per il collegamento via ODBC

entrambi fanno parte della distribuzione Oracle Client Software (ad es. su CD), assieme ad un'applicazione per il test della connettività ODBC (utilizzabile anche per il collegamento a db diversi da Oracle);

l'installazione dell'interprete php è abbastanza complessa: manuali, istruzioni per l'installazione, sorgenti, mailing-list ecc. sono sul sito php; gli eseguibili per Windows sono su php4win (la procedura di compilazione a partire dai sorgenti è molto complicata e richiede vari altri componenti oltre ad un compilatore C++);

i server SMTP e POP non sono da installare, ma sono quelli forniti dal provider (ISP);
per chi usa M$ Outlook: scegliere dal Menu strumenti - account - proprietà - server;
l'indirizzo del server SMTP è da impostare nel file di inizializzazione del php (php.ini), alla voce SMTP; quello del server POP (o IMAP), assieme a user e password per l'accesso alla mailbox, vanno invece inserite direttamente nello script;

il browser da command-line serve unicamente a poter inviare ripetutamente la stessa richiesta al server Web (ad esempio con un processo batch in un .bat) in caso di crash del server;
per questo è sufficiente cURL, che non offre nessuna funzione di visualizzazione e formattazione dei dati, ma solo la connettività per vari protocolli over TCP/IP.

schema di MOOM

MOOM alla fin dei conti è un server: comunica in ingresso via POP/IMAP e in uscita via SMTP;
l'elaborazione interna avviene via ODBC, attraverso cui invia e riceve le richieste a/da Oracle;

ciclo infinito: controlla mail box: per ogni mail arrivata: se è da elaborare: per ogni statement contenuto nella mail: esegue statement invia risultato completo
crea file di log del risultato elimina mail (elaborata o no) sleep per x secondi

il sorgente php è qui

sicurezza

Si possono considerare principalmente 3 livelli relativi alla sicurezza:

rete
database
password

nonostante l'apparente pericolosità dell'ambiente nel suo complesso descritto nell'implementazione, non dovrebbero essere possibili attacchi a livello di rete;
il server Web su cui gira MOOM è mascherato dal firewall, e non è accessibile in alcun modo da internet (a meno di flaws nel firewall stesso, ma questo non dipende da MOOM);
tutti i comandi diretti al db passano attraverso ODBC: quindi possono essere eseguiti solo comandi interpretati correttamente dell'RDBMS;

a livello di database, i problemi (causati anche involontariamente) possono essere maggiori:

sicurezza dei dati (il db a cui attualmente MOOM si collega è di sviluppo, ma contiene comunque dati sensibili)
sovra-utilizzo delle risorse di sistema:
- CPU
- memoria
- spazio su disco

in tutti i casi la sicurezza è garantita dall'RDBMS (non sono presenti controlli specifici sui comandi inviati);

in Oracle il procedimento per la creazione di un utente è:
creazione di un profilo (opzionale), quindi dell'utente che ha quel profilo (se non specificato viene usato un profilo di default), e assegnazione dei privilegi;

creazione del profilo (profile)

create profile nome_profilo limit
sessions_per_user 2
cpu_per_session 300
cpu_per_call 50
connect_time  36
idle_time  12
logical_reads_per_session 50
logical_reads_per_call 10
private_sga 500 k ;

creazione dell'utente (user)

create user nome_user identified by nome_password
quota 2 m on data
quota 2 m on temp
quota 2 m on indici
quota 2 m on rbs1
quota 2 m on system
quota 2 m on work
profile nome_profilo;

grant dei privileges

grant create session         to nome_user;
grant create procedure       to nome_user;
grant create sequence        to nome_user;
grant create synonym         to nome_user;
grant create table           to nome_user;
grant create trigger         to nome_user;
grant create view            to nome_user;

come si può vedere, le caratteristiche dell'utente e del profilo sono estremamente limitate;
il problema più serio che può capitare è il riempimento dello spazio su disco dell'area riservata all'utl_file (non esiste, in Oracle 7.3.4, un modo per limitare l'utente da questo punto di vista);

Un grosso rischio da tenere presente sono i public db_links, che permettono, anche a una user definita come sopra, di accedere al db a cui sono diretti (a differenza dei public synonyms, che invece non possono essere usati).

a livello di password ho volutamente trascurato la possibilità di intercettazione delle mail (per sniffing o semplicemente per "curiosità" dei provider..) perchè

la possibilità è abbastanza remota
L'RDBMS offre comunque una protezione della intranet e limita sufficientemente l'uso delle risorse
una mail proveniente da un falso utente che avesse intercettato la password
1. non otterrebbe nessuna risposta (che andrebbe invece al vero utente)
2. può essere individuato con molta facilità utilizzando gli header Received del messaggio (anonymizer a parte...)

possibili sviluppi futuri

Gli sviluppi futuri che potrebbero essere interessanti sono, secondo me:

invio (in risposta) di file creati tramite utl_file, eventualmente zippati;
interazione e collaborazione tra più utenti (creazione di una user comune, oppure visibilità di un sottinsieme di oggetti di un utente da parte di un altro)
accesso ad altre tabelle del db non contenenti dati sensibili, utili per i test (ed esempio: province, nazioni, divise..)

note

3000 persone:
fonte: Metro
1.3.x tree:
attualmente (2-4-2001) la versione 2 dell'httpd Apache è alla fase alpha 0.14 (il branch1.3 è ufficialmente l'ultimo supportato): Oracle ha scelto di puntare sulla versione stabile, anche se quella nuova potrebbe essere in production release in pochi mesi;
arkesis:
aiuto, soccorso, assistenza, utilità; v. trad.
praxis:
pratica, prassi; v. trad.
posizione di attuale dominanza del mercato:
Larry Ellison, Presidente e CEO, Oracle Corporation: "Siamo tuttavia soddisfatti delle nostre capacità che ci hanno consentito di ottenere in questo trimestre un margine operativo del 33%..."; v. articolo
iAS:
dalla presentazione italiana del prodotto: (testualmente, compresa "necessitagrave")
"Salva tempo e risorse. Costruisci i tuoi portali rapidamente con Oracle9i Application Server - non sprecare tempo prezioso ad integrare componenti da fornitori diversi. Oracle9i Application Server elimina il bisogno di scrivere codice customizzato e la necessitagrave; di comprare prodotti ulteriori separatamente."
v. articolo
but life goes on..:
My charred body will decay
But my soul will be floating anyway v. CD
connessioni in uscita su porte standard:
es.: 7 (echo), 9 (discard), 20 (FTP-data), 21 (FTP), 23 (telnet), 25 (SMTP), 42 (name), 53 (domain), 103 (dictionary), 110 (POP3), 143 (IMAP)...
molti firewall impediscono il collegamento su porte "insolite", anche se definite nell' RFC 1700

outro

Mi auguro che partecipiate attivamente,
per rendere vivo, utile e fruibile questa area
che rappresenta un momento di crescita professionale ...
da Lezioni di... - indice [il link era questo, una volta..]