Re: execute immediate parsing update

You are passing the returning and using parts of the command as part of the SQL to be executed. Oracle is trying to execute "UPDATE...RETURNING..", which is not a valid command.

The syntax is:

EXECUTE IMMEDIATE ... RETURNING ... INTO ... USING ... ; You need to fill in the dots. What you can try is:

EXECUTE IMMEDIATE l_str2 RETURNING INTO p_rowid USING l_str3;

l_str2 can only contain the UPDATE command, not RETURNING INTO or USING.  From the original post I gather that l_str3 has many values, separated by commas - an array of values in other words. I am unsure if you can use l_str3 in place of the comma-separated list that the documentation specifies after USING.

Cheers,
Tony van Lingen

On 3/03/10 2:43 PM, Ujang Jaenudin wrote:
> de,
>
> try this:
> execute immediate l_str2 ||' returning into p_rowid using '||l_str3;
>
> got missing expression.... is it related to array?
>
> UPDATE emp SET ename =:b1 ,hiredate =:b2 ,sal =:b3 WHERE empno =:b4 AND
> deptno
> IN(:5) AND job =:b6 AND ENAME =:b7 AND SAL =:b8 returning into p_rowid using
> l_str_var2(1),l_str_var2(2),l_str_var2(3),l_str_var2(4),l_str_var2(5),l_str_var2
> (6),l_str_var2(7),l_str_var2(8)
> declare
> *
> ERROR at line 1:
> ORA-00936: missing expression
> ORA-06512: at line 64
>
>
> On Wed, Mar 3, 2010 at 6:55 AM, De DBA <dedba_at_tpg.com.au
> <mailto:dedba_at_tpg.com.au>> wrote:
>
> Again, got caught by overquoting.. ;)
>
>
> -------- Original Message --------
> Subject: Re: execute immediate parsing update
> Date: Wed, 03 Mar 2010 09:34:03 +1000
> From: De DBA <dedba_at_tpg.com.au <mailto:dedba_at_tpg.com.au>>
> To: ujang.jaenudin_at_gmail.com <mailto:ujang.jaenudin_at_gmail.com>
> CC: Oracle Discussion List <oracle-l_at_freelists.org
> <mailto:oracle-l_at_freelists.org>>
>
> Hi Ujang,
>
> I may be wrong, but I think that the part that starts with "returning
> ..." should not be in the string that you pass to "execute immediate".
> Instead I think you should hard-code this as part of the execute
> immediate statement. Also, the variable in the into clause should be
> PL/SQL variable local to your block, not a bind variable as far as I
> understand.
>
> i.e.: execute immediate l_str2 returning rowid into p_rowid using .... ;
>
> I'm not sure if you can pass the array of values in the using clause as
> a single variable though..
>
> Hope this helps,
> Tony
>
> On 2/03/10 10:47 PM, Ujang Jaenudin wrote:
>
> <snip>
>
> end loop;
>
> l_str2 := l_str2 ||l_str3;
> --|| substr(l_str3,1,length(l_str2)-1);
> l_str2 := l_str2 || ' returning into p_rowid';
> dbms_output.put_line(l_str2);
> execute immediate l_str2; --line 68
> end;
> /
>
> UPDATE emp SET ename =:1 ,hiredate =:2 ,sal =:3 WHERE empno =:4 AND
> deptno IN(:5) AND job =:6 AND ENAME =:7 AND SAL =:8
> returning rowid into :out using
> 'xxx','SYSDATE','100000','7934','10,20','CLERK','MILLER','1300'
> returning into p_rowid
> declare
> *
> ERROR at line 1:
> ORA-00933: SQL command not properly ended
> ORA-06512: at line 68
>
> <snip>
>
>
>
> ujang | oracle dba | mysql dba
> jakarta - indonesia
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
> --
> thanks and regards
> ujang | oracle dba | mysql dba
> jakarta - indonesia

--
http://www.freelists.org/webpage/oracle-l