Narasimha Maiya
Narasimha Maiya

Reputation: 1029

select as DML Command

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

Answers (2)

Jon Heller
Jon Heller

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:

  1. 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
    
  2. Data Manipulation Language (DML) Statements

    CALL,DELETE,EXPLAIN PLAN,INSERT,LOCK TABLE,MERGE,SELECT,UPDATE
    
  3. Transaction Control Statements

    COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION,SET CONSTRAINT
    
  4. Session Control Statements

    ALTER SESSION,SET ROLE
    
  5. System Control Statement

    ALTER SYSTEM
    
  6. 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

  1. 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

Durga Viswanath Gadiraju
Durga Viswanath Gadiraju

Reputation: 3956

Database operations can be categorized into three:

  1. DDL (Data Definition Language). It is typically used to create tables, indexes, views etc.
  2. DML (Data Manipulation language). As name suggests the commands are typically used to insert, update or delete the data from existing tables.
  3. SQL (Structured Query Language). It is primarily used to query the tables
  4. Also there is 4th one called DCL (Data Control Language). It is primarily to grant and revoke permissions.

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

Related Questions