Oracle Cannot retain data

user_11498860 0
Viewed 46

I have oracle database installed on my system

I access it through command line.

win+r > cmd > sqlplus

I had created a user 

system

and 

password system

I created a table 

create table users ( username varchar(20));

Inserted few records. 

insert into users (username ) values ('user1');
insert into users (username ) values ('user2');

then I exit

>exit

Problem is that when I try to login again.

table is still there. But the data which I had inserted is gone.

select * from users;

OUTPUT

no rows selected
oracle sql
1 Answers

SQL*Plus has option to auto-commit:

SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n}

From documentation:

  • ON commits pending changes to the database after Oracle Database executes each successful INSERT, UPDATE, or DELETE, or PL/SQL block.
  • OFF suppresses automatic committing so that you must commit changes manually.
  • SET AUTOCOMMIT does not alter the commit behavior when SQL*Plus exits. Any uncommitted data is committed by default.

To control the commit on EXIT, you need to set the EXITCOMMIT parameter in SQL*Plus:

SQL> show exitcommit
exitcommit ON
SQL> set exitcommit off

If it's et to OFF, then you need to explicitly COMMIT after the insert statement which is a DML transaction. Create table is a DDL statement which does an implicit commit.

insert into users (username ) values ('user1');
insert into users (username ) values ('user2');
COMMIT; --> add this

I have created a user system

SYSTEM is an administrative user account which is automatically created when you install Oracle database. It is meant for administration tasks and not to store application data etc. Create a separate user for your application.

user_39896080
Related