Extracts data from the emp table and writes it to a text file in a comma-delimited format : spool « SQL PLUS Session Environment « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. SQL PLUS Session Environment
3. spool

SQL> CREATE TABLE emp (
  2    emp_id               NUMBER,
  3    ename             VARCHAR2(40),
  4    hire_date        DATE DEFAULT sysdate,
  5    end_date DATE,
  6    rate     NUMBER(5,2),
  7    CONSTRAINT emp_pk    PRIMARY KEY (emp_id)
  8  );

Table created.

SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, 'Mary', to_date('15-Nov-1961','dd-mon-yyyy'),null,169);

1 row created.

SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, 'Tom', to_date('16-Sep-1964','dd-mon-yyyy'),to_date('5-May-2004','dd-mon-yyyy'),135);

1 row created.

SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, 'Peter', to_date('29-Dec-1987','dd-mon-yyyy'),to_date('1-Apr-2004','dd-mon-yyyy'),99);

1 row created.

SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, 'Mike', to_date('15-Jun-2004','dd-mon-yyyy'),null,121);

1 row created.

SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, 'Less', to_date('2-Jan-2004','dd-mon-yyyy'),null,45);

1 row created.

SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, 'Park', to_date('1-Mar-1994','dd-mon-yyyy'),to_date('15-Nov-2004','dd-mon-yyyy'),220);

1 row created.

SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, 'Ink', to_date('4-Apr-2004','dd-mon-yyyy'),to_date('30-Sep-2004','dd-mon-yyyy'),84);

1 row created.

SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, 'Tike', to_date('23-Aug-1976','dd-mon-yyyy'),null,100);

1 row created.

SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, 'Inn', to_date('15-Nov-1961','dd-mon-yyyy'),to_date('4-Apr-2004','dd-mon-yyyy'),70);

1 row created.

SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, 'Kate', to_date('3-Mar-2004','dd-mon-yyyy'),to_date('31-Oct-2004','dd-mon-yyyy'),300);

1 row created.

SQL>
SQL> --Set the linesize large enough to accommodate the longest possible line.
SQL> SET LINESIZE 80
SQL>
SQL> --Turn off all page headings, column headings, etc.
SQL> SET PAGESIZE 0
SQL>
SQL> --Turn off feedback
SQL> SET FEEDBACK OFF
SQL>
SQL> --Eliminate trailing blanks at the end of a line.
SQL> SET TRIMSPOOL ON
SQL>
SQL> SET TERMOUT OFF
SQL> SPOOL current_emps.csv
SQL> SELECT '"ID","Billing Rate","Hire Date","Name"'
  2  FROM dual;
"ID","Billing Rate","Hire Date","Name"
SQL>
SQL> SELECT    TO_CHAR(emp_id) || ','
  2         || TO_CHAR(rate) || ','
  3         || TO_CHAR(hire_date,'MM/DD/YYYY') || ','
  4         || '"' || ename || '"'
  5  FROM emp
  6  WHERE end_date IS NULL;
101,169,11/15/1961,"Mary"
105,121,06/15/2004,"Mike"
107,45,01/02/2004,"Less"
111,100,08/23/1976,"Tike"
SQL> SPOOL OFF
SQL>
SQL> SET FEEDBACK ON
SQL>
SQL> drop table emp;

Table dropped.