M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL-2 T5

Autoritzar al usuari HR a emprar el paquet UTL_FILE:

  SQL> connect sys as sysdba;

  SQL> grant execute on sys.utl_file to hr;

En aquesta pràctica, utilitzarà el paquet UTL_FILE per generar un informe d'arxiu de text dels empleats de cada departament. Primer cal crear i executar un procediment anomenat EMPLOYEE_REPORT, que generà un informe d'empleats en un arxiu del sistema operatiu utilitzant el paquet UTL_FILE. Aquest informe generarà una llista dels empleats que tenen un salari superior al salari mig del seu departament. Finalment, es mostrara el contingut de l'arxiu de text per pantalla.

1. Crea un procediment anomenat EMPLOYEE_REPORT, que generi un informe d'empleats en un arxiu del sistema operatiu utilitzant el paquet UTL_FILE.

Aquest informe generarà una llista dels empleats que tenen un salari superior al salari mig del seu departament.

a) El procediment rebrà dos paràmetres. El primer és el directori de sortida. El segon és el nom de l'arxiu de text.

Nota: Utilitza el valor de la ubicació del directori UTL_FILE. Agrega una secció per manegar les excepcions que es poden produir al utilitzar el paquet UTL_FILE.

-- Verify with your instructor that the database initSID.ora
-- file has the directory path you are going to use with this
-- procedure.
-- For example, there should be an entry such as:
-- UTL_FILE_DIR = /home1/teachX/UTL_FILE in your initSID.ora
-- (or the SPFILE)
-- HOWEVER: The course has a directory alias provided called
-- "REPORTS_DIR" that is associated with an appropriate
-- directory. Use the directory alias name in quotes for the
-- first parameter to create a file in the appropriate
-- directory.

CREATE OR REPLACE PROCEDURE employee_report(
  p_dir IN VARCHAR2, p_filename IN VARCHAR2) IS
  f UTL_FILE.FILE_TYPE;
  CURSOR cur_avg IS
    SELECT last_name, department_id, salary
    FROM employees outer
    WHERE salary > (SELECT AVG(salary)
                    FROM employees inner
                    GROUP BY outer.department_id)
    ORDER BY department_id;
BEGIN
  f := UTL_FILE.FOPEN(p_dir, p_filename,'W');
  UTL_FILE.PUT_LINE(f, 'Employees who earn more than average salary: ');
  UTL_FILE.PUT_LINE(f, 'REPORT GENERATED ON ' ||SYSDATE);
  UTL_FILE.NEW_LINE(f);
  FOR emp IN cur_avg
  LOOP
    UTL_FILE.PUT_LINE(f, RPAD(emp.last_name, 30) || ' ' ||
    LPAD(NVL(TO_CHAR(emp.department_id,'9999'),'-'), 5) || ' '||
    LPAD(TO_CHAR(emp.salary, '$99,999.00'), 12));
  END LOOP;
  UTL_FILE.NEW_LINE(f);
  UTL_FILE.PUT_LINE(f, '*** END OF REPORT ***');
  UTL_FILE.FCLOSE(f);
END employee_report;
/

b) Crea i compila el procediment.

PROCEDURE employee_report Compiled.

2. Crida al procediment amb els dos arguments següents:

a) Utilitza REPORTS_DIR com alies per l'objecte de directori com a primer paràmetre.

b) Utilitza sal_rpt.txt com a segon paràmetre.

EXECUTE employee_report('REPORTS_DIR','sall_rpt.txt')

3. Per mostrar el contingut de l'arxiu, procedeix de la següent forma:

a) Fes clic dues vegades en la icona Terminal de l'escriptori. Es mostrarà la finestra Terminal.

b) Situat en el directori on es trobi l'arxiu generat (/home/oracle/labs/plpu/reports),

Nota: pots utilitzar l'ordre pwd per mostrar el directori actual.

c) Llista el contingut del directori (ordre ls).

d) Obre l'arxiu transferit sal_rpt.txt, amb l'editor que vulguis.