Do I need to issue commit command after running the stored function with select query?
-
If you are selecting from @dblink then you'd better rollback after that.Egor Skriptunoff– Egor Skriptunoff2014-04-25 16:04:22 +00:00Commented Apr 25, 2014 at 16:04
Add a comment
|
3 Answers
Yes, you do, in some cases (please read the discussion in the link below). The rule is: Always commit if you made change in DB (after DML commands), even with SELECT statement.
Use the COMMIT statement to end your current transaction and make permanent all changes performed in the transaction.
Read more: oracle - what statements need to be committed?
Thanks @Ben for the head up!
1 Comment
Ben
Actually, Oracle considers SELECT to be DML, though Wikipedia states that in SQL 92 SELECT (on it's own) is not DML, as this is in a stored procedure even Wikipedia counts it as DML.
commit means "save the changes"
select statement does not change any data.
changing data can be done by Insert, update, delete statements (Data Manipulation language) .
2 Comments
Ben
Actually, Oracle considers SELECT to be DML, though Wikipedia states that in SQL 92 SELECT (on it's own) is not DML, as this is in a stored procedure even Wikipedia counts it as DML...
Eng. Samer T
@Ben: ok, we know what DML is. my answer is not contrast with you comment.
You need to commit an sql statement only if you have performed a DML statement (INSERT, DELETE, UPDATE, MERGE) in your stored procedure. So, if you only queried the data, there is no need to commit.
1 Comment
Ben
Actually, Oracle considers SELECT to be DML, though Wikipedia states that in SQL 92 SELECT (on it's own) is not DML, as this is in a stored procedure even Wikipedia counts it as DML.