Save table records to a file : UTL_FILE « System Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Packages
3. UTL_FILE

SQL>
SQL> CREATE TABLE employees
  2  ( employee_id          number(10)      not null,
  3    last_name            varchar2(50)      not null,
  4    email                varchar2(30),
  5    hire_date            date,
  6    job_id               varchar2(30),
  7    department_id        number(10),
  8    salary               number(6),
  9    manager_id           number(6)
 10  );

Table created.

SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
  2                values ( 1001, 'Lawson', 'lawson@g.com', '01-JAN-2002','MGR', 30000,1 ,1004);

1 row created.

SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                values ( 1002, 'Wells', 'wells@g.com', '01-JAN-2002', 'DBA', 20000,2, 1005 );

1 row created.

SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                 values( 1003, 'Bliss', 'bliss@g.com', '01-JAN-2002', 'PROG', 24000,3 ,1004);

1 row created.

SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1004,  'Kate', 'YourName@a.com', SYSDATE-3650, 'MGR',25000 ,4, 1005);

1 row created.

SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1005, 'Dillon', 'sdillon@a .com', SYSDATE, 'PROG', 20000, 1, 1006);

1 row created.

SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
  2                 values( 1006, 'Beck', 'clbeck@g.com', SYSDATE, 'PROG', 20000, 2, null);

1 row created.

SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1007, 'Java', 'java01@g.com', SYSDATE, 'PROG', 20000, 3, 1006);

1 row created.

SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1008, 'Oracle', 'oracle1@g.com', SYSDATE, 'DBA', 20000, 4, 1006);

1 row created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE emps2file (
  2     loc IN VARCHAR2,
  3     file IN VARCHAR2
  4     )
  5  IS
  6     fid UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN (loc, file, 'W');
  7     line VARCHAR2(2000);
  8  BEGIN
  9     FOR rec IN (SELECT last_name, hire_date, salary FROM employee)
 10     LOOP
 11        line :=
 12           rec.last_name || ',' ||
 13           TO_CHAR (rec.hire_date, 'MM/DD/YYYY') || ',' ||
 14           rec.salary;
 15        UTL_FILE.PUT_LINE (fid, line);
 16     END LOOP;
 17     UTL_FILE.FCLOSE (fid);
 18  EXCEPTION
 19     WHEN OTHERS THEN UTL_FILE.FCLOSE (fid);
 20  END;
 21  /



SQL>
SQL> drop table employees;