Can't Solve ora-06550 pls-00103 ERROR in Qyery

Question

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;

The trick to resolve such issue is to first display the what you are trying to execute immediate. Just print it and then try debug it. — XING
You have many issues in your code. In day_date = day_date + 1; you are missing a :; in ||'-'|| you need to double the quotes; it seems that EMPSHIFT has both an EmpId and an EMPLOYEEID column, is this right? The Query you build uses the date without quoting or ( better) using a to_date,... — Aleksej
thanks yes the column name is EMPLOYEEID and i added double the quotes but still not working — Mohamed Hashem

Ychdziu Ychdziu · Accepted Answer · 2017-05-11T10:47:48

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.

Can't Solve ora-06550 pls-00103 ERROR in Qyery

1 Answers