Oracle PL/SQL Tutorial/SQL PLUS Session Environment/spool

Extracts data from the emp table and writes it to a text file in a comma-delimited format

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.

Spool result to a text file

SQL>
SQL> set heading off
SQL> set feedback off
SQL>
SQL> spool c:\ALIASES.TXT
SQL>
SQL> select object_name || "=" || substr(object_name,1,5) from user_objects wher
e object_type = "TABLE";

MYSTATS=MYSTA
WORKING_CUSTOMERS=WORKI
CUST_WITH_VARRAY_TEMP_TABLE=CUST_
P1=P1
IT=IT
CUST_NO_KEY_TABLE=CUST_
DEMO=DEMO
P=P
CUSTLOG=CUSTL
IX=IX
EMP_DELTAS=EMP_D
PARMS=PARMS
PASSWORD_AUDIT=PASSW
DEPT$AUDIT=DEPT$
INVENTORY_TBL=INVEN
TMP=TMP
EMPLOYEETEMPTABLE=EMPLO
SYSTEM_STATS=SYSTE
TEMP_TABLE=TEMP_
MYTABLE1=MYTAB
C1=C1
WORKING_EMPLOYEES=WORKI


SQL>
SQL> spool off
SQL> set heading on
SQL> set feedback on
SQL>

Spool to a file with compute

