Re: if I call a plsql is there a commit?

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_at_x.washington.edu
(replace x with u to respond)