Oracle: Commit and Forward Slash

Introduction

Further to my post earlier about DDL and Forward Slash, I would like to explain Forward slash’s role (or lack thereof) in other scenarios. One such scenario people seem to get confused with is COMMIT. They seem to want to find some type of connection between the two. Let me say it upfront, there is no connection between COMMIT and “/”.

Forward Slash is simply a SQL Executor and it happens to show up in various situations. This combined with SQL*Plus’s own quirks seem to imply certain other functions for this character. Let me repeat: it’s just an executor – short for RUN command in SQL*Plus.

One reason it could be construed as connected with COMMIT is that it may often be the last character in a file. When you run SQL*Plus in batch mode (running a script in a file), it typically exits after the last line (on *nix. It doesn’t DoS/Windows). And the SQL*Plus’s own behavior is to COMMIT a transaction when exiting. (Why? See here for a nice discussion about this.) So, it’s the exit that committed not the “/”. But. you can see why some newcomer to Oracle who just inherited some scripts, may think that “/” (being the last statement) actually committed the transaction!!!

The other reason could be DDL. DDLs are implicitly committed. (Actually it commits before and after the DDL statement itself, so beware if you are mixing DMLs and DDLs).

Now, typically a DDL could be ended with “/”. For e.g.,

INSERT INTO department(100, 'HR');

CREATE TABLE employee(
    emp_id NUMBER,
    emp_name VARCHAR2(40)
/ 

In the above case, department table will have the entry, even if the CREATE TABLE below failed. In general, any DML (like INSERT/UPDATE/DELETE) before a DDL, DML would have been committed, even if the DDL itself failed. Since “/” is the last statement here, one could think, “/” did it!!! Another strike against “/”.

So, trust me folks! “/” doesn’t do anything else, except sending a SQL to Oracle in SQL*Plus. It’s just a shortcut for Run Command. But, depending on the situation, it may look like it’s doing something more. In such cases, analyze the SQL and try to rearrange them. Try to keep DMLs separate from DDLs to avoid surprises. And of course, set some standards for punctuations and stick to it to avoid any undesired effects.