Pachete sistem

Exemple de pachete sistem

Observaţie.

...\RDBMS\ADMIN\

select object_name from all_objects where object_type='PACKAGE' order by 1;

DBMS_STANDARD - defineşte limbajul PL/SQL pentru interacţiunea cu serverul Oracle

DBMS_ALERT - permite avertizarea unei aplicaţii atunci când apare un anumit eveniment

DBMS_OUTPUT - permite extragerea de date din blocurile PL/SQL (în SQL*Plus, sau iSQL*Plus)

DBMS_PIPE - oferă posibilitatea de comunicare între procese, prin intermediul unui canal de comunicare. Pe un astfel de canal se pot trimite şi se pot recepţiona mesaje.

UTL_FILE - permite programelor PL/SQL să citească şi să scrie fişiere text ale sistemului de operare (unde se află serverul Oracle)

DBMS_LOB - este pachetul care permite gestiunea datelor de tip LOB: BLOB (date binare), CLOB (şiruri de caractere, 1 caracter/1 octet), NCLOB (şiruri de caractere, 1 caracter/ 1,2,... octeţi), BFILE (date BLOB memorate în fişiere externe).
Valoarea unei coloane de tip LOB, dintr-o înregistrare a unui tabel memorat, nu poate fi null, ea poate fi: o valoare concretă, sau o valoare obţinută cu una din funcţiile EMPTY_BLOB() sau EMPTY_CLOB()

UTL_JOB - permite planificarea spre execuţie a unor proceduri memorate. Execuţia se poate face regulat, la anumite intervale de timp.

DBMS_METADATA - permite obţinerea de informaţii despre componentele memorate în baza de date

DBMS_SQL - permite execuţia comenzilor SQL-DDL care definesc, modifică, şterg componente din baza de date (se va vedea şi instrucţiunea Execute immediate).

Pentru a executa instrucţiuni SQL, cu acest pachet, trebuie să existe un cursor deschis, identificat printr-un număr. Un astfel de cursor se crează prin funcţia:
```
DBMS_SQL.OPEN_CURSOR return integer
```
Fiecare instrucţiune SQL (DDL sau de gestiune a componentelor memorate în baza de date) trebuie analizată într-un astfel de cursor cu procedura PARSE:
- PARSE(c in integer, instrucţiune in varchar2, versiune in integer)
  unde:
  - c - este identificatorul cursorului, furnizat de OPEN_CURSOR
  - instrucţiune - este un şir de caractere care precizează instrucţiunea care se analizează
  - versiune - precizează versiunea de Oracle în care se face analiză, cu valoarea 'NATIVE' se foloseşte versiunea curentă în care se execută programul
- Dacă instrucţiunea care trebuie analizată este lungă (are multe caractere), atunci se poate trece pe poziţii consecutive: inc, inc+1, ..., sf într-un tabel definit astfel:
```
type t is table of varchar2(256) index by binary_integer;
```
  In acest caz procedura este:
```
    	  PARSE(c in integer, instructiune in varchar2, inc in integer, sf in integer,   
    	        lf in boolean, versiune in integer);
    	  
```
  Parametrul lf precizează cu true că se adaugă "newline" după fiecare linie din instrucţiune.
O instrucţiune analizată într-un cursor se poate executa de oricâte ori prin funcţia:
```
EXECUTE(c in integer) return integer;
```
Valoarea funcţiei are sens la execuţia instrucţiunilor Insert, Delete, Update - şi precizează numărul de linii prelucrate
Inchiderea unui cursor se realizează cu procedura:
```
CLOSE_CURSOR(c in out integer);
```
După apel valoarea parametrului c (identificarea cursorului) va fi null.
Obs. Un cursor neînchis ocupă memorie, deci este necesară inchiderea lui (inclusiv la apariţia de erori).
Cu funcţia:
```
IS_OPEN(c in integer) return boolean
```
se poate verifica dacă un cursor, dat printr-un număr, este deschis sau nu
Alte funcţii şi proceduri utile din acest pachet: DEFINE_COLUMN, FETCH_ROW, COLUMN_VALUE

Exemple

Funcţie pentru execuţia unei instrucţiuni

--executia unui sir de caractere, interpretat ca o instructiune
  create or replace function exec(sir in varchar2) return boolean 
  as
    c integer;
    n integer;
  begin
    -- deschide un cursor
    c:=dbms_sql.open_cursor;
    -- analizeaza instructiunea SQL data ca argument
    dbms_sql.parse(c, sir, dbms_sql.native);
    -- executa aceasta instructiune
    n:=dbms_sql.execute(c);
    -- inchide cursorul
    dbms_sql.close_cursor(c);
    return true;
  exception
    -- daca apare eroare, se inchide cursorul
    when others then
      dbms_sql.close_cursor(c);
      return false;
  end;

Utilizarea funcţiei precedente

set serveroutput on
  
  declare
    r boolean;
    x char(2);
  begin
    dbms_output.enable;
    r:=exec('create table test (nume char(20), prenume char(20), cnp char(13))');
    if r then x:='Da'; else x:='Nu'; end if;
    dbms_output.put_line(x);
  end;

Funcţie pentru ştergerea unui tabel

