0

Getting error for syntax for the execute immediate

select distinct hire_date BULK COLLECT into v_yr from employees;
 
 for i in 1..v_yr.count LOOP
 

   
 v_1:='select * from employees where EXTRACT(YEAR FROM TO_DATE(HIRE_DATE,'''
    ||DD-MM-RR
||'''
||'=:1';

     open c_emp for v_1 using v_yr(i);
TBose
  • 115
  • 2
  • 10

1 Answers1

1

Maybe this?

select distinct extract(year from hire_date)
  bulk collect into v_yr
  from employees;
 
for i in 1..v_yr.count loop

   for c_emp in (select *
                   from employees
                  where extract(year from hire_date) = v_yr(i)) loop
.
.
.
   end loop;

end for;
archimede
  • 706
  • 5
  • 9
  • Can yo also help me with below also: – TBose Aug 28 '20 at 11:16
  • execute immediate 'UTL_FILE.fopen ('''||CSVDIR||''', '''||empdata.csv||:1''', '''||w||''', '''||1000||''')' using v_emp(i) I am getting error for the above.I think issue is with quotes. I want empdata.csv1,empdata.csv2....etc during run time from collection v_emp; – TBose Aug 28 '20 at 11:16
  • Again, I don't think you need `execute immediate`. Declare a var `f1 utl_file.file_type;` and then `f1 := utl_file.fopen(CSVDIR, 'empdata.csv'||i, 'w', 1000);`. – archimede Aug 28 '20 at 11:27
  • v_1:= 'select * from employees where EXTRACT(YEAR FROM HIRE_DATE)=:1 AND EXTRACT(month FROM hire_date)=:2 AND to_number(to_char(to_date(HIRE_DATE,'DD/MM/YYYY'),'WW'))=:3' ; What can I do in this case? I wanted to put week number like 1,2,3 etc during runtime – TBose Aug 28 '20 at 11:58
  • @TBose you can use PL/SQL variables for those cases as well. – William Robertson Aug 28 '20 at 13:35