| Is select a DML statement? [message #292501] |
Wed, 09 January 2008 01:30  |
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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
