Re: Oracle/VB....can't we all just get along?

A copy of this was sent to "Steve P" <stevep_at_nospamprosoftcorp.com> (if that email address didn't require changing) On 25 May 1999 10:34:57 PDT, you wrote:

>Hi,
>
>Does anyone know how to submit multiple SQL statements to an Oracle database
>using
>SQL created in VB?
>
>For example in SQL Worksheet for Oracle I can successfully submit the
>following statement:
>
>UPDATE Users Set Name = 'Steve' WHERE User_ID = 1;
>UPDATE Users Set Name = 'Frank' WHERE User_ID = 2;
>
>and execute both at once. But in VB when I try:
>

no - -they are not executed at once but are executed one after the other.

>sSql = "UPDATE Users Set Name = 'Steve' WHERE User_ID = 1; " & vbCrLf &
>"UPDATE Users Set Name = 'Frank' WHERE User_ID = 2;"
>
>I get an Oracle error which says 'invalid character'. I get the same
>message when using SQL*Plus. Is there any way around this in VB?
>
>Please let me know if you know of a solution.
>

if you want to submit them both at once, try:

begin
  update users set name = 'Steve' where user_id = 1;   update users set name = 'Frank' where user_id = 2; end;

But if you want *performance* (thats probably what you are trying to get by submitting >1 statement in a block -- but it won't go as fast as it could) submit something like:

update users set name = ? where user_id = ?

and bind the values (Steve,1), ( Frank, 2 ) in and execute the statement 2 times from the client.

>Thanks a lot,
>
>SP
>
>
>
>
>
>

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue May 25 1999 - 14:37:01 CDT