Re: if I call a plsql is there a commit?
- From: DA Morgan <damorgan@xxxxxxxxx>
- Date: Tue, 07 Mar 2006 08:32:42 -0800
Delio Nasso wrote:
Hi,
excuse me for the simplicity of the question but I'm a beginner..
I have seen somethings similar in this forum.
If i have a procedure plsql that calls an other one like this:
CREATE OR REPLACE PROCEDURE p1
as
begin p2();
p3();
...
end;
After p2 is there a commit or it is after end?
Thanks in advance!
Sorry to disagree Ianal but the answer is no. Nothing is committed
without the explicit use of COMMIT;
Here's the proof:
-- Session 1
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 7 08:27:48 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> CREATE TABLE test (
2 testcol VARCHAR2(5));
Table created.
SQL>
SQL> CREATE PROCEDURE p2 IS
2 BEGIN
3 INSERT INTO test VALUES ('BBB');
4 END p2;
5 /
Procedure created.
SQL> CREATE PROCEDURE p3 IS
2 BEGIN
3 INSERT INTO test VALUES ('DDD');
4 END p3;
5 /
Procedure created.
SQL> BEGIN
2 insert into test VALUES ('AAA');
3 p2;
4 insert into test VALUES ('CCC');
5 p3;
6 insert into test VALUES ('EEE');
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
-- Session 2 in a different SQL*Plus session
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 7 08:28:14 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select count(*) from test;
COUNT(*)
----------
0
SQL>
-- close the first SQL*Plus session
SQL> /
COUNT(*)
----------
5
SQL>
The commit that took place was an autocommit from SQL*Plus when
the first session ended. Had this been triggered by another tool
or some other means no commit would ever take place.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@xxxxxxxxxxxxxxxx
(replace x with u to respond)
.
- References:
- if I call a plsql is there a commit?
- From: Delio Nasso
- if I call a plsql is there a commit?
- Prev by Date: Re: SQL Basics
- Next by Date: Unit Testing Software for PL/SQL
- Previous by thread: Re: if I call a plsql is there a commit?
- Next by thread: SQL Basics
- Index(es):
Relevant Pages
|
