Fwd: Re: execute immediate parsing update

From: De DBA <dedba_at_tpg.com.au>
Date: Wed, 03 Mar 2010 09:55:15 +1000
Message-ID: <4B8DA563.7060104_at_tpg.com.au>

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> To: ujang.jaenudin_at_gmail.com CC: Oracle Discussion List <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

Received on Tue Mar 02 2010 - 17:55:15 CST