All Statements in the column NEW have been updated in the 12.1.0.2 release.
The Oracle Database 12c SQL DML statements access and manipulate data in existing schema objects
| SATEMENT | PAGE | NEW | COMMIT | PL/SQL SUPPORT |
|---|---|---|---|---|
| SELECT | 77 | SELECT | DOES NOT implicitly commit the current transaction. | SELECT INTO is fully supported in PL/SQL code, but SELECT without INTO must be executed dynamically in PL/SQL code. |
| INSERT | 17 | DOES NOT implicitly commit the current transaction. | Fully Supported in PL/SQL. | |
| UPDATE | 71 | DOES NOT implicitly commit the current transaction. | Fully Supported in PL/SQL. | |
| DELETE | 9 | DOES NOT implicitly commit the current transaction. | Fully Supported in PL/SQL. | |
| MERGE | 5 | DOES NOT implicitly commit the current transaction. | Fully Supported in PL/SQL. | |
| LOCK TBLE | 3 | DOES NOT implicitly commit the current transaction. | Fully Supported in PL/SQL. | |
| EXPLAIN PLAN | 4 | DOES NOT implicitly commit the current transaction. | Supported in PL/SQL only when executed dynamically. | |
| CALL | 4 | DOES NOT implicitly commit the current transaction. | Supported in PL/SQL only when executed dynamically. |
The Oracle Database 12c Transaction Control Statements manage changes made by DML statements
| SATEMENT | PAGE | NEW | COMMIT | PL/SQL SUPPORT |
|---|---|---|---|---|
| COMMIT | 14 | Commits a transaction. | Fully Supported in PL/SQL. | |
| ROLLBACK | 2 | Rolls back a transaction. | Fully Supported in PL/SQL. | |
| SAVEPOINT | 2 | DOES NOT implicitly commit the current transaction. | Fully Supported in PL/SQL. | |
| SET TRANSACTION | 3 | DOES NOT implicitly commit the current transaction. | Fully Supported in PL/SQL. | |
| SET CONSTRAINT[S] | 2 | DOES NOT implicitly commit the current transaction. | Fully Supported in PL/SQL. |
The Oracle Database 12c SQL DDL statements create, alter, and drop schema objects, grant and revoke privileges and roles, analyze information on a table, index, or cluster, establish auditing options, add comments to the data dictionary
| SATEMENT | PAGE | NEW | COMMIT | PL/SQL SUPPORT |
|---|---|---|---|---|
| ADMINISTER KEY MANAGEMENT | 19 | ADMINISTER KEY MANAGEMENT | Implicit COMMIT of the current transaction before and after every DDL statement. | Supported by PL/SQL with the use of the DBMS_SQL package. |
| ALTER … | 345 |
ALTER PLUGGABLE DATABASE ALTER CLUSTER |
Implicit COMMIT of the current transaction before and after every DDL statement. | Supported by PL/SQL with the use of the DBMS_SQL package. |
| CREATE … | 361 |
CREATE PLUGGABLE DATABASE CREATE CLUSTER | Implicit COMMIT of the current transaction before and after every DDL statement. | Supported by PL/SQL with the use of the DBMS_SQL package. |
| DROP … | 56 |
DROP PLUGGABLE DATABASE DROP MATERIALIZED ZONEMAP |
Implicit COMMIT of the current transaction before and after every DDL statement. | Supported by PL/SQL with the use of the DBMS_SQL package. |
| FLASHBACK … | 9 | Implicit COMMIT of the current transaction before and after every DDL statement. | Supported by PL/SQL with the use of the DBMS_SQL package. | |
| ANALYZE | 8 | Implicit COMMIT of the current transaction before and after every DDL statement. | Supported by PL/SQL with the use of the DBMS_SQL package. | |
| ASSOCIATE STATISTICS |
4 | Implicit COMMIT of the current transaction before and after every DDL statement. | Supported by PL/SQL with the use of the DBMS_SQL package. | |
| AUDIT … | 19 | CREATE AUDIT POLICY (Unified Auditing) AUDIT (Unified Auditing) |
Implicit COMMIT of the current transaction before and after every DDL statement. | Supported by PL/SQL with the use of the DBMS_SQL package. |
| CREATE COMMENT | 2 | Implicit COMMIT of the current transaction before and after every DDL statement. | Supported by PL/SQL with the use of the DBMS_SQL package. | |
| DROP DISASSOCIATE STATISTICS | 2 | Implicit COMMIT of the current transaction before and after every DDL statement. | Supported by PL/SQL with the use of the DBMS_SQL package. | |
| GRANT | 27 | GRANT | Implicit COMMIT of the current transaction before and after every DDL statement. | Supported by PL/SQL with the use of the DBMS_SQL package. |
| NOAUDIT … | 9 | NOAUDIT (Unified Auditing) | Implicit COMMIT of the current transaction before and after every DDL statement. | Supported by PL/SQL with the use of the DBMS_SQL package. |
| PURGE | 2 | Implicit COMMIT of the current transaction before and after every DDL statement. | Supported by PL/SQL with the use of the DBMS_SQL package. | |
| RENAME | 2 | Implicit COMMIT of the current transaction before and after every DDL statement. | Supported by PL/SQL with the use of the DBMS_SQL package. | |
| REVOKE | 10 | REVOKE | Implicit COMMIT of the current transaction before and after every DDL statement. | Supported by PL/SQL with the use of the DBMS_SQL package. |
| TRUNCATE… | 6 | TRUNCATE TABLE | Implicit COMMIT of the current transaction before and after every DDL statement. | Supported by PL/SQL with the use of the DBMS_SQL package. |
Be aware of the following facts:
- 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.
- Many DDL statements may cause Oracle Database to recompile or reauthorize schema
objects.
The Oracle Database 12c Session Control Statements dynamically manage the properties of a user session
| SATEMENT | PAGE | NEW | COMMIT | PL/SQL SUPPORT |
|---|---|---|---|---|
| ALTER SESSION … | 14 | DOES NOT implicitly commit the current transaction. | PL/SQL DOES NOT Support session control statements | |
| SET ROLE | 3 | DOES NOT implicitly commit the current transaction. | To run the SET ROLE command from PL/SQL, you must use dynamic SQL, preferably the EXECUTE IMMEDIATE statement. |
The Oracle Database 12c System Control Statement Dynamically manages the properties of an Oracle Database instance
| SATEMENT | PAGE | NEW | COMMIT | PL/SQL SUPPORT |
|---|---|---|---|---|
| ALTER SYSTEM … | 22 | ALTER SYSTEM | DOES NOT implicitly commit the current transaction. | PL/SQL DOES NOT Support ALTER SYSTEM statement. This statement does not clear shared SQL and PL/SQL areas for items that are currently being executed. You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed. |
Reference
Oracle® Database SQL Language Reference 12c Release 1 (12.1) E41329-09
Climbing Olympus 