Write Oracle DML Statements

To help you truly understand how all the disparate pieces of the Oracle Database work together, this page contains an example of the steps taken by the Oracle Database to respond to a user request. This example examines the work of a user who is adding new information to the database, in other words, executing a transaction

Oracle and Transactions

A transaction is a work request from a client to retrieve, insert, update, or delete data. We will focus on transactions that modify data, as opposed to retrieving data. The statements that change data are a subset of the SQL language called Data Manipulation Language. Transactions must be handled in a way that guarantees their integrity. I will discuss a few basic concepts relating to transactions now in order to understand the example in this section:

Transactions are logical and complete

In database terms, a transaction is a logical unit of work composed of one or more data changes. A transaction may consist of multiple INSERT, UPDATE, and/or DELETE statements affecting data in multiple tables. The entire set of changes must succeed or fail as a complete unit of work. A transaction starts with the first DML statement and ends with either a commit or a rollback.
Oracle also supports autonomous transactions, which are transactions whose work is committed or rolled back, but that exist within the context of a larger transaction. Autonomous transactions are important because they can commit work without destroying the context of the larger transaction.

Oracle PL/SQL Here is the finished code string:

UPDATE customer_obj_table cot
set cot.full_address.state = ‘FL’
WHERE cust_id = 26

How commit and rollback Work in SQL

select * from CANINE:
NAME
-------
TRAUBLE
insert into CANINE values ('SPECS');
insert into CANINE values ('BLACKIE');
insert into CANINE values ('FRED');
insert into CANINE values ('MISTY');
update CANINE set Name = 'TROUBLE' where Name = 'TRAUBLE';
rollback;
select * from CANINE:

NAME
--------
TRAUBLE
update CANINE set Name = 'TROUBLE' where Name = 'TRAUBLE';
insert into CANINE values ('SPECS');
insert into CANINE values ('BLACKIE');
insert into CANINE values ('FRED');
commit;
insert into CANINE values ('MISTY');
rollback;
select * from CANINE:
NAME
-------
TROUBLE
SPECS
BLACKIE
FRED