I am using following store procedure to insert data to a table.

create or replace PROCEDURE PM
(
  date1 in varchar2
 ,date2 in varchar2
 ,date3 in varchar2
) AS

cursor cur_cd is
(
  select to_date(date1,'DD-MON-YY') as date1
          ,trim(t.DEPT_CODE) DEPT_CODE
          ,count(t.DEPT_CODE) count_dept
          ,sum(t.amount) amount
  from department t
  where t.date >= to_date(date2,'DD-MON-YY') 
  and t.date <= to_date(date2,'DD-MON-YY')
  and t.dept_name like 'finance%' 
  and (trim(t.DT_code)='TR_01' or t.DT_file like 'DTF_20%')
  and t.DEPT_CODE not in ('HR','ADMIN','ACADEMIC')
  group by t.DEPT_CODE 
);

Type rec_set is table of dept_file%rowtype;
    v_rec_set record_set;
    begin
        open cur_cd;
        loop
        fetch cur_cd
           bulk collect into v_rec_set limit 100;
           exit when v_rec_set.count()=0;
     
        begin
          forall i in v_rec_set.first..v_rec_set.last
          insert into dept_file
          values v_rec_set(i);
        end;
      end loop;
      close cur_cd;

    exception when others then raise;
end PM;

It's giving me a runtime error when execute procedure. But the query execute without error manually.

ORA-000979 : not a GROUP BY expression
ORA-006512 : at "ABS.PM", line 9

Also, when hard code the parameters (date1, date2 and date3) the procedure working without error.

Can you please help me to resolve this error?

CodePudding user response：

All non-aggregated columns must be specified in the GROUP BY clause. Therefore:

GROUP BY TO_DATE (date1, 'DD-MON-YY'), TRIM (t.dept_code)

By the way, are you really storing date values as strings? Why do I ask? Because you're using TO_DATE functions all over the code. If you are, then try not to do it in the future. Oracle offers DATE datatype, you should use it.