error "operation not properly ended" when attempting to use two update commands

Question

I am very new to SQL, why does the code below give the error "operation not properly ended"? separately the 2 commands work, but when together it gives the error?

UPDATE sec0503_checking_accounts
   SET balance = balance + 5000
WHERE customer = 'name';

UPDATE sec0503_savings_accounts
    SET balance = balance -5000
WHERE customer = 'name';

i have tried removing the semicolons, and have looked around for some solutions, but none of them seem to work. either of these commands also give the same error when used in conjunction with other commands.

keep in mind, i am using apex oracle and this is just for practice.

Answer 1

A semi-colon is not part of the SQL language nor interpreted by the Oracle SQL engine (except for the rare case of marking the end of a CTE function, but that's technically a PL/SQL boundary). It is rather a commonly used convention in clients and programming languages for end-of-statement markers. In Oracle, it has meaning within the PL/SQL language, and in the SQLPlus client interface tool. But it isn't part of SQL itself.

Most likely you are using a client or submitting this SQL through a tool that does not interpret the semicolon on its own, and it's sending it as-is to the database engine as part of the SQL itself. If that's the case, remove the semicolon and submit each statement separately. If you want to send both statements in one action, find out how your particular client software handles multiple statements - it will vary by software. If you want the database to process both as a unit, then you'll need to use a PL/SQL block by wrapping it in BEGIN ... END. Then your client is submitting the PL/SQL block as a single unit and then PL/SQL engine in the database will interpret the semicolons as statement-endings within that programming language:

BEGIN -- this makes the following into PL/SQL

  UPDATE sec0503_checking_accounts
     SET balance = balance + 5000
  WHERE customer = 'name';

  UPDATE sec0503_savings_accounts
     SET balance = balance -5000
  WHERE customer = 'name';
END;

PL/SQL will internally submit each SQL separately to the SQL engine without the semi-colon, as that has meaning only to PL/SQL itself.

Answer 2

In Oracle Apex, if you are using the SQL Workshop then you can only run a single command at a time.

If you try to run an entire script, consisting of multiple statements, then it will be passed in its entirety to the database's SQL engine which will then fail because it is expecting a single command and not multiple commands (and the ; terminating the first statement is not expected syntax and so the operation is not properly ended and an error stating that is raised).

Instead, what you should do is highlight the statement that you want to run and then click the run button.

This answer gives an example:

Alternatively, you can wrap multiple SQL statements in a single PL/SQL block (as described by @PaulW's answer) so that the single statement being evaluated is the entire PL/SQL block, within which each individual SQL statement will be evaluated in sequence.