9 Replies Latest reply on Feb 16, 2006 7:50 PM by user488346

    ORA-01007: variable not in select list -- Any suggestions, tips??

    user488346
      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
        • 1. Re: ORA-01007: variable not in select list -- Any suggestions, tips??
          Kamal Kishore
          Your INTO variable is defined as:
          r emp1%rowtype ;
          Are you selecting all the columns from your EMP1 table in that select statement?

          ORA-01007 variable not in select list

          Cause: A reference was made to a variable not listed in the SELECT clause. In OCI, this can occur if the number passed for the position parameter is less than one or greater than the number of variables in the SELECT clause in any of the following calls: DESCRIBE, NAME, or DEFINE. In SQL*Forms or SQL*Report, specifying more variables in an INTO clause than in the SELECT clause also causes this error.

          Action: Determine which of the problems listed caused the problem and take appropriate action.

          Message was edited by:
          Kamal Kishore
          • 2. Re: ORA-01007: variable not in select list -- Any suggestions, tips??
            user488346
            Thanks Kamal -

            Are you selecting all the columns from your EMP1 table in that select statement?
            -- No I am not.

            I am selecting a few tables from the EMP1 table and a few more from the EMP2 table in the select statement. Any help in fixing this would be great.
            Thanks for your time Kamal-
            • 3. Re: ORA-01007: variable not in select list -- Any suggestions, tips??
              12826
              You can define your own type
              TYPE EMP_RECORD IS RECORD
              ( put all the columns you are selecting
              ex. ssn emp1.ssn%type,
              first_name emp1.first_name%type,
              etc...

              );
              EMP_DATA EMP_RECORD;

              BEGIN
              EXECUTE IMMEDIATE ...
                     INOT EMP_DATA;

              VG

              null                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
              • 4. Re: ORA-01007: variable not in select list -- Any suggestions, tips??
                487355
                Kamal has already found the problem but I thought you might like to see this, it can simplfy this type of work, removing the if statements and some of the concatenation.

                JUST NOTICED: You select statement uses EMP1, EMP2 but in your select statement you use
                9 EMP1_STREET ,
                10 EMP1_CITY ,
                Shouldn't those be
                9 EMP1.STREET ,
                10 EMP1.CITY ,
                changing all of the EMP<#>_ to EMP<#>.
                select emp1.ssno,
                       emp1.last_name,
                       emp1.first_name,
                       emp1.street,
                       emp1.city,
                       emp1.state,
                       emp1.zip,
                       emp1.phone,
                       emp1.date_of_birth,
                       emp1.title,
                       emp1.department,
                       emp1.school,
                       emp1.calender_group,
                       emp1.staff_group,
                       emp1.ethnic_code,
                       emp1.martial_status,
                       emp1.gender,
                       emp1.term_of_contract,
                       emp1.hire_date,
                       emp1.term_date,
                       emp1.term_reason,
                       emp1.tenure_date,
                       emp2.guide,
                       emp2.degree,
                       emp2.step,
                       emp2.salary,
                       emp2.longevity_amount,
                       emp2.off_guide,
                       emp2.step_incr_denied,
                       get_transposed_cert(emp1.ssno, emp1.year) sch17_info
                from   emp1,
                       emp2
                where  emp1.year = '20042005'
                and    emp1.year = emp2.year
                and    emp1.ssno = emp2.ssno
                and    (emp1.title = p_title or p_title = 'ALL')
                and    (emp1.department = p_dept or p_dept = 'ALL')
                and    (emp1.school = p_loc or p_loc = 'ALL')
                You could use this format using dynamic SQL just wrapping your parameters as you did previously '''||P_TITLE ||''' etc.
                Based on Kamal's comment below, you could create a cursor for the above select statement, with parameters for title, dept, and loc. I should have thought of that.
                Bob.

                Message was edited by:
                Bob C
                • 5. Re: ORA-01007: variable not in select list -- Any suggestions, tips??
                  Kamal Kishore
                  Just to add to what Bob said above,
                  You can declare a CURSOR in your program that is exactly same as the SELECT statement you are building (except the where conditions, that change for each run).

                  If you do that, then you can use that cursor % ROWTYPE as the type for your FETCH INTO variable (instead of using EMP1%ROWTYPE) that way, all your SELECT list columns will match in number and in type with the ROWTYPE declaration without any problems.
                  DECLARE
                  .
                  .
                  .
                    CURSOR dummy_emp_cursor IS
                  select emp1.ssno,
                         emp1.last_name,
                         emp1.first_name,
                         emp1.street,
                         emp1.city,
                         emp1.state,
                         emp1.zip,
                         emp1.phone,
                         emp1.date_of_birth,
                         emp1.title,
                         emp1.department,
                         emp1.school,
                         emp1.calender_group,
                         emp1.staff_group,
                         emp1.ethnic_code,
                         emp1.martial_status,
                         emp1.gender,
                         emp1.term_of_contract,
                         emp1.hire_date,
                         emp1.term_date,
                         emp1.term_reason,
                         emp1.tenure_date,
                         emp2.guide,
                         emp2.degree,
                         emp2.step,
                         emp2.salary,
                         emp2.longevity_amount,
                         emp2.off_guide,
                         emp2.step_incr_denied,
                         get_transposed_cert(emp1.ssno, emp1.year) sch17_info
                  from   emp1,
                         emp2 ;
                    r dummy_emp_cursor%ROWTYPE ;
                  BEGIN
                  .
                  .
                  .
                  Now you can safely FETCH rows into your variable "r" since it has same number of columns and types as your dynamic select.

                  The cursor declaration serves no other purpose other than to allow you to declare your FETCH INTO variable. You would not be doing anything else with this cursor.
                  • 6. Re: ORA-01007: variable not in select list -- Any suggestions, tips??
                    Kamal Kishore
                    Forgot to add:
                    Try using bind variables for the input parameters used in the dynamic SELECT (do not concatenate them into the select itself like you posted above).

                    Message was edited by:
                    Kamal Kishore
                    • 7. Re: ORA-01007: variable not in select list -- Any suggestions, tips??
                      user488346
                      Thanks Bob-

                      Here is a sample of my table desc EMP1 as well as EMP2 :

                      SQL> desc emp1
                      Name Null? Type
                      ----------------------------------------- -------- -------------------------
                      EMP1_YEAR VARCHAR2(8)
                      EMP1_SSNO VARCHAR2(10)
                      EMP1_LAST_NAME VARCHAR2(60)
                      EMP1_FIRST_NAME VARCHAR2(60)
                      EMP1_STREET VARCHAR2(60)
                      EMP1_CITY VARCHAR2(60)
                      EMP1_STATE VARCHAR2(60)
                      EMP1_ZIP VARCHAR2(15)
                      EMP1_PHONE VARCHAR2(15)
                      EMP1_DATE_OF_BIRTH DATE

                      I cannot take out the emp#_ as you have mentioned.
                      It is the same deal with my EMP2 table.
                      SQL> desc emp2
                      Name Null? Type
                      ----------------------------------------- -------- --------------------------
                      EMP2_YEAR NOT NULL VARCHAR2(8)
                      EMP2_SSNO NOT NULL VARCHAR2(10)
                      EMP2_GUIDE VARCHAR2(20)
                      EMP2_DEGREE VARCHAR2(25)
                      EMP2_STEP VARCHAR2(5)
                      EMP2_SALARY NUMBER
                      EMP2_GUIDE_PERC NUMBER
                      EMP2_LONGEVITY_CODE VARCHAR2(15)
                      • 8. Re: ORA-01007: variable not in select list -- Any suggestions, tips??
                        487355
                        I guess that is why emp1_ and emp2_ are working.
                        I hope you find the changes I made to the where clause a useful trick.
                        Bob.
                        • 9. Re: ORA-01007: variable not in select list -- Any suggestions, tips??
                          user488346
                          Thanks to every one who spent time on my problem. I fixed it, my process is up and running.

                          Thanks once again !!