0

Do I need to issue commit command after running the stored function with select query?

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Tracey
  • 3
  • 4

3 Answers3

1

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!

Community
  • 1
  • 1
MinhD
  • 1,790
  • 11
  • 14
  • 1
    [Actually, Oracle considers SELECT to be DML](http://docs.oracle.com/cd/E11882_01/server.112/e40540/sqllangu.htm#CNCPT516), though [Wikipedia states that in SQL 92 SELECT (on it's own) is _not_ DML](http://en.wikipedia.org/wiki/Data_Manipulation_Language), as this is in a stored procedure even Wikipedia counts it as DML. – Ben Apr 25 '14 at 17:17
0

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.

Guneli
  • 1,691
  • 1
  • 11
  • 17
  • 1
    [Actually, Oracle considers SELECT to be DML](http://docs.oracle.com/cd/E11882_01/server.112/e40540/sqllangu.htm#CNCPT516), though [Wikipedia states that in SQL 92 SELECT (on it's own) is _not_ DML](http://en.wikipedia.org/wiki/Data_Manipulation_Language), as this is in a stored procedure even Wikipedia counts it as DML. – Ben Apr 25 '14 at 17:17
0

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

Eng. Samer T
  • 6,465
  • 6
  • 36
  • 43
  • 1
    [Actually, Oracle considers SELECT to be DML](http://docs.oracle.com/cd/E11882_01/server.112/e40540/sqllangu.htm#CNCPT516), though [Wikipedia states that in SQL 92 SELECT (on it's own) is _not_ DML](http://en.wikipedia.org/wiki/Data_Manipulation_Language), as this is in a stored procedure even Wikipedia counts it as DML... – Ben Apr 25 '14 at 17:16
  • @Ben: ok, we know what DML is. my answer is not contrast with you comment. – Eng. Samer T Apr 25 '14 at 18:50