Re: execute immediate parsing update
- From: Ujang Jaenudin <ujang.jaenudin@xxxxxxxxx>
- To: De DBA <dedba@xxxxxxxxxx>
- Date: Wed, 3 Mar 2010 14:43:30 +0700
tony,
it seems couldnot using array....
one more case is the data type should match during DML by execute
immediate....
l_str3 := '
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)';
execute immediate l_str2 using l_str3;
UPDATE emp SET ename =:b1 ,hiredate =:b2 ,sal =:b3 WHERE empno =:b4 AND
deptno =
:b5 AND job =:b6 AND ENAME =:b7 AND SAL =:b8 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-01008: not all variables bound
ORA-06512: at line 85
this code works, but it should be hardcoded after 'USING'....
when all the string l_str_201 till l_str208 bound to a variable... got
complains that missing expressions....
execute immediate l_str2 using
l_str201,l_str202,l_str203,l_str204,l_str205,l_str206,l_str207,l_str208;
UPDATE emp SET ename =:b1 ,hiredate =:b2 ,sal =:b3 WHERE empno =:b4 AND
deptno =
:b5 AND job =:b6 AND ENAME =:b7 AND SAL =:b8 using
l_str201,l_str202,l_str203,l_str204,l_str205,l_str206,l_str207,l_str208
PL/SQL procedure successfully completed.
On Wed, Mar 3, 2010 at 1:58 PM, De DBA <dedba@xxxxxxxxxx> wrote:
> Ujang,
>
> 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@xxxxxxxxxx
>> <mailto:dedba@xxxxxxxxxx>> 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@xxxxxxxxxx <mailto:dedba@xxxxxxxxxx>>
>> To: ujang.jaenudin@xxxxxxxxx <mailto:ujang.jaenudin@xxxxxxxxx>
>> CC: Oracle Discussion List <oracle-l@xxxxxxxxxxxxx
>> <mailto:oracle-l@xxxxxxxxxxxxx>>
>>
>> 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
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>>
>>
>>
>> --
>> thanks and regards
>> ujang | oracle dba | mysql dba
>> jakarta - indonesia
>>
>
--
thanks and regards
ujang | oracle dba | mysql dba
jakarta - indonesia
Other related posts:
