Oracle APEX Beginner - ORA-00900 Error When Running SQL Script

Question

I'm new to Oracle APEX and I'm trying to run a simple SQL script in my application to update some data in the database. Here's the script I'm using:

START TRANSACTION;
SELECT MAX(SAL) FROM Employee;
UPDATE Employee SET SAL = SAL * 100;
COMMIT;

However, I'm encountering the following error:

ORA-00900: invalid SQL statement
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_230100", line 797
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_230100", line 782
ORA-06512: at "APEX_230100.WWV_FLOW_DYNAMIC_EXEC", line 2035

I searched on the internet to get a solution. But I couldn't find anything Similler. I want to know why this is'nt working.

This is not an Oracle SQL syntax. Please check docs.oracle.com/cd/E11882_01/server.112/e41085/sqlqr01001.htm — cengiz sevimli
– cengiz sevimli, Commented Oct 10, 2023 at 8:52

Littlefoot · Accepted Answer · 2023-10-10 09:25:52Z

1

If you're running that piece of code in SQL Workshop (looks like you are), note that:

there's no start transaction in Oracle (as you were already told; this is not MySQL)
commands can be run one-at-a-time. In other words, you can have several statements in the window, but if you just push the Run button, you'll get an error.

Mark a command (with a mouse, for example) and then run it
finally, commit is useless in this context. If you execute it, you'll get

Commit statement not applicable. All statements are automatically committed.

answered Oct 10, 2023 at 9:25

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Al Mohaimin Farabi Over a year ago

BEGIN DECLARE max_salary NUMBER; BEGIN SELECT MAX(SAL) INTO max_salary FROM Employee; UPDATE Employee SET SAL = SAL * 2; COMMIT; END; END; I just found it on the internet. And it works. I don't know why. I am actually very new to these things

Littlefoot Over a year ago

That's a PL/SQL block. Statements you originally posted are pure SQL.

MT0 · Accepted Answer · 2023-10-12 08:21:09Z

0

START TRANSACTION; is not a valid statement in Oracle.

Transactions are started implicitly with the first statement you use and you cannot explicitly declare a transaction.

Your remaining statements are, individually, valid SQL statements in Oracle's dialect.

fiddle

BEGIN
  DECLARE
    max_salary NUMBER;
  BEGIN
    SELECT MAX(SAL) INTO max_salary FROM Employee;
    UPDATE Employee SET SAL = SAL * 2;
    COMMIT;
  END;
END;

And it works. I don't know why.

If you are using an editor where you can ony run single statements then you have used a PL/SQL anonymous block to wrap multiple SQL statements into a single PL/SQL statement and your editor is running the single PL/SQL statement and passing it to the database which is using the PL/SQL engine to run each of the SQL statements.

Note: You do not need the initial BEGIN and the final END.

edited Oct 12, 2023 at 8:21

answered Oct 10, 2023 at 9:17

MT0

173k12 gold badges70 silver badges136 bronze badges

1 Comment

Al Mohaimin Farabi Over a year ago

BEGIN DECLARE max_salary NUMBER; BEGIN SELECT MAX(SAL) INTO max_salary FROM Employee; UPDATE Employee SET SAL = SAL * 2; COMMIT; END; END; I just found it on the internet. And it works. I don't know why. I am actually very new to these things

Collectives™ on Stack Overflow

Oracle APEX Beginner - ORA-00900 Error When Running SQL Script

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related