Hi Expertrs,
I am trying to execute below script it executed fine, I am using a concept of bulk collect without any cursor- But when i am trying to add Limit 100 using cursor at end of SQL statement it is throwing error saying SQL Statement ignored.
Cant we use limit clause without a cursor ?
QUERY 1 - In this script Id did not used cursor but executed fine, when i am trying to add limit 100 at end of SQL statement it is throwing error.
create or replace procedure P_name (v_dept_id IN employees.department_id%type, v_percent IN number) is
type v_emp_ls is table of employees.employee_id%type;
v_empid v_emp_ls;
begin
select employee_id bulk collect into v_empid from employees; -- LIMIT 100
forall i in 1..v_empid.count
update employees
set salary= salary + salary * v_percent
where department_id = v_dept_id
and employee_id = v_empid(i);
commit;
exception
when others then
null;
end;
---------------------------------------------------------------------------------
QUERY 2 In this script I used cursor , when i am trying to add limit 100 at end of SQL statement it is throwing error.
create or replace procedure P_name (v_dept_id IN employees.department_id%type, v_percent IN number) is
type v_emp_ls is table of employees.employee_id%type;
v_empid v_emp_ls;
cursor c_name is select * from employees;
begin
for i in c_name loop
select i.employee_id bulk collect into v_empid from employees limit 100;
UPDATE employees emp
SET emp.salary = emp.salary + emp.salary * v_percent
WHERE emp.employee_id = i.employee_id
AND emp.department_id = v_dept_id;
end loop;
commit;
exception
when others then
null;
end;
Could you please help me by modifying above for loop to set the limit clause , I don't want to use the other of cursor that is using FETCH OPEN AND CLOSE cursor coding
Thanks