Skip to main content

We need to be very careful with DDL statements....

Take a look at the following SQL statements...

Select * from employee; -- the table has 1000 records

Insert into employee(emp_id,emp_name,salary)
values('E_101','Susil Kumar',20000);

Insert into employee(emp_id,emp_name,salary)
values('E_102','Sukumar',20000);

Insert into employee(emp_id,emp_name,salary)
values('E_103','Ramuk Lisus',10000);

-- We have not committed the inserts yet

create table employee
as
Select * from employee; -- errors out with exception 'name is already used by an existing object'

rollback; -- We are trying to roll back the data insert operation

Select * from employee where emp_id='E_102'; -- This query returns row though we didnt explicitly commit the changes. It is because we ran Create table statement ( DDL ) which did the trick and ran a commit on this session ....

Conclusion : Be careful when writing DDL statements in a session. Make sure you have rolled back the changes if need be.

Comments

Khaja V Shaik said…
This comment has been removed by the author.
January 9, 2012 at 8:37 AM
Khaja V Shaik said…
very useful info...nice to know this
January 9, 2012 at 8:48 AM
Post a Comment

Popular posts from this blog

SQL to return records in m,n sequence

I want my SQL to return only records from 101 to 200. See below options which would do the required.. Select * from (Select rownum rownumber,employee.* from employee) Where rownumber between 101 and 200; -- Using rownum label in oracle Select * from (Select employee.*, row_number() over (order by emp_id desc) Rownumber from employee) where Rownumber between 101 and 200; --Using the analytical function row_number Select * from (Select employee.*, row_number() over (order by emp_id desc) Rownumber from employee) where Rownumber between :Fromnumber and :tonumber; -- lets user decide the recordset chunk... Hope this is helpful!!!!!!
Post a Comment
Read more

PLSQL programming - Part 1

Pre-requisite : Basic SQL Knowledge in Oracle and programming knowledge in any language Browse my blog to read the overview of PLSQL if you need it. Anonymous block ............. ........... This is where you declare Variables that you are going to use it in PLSQL block ............ Data types are chosen based on requirement. For example, if you want to process strings you go for data types of characters and for numeric operations Int, float , double or number. Begins ..... ......... ......... .......... This is where you implement programming logic and get the desired outcome. .......... .......... Ends..... We call it anonymous block because we don't name it or reference it. (Unlike Package, Stored procedure,& Other objects) Learn from examples Example 1: declare var1_num number; --Declaring number type variable var2_str varchar2; begin var1_num := 10; --Assigning 'Number' Value to the variable var2_str := 'I am a string value...
1 comment
Read more

What happens when you create tables with the existing SYNONYM names

See below snippets: Select * from dba_synonyms where synonym_name='DUAL'; OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK PUBLIC DUAL SYS DUAL (NULL) We know that DUAL table is accessible by users. Lets create a table named DUAL under our schema (USER). CREATE TABLE dual AS SELECT * FROM dual; Select * from dual; insert into dual values('Y'); commit; Select * from dual; DUMMY ---------- X Y The Synonym is not referred unless the Synonym name is not used by any object under the USER/SCHEMA... So , When creating tables in your schema, take a look at SYNONYMs declared as PUBLIC and try not to use them.
1 comment
Read more