What?
- PL/SQL implicitly declares a cursor for all SQL data manipulation statements
- Feature of PL/SQL that allows SQL syntax directly in a PL/SQL statement
- SQL Statement here is defined during compile time.
- PL/SQL static SQL statements:
SELECT, INSERT, UPDATE, DELETE,
and MERGE
COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION
LOCK TABLE
- A PL/SQL static SQL statement can have a PL/SQL identifier wherever its SQL counterpart can have a placeholder for a bind argument.
- PseudoColumns: behave like a table column, but it is not stored in the table
- CURRVAL and NEXTVAL
- LEVEL
- OBJECT_VALUE
- ROWID and ROWNUM
Cursors - Pointer to a private SQL area that stores information about processing a specific SELECT or DML statement.
- Session cursors live in session memory until the session ends
- A Session cursor can be implicit or explicit.
V$OPEN_CURSOR
lists session cursors that each user session has opened and parsed
- A PL/SQL cursor variable lets you retrieve multiple rows from a stored subprogram.
- You can pass cursor variables as parameters in your 3GL application.
- With cursors, an SQL stmt can be reexecuted without including the parse stage.
- Repeated execution of the same SQL statements: can begin at the describe, define, bind, or run step, saving the repeated cost of opening cursors and parsing.
Check the performance characteristics of a cursor |
V$SQL_PLAN |
Execution plan information for each child cursor loaded in the library cache
|
V$SQL_PLAN_STATISTICS |
Execution plan information for each child cursor loaded in the library cache
|
V$SQL_PLAN_STATISTICS_ALL |
Memory usage statistics for row sources that use SQL memory (sort or hash-join). This view concatenates information in V$SQL_PLAN with execution statistics from V$SQL_PLAN_STATISTICS and V$SQL_WORKAREA
|
How many cursors can a session maintain? - Depends on: The amount of memory available to the session
- Depends on: The value of the initialization parameter
OPEN_CURSORS
- Also called SQL Cursor
- PL/SQL opens an implicit cursor every time you run a SELECT or DML statement
%ROWCOUNT
attribute: stores the number of rows affected by a DML.
- You cannot control an implicit cursor, but you can get information from its attributes
- SQLattribute always refers to the most recently run SELECT or DML statement. If no such statement has run, the value of SQLattribute is NULL.
- Cursor attribute values remain available until another SELECT or DML statement runs.
Implicit Cursor Attributes |
SQL%ISOPEN (Is the Cursor Open?) |
- Always returns FALSE. (Since implicit cursors always close after the statement runs.)
|
SQL%FOUND and SQL%NOTFOUND (Were any rows affected?) | - NULL: If no SELECT OR DML was executed.
- TRUE: If SELECT or DML returned/affected +0 rows.
- FALSE: If no rows affected.
create table dept_temp as select * from departments;
create or replace procedure p ( dept_no number)
authid definer as
begin
delete from dept_temp
where department_id = dept_no;
If sql%found then
dbms_output.put_line('delete succeeded. dept_no: '
|| dept_no);
else
dbms_output.put_line('No Dept_no: '|| dept_no);
end if;
end;
/
SQL> set serveroutput on
SQL> exec p(400);
anonymous block completed
No Dept_no: 400
- If SELECT INTO returns no rows: predefined exception NO_DATA_FOUND is raised immediately (before SQL%NOTFOUND can be checked).
- SELECT INTO that invokes a SQL aggregate function (i.e. avg, sum, count, etc) always returns a value (possibly NULL). Here SQL%NOTFOUND is always FALSE.
|
SQL%ROWCOUNT (how many rows affected?) | - NULL: If no SELECT OR DML was executed.
- N: N is the number of rows returned (select) or affected (dml).
- FALSE: If no rows affected.
SQL> set serveroutput on
SQL> declare
mgr_no number(6) := 122;
begin
delete from emp_temp where manager_id = mgr_no;
dbms_output.put_line
('number of employees deleted: ' || to_char(sql%rowcount));
end;
/
anonymous block completed
number of employees deleted: 8
set serveroutput on
declare
n number;
begin
dbms_output.put_line('Select.. ---------');
select count(*) into n from employees;
dbms_output.put_line('SQL%ROWCOUNT: [' || SQL%ROWCOUNT
||'] rows returned');
dbms_output.put_line('Update.. ---------');
update employees
set salary = salary*1.10
where job_id = 'SA_REP';
dbms_output.put_line('SQL%ROWCOUNT: [' || SQL%ROWCOUNT
||'] rows updated');
end;
anonymous block completed
Select.. ---------
SQL%ROWCOUNT: [1] rows returned
Update.. ---------
SQL%ROWCOUNT: [30] rows updated
|
SQL%BULK_ROWCOUNT | - Is like an associative array whose ith element is the number of rows affected by the ith DML statement in the most recently completed FORALL statement..
set serveroutput on
declare
type NumList is table of number;
depts numlist := NumList(30, 50, 60);
begin
forall j in depts.first..depts.last
delete from emp_temp where department_id = depts(j);
for i in depts.first..depts.last loop
dbms_output.put_line(
'Statement #' || i || ' deleted ' ||
sql%bulk_rowcount(i) || 'rows.');
end loop;
dbms_output.put_line('Total rows deleted: '
|| sql%rowcount);
end;
/
anonymous block completed
Statement #1 deleted 0rows.
Statement #2 deleted 45rows.
Statement #3 deleted 5rows.
Total rows deleted: 50
|
SQL%BULK_EXCEPTIONS | - SQL%BULK_EXCEPTIONS is like an associative array of information about the DML statements that failed during the most recently run FORALL statement.
- To allow a FORALL statement to continue even if some of its DML statements fail, include the SAVE EXCEPTIONS clause.
- After the FORALL statement completes, PL/SQL raises a single exception for the FORALL statement (ORA-24381). In the exception handler for ORA-24381, you can get information about each individual DML statement failure from the implicit cursor attribute SQL%BULK_EXCEPTIONS.
SQL%BULK_EXCEPTIONS.COUNT :# of DML stmts that failed.
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX : # of the ith failed DML stmt.
SQL%BULK_EXCEPTIONS(i).ERROR_CODE : error code for the ith failure.
SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE)) : returns the associated error message
create or replace procedure p
authid definer as
type deplist is table of number;
depts deplist := deplist(40, 90, 100);
type emptab is table of employees.employee_id%type;
type deptab is table of employees.department_id%type;
enums emptab;
dnums deptab;
errm varchar2(100);
bad_stmt_no pls_integer;
bad_deptno emp_temp.department_id%type;
bad_empno emp_temp.employee_id%type;
bad_email emp_temp.email%type;
dml_errors exception;
pragma exception_init(dml_errors, -24381);
begin
select employee_id, department_id
bulk collect into enums, dnums
from emp_temp
where department_id in (depts(1), depts(2), depts(3));
forall j in enums.first..enums.last save exceptions
update emp_temp
set email = email || '@dep' || dnums(j)
|| '.company.com'
where employee_id = enums(j);
exception
when dml_errors then
for i in 1..sql%bulk_exceptions.count loop
errm := sqlerrm(-(sql%bulk_exceptions(i).error_code));
dbms_output.put_line(error_message);
bad_stmt_no := sql%bulk_exceptions(i).error_index;
dbms_output.put_line('Bad stmt #: '|| bad_stmt_no);
bad_empno := enums(bad_stmt_no);
dbms_output.put_line('Bad empno #: '|| bad_empno);
bad_deptno := dnums(bad_stmt_no);
select email into bad_email
from emp_temp
where employee_id = bad_empno;
dbms_output.put_line('Bad email #' || i
|| ' : '|| bad_email);
end loop;
-- commit;
when others then
dbms_output.put_line('other error');
raise;
end;
set serveroutput on;
exec p;
anonymous block completed
ORA-12899: value too large for column (actual: , maximum: )
Bad stmt #: 2
Bad empno #: 101
Bad email #1 : NKOCHHAR
(...)
ORA-12899: value too large for column (actual: , maximum: )
Bad stmt #: 5
Bad empno #: 109
Bad email #3 : DFAVIET
(...)
|
Using SELECT INTO: Implicit cursor
- May return a single row - (here select directly into a scalar or record)
- you may also use WHERE ROWNUM = n to select a specific row.
- Or return multiple rows: here you need to use Select xxx BULK COLLECT INTO collection;
Returning a single row:
DECLARE
bonus NUMBER(8,2);
BEGIN
SELECT salary * 0.10 INTO bonus
FROM employees
WHERE employee_id = 100;
END;
DBMS_OUTPUT.PUT_LINE('bonus = ' || TO_CHAR(bonus));
returning into a record SELECT select_list INTO record_variable_name FROM table_or_view_name;
DECLARE
TYPE RecordTyp IS RECORD (
last employees.last_name%TYPE
id employees.employee_id%TYPE
);
rec1 RecordTyp;
BEGIN
SELECT last_name, employee_id INTO rec1
FROM employees
WHERE job_id = 'AD_PRES';
DBMS_OUTPUT.PUT_LINE ('Employee #' || rec1.id || ' = ' || rec1.last);
END;
/
Returning multiple rows: BULK COLLECT clause DECLARE
TYPE NumTab IS TABLE OF employees.employee_id%TYPE;
TYPE NameTab IS TABLE OF employees.last_name%TYPE;
enums NumTab;
names NameTab;
PROCEDURE print_first_n (n POSITIVE) IS
BEGIN
IF enums.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE ('Collections are empty.');
ELSE
DBMS_OUTPUT.PUT_LINE ('First ' || n || ' employees:');
FOR i IN 1 .. n LOOP
DBMS_OUTPUT.PUT_LINE (
' Employee #' || enums(i) || ': ' || names(i));
END LOOP;
END IF;
END;
BEGIN
SELECT employee_id, last_name
BULK COLLECT INTO enums, names
FROM employees
ORDER BY employee_id;
print_first_n(3);
print_first_n(6);
END;
/
Using RETURNING INTO clause on a DML
SQL> create table emp as
select employee_id, last_name, salary
from employees;
set serveroutput on
declare
type emprec is record
(empid emp.employee_id%type,
lname emp.last_name%type,
sal emp.salary%type);
vemprec emprec;
begin
delete from emp
where employee_id = 188
returning employee_id, last_name, salary
INTO vemprec;
dbms_output.put_line('the record deleted was: ');
dbms_output.put_line(vemprec.lname || 'id: '|| vemprec.empid
||' sal: '|| vemprec.sal);
exception
when others then
dbms_output.put_line(sqlerrm);
end;
/
anonymous block completed
the record deleted was:
Chungid: 188 sal: 3800
Cursor FOR LOOP
- Use with either implicit or explicit cursor.
- The cursor FOR LOOP statement implicitly declares its loop index as a %ROWTYPE record variable of the type that its cursor returns.
- Implicit cursor FOR LOOP stmt
- SELECT stmt inside the FOR LOOP
set serveroutput on
begin
for item in
( select last_name, job_id
from employees
where job_id like '%CLERK%'
and manager_id > 100
order by last_name)
loop
dbms_output.put_line
('Name = '|| item.last_name || ', Job = ' || item.job_id);
end loop;
end;
anonymous block completed
Name = Atkinson, Job = ST_CLERK
Name = Baida, Job = PU_CLERK
Name = Bell, Job = SH_CLERK
(...)
- explicit cursor FOR LOOP
- Move the select statement to the declaration section and explicitly delcare a cursor.
set serveroutput on
declare
cursor c1 is
select last_name, job_id
from employees
where job_id like '%CLERK%'
and manager_id > 100
order by last_name;
begin
for item in c1 loop
dbms_output.put_line
('Name = '|| item.last_name || ', Job = ' || item.job_id);
end loop;
end;
anonymous block completed
Name = Atkinson, Job = ST_CLERK
Name = Baida, Job = PU_CLERK
Name = Bell, Job = SH_CLERK
(...)
- Cursor FOR LOOP with parameters
set serveroutput on
declare
cursor c1 (job employees.job_id%type, max_wage number) is
select * from employees
where job_id = job
and salary > max_wage;
begin
for person in c1 ('ST_CLERK', 3000) loop
dbms_output.put_line(
'Name = '|| person.last_name || ', salary = ' ||
person.salary ||', Job Id = '|| person.job_id);
end loop;
end;
anonymous block completed
Name = Nayer, salary = 3200, Job Id = ST_CLERK
Name = Bissot, salary = 3300, Job Id = ST_CLERK
(...)
Cursors with subqueries
set serveroutput on
declare
cursor c1 is
select t1.department_id, department_name, staff
from departments t1,
( select department_id, count(*) as staff
from employees
group by department_id
) t2
where (t1.department_id = t2.department_id)
and staff >= 5
order by staff;
begin
for dept in c1 loop
dbms_output.put_line ('Department = '
|| dept.department_name || ', staff = ' || dept.staff);
end loop;
end;
anonymous block completed
Department = IT, staff = 5
Department = Finance, staff = 6
Department = Purchasing, staff = 6
Department = Administration, staff = 7
Department = Sales, staff = 34
Department = Shipping, staff = 45
- Also called a named cursor, since unlike implicit ones, an explicit cursor can be referenced by its name
- is a named pointer to a private SQL area that stores information for processing a specific query or DML statement
- Before using an explicit cursor, you must declare (cursor specification) and define it (cursor body).
- You cannot assign a value to an explicit cursor, use it in an expression, or use it as a formal subprogram parameter or host variable.
- You can do those things with a cursor variable.
Explicit cursor declaration, which only declares a cursor:
CURSOR cursor_name [ parameter_list ] RETURN return_type;
An explicit cursor definition has this syntax:
CURSOR cursor_name [ parameter_list ]
[ RETURN return_type ] IS select_statement;
DECLARE
CURSOR c1 RETURN departments%ROWTYPE; -- Declare c1
CURSOR c2 IS -- Declare and define c2
SELECT employee_id, job_id, salary FROM employees
WHERE salary > 2000;
CURSOR c1 RETURN departments%ROWTYPE IS -- Define c1,
SELECT * FROM departments -- repeating return type
WHERE department_id = 110;
BEGIN
NULL;
END;
OPEN, FETCH, and CLOSE
- Process multiple result sets in parallel, using multiple cursors.
- Process multiple rows in a single loop iteration, skip rows, or split the processing into multiple loops.
- Specify the query in one PL/SQL unit but retrieve the rows in another.
Managing Explicit Cursors |
OPEN |
- Allocates database resources
- Process the query (identify result set and lock rows (if FOR UPDATE clause is there)
- Position cursor before the first row of the result set.
|
FETCH |
FETCH cursor_name INTO into_clause
- retrieves the current row of the result set, stores the column values of that row into the variables or record, and advances the cursor to the next row.
- PL/SQL does not raise an exception when a FETCH statement returns no rows. To detect this, use the attribute %NOTFOUND.
|
CLOSE |
- After closing a cursor, you cannot fetch records from its result set or reference its attributes. If you try, PL/SQL raises the predefined exception INVALID_CURSOR.
- You can reopen a closed cursor. You must close an explicit cursor before you try to reopen it. Otherwise, PL/SQL raises the predefined exception CURSOR_ALREADY_OPEN.
|
set serveroutput on
declare
cursor c1 is
select last_name, job_id from employees
where regexp_like(job_id, 'S[HT]_CLERK')
order by last_name;
v_lastname employees.last_name%type;
v_jobid employees.job_id%type;
cursor c2 is
select * from employees
where regexp_like(job_id, '[ACADFIMKSA]_M[ANGR]')
order by job_id;
v_employees employees%rowtype;
begin
open c1;
loop
fetch c1 into v_lastname, v_jobid;
exit when c1%notfound;
dbms_output.put_line(RPAD(v_lastname, 25, ' ') || v_jobid);
end loop;
close c1;
dbms_output.put_line( '-----------------------------');
open c2;
loop
fetch c2 into v_employees;
exit when c2%notfound;
dbms_output.put_line(RPAD(v_employees.last_name, 25, ' ') ||
v_employees.job_id);
end loop;
close c2;
end;
anonymous block completed
Atkinson ST_CLERK
Bell SH_CLERK
...
Walsh SH_CLERK
-----------------------------
Higgins AC_MGR
Glenn AC_MGR
...
Zlotkey SA_MAN
Explicit cursors with parameter set serveroutput on
declare
cursor c (job varchar2, max_sal number) IS
select last_name, first_name, (salary-max_sal) overpayment
from employees
where job_id = job
and salary > max_sal
order by salary;
procedure print_overpaid is
ln_ employees.last_name%type;
fn_ employees.first_name%type;
overpayment_ employees.salary%type;
begin
loop
fetch c into ln_, fn_, overpayment_;
exit when c%notfound;
dbms_output.put_line(ln_ || ', ' || fn_ ||
' (by ' || overpayment_ || ')');
end loop;
end print_overpaid;
begin
dbms_output.put_line('---------------------');
dbms_output.put_line('Overpaid Clerks:');
dbms_output.put_line('---------------------');
Open c('ST_CLERK', 5000);
print_overpaid;
close c;
dbms_output.put_line('---------------------');
dbms_output.put_line('Overpaid Sales Rep:');
dbms_output.put_line('---------------------');
Open c('SA_REP', 10000);
print_overpaid;
close c;
end;
Explicit Cursor Attributes Syntax: cursor_name%attribute |
%ISOPEN (Is the Cursor Open?) |
- Useful for: Checking if an explicit cursor is already open before trying to open it.
- If you try to open an explicit cursor that is already open, PL/SQL raises the predefined exception CURSOR_ALREADY_OPEN. You must close an explicit cursor before you can reopen it.
- Checking that an explicit cursor is open before you try to close it.
declare
cursor c1 is
select last_name, salary from employees
where rownum < 11;
v1 employees.last_name%type;
v2 employees.salary%type;
begin
if not c1%isopen then
open c1;
end if;
fetch c1 into v1, v2;
if c1%isopen then
close c1;
end if;
end;
|
%FOUND and %NOTFOUND (Has a row been fetched?) | - NULL: after the explicit cursor is opened but before the first fetch
- TRUE: if the most recent fetch from the explicit cursor returned a row
- FALSE: otherwise
- %FOUND use to check if there is a fetched row to process.
set serveroutput on
DECLARE
CURSOR c1 IS
SELECT last_name, salary FROM employees
WHERE ROWNUM < 11
ORDER BY last_name;
my_ename employees.last_name%TYPE;
my_salary employees.salary%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_salary;
IF c1%FOUND THEN -- fetch succeeded
DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ',
salary = ' || my_salary);
ELSE -- fetch failed
DBMS_OUTPUT.PUT_LINE('---------------------');
DBMS_OUTPUT.PUT_LINE('C''est fini');
EXIT;
END IF;
END LOOP;
END;
/
|
%ROWCOUNT (How may rows fetched?) |
- Zero after the explicit cursor is opened but before the first fetch
- Otherwise, the number of rows fetched
|
Cursor variable is a
pointer: its value is the address of an item, not the item itself. - Similar to an an e
xplicit cursor, except that:
- It is not limited to one query.
- You can assign a value to it.
- You can use it in an expression.
- It can be a subprogram parameter.
- It can be a host variable.
- It cannot accept parameters. But you can pass whole queries to it.
- To create a cursor variable, either declare a variable of the predefined type
SYS_REFCURSOR
or define a REF CURSOR
type and then declare a variable of that type.
Cursor variable declarations: Syntax:
TYPE type_name IS REF CURSOR [ RETURN return_type ]
DECLARE
DECLARE
-- strong type
TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
-- weak type
TYPE genericcurtyp IS REF CURSOR;
cursor1 empcurtyp; -- strong cursor variable
cursor2 genericcurtyp; -- weak cursor variable
my_cursor SYS_REFCURSOR; -- weak cursor variable
BEGIN
NULL;
END;
Cursor Variable - Examples
(1) Opens a cursor variable for a query that references the variable factor.
(2) Notice that the variable is evaluated only once: when the cursor is opened.
set serveroutput on
declare
sal employees.salary%type;
sal_multiple employees.salary%type;
factor integer := 2;
-- declare cursor variable predefined type
cv sys_refcursor;
begin
-- open cursor defining SQL stmt.
-- once closed later, the cursor can be opened
-- again with a different stmt
Open cv for
select salary, salary*factor
from employees
where job_id like 'AD_%';
loop
--load the next row of data into variables
-- if no rows left, exit loop
fetch cv into sal, sal_multiple;
exit when cv%notfound;
dbms_output.put_line('factor = '|| factor);
dbms_output.put_line('sal = '|| sal);
dbms_output.put_line('sal_multiple = '|| sal_multiple);
-- note that the variable factor is evaluated only one
-- when the cursor is opened. After this, new variable
-- values are not seen by the cursor unless it is
-- closed and opened again.
factor := factor + 1;
end loop;
close cv;
end;
anonymous block completed
factor = 2
sal = 4400
sal_multiple = 8800
factor = 3
sal = 24000
sal_multiple = 48000
factor = 4
sal = 17000
sal_multiple = 34000
factor = 5
sal = 17000
sal_multiple = 34000
| Passing cursor variables as parameter |
(1) Package specification: Define public (a) cursor variable; (b) procedures to open and manipulate cursor. (2) Package body: Procedures that open the cursor for read and write (IN OUT). And list cursor contents (IN). (3) Anonymous block: declares cursor variable of the same type defined in package specification. (4) Anonymous block: Calls procedures to open and iteract through the cursor.
set serveroutput on
-- Package specification.
create or replace package emp_data as
-- Defines public cursor variable type (strongly enforced)
type empcurtype is ref cursor return employees%rowtype;
-- define public procedures
procedure open_emp_cv (emp_cv in out empcurtype);
procedure list_emp_cv (emp_cv in empcurtype);
procedure close_emp_cv (emp_cv in empcurtype);
end emp_data;
-- package body
create or replace package body emp_data as
-- procedure receives cursor var as parameter
-- and writes on it.
-- NOTICE: that the SQL is fixed. You can't change
-- parameters without recompiling the package body.
procedure open_emp_cv(emp_cv in out empcurtype) is
begin
open emp_cv for select *
from employees
where rownum <=5
order by hire_date;
end open_emp_cv;
-- procedure receives cursor var as parameter.
-- read only.
procedure list_emp_cv (emp_cv in empcurtype) is
v_emp employees%rowtype;
begin
-- loop through all cursor records.
-- exit loop when last row reached.
-- each FETCH increments %ROWCOUNT attribute.
loop
fetch emp_cv into v_emp;
exit when emp_cv%notfound;
dbms_output.put_line('Oldest #' || emp_cv%rowcount ||': '
|| v_emp.first_name
||' '|| v_emp.last_name ||
'. Hire date: '|| v_emp.hire_date);
end loop;
end list_emp_cv;
procedure close_emp_cv (emp_cv in empcurtype) is
begin
if emp_cv%isopen then
dbms_output.put_line('Closing cursor');
close emp_cv;
end if;
end close_emp_cv;
end emp_data;
-- anonymous block
set serveroutput on
declare
-- declare cursor variable of the same type
-- specified in the package spec.
v_empcv emp_data.empcurtype;
begin
emp_data.open_emp_cv(v_empcv);
emp_data.list_emp_cv(v_empcv);
emp_data.close_emp_cv(v_empcv);
end;
anonymous block completed
Oldest #1: Steven King. Hire date: 17-JUN-87
Oldest #2: Neena Kochhar. Hire date: 21-SEP-89
Oldest #3: Alexander Hunold. Hire date: 03-JAN-90
Oldest #4: Bruce Ernst. Hire date: 21-MAY-91
Oldest #5: Lex De Haan. Hire date: 13-JAN-93
Closing cursor
set serveroutput on
declare
type emp_cur_type is ref cursor;
emp_cur emp_cur_type;
dept_name departments.department_name%type;
emp_name employees.last_name%type;
cursor c1 is
select department_name,
cursor ( select e.last_name
from employees e
where e.department_id = d.department_id
order by e.last_name
) employees
from departments d
where department_name like 'A%'
order by department_name;
begin
open c1;
loop
fetch c1 into dept_name, emp_cur;
exit when c1%notfound;
dbms_output.put_line('Dept: ' || dept_name);
loop
fetch emp_cur into emp_name;
exit when emp_cur%notfound;
dbms_output.put_line('-- Employee: '|| emp_name);
end loop;
end loop;
close c1;
end;
anonymous block completed
Dept: Accounting
-- Employee: Gietz
-- Employee: Higgins
Dept: Administration
-- Employee: Fansom
-- Employee: Fay
-- Employee: Fergunson
-- Employee: Glenn
-- Employee: Hartstein
-- Employee: Mavris
-- Employee: Whalen