SQL>
SQL> create table employees(
  2    empno      NUMBER(4)
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , msal       NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason",  "N",  "TRAINER", 2,   date "1965-12-18",  800 , NULL,  10);
1 row created.
SQL> insert into employees values(2,"Jerry",  "J",  "SALESREP",3,   date "1966-11-19",  1600, 300,   10);
1 row created.
SQL> insert into employees values(3,"Jord",   "T" , "SALESREP",4,   date "1967-10-21",  1700, 500,   20);
1 row created.
SQL> insert into employees values(4,"Mary",   "J",  "MANAGER", 5,   date "1968-09-22",  1800, NULL,  20);
1 row created.
SQL> insert into employees values(5,"Joe",    "P",  "SALESREP",6,   date "1969-08-23",  1900, 1400,  30);
1 row created.
SQL> insert into employees values(6,"Black",  "R",  "MANAGER", 7,   date "1970-07-24",  2000, NULL,  30);
1 row created.
SQL> insert into employees values(7,"Red",    "A",  "MANAGER", 8,   date "1971-06-25",  2100, NULL,  40);
1 row created.
SQL> insert into employees values(8,"White",  "S",  "TRAINER", 9,   date "1972-05-26",  2200, NULL,  40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C",  "DIRECTOR",10,  date "1973-04-27",  2300, NULL,  20);
1 row created.
SQL> insert into employees values(10,"Pink",  "J",  "SALESREP",null,date "1974-03-28",  2400, 0,     30);
1 row created.
SQL>
SQL>
SQL>
SQL> --clear   screen
SQL> set     pause off
SQL> break   on deptno skip page -
>         on job
SQL>
SQL> compute sum label total of msal on deptno
SQL> compute count number    of comm on deptno
SQL>
SQL> set     trimspool on
SQL> set     feedback off
SQL> spool   report.txt replace
SQL>
SQL> select  deptno, job, empno, ename, msal, comm
  2  from    employees
  3  order   by deptno, job;
DEPTNO JOB       EMPNO      last_name         MSAL
------ -------- ------ -------------------- ------
    10 SALESREP      2 Jerry                  1600
       TRAINER       1 Jason                   800
****** ********                             ------
count
number
total                                         2400
DEPTNO JOB       EMPNO      last_name         MSAL
------ -------- ------ -------------------- ------
    20 DIRECTOR      9 Yellow                 2300
       MANAGER       4 Mary                   1800
       SALESREP      3 Jord                   1700
****** ********                             ------
count
number
total                                         5800
DEPTNO JOB       EMPNO      last_name         MSAL
------ -------- ------ -------------------- ------
    30 MANAGER       6 Black                  2000
       SALESREP     10 Pink                   2400
                     5 Joe                    1900
****** ********                             ------
count
number
total                                         6300
DEPTNO JOB       EMPNO      last_name         MSAL
------ -------- ------ -------------------- ------
    40 MANAGER       7 Red                    2100
       TRAINER       8 White                  2200
****** ********                             ------
count
number
total                                         4300
SQL>
SQL> spool   off
SQL>
SQL> clear   computes
computes cleared
SQL> clear   breaks
breaks cleared
SQL> set     pause on
SQL> set     feedback on
SQL>
SQL>
SQL> drop table employees;
Table dropped.
SQL>
SQL> set pause off

Use Spoll to save query to a file

SQL>
SQL>
SQL> set linesize 500
SQL> set trimspool on
SQL> set embedded on
SQL> spool logmnr.opt
SQL> select
  2      "colmap = " || user || " " || table_name || " (" ||
  3     max( decode( column_id, 1,       column_id  , null ) ) ||
  4     max( decode( column_id, 1, ", "||column_name, null ) ) ||
  5     max( decode( column_id, 2, ", "||column_id  , null ) ) ||
  6     max( decode( column_id, 2, ", "||column_name, null ) ) ||
  7     max( decode( column_id, 3, ", "||column_id  , null ) ) ||
  8     max( decode( column_id, 3, ", "||column_name, null ) ) ||
  9     max( decode( column_id, 4, ", "||column_id  , null ) ) ||
 10     max( decode( column_id, 4, ", "||column_name, null ) ) ||
 11     max( decode( column_id, 5, ", "||column_id  , null ) ) ||
 12     max( decode( column_id, 5, ", "||column_name, null ) ) || ");" colmap
 13   from user_tab_columns
 14  group by user, table_name
 15  /
COLMAP
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
colmap = sqle AVAIL_TRACE_FILES (1, USERNAME, 2, FILENAME, 3, DT);
COLMAP
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
colmap = sqle INTEREST (1, YR, 2, RATE);
colmap = sqle AUTHOR_PUBLICATION (1, ID, 2, NAME, 3, TITLE, 4, WRITTEN_DATE);
colmap = sqle DEPT_OR (1, DEPTNO, 2, DNAME, 3, LOC, 4, EMPS);
colmap = sqle BIN$z+/2/T9WTRWmOfXZhvGBPQ==$0 (1, ID, 2, WORKER_ID, 3, LOGICAL_WORKPLACE_ID, 4, ACTIVE_DATE, 5, INACTIVE_DATE);
colmap = sqle HOME_ADDRESS_LIST (1, LIST_ID, 2, HOME_ADDRESSES);
colmap = sqle MYCLOB (1, ID, 2, CLOB_DATA);
colmap = sqle CRS_OFFERINGS (1, COURSE_CODE, 2, DESCRIPTION, 3, BEGINDATE);
colmap = sqle BIN$2AYTRnCPRfG80DYlHN4sJg==$0 (1, ID, 2, WORKER_ID, 3, LOGICAL_WORKPLACE_ID, 4, ACTIVE_DATE, 5, INACTIVE_DATE);
colmap = sqle HRC_ORG_SITE (1, HRC_CODE, 2, HRC_DESCR, 3, ORG_ID, 4, ORG_SHORT_NAME, 5, ORG_LONG_NAME);
colmap = sqle EMPLOYEE_EMPLOYEE_EVALUATION (1, ID, 2, NAME, 3, TITLE, 4, WRITTEN_DATE);
colmap = sqle EMPLOYEE_EVALUATION (1, ID, 2, TITLE, 3, WRITTEN_DATE);
COLMAP
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
colmap = sqle I2 (1, N, 2, V);
colmap = sqle DEPT20_V (1, EMPNO, 2, ENAME, 3, INIT, 4, JOB, 5, MGR);
colmap = sqle USER_AVAIL_TRACE_FILES (1, USERNAME, 2, FILENAME, 3, DT);
colmap = sqle T1_TIMES (1, XSTART, 2, XSTOP);
colmap = sqle WORKING_CUSTOMERS (1, CUST_NO, 2, LASTNAME, 3, FIRSTNAME, 4, MIDINIT, 5, STREET);
colmap = sqle STUDENT (1, STUDENT_ID, 2, PERFORMANCE, 3, GENDER, 4, ETHNICITY, 5, AGE);
colmap = sqle WORKING_EMPLOYEES (1, EMPL_NO, 2, LASTNAME, 3, FIRSTNAME, 4, MIDINIT, 5, STREET);
colmap = sqle EMP_V (1, EMPNO, 2, ENAME, 3, JOB, 4, MGR, 5, SAL);
colmap = sqle BIN$BIALfQCESHa/SipQl+oqGg==$0 (1, ID, 2, WORKER_ID, 3, LOGICAL_WORKPLACE_ID, 4, ACTIVE_DATE, 5, INACTIVE_DATE);
colmap = sqle COURSE_DAYS (1, EMPNO, 2, ENAME, 3, DAYS);
colmap = sqle HAZARD_LEVEL_T (1, ID, 2, MAGNITUDE, 3, CODE, 4, DESCRIPTION, 5, ACTIVE_DATE);
COLMAP
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
colmap = sqle EMP_VIEW (1, ENAME, 2, EMPNO);
colmap = sqle DDL_AUDIT (1, OBJECT_NAME, 2, OBJECT_TYPE, 3, WHEN_CREATED, 4, WHO_CREATED, 5, WHEN_UPDATED);
colmap = sqle MYTABLE1_TIMES (1, XSTART, 2, XSTOP);
colmap = sqle I1 (1, N, 2, V);
colmap = sqle CRS_COURSE_SCHEDULE (1, COURSE_CODE, 2, DESCRIPTION, 3, BEGINDATE);
colmap = sqle EMPLOYEE_PUBLICATION (1, ID, 2, NAME, 3, TITLE, 4, WRITTEN_DATE);
colmap = sqle SALES_BY_ATLAS_V (1, PRODUCT_NAME, 2, SALESPERSON, 3, ORDER_DATE, 4, QUANTITY);
colmap = sqle SALES_PER_PERSON_V (1, SALESPERSON, 2, PRODUCT_NAME, 3, ORDER_DATE, 4, QUANTITY);
colmap = sqle AVG_EVALUATIONS (1, COURSE, 2, AVG_EVAL);
colmap = sqle APPLICATION_USERS (1, UNAME, 2, PW, 3, ROLE_TO_GRANT);
colmap = sqle SITE_TAB (1, SITE_NO, 2, SITE_DESCR);
COLMAP
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
colmap = sqle OUTRAIN (1, DY, 2, RAINFALL);
colmap = sqle DUMMY (1, COL1);
colmap = sqle BIN$uR9LzoavTbyjK4b4t8kgXg==$0 (1, ID, 2, WORKER_ID, 3, PHYSICAL_WORKPLACE_ID, 4, ACTIVE_DATE, 5, INACTIVE_DATE);
colmap = sqle BIN$ukEn3nqRT+u/paBe8fJpxQ==$0 (1, ID, 2, WORKER_ID, 3, LOGICAL_WORKPLACE_ID, 4, ACTIVE_DATE, 5, INACTIVE_DATE);
colmap = sqle MAP (1, N, 2, I1, 3, I2);
colmap = sqle EMP_DEPT_CLUSTER (1, DEPTNO);
colmap = sqle HASH_CLUSTER (1, HASH_KEY);
41 rows selected.
SQL> spool off
SQL>