An Oracle database transaction involves one or more statements consisting of one

Question

An Oracle database transaction involves one or more statements consisting of one data definition language (DDL) that is responsible for the implicit ending of transactions and at least one or more data manipulation language (DML) that constitutes atomic database changes.  

There are many ways to interface with an Oracle database.

Question A

Name one way to interface with an oracle database and give an example

Question B

Name a different way to interface with an oracle database and give an example

Explanation / Answer

Data Definition Languages (DDL) are used to define the database structure. Any CREATE, DROP and ALTER commands are examples of DDL SQL statements.

Data definition language (DDL) statements let you to perform these tasks:

The CREATE, ALTER, and DROP commands require exclusive access to the specified object. For example, an ALTER TABLE statement fails if another user has an open transaction on the specified table.

The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table.

Oracle Database implicitly commits the current transaction before and after every DDL statement.

Many DDL statements may cause Oracle Database to recompile or reauthorize schema objects. For information on how Oracle Database recompiles and reauthorizes schema objects and the circumstances under which a DDL statement would cause this, see Oracle Database Concepts.

DDL statements are supported by PL/SQL with the use of the DBMS_SQL package.

The DDL statements are:

ALTER ... (All statements beginning with ALTER, except ALTER SESSION and ALTER SYSTEM—see "Session Control Statements" and "System Control Statement")

ANALYZE

ASSOCIATE STATISTICS

AUDIT

COMMENT

CREATE ... (All statements beginning with CREATE)

DISASSOCIATE STATISTICS

DROP ... (All statements beginning with DROP)

FLASHBACK ... (All statements beginning with FLASHBACK)

GRANT

NOAUDIT

PURGE

RENAME

REVOKE

TRUNCATE

DML statements are SQL statements that manipulate data. DML stands for Data Manipulation Language. The SQL statements that are in the DML class are INSERT, UPDATE and DELETE. Some people also lump the SELECT statement in the DML classification.

Data manipulation language (DML) statements access and manipulate data in existing schema objects. These statements do not implicitly commit the current transaction. The data manipulation language statements are:

CALL

DELETE

EXPLAIN PLAN

INSERT

LOCK TABLE

MERGE

SELECT

UPDATE

The SELECT statement is a limited form of DML statement in that it can only access data in the database. It cannot manipulate data stored in the database, although it can manipulate the accessed data before returning the results of the query.

The CALL and EXPLAIN PLAN statements are supported in PL/SQL only when executed dynamically. All other DML statements are fully supported in PL/SQL.

Transaction control statements manage changes made by DML statements. The transaction control statements are:

COMMIT

ROLLBACK

SAVEPOINT

SET TRANSACTION

SET CONSTRAINT

All transaction control statements, except certain forms of the COMMIT and ROLLBACK commands, are supported in PL/SQL. For information on the restrictions, see COMMIT and ROLLBACK.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.