Skip to Main Content
  • Questions
  • Create Table Within SQL Script - When Changes Visible?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Karthikeyan.

Asked: August 05, 2018 - 3:45 pm UTC

Last updated: August 06, 2018 - 10:03 am UTC

Version: oracle 10.1.2

Viewed 1000+ times

You Asked

HI TOM,
I have one SQL script. In That I am creating backup table say table A. After that I am updating the table A with some condition (with No Commit). After that I am creating another backup table say table B. After that I am updating table B with some condition (with No Commit).All those conditions are in My SQL script.

My Assumption is when ever you are updating the table, the changes will be visible for that particular session only (irrespective of DDL Statements issued in the SQL script). It will be visible to other sessions only when we issue commit statement explicitly.


My Colleague statement is (which is little bit confusing) If we use DDL statement and update statement again another DDL statement and update statement in one single SQL script it will be auto commit for that particular session. Please let me know which statement is clear and correct (My Assumption or My colleague assumption

and Chris said...

Your colleague is (sort-of) correct.

DDL includes an implicit commit before and after the statement. So if you create a table part way through a script, you've issued a commit:

create table t1 (
  c1 int
);

insert into t1 values ( 1 );

create table t2 (
  c1 int
);

insert into t2 values ( 1 );

rollback;

select * from t1;

C1   
   1 

select * from t2;

no rows selected


So others can view your changes. And you can't rollback to before creating it.

Although 18c does bring an exception to this rule. Private temporary tables. You can rollback to before you created one:

insert into t1 values ( 2 );

select * from t1;

C1   
   2 
   1 

create private temporary table ora$ptt_t (
  c1 int
);

insert into ora$ptt_t values ( 1 );

rollback;

select * from t1;

C1   
   1 


Though the rollback also destroys your PTT:

select * from ora$ptt_t;

SQL Error: ORA-00942: table or view does not exist


You need to set the on commit clause to preserve definition to avoid this. But whatever you do, when your session ends the PTT is gone!

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.