2

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?

Ade Rianto
  • 31
  • 6

1 Answers1

1

Zooming in on just this part of the code:

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, pnewarea2;

You have four variables in your query. Although two of them have the same name you have to provide a value for each position. To fix this use pnewarea1 twice:

execute immediate v_sql_statement                         
    using pid, pnewarea1, pnewarea1, pnewarea2;

-- Edit----

Conclusion is that update .. connect by prior is not supported in Oracle.

Rene
  • 10,391
  • 5
  • 33
  • 46
  • oh, ok, thanks for the advice @Rene, i've added that parameter but still had a same error. is it from the statement? or something? – Ade Rianto Apr 11 '16 at 07:57
  • Without the actual error it's hard to give an answer. Pick up the query and run in it SQLplus. – Rene Apr 11 '16 at 08:04
  • ok @Rene, when i take out that query, the error message is: `ORA-00933: SQL command not properly ended` am i wrong? – Ade Rianto Apr 11 '16 at 08:09
  • And what are the values for pid, pnewarea1, pnewarea1, pnewarea2? Do they all have a value? If not that might be your problem. – Rene Apr 11 '16 at 08:12
  • Maybe the question is: Is update and connect by prior supported? – Rene Apr 11 '16 at 08:20
  • yeah they all have a value. that's the point! how connecting `update` with `connect by prior` thanks @Rene. Sorry, i got confused here. Soo, i still can't fix this. But, many thanks for the advices :) – Ade Rianto Apr 11 '16 at 09:01