3 Replies Latest reply on Nov 29, 2013 5:36 AM by Karthick2003

    ORA-01007 - variable not in select list error in pl\sql code

    User395957

      Hi,

       

      When I tried to run this program I am getting below error:

       

      ORA-01007 - variable not in select list.Please help to resolve.

       

      Code:

       

       

      create or replace procedure "XX_BPM_DATA_P" (P_PROCESS_ID IN VARCHAR2)
      is
      TYPE l_entity_type IS TABLE OF xx_BPM_data.ENTITY%TYPE INDEX BY PLS_INTEGER;
      TYPE l_data_type IS TABLE OF XX_BPM_DATA.DATA%TYPE INDEX BY PLS_INTEGER;
      TYPE l_count_type IS TABLE OF XX_BPM_DATA.count%TYPE INDEX BY PLS_INTEGER;

       

      l_Entity_v l_Entity_type;
      l_data_v l_data_type;
      l_count_v l_count_type;
      l_security_group_id number;
      app_id number(20);

      l_Actual_value XX_BPM_DATA.DATA%TYPE;
      cursor BPM_CUR is select id,process_id , sequence, to_char(query) query,report_num from xx_test_bpm_dynamic
      where
          process_id = p_process_id
             and report_num=1
      order by process_id, sequence;

      BEGIN
      --delete xx_bpm_data where process_id = p_process_id;
      for bpm_rec in bpm_cur
      loop

      delete xx_bpm_data
      where process_id = bpm_rec.process_id
      and sequence = bpm_rec.sequence
      and report_num = bpm_rec.report_num;
      l_security_group_id := apex_custom_auth.get_session_id_from_cookie;
      --dbms_output.put_line(l_security_group_id);

      execute immediate bpm_rec.query BULK COLLECT INTO l_ENTITY_v,l_DATA_v,l_count_v;
      if (bpm_rec.report_num=2) then
      app_id:= 108;--NV('APP_ID');
      FORALL i IN l_ENTITY_v.FIRST..L_ENTITY_V.LAST
      INSERT INTO XX_BPM_DATA
          (EnTITY,
      value,data,count,
      Process_ID,
      Sequence,report_num)
      VALUES(l_entity_v(i),
      l_data_v(i),
      '<A HREF="f?p='||app_id||':301:'||':APP_SESSION'||'::::P301_process_id,p301_sequence,p301_id,p301_entity:'||bpm_rec.process_id||','||bpm_rec.sequence||','||bpm_rec.id||','||l_entity_v(i)||':">'||l_data_v(i)||'</A>',
          l_count_v(i),bpm_rec.process_id,
      BPM_rec.sequence,
      bpm_rec.report_num);
      else
      FORALL i IN l_ENTITY_v.FIRST..L_ENTITY_V.LAST
      INSERT INTO XX_BPM_DATA(EnTITY,data,
          count,
      Process_ID,
      Sequence,report_num)
      VALUES(l_entity_v(i),
      l_data_v(i),
          l_count_v(i),
          bpm_rec.process_id,
      BPM_rec.sequence,
      bpm_rec.report_num);

      end if;
      select round(avg(value),2) into l_actual_value from xx_bpm_data where process_id=bpm_rec.process_id and sequence=bpm_rec.sequence and report_num=bpm_rec.report_num;

      update xx_test_bpm_dynamic set value=l_actual_value where process_id=bpm_rec.process_id and sequence=Bpm_rec.sequence and report_num= bpm_rec.report_num;

      end loop;
      Commit;

      END;

        • 1. Re: ORA-01007 - variable not in select list error in pl\sql code
          Lalit Kumar B

          Error Message

          ORA-01007: variable not in select list

          Cause of Error

          You tried to reference a variable that was not in the SELECT clause.

          Resolution

          The option(s) to resolve this Oracle error are:

          Option #1

          This error can occur in OCI when the value for the position parameter is not a value between 1 and the number of variables in the SELECT clause.

          Option #2

          This error can occur in SQL*Forms or SQL*Report when you specify more variables in the INTO clause than there are values in the SELECT clause.

          • 2. Re: ORA-01007 - variable not in select list error in pl\sql code

            When I tried to run this program I am getting below error:

             

            ORA-01007 - variable not in select list.Please help to resolve.

            You likely would not need any help in you wrote and tested your code using standard best practices.

             

            Your code has NO exception handler and you are NOT identifying each step in the code so that the exception handler could print/log a message telling you EXACTLY which step raised the exception.,

            v_step NUMBER;

            . . .

            -- before step 1

            v_step := 1;

            . . .

            -- before step 2

            v_step := 2;

            Then in the exception handler the value of 'v_step' will tell you which step raised the exception.

             

            The exception you posted refers to a 'select list' so examine ALL of the select lists in your code. If you do that you will see that the primary query being run is obtained from a database table and then executed using dynamic SQL

            execute immediate bpm_rec.query BULK COLLECT INTO l_ENTITY_v,l_DATA_v,l_count_v;

            We have no way of knowing what query is in 'bpm_rec.query' when the exception happens or what columns that query returns. For all we know the query returns 2 columns and you are trying to load 3 collections. Or maybe the query returns 8 columns and you are trying to load 3 collections.

             

            Why don't you print out the query and execute it manually so you can see exactly what the result set looks like?

            dbms_output.put_line(bpm_rec.query);

            And don't even get us started on why you are using such security-prone dynamic sql to perform this processing instead of using ordinary SQL statements. Or why you are using associative arrays for the BULK COLLECT instead of nested tables.

             

            In short your code could blow up in several places and, because you have NO logging statements, control statements or exception handlers, anyone having to troubleshoot that code would have absolutely no idea what part of it may be the problem.

            • 3. Re: ORA-01007 - variable not in select list error in pl\sql code
              Karthick2003

                 cursor bpm_cur

                 is

                 select id

                      , process_id

                      , sequence

                      , to_char(query) query

                      , report_num

                   from xx_test_bpm_dynamic

                  where process_id = p_process_id

                    and report_num = 1

                  order

                     by process_id

                      , sequence;

               

              You store SQL in table? Is your application a super flexible one that does everything on the fly? For me it looks like the starting point of a never ending problem. Storing SQL in table is not the best way to do things. Right place for SQL is within PL/SQL. If you can fix it now please do it.