Home » SQL & PL/SQL » SQL & PL/SQL » Is select a DML statement?
Is select a DML statement? [message #292501] Wed, 09 January 2008 01:30 Go to next message
raji.s
Messages: 52
Registered: February 2005
Member
Hi ,

We all know that select is only used to read the data but still some books categorise it as a part of DML statement.

Below is a simple test .. i feel it is not part of DML statement.

I am creating a simple function which returns a number .. and this function is created using PRAGMA AUTONOMOUS_TRANSACTION.
create or replace function f1 return number
is
  pragma autonomous_transaction;
  v_a number;
begin
  select count(*) into v_a from a;
  return sql%rowcount;
end;
/

Function created.

Since the above fuunction is created using the pragma autonomous transaction, it should have a commit or rollback if has a DML statement(s) in it .. and the above function doesn't have a TCL statement in it.
SQL> SET SERVEROUT ON

  declare
    v_t number;
  begin
    v_t :=f1;
  end;
  /

PL/SQL procedure successfully completed.

The above Anonymous PL/SQL blk compiled and executed successfully .. it didn't raised the error
" ORA-06519: active autonomous transaction detected and rolled back ".

If instead of a select statement, there had been a dml like an update, insert or delete, this pl/sql blk would have failed with ORA-06519 error .

So i guess SELECT should not be categorised as part of DML statement.

[Edit MC: change title and add code tags]

[Updated on: Wed, 09 January 2008 01:33] by Moderator

Report message to a moderator

Re: Is select a DML statement? [message #292506 is a reply to message #292501] Wed, 09 January 2008 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sometimes it is inside DML and sometimes not.
But it is often simpler or shorter to write "DML statements" for "DML and SELECT statements" or, if you assume the opposite, "DML statements" for "DML statements but SELECT".

Wikipedia (http://en.wikipedia.org/wiki/Data_Manipulation_Language) and Orafaq (http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands) assumes SELECT is in DML (but it also includes LOCK TABLE).

For Oracle, SELECT is not inside DML: http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/glossary.htm#sthref4150 but the definition missed MERGE.
And in the section DML Locks Automatically Acquired for DML Statements it includes SELECT and LOCK TABLE.
SQL Reference in Data Manipulation Language (DML) Statements includes SELECT and also EXPLAIN PLAN and CALL.

So no conclusion, I don't have ISO/ANSI standard by hand so can't verify if there is a definition of it inside.

Regards
Michel

[Updated on: Wed, 09 January 2008 01:51]

Report message to a moderator

Re: Is select a DML statement? [message #292508 is a reply to message #292501] Wed, 09 January 2008 01:51 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Good Experimentation indeed .

DDL : Data Definition Language
DML : Data Manipulation Language
DCL : Data Control Language
TCL : Transaction Control Language

By this time , you might have come to know that SELECT is not TCL , Its is neither DDL or DCL by fuctionality . But even it is very close to DML (manipulation) by Functinality . That is why it is categorized as DML by some authors .

Otherwise you could have craeted one more category exclusively for SELECT statement . As it is very close to Other DML Statements by general functinality and by definition , it is categorized as DML . It is convenient to categorize like that Smile

Thumbs Up
Rajuvan.
Re: Is select a DML statement? [message #292516 is a reply to message #292506] Wed, 09 January 2008 02:00 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
Thanks Michel.

Regards,

Rajiv.
Re: Is select a DML statement? [message #292540 is a reply to message #292506] Wed, 09 January 2008 03:12 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Wed, 09 January 2008 08:48

For Oracle, SELECT is not inside DML: http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/glossary.htm#sthref4150 but the definition missed MERGE.
I think you misread the definition there, Michel. It clearly states "Includes statements like INSERT, UPDATE, and DELETE, which change data in tables." but it does NOT state that the list of commands is exhaustive. That is an assumption made by the reader, I'm afraid.

MHE
Re: Is select a DML statement? [message #292551 is a reply to message #292540] Wed, 09 January 2008 03:31 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

That is an assumption made by the reader, I'm afraid.


And you are right. Embarassed

Regards
Michel
Previous Topic: Send data SMS from mobile to Oracle
Next Topic: Merge Problem
Goto Forum:
  


Current Time: Sun Dec 11 08:18:26 CST 2016

Total time taken to generate the page: 0.09137 seconds