title: Is UPDATE and CONNECT BY supported?
body: <p>I created a procedure using <code>execute immediate</code>, when I debug and step over to <code>execute immediate</code> in this procedure, it shows the following message :</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>and then I take out the query, the query in execute immediate is:</p>
  
  <pre><code>update mstarea 
  set '||v_temp_position||' = :pid 
  where areacode = :pnewarea1 
  connect by prior areacode = areaparent 
  start with areacode in (:pnewarea1, :pnewarea2);
  </code></pre>
  
  <p>and I try to execute/compile that query, it has an error like this:</p>
  
  <blockquote>
    <p>ORA-00933: SQL command not properly ended</p>
  </blockquote>
  
  <p>Is <code>connect by prior</code> supported with <code>update</code>?</p>
  
  <p>This is my procedure, and the error is below <code>update mstarea</code> when it reaches <code>execute immediate</code>.</p>
  
  <pre><code>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 &gt; 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;
  </code></pre>
  
  <p>What should I do to fix this? </p>