Home » SQL & PL/SQL » SQL & PL/SQL » need help data getting committed without commit statement in procedure (oracle 10 g,Windows XP)
need help data getting committed without commit statement in procedure [message #379766] Wed, 07 January 2009 21:35 Go to next message
sherifbasha
Messages: 11
Registered: February 2007
Location: Chennai,India
Junior Member

Hi.
I have a main procedure 'MAIN' which calls another procedure 'CALLED' which insert data into some table.
from the main proc i get err out parameter from CALLED.Here the issue the data is still inserted into the table
and committed though i have not given any commit statement in my proc. this is the skeleton of my code
CREATE OR REPLACE
PROCEDURE MAIN AS
a number;
BEGIN
called(2,a);
if(a=1) then
rollback;
else
null;
end if;
END MAIN;

CREATE OR REPLACE
PROCEDURE called(i_d in number,err out number) AS
BEGIN
insert into bas values(i_d,'me');
err:=0;
END called;
the issue is i find data in tables inserted if return I error 0.I do not understand why is it present in the table if it is not committed through commit statement.I checked with differnt sessions also but data is inserted.
Can anybody please help.
Re: need help data getting committed without commit statement in procedure [message #379794 is a reply to message #379766] Thu, 08 January 2009 00:15 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hi

I dont know what you are trying achive, I can still see that your code is working fine.

Executing your procedure I can validate it by two ways

1. 
EXEC  MAIN

SELECT * FROM bas;

 A B
-- ---
 2 me                                                                  
1 row selected.

Rollback

SELECT * FROM bas;
no rows selected



It means the data is not commit in the database

2. 

Session 1

EXEC  MAIN

Session 2

SELECT * FROM bas;

no rows selected


I hope you must be doing some thing else after executing the procedure. Like issuing any DDL

Thanks
Trivendra





Re: need help data getting committed without commit statement in procedure [message #379799 is a reply to message #379766] Thu, 08 January 2009 00:28 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Lets try the scenario with two session.

Session 1:

SQL> desc test1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 COL1                                               NUMBER

SQL> select * from test1;

no rows selected

SQL> CREATE OR REPLACE
  2  PROCEDURE called_prc(i_d in number,err out number) AS
  3  BEGIN
  4  insert into test1 values(i_d);
  5  err:=0;
  6  END called_prc;
  7  /

Procedure created.

SQL>  CREATE OR REPLACE
  2   PROCEDURE main_proc AS
  3   a number;
  4   BEGIN
  5   called_prc(2,a);
  6   if(a=1) then
  7   rollback;
  8   else
  9   null;
 10   end if;
 11   END  main_proc;
 12  /

Procedure created.

SQL>
SQL>  exec main_proc;

PL/SQL procedure successfully completed.

SQL> select * from test1;

      COL1
----------
         2

SQL>



This is what you made to thing that Data is committed without giving commit.

No go to

session 2.

SQL> select * from test1;

no rows selected

SQL>


Surprised . Data will not be committed unless it is committed implicitly or explicitly ( eg: issueing DDL command will implicitly commit the transaction)

Lets go to Session 1 again

SQL> commit;

Commit complete.

SQL> select * from test1;

      COL1
----------
         2

SQL>


And back to session 2

SQL> select * from test1;

      COL1
----------
         2

SQL>


See, data is committed finally.

[ooops -- Didn't notice trivendra's reply ]


Smile
Rajuvan



[Updated on: Thu, 08 January 2009 00:29]

Report message to a moderator

Re: need help data getting committed without commit statement in procedure [message #379805 is a reply to message #379766] Thu, 08 January 2009 00:36 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As it has been said in one of your previous post:
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: comparison of to_char function
Next Topic: Flashback update statement
Goto Forum:
  


Current Time: Fri Dec 02 22:46:15 CST 2016

Total time taken to generate the page: 0.08749 seconds