Reputation: 31
I wrote the following query to Update Table EMPSHIFT VALUES From SCHEDULEEMPLOYEES Table but get the following Error ora-06550 pls-00103 and can't solve it so what is the problem
DECLARE day_date DATE:=TO_DATE('01/04/2017','DD/MM/YYYY'); BEGIN LOOP FOR employees IN (SELECT EmpID FROM EMPSHIFT) LOOP EXECUTE IMMEDIATE ' UPDATE EMPSHIFT SET EMPSHIFT."'||TO_CHAR(day_date)||'" = ( SELECT SCHEDULEEMPLOYEES.SCHEDULEID ||'-'|| SCHEDULEEMPLOYEES.SHIFTS FROM SCHEDULEEMPLOYEES INNER JOIN EMPSHIFT ON SCHEDULEEMPLOYEES.EMPLOYEEID = EMPSHIFT.EMPLOYEEID WHERE SCHEDULEEMPLOYEES.DAYDATE = '||TO_CHAR(day_date)||' and EMPSHIFT.EMPLOYEEID = ' || employees.EmpID ||' ) WHERE EMPSHIFT.EMPLOYEEID =' ||employees.EmpID ||';'; day_date = day_date + 1; EXIT WHEN day_date >TO_DATE('30/04/2017','DD/MM/YYYY'); END LOOP; END LOOP; END;
Upvotes: 0
Views: 204
Reputation: 445
1) As others mentioned,"day_date = day_date + 1;" has missing the correct assignment operator ":=". 2) The "EXECUTE..." part is not required here. Why are You using it? 3) What is your goal? The current structure looks "weird". The first loop statement has no control of the flow, only the inner one has, but its loop iterations is only based on the employees count, not the dates. 4) Is the update statement correct? I mean the "set empshift.<..>. I doubt, he has an attribute named "01/04/2017". Created an example,:
declare
l_day_date date:=to_date('01/04/2017','DD/MM/YYYY');
l_res varchar2(400);
begin
loop
for l_emp in (select emp_id from empshift_test_v)
loop
dbms_output.put_line('the emp_id is :'||l_emp.emp_id);
--update empshift_test_v etv
--set etv.empshift_code/*<correct_att_name>*/ = (
select
nvl((select
sct.sch_id ||'-'|| sct.shifts shift_code
from
SCHEDULEEMPLOYEES_TEST_V sct,
empshift_test_v etv1
where
sct.day_date = l_day_date and
sct.emp_id = etv1.emp_id and
etv1.emp_id = l_emp.emp_id),'no_info')
into
l_res
from
empshift_test_v etv
where
etv.emp_id = l_emp.emp_id;
dbms_output.put_line('day_date is :'||to_char(l_day_date,'DD/MM/YYYY'));
dbms_output.put_line('l_res is :'||l_res);
end loop;
l_day_date := l_day_date + 1;
exit when l_day_date >to_date('30/04/2017','DD/MM/YYYY');
end loop;
end;
WHERE views "EMPSHIFT_TEST_V" and "SCHEDULEEMPLOYEES_TEST_V" has info like: enter image description here
Hope it helps.
UPDATE: Modified it according to you data.
declare
l_day_date date:=to_date('01/04/2017','DD/MM/YYYY');
l_res number;
l_stmt varchar2(4000);
begin
loop
for l_emp in (select emp_id from empshift_test)
loop
dbms_output.put_line('the emp_id is :'||l_emp.emp_id);
begin
select
sct.shift
into
l_res
from
SCHEDULEEMPLOYEES_TEST sct,
empshift_test etv
where
sct.daydate = l_day_date and
sct.emp_id = etv.emp_id and
etv.emp_id = l_emp.emp_id;
exception
when NO_DATA_FOUND then
l_res := 0;
end;
dbms_output.put_line('day_date is :'||to_char(l_day_date,'DD/MM/YYYY'));
dbms_output.put_line('l_res is :'||l_res);
if l_res > 0 then
l_stmt := 'update empshift_test emp
set emp."'||to_char(l_day_date,'DD/MM/YYYY')||'" = '||l_res||'
where emp.emp_id = '||l_emp.emp_id||';';
dbms_output.put_line('l_stmt is :'||l_stmt);
execute immediate l_stmt;
end if;
end loop;
l_day_date := l_day_date + 1;
exit when l_day_date >to_date('30/04/2017','DD/MM/YYYY');
end loop;
end;
But there is a catch: if you run the DML statement manually - it works, but with execute immediate - it throws error ora-00933. He cant read the number column for some reason. Another good stack question to solve:) So the plan is: 1) Change the table structure; or 2) Solve the problem, when calling a attribute, named as number (with symbols like "/") using execute immediate.
Upvotes: 0