If I execute a simple select statement in pl/sql developer against a database table, I get a standard set of results back as I would expect.
Recently, I pasted a query from a stored procedure that happened to select from a view, and noticed that a transaction was seemingly left open. This was appraent by the rollback and commit options were available in PL/SQL developer.
A poll of other developers revealed that this seems to affect some but not others, which lead me to suspect PL/SQL Developer settings.
Why on earth would this be the case? The view itelf has a DBLink to another database, but I wouldn't expect this to have any effect.
Any thoughts?
Any SQL Statement starts a transaction in Oracle.
A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL statement is issued. [...] An executable SQL statement is a SQL statement that generates calls to an instance, including DML and DDL statements
Most probably those who are not seing this are running in auto-commit mode where the transaction started by a statement is immediately committed after the statement has finished.
Others have claimed that a SELECT is not DML, but again the manual clearly states:
Data manipulation language (DML) statements query or manipulate data in existing schema objects. They enable you to:
* Retrieve or fetch data from one or more tables or views (SELECT)
* Add new rows of data into a table or view (INSERT)
[...]