Reputation: 1029
I know for a fact that select is dml command because of
select * into new_table from old_table
in SQL server how can i write equivalent query in oracle for showing select is a dml operation.
Upvotes: 2
Views: 2117
Reputation: 36807
SELECT
is a Data Manipulation Language (DML) statement according to the Oracle SQL Language Reference:
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.
It's common to colloquially treat SELECT
as separate from DML because in practice they generally do different things; SELECT
reads data and DML writes data. But there are some important cases where a SELECT
statement can act like DML:
SELECT some_function() FROM DUAL;
This SELECT
calls a function that could potentially change data. This is usually a bad idea, but it's possible if the function is created with PRAGMA AUTONOMOUS TRANSACTION;
.SELECT * FROM TABLE FOR UPDATE;
This SELECT
locks rows in the table and starts a transaction. It doesn't change any data but it can certainly affect other processes that
are trying to modify data.Also, per the Oracle manual there are six types of statements:
Data Definition Language (DDL) Statements
ADMINISTER KEY MANAGEMENT, ALTER (except ALTER SESSION and ALTER SYSTEM),
ANALYZE,ASSOCIATE STATISTICS,AUDIT,COMMENT,CREATE,DISASSOCIATE STATISTICS,
DROP,FLASHBACK,GRANT,NOAUDIT,PURGE,RENAME,REVOKE,TRUNCATE
Data Manipulation Language (DML) Statements
CALL,DELETE,EXPLAIN PLAN,INSERT,LOCK TABLE,MERGE,SELECT,UPDATE
Transaction Control Statements
COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION,SET CONSTRAINT
Session Control Statements
ALTER SESSION,SET ROLE
System Control Statement
ALTER SYSTEM
Embedded SQL Statements
SQL statements inside a procedural language program.
If you want to fully classify a SQL statement programmatically, like I've done in this program, then "Embedded SQL Statements" doesn't make much sense and almost nobody uses them anymore. And in practice it would be useful to have an additional category for
PL/SQL
Statements run through anonymous blocks.
Although SQL and PL/SQL are not the same thing, in practice they can both be treated as types of statements since they can both be executed in the same contexts. Especially with 12c, where PL/SQL and SQL can be embedded in each other.
Upvotes: 2
Reputation: 3956
Database operations can be categorized into three:
Also theoretically DDL, DML and DCL are considered to be subset of SQL.
If the old table and new table already exists and structures are same, then you can use insert into new_table select * from old_table
.
If the table does not exists, you can use CTAS - create table new_table as select * from old_table
Upvotes: 0