Writing content from Oracle

This note provides a PL/SQL programme that writes the the content of a table to a file. It also uses a steering table (emp_ids) that contains a series of ids that can be used in a susequent query from which the content can be generated.

Let me first provide the code:

create or replace PROCEDURE print_emp_name_dpmt AS
   CURSOR id_cur
   IS SELECT distinct department_id  FROM HR.emp_ids;
   emp_rec id_cur%ROWTYPE;
   CURSOR id_dpt 
   IS SELECT employee_id, first_name FROM employees WHERE department_id = emp_rec.department_id;
   dpt_rec id_dpt%ROWTYPE;
 BEGIN
  OPEN id_cur;
  LOOP
    FETCH id_cur INTO emp_rec;
    EXIT WHEN id_cur%NOTFOUND;
    OPEN id_dpt;
    LOOP
      FETCH id_dpt INTO dpt_rec;
      EXIT WHEN id_dpt%NOTFOUND;
      dbms_output.put_line('The employee ' || dpt_rec.first_name ||';'||emp_rec.department_id||';'||dpt_rec.employee_id);
    END LOOP;
    CLOSE id_dpt;
  END LOOP;
 END;

I created two cursors. One cursor contains the query outcomes from the steering table. A second cursor contains the outcomes from a query that must be written to a file.

The two cursors are interwoven in two loops. Each loop fetches a record from a query. In the centre, the results are written.

If one executes this PL/SQL procedure in SQL plus with a spool on-command, one captures the results.