Received: (qmail 26146 invoked from network); 3 Mar 2010 01:45:14 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-85-25-126-90.inaddr.intergenia.de with SMTP; 3 Mar 2010 01:44:59 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 53EF0CD778D; Wed, 3 Mar 2010 02:44:54 -0500 (EST) X-Virus-Scanned: Debian amavisd-new at localhost.localdomain Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id e8h26VHSyIY1; Wed, 3 Mar 2010 02:44:54 -0500 (EST) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D4497CD74A6; Wed, 3 Mar 2010 02:44:12 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 03 Mar 2010 02:43:31 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B2940CD746F for ; Wed, 3 Mar 2010 02:43:31 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 0s8Gv0geItBo for ; Wed, 3 Mar 2010 02:43:31 -0500 (EST) Received: from mail-pv0-f179.google.com (mail-pv0-f179.google.com [74.125.83.179]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3545ACD746C for ; Wed, 3 Mar 2010 02:43:30 -0500 (EST) Received: by pvg16 with SMTP id 16so332885pvg.10 for ; Tue, 02 Mar 2010 23:43:30 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=LFCKPiR0YWTrlWgyhiKzLLfRjlafrInBybOMbP7sOAA=; b=ssO+DWWXllFadizyBFZGMRKrz9vjZP6830lChrZrITR4FJC4tnrmnsltp0PNATo065 dlpgJWktw3aA6o8V/obtca2l2tDKOub8IU3j5h3g1Un3XYI+Ek3FpMOqVd8K6f40TcvW uHO3flz/rZLzm/bXItJxfBJDKUErImjDgBROE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=q8N7PLik+hMVZfBsYPsR6vo35kkEDAc6k5h4dsu/SYX2wCUUT7krYFx6t4lBQvrbQQ YohTwvJ+hb7LRKjUsxhL2NhxkKBXob0PPpzInx6A38S5uhOoKL4zve+1f9Epws3BpPrp dEK2CKs614pDu5x7wldOpPaFqHWvyeyC/sMys= MIME-Version: 1.0 Received: by 10.142.247.23 with SMTP id u23mr4177678wfh.319.1267602210159; Tue, 02 Mar 2010 23:43:30 -0800 (PST) In-Reply-To: <4B8E08B3.9090309@tpg.com.au> References: <4B8DA563.7060104@tpg.com.au> <3edcb66e1003022043x69d85a6dma82efdaa7d94fdf4@mail.gmail.com> <4B8E08B3.9090309@tpg.com.au> Date: Wed, 3 Mar 2010 14:43:30 +0700 Message-ID: <3edcb66e1003022343y1e7a2a33me06c5bd2faf7af74@mail.gmail.com> Subject: Re: execute immediate parsing update From: Ujang Jaenudin To: De DBA Cc: Oracle Discussion List Content-Type: multipart/alternative; boundary=00504502c6c973feb60480e0a328 X-archive-position: 26228 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: ujang.jaenudin@gmail.com Precedence: normal Reply-To: ujang.jaenudin@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --00504502c6c973feb60480e0a328 Content-Type: text/plain; charset=ISO-8859-1 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 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 > > 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 > >> To: ujang.jaenudin@gmail.com >> CC: Oracle Discussion List > > >> >> 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: >> >> >> >> 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 >> >> >> >> >> >> ujang | oracle dba | mysql dba >> jakarta - indonesia >> >> -- >> http://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 --00504502c6c973feb60480e0a328 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable tony,

it seems couldnot using array....
one more case is the data= type should match during DML by execute immediate....


l_str3 := =3D ' l_str_var2(1),l_str_var2(2),l_str_var2(3),l_str_var2(4),l_str_var= 2(5),l_str_var2(6),l_str_var2(7),l_str_var2(8)';

execute immediate l_str2 using l_str3;

UPDATE emp SET ename =3D:= b1 ,hiredate =3D:b2 ,sal =3D:b3 WHERE empno =3D:b4 AND deptno =3D
:b5 AN= D job =3D:b6 AND ENAME =3D:b7 AND SAL =3D:b8 using
l_str_var2(1),l_str_v= ar2(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 cod= e works, but it should be hardcoded after 'USING'....
when all t= he string l_str_201 till l_str208 bound to a variable... got complains that= missing expressions....

=A0execute immediate l_str2 using l_str201,l_str202,l_str203,l_str204,l= _str205,l_str206,l_str207,l_str208;

UPDATE emp SET ename =3D:b1 ,hir= edate =3D:b2 ,sal =3D:b3 WHERE empno =3D:b4 AND deptno =3D
:b5 AND job = =3D:b6 AND ENAME =3D:b7 AND SAL =3D: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 &= lt;dedba@tpg.com.au> wrot= e:
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. Fr= om the original post I gather that l_str3 has many values, separated by com= mas - 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:
=A0execute immediate l_str2 ||' returning into p_rowid using '||l_= str3;

got missing expression.... is it related to array?

UPDATE emp SET ename =3D:b1 ,hiredate =3D:b2 ,sal =3D:b3 WHERE empno =3D:b4= AND
deptno
IN(:5) AND job =3D:b6 AND ENAME =3D:b7 AND SAL =3D:b8 returning into p_rowi= d 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@tpg.com.au
<mailto:dedba@tpg.= com.au>> wrote:

=A0 =A0Again, got caught by overquoting.. ;)


=A0 =A0-------- Original Message --------
=A0 =A0Subject: Re: execute immediate parsing update
=A0 =A0Date: Wed, 03 Mar 2010 09:34:03 +1000
=A0 =A0From: De DBA <dedba@tpg.com.au <mailto:dedba@tpg.com.au>>
=A0 =A0To: u= jang.jaenudin@gmail.com <mailto:ujang.jaenudin@gmail.com>
=A0 =A0CC: Oracle Discussion List <oracle-l@freelists.org
=A0 =A0<mailto:oracle-l@freelists.org>>

=A0 =A0Hi Ujang,

=A0 =A0I may be wrong, but I think that the part that starts with "re= turning
=A0 =A0..." should not be in the string that you pass to "execut= e immediate".
=A0 =A0Instead I think you should hard-code this as part of the execute =A0 =A0immediate statement. Also, the variable in the into clause should b= e
=A0 =A0PL/SQL variable local to your block, not a bind variable as far as = I
=A0 =A0understand.

=A0 =A0i.e.: execute immediate l_str2 returning rowid into p_rowid using .= ... ;

=A0 =A0I'm not sure if you can pass the array of values in the using c= lause as
=A0 =A0a single variable though..

=A0 =A0Hope this helps,
=A0 =A0Tony

=A0 =A0On 2/03/10 10:47 PM, Ujang Jaenudin wrote:

=A0 =A0<snip>

=A0 =A0 =A0 =A0 =A0end loop;

=A0 =A0 =A0 =A0 =A0 =A0 l_str2 :=3D l_str2 ||l_str3;
=A0 =A0 =A0 =A0 =A0 =A0 --|| substr(l_str3,1,length(l_str2)-1);
=A0 =A0 =A0 =A0 =A0 =A0 l_str2 :=3D l_str2 || ' returning into p_rowid= ';
=A0 =A0 =A0 =A0 =A0 =A0 dbms_output.put_line(l_str2);
=A0 =A0 =A0 =A0 =A0 =A0 execute immediate l_str2; =A0--line 68
=A0 =A0 =A0 =A0end;
=A0 =A0 =A0 =A0/

=A0 =A0 =A0 =A0UPDATE emp SET ename =3D:1 ,hiredate =3D:2 ,sal =3D:3 WHERE= empno =3D:4 AND
=A0 =A0 =A0 =A0deptno IN(:5) AND job =3D:6 AND ENAME =3D:7 AND SAL =3D:8 =A0 =A0 =A0 =A0returning rowid into :out using
=A0 =A0 =A0 =A0'xxx','SYSDATE','100000','7934&= #39;,'10,20','CLERK','MILLER','1300'
=A0 =A0 =A0 =A0returning into p_rowid
=A0 =A0 =A0 =A0declare
=A0 =A0 =A0 =A0*
=A0 =A0 =A0 =A0ERROR at line 1:
=A0 =A0 =A0 =A0ORA-00933: SQL command not properly ended
=A0 =A0 =A0 =A0ORA-06512: at line 68

=A0 =A0<snip>



=A0 =A0 =A0 =A0ujang | oracle dba | mysql dba
=A0 =A0 =A0 =A0jakarta - indonesia

=A0 =A0--
=A0 =A0http://www.freelists.org/webpage/oracle-l





--
thanks and regards
ujang | oracle dba | mysql dba
jakarta - indonesia



--
thanks and regards
u= jang | oracle dba | mysql dba
jakarta - indonesia
--00504502c6c973feb60480e0a328-- -- http://www.freelists.org/webpage/oracle-l