I created a procedure using execute immediate
, when I debug and step over to execute immediate
in this procedure, it shows the following message :
An Oracle exception has occurred in your code. If the code contains an exception handler, you can continuing stepping through the handler; otherwise, the error message will display and the next step will exit execution.
and then I take out the query, the query in execute immediate is:
update mstarea
set '||v_temp_position||' = :pid
where areacode = :pnewarea1
connect by prior areacode = areaparent
start with areacode in (:pnewarea1, :pnewarea2);
and I try to execute/compile that query, it has an error like this:
ORA-00933: SQL command not properly ended
Is connect by prior
supported with update
?
This is my procedure, and the error is below update mstarea
when it reaches execute immediate
.
CREATE OR REPLACE procedure PROC1(pid in varchar2, pposition in varchar2, pnewarea1 in varchar2, pnewarea2 in varchar2)
is
v_error_message varchar2(255);
v_temp_position varchar2(25 byte);
v_sql_statement varchar2(255);
n_count_role number;
cursor cuser is
select userid from master.mstuser where id = pid;
begin
for cdata in cuser
loop
begin
select count(role) into n_count_role from mstmapping where role = pposition;
if n_count_role > 0 then
begin
..........
if pnewarea1 = 'ALL' then
..........
else
..........
------------------- update mstarea -------------------
if pposition in ('A', 'B') then
select 'AB' into v_temp_position from dual;
else
select pposition into v_temp_position from dual;
end if;
v_sql_statement := 'update mstarea set '||v_temp_position||' = :pid where areacode = :pnewarea1 connect by prior areacode = areaparent start with areacode in (:pnewarea1, :pnewarea2)';
execute immediate v_sql_statement using pid, pnewarea1, pnewarea1, pnewarea2; -- advice from @Rene
------------------------------------------------------
..........
end;
end if;
end;
end if;
exception
when others then
..........
rollback;
end;
commit;
exit when cuser%notfound;
end loop;
end;
What should I do to fix this?