In Oracle, does a DDL script need commit statements when run with sqlplus?

Question

DDL scripts do not need COMMIT statements as per the docs.

If I run these statements in SQL Developer, no commit is required:

CREATE TABLE dummy_table(ID NUMBER(38,0) NOT NULL PRIMARY KEY);
CREATE SEQUENCE dummy_seq START WITH 1 INCREMENT BY 1 NOCACHE;

When I run with sqlplus, log says :

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 3 11:28:54 2024
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
Last Successful login time: Wed Jul 03 2024 11:28:53 +02:00
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0
 
 
Table created.
 
 
Sequence created.
 
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

But when I check if the table and sequence were created, I can see that they were not created.

After adding COMMIT at the end of the script, still neither of them were created.

Only after adding COMMIT after each statement, both the table and the sequence were created.

My sqlplus command:

sqlplus  USER/"PASSWORD"@DATABASE @ /path/to/script.sql

DDL doesn't require COMMIT. SQL Plus reported that both table and sequence were created. Please, post the whole SQL Plus session which shows everything you did - how you connected, ran the script, checked whether objects exist. — Littlefoot
– Littlefoot, Commented Jul 3, 2024 at 11:42
@Sybuser - "But when I check if the table and sequence were created, I can see that they were not created." - show us exactly how you're doing this please — thatjeffsmith
– thatjeffsmith, Commented Jul 3, 2024 at 11:59
I do a select on the table, it says : "ORA-00942: table or view does not exist" — Sybuser
– Sybuser, Commented Jul 3, 2024 at 12:12
There's a sh script which executes SQL scripts with sqlplus in a loop. I have to do a CTRL+D after each script to exit each sqlplus session (any idea how to avoid that?). It appears that when my SQL script is executed alone, I don't have the issue. — Sybuser
– Sybuser, Commented Jul 4, 2024 at 6:16

thatjeffsmith · Accepted Answer · 2024-07-03 11:51:49Z

2

No.

DDL causes everything in your session to be implicitly committed.

From the Docs:

Oracle Database issues an implicit COMMIT under the following circumstances:

Before any syntactically valid data definition language (DDL) statement, even if the statement results in an error

After any data definition language (DDL) statement that completes without an error

This is true for any client side application issuing the work, INCLUDING but not limited to, SQLPlus.

If you don't see your table and sequence when you 'went back in' to 'check it' - you either connected to the wrong database or you were looking in the wrong schema.

answered Jul 3, 2024 at 11:51

thatjeffsmith

22.7k6 gold badges44 silver badges139 bronze badges

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

2 Comments

Sybuser Over a year ago

Colleagues gaslighting me and urging me to put additional commits where it makes no sense and by coincidence of circumstances it happens to work. I still try to figure out how.

thatjeffsmith Over a year ago

Share your script, then we can teach you.

Collectives™ on Stack Overflow

In Oracle, does a DDL script need commit statements when run with sqlplus?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related