create or replace function drop_table(nume_tabel in varchar2) return boolean 
  as
    c integer;
    n integer;
  begin
    -- deschide un cursor
    c:=dbms_sql.open_cursor;
    -- analizeaza o instructiune SQL ce sterge un tabel dat ca argument
    dbms_sql.parse(c,'drop table ' || nume_tabel, dbms_sql.native);
    -- executa aceasta instructiune
    n:=dbms_sql.execute(c);
    -- inchide cursorul
    dbms_sql.close_cursor(c);
    return true;
  exception
    -- daca apare eroare se inchide cursorul
    when others then
      dbms_sql.close_cursor(c);
      return false;
  end;

Utilizarea funcţiei precedente

set serveroutput on
  declare
    r boolean;
    x char(2);
  begin
    dbms_output.enable;
    r:=drop_table('test');
    if r then x:='Da'; else x:='Nu'; end if;
    dbms_output.put_line(x);
  end;

DBMS_RANDOM - permite generarea de date numerice, caracter, alfanumerice, caractere imprimabile.
Funcţii utile din acest pachet:

value(a,b) - generează un număr aleator în intervalul [a,b]
value - generează un număr aleator în intervalul [0,1]
random - generează un număr aleator întreg
string(c,lungime) - generează un şir de caractere cu lungimea precizată de al doilea argument. Sirul de caractere este format din caractere precizate de primul argument (un caracter):
- 'U' - litere mari
- 'L' - litere mici
- 'A' - caractere alfanumerice
- 'X' - caractere alfanumerice cu litere mari
- 'P' - caractere imprimabile (litere, cifre, caractere imprimabile)

Exemplu pentru generarea de date într-un tabel:
Considerăm tabelul:

create table temp(nume varchar2(20), prenume varchar2(20), cnp char(13), media number(5,2));

Următoarele instrucţiuni generează 10000 înregistrări în acest tabel:

declare
  m number(5,2);  
  n integer;
  cnp varchar2(13);
  
  function gen_cnp return varchar2 as
    --generarea unui cod numeric personal
    i integer;
    j integer;
    c varchar2(13);
    type t is VARRAY(12) of number(1);
    a t:=t(2,7,9,1,4,6,3,5,8,2,7,9);  --ponderi pentru primele 12 cifre
    k integer;
  begin
    i:=dbms_random.value(1,2);
    c:=to_char(i);  -- prima pozitie din cnp
    k:=i*a(1);
    for j in 1..11 loop
      i:=dbms_random.value(0,9);
      c:=c || to_char(i);
      k:=k+i*a(j+1);
    end loop;
    if mod(k,11)=10 then k:=k-9; end if;
    k:=mod(k,11);  --cifra de control
    c:=c || to_char(k);
    return c;
  end;
begin
  for n in 1..10000 loop
    m:=dbms_random.value(5,10);
    cnp:=gen_cnp;
    insert into temp values(dbms_random.string('U', 1) || dbms_random.string('L', 19),
                            dbms_random.string('U', 1) || dbms_random.string('L', 19),cnp,m);
  end loop;
end;
/
select * from temp;
--drop table temp;

DBMS_XPLAN - permite extragerea informatiilor create de instructiunea EXPLAIN PLAN. In acest pachet există funcţia DISPLAY, care furnizează o colecţie de informaţii. Această funcţie are următorii parametri:

DBMS_XPLAN.DISPLAY(
  table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
  statement_id IN VARCHAR2 DEFAULT NULL,
  format IN VARCHAR2 DEFAULT 'TYPICAL');

Al treilea parametru precizează informaţiile care sunt furnizate: BASIC, TYPICAL, ALL, SERIAL.
Pentru afişarea acestor informatii (cu instructiunea select-sql) se va folosi funcţia TABLE, care converteşte colecţia de informaţii la un tabel.

UTL_TCP, UTL_SMTP, UTL_MAIL - trimiterea de emailuri din PL/SQL

Exemplu:

DECLARE
  exp        VARCHAR2(80) := 'horea@cs.ubbcluj.ro';
  dest       VARCHAR2(80) := 'horea@cs.ubbcluj.ro';
  subiect    VARCHAR2(80) := 'test subject';
  server_mail VARCHAR2(30) := 'nessie.cs.ubbcluj.ro';
  mail_conn utl_smtp.Connection;
  crlf       VARCHAR2(2)  := chr(13) || chr(10);
BEGIN
   Mail_Conn := utl_smtp.Open_Connection(server_mail, 25); 
   -- deschide o conectiune la un server de mail
   -- 25 este portul implicit
   utl_smtp.Helo(mail_conn, server_mail);
   -- necesar dupa conectare (autentificare dupa conectare?)
   utl_smtp.Mail(mail_conn, exp);
   -- initiaza o tranzactie cu serverul de mail
   utl_smtp.Rcpt(mail_conn, dest);
   -- specifica destinatarul
   utl_smtp.Data(mail_conn,
     'Date: '   || to_char(sysdate, 'MONTH DD, YYYY, HH24:MI:SS') || crlf ||
     'From: '   || exp || crlf ||
     'Subject: '|| subiect || crlf ||
     'To: '     || dest || 
     crlf || crlf ||
     'linie din mesaj'|| crlf ||	
     'alta linie din mesaj'|| crlf
   );
   -- continutul emailului, inclusiv headers
   -- necesar "crlf || crlf" inainte de mesaj
   utl_smtp.Quit(mail_conn);
  EXCEPTION
   WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
     raise_application_error(-20000, 'Eroare: ' || sqlerrm);
END;