SQL & PL/SQL

Guys-

Any help in debugging this piece of code will be greately appreciated!!

Procedure compiles fine, but does not execute.
-------------


1 CREATE OR REPLACE procedure SP_DYN_WHERE (P_TITLE in varchar2,
2 P_DEPT IN VARCHAR2,
3 P_LOC IN VARCHAR2)
4 is
5 r emp1%rowtype ;
6 stmts varchar2(10000);
7 begin
8 stmts := 'SELECT EMP1_SSNO,EMP1_LAST_NAME,EMP1_FIRST_NAME,
9 EMP1_STREET ,
10 EMP1_CITY ,
11 EMP1_STATE ,
12 EMP1_ZIP ,
13 EMP1_PHONE ,
14 EMP1_DATE_OF_BIRTH ,
15 EMP1_TITLE ,
16 EMP1_DEPARTMENT ,
17 EMP1_SCHOOL ,
18 EMP1_CALENDER_GROUP ,
19 EMP1_STAFF_GROUP,
20 EMP1_ETHNIC_CODE,
21 EMP1_MARTIAL_STATUS ,
22 EMP1_GENDER ,
23 EMP1_TERM_OF_CONTRACT,
24 EMP1_HIRE_DATE ,
25 EMP1_TERM_DATE ,
26 EMP1_TERM_REASON ,
27 EMP1_TENURE_DATE,
28 EMP2_GUIDE,
29 EMP2_DEGREE,
30 EMP2_STEP ,
31 EMP2_SALARY ,
32 EMP2_LONGEVITY_AMOUNT,
33 EMP2_OFF_GUIDE,
34 EMP2_STEP_INCR_DENIED,
35 get_transposed_cert(EMP1_SSNO,EMP1_YEAR ) SCH17_INFO
36 FROM EMP1, EMP2
37 WHERE EMP1_YEAR = ''20042005''
38 AND EMP1_YEAR = EMP2_YEAR
39 AND EMP1_SSNO = EMP2_SSNO ' ;
40 IF p_TITLE <> 'ALL'
41 THEN
42 stmts := stmts||' AND EMP1_TITLE = '''||P_TITLE ||''' ' ;
43 ELSE
44 stmts := stmts||' ';
45 END IF;
46 IF P_DEPT <> 'ALL'
47 THEN
48 stmts := stmts||' AND EMP1_DEPARTMENT = '''|| P_DEPT||''' ' ;
49 ELSE
50 stmts := stmts||' ' ;
51 END IF;
52 IF P_LOC <> 'ALL'
53 THEN
54 stmts := stmts||' AND EMP1_SCHOOL = ''' ||P_LOC||''' ' ;
55 ELSE
56 stmts := stmts||' ' ;
57 END IF;
58 execute immediate stmts into r ;
59 dbms_output.put_line(r.emp1_ssno||' '||r.EMP1_LAST_NAME ||' '||r.EMP1_first_NAME );
60* end;
SQL> /

Procedure created.

SQL>
SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> exec SP_DYN_WHERE('TEACHER-ART','ART','01 GEORGE WASHINGTON');
BEGIN SP_DYN_WHERE('TEACHER-ART','ART','01 GEORGE WASHINGTON'); END;

*
ERROR at line 1:
ORA-01007: variable not in select list
ORA-06512: at "EDUMET_EBOE.SP_DYN_WHERE", line 58
ORA-06512: at line 1