Home » SQL & PL/SQL » SQL & PL/SQL » commit in side stored procedure (oracle 10g)
commit in side stored procedure [message #314293] Wed, 16 April 2008 05:32 Go to next message
just.rahul
Messages: 9
Registered: November 2007
Junior Member
can i commit implicitly inside a procedude,function or package?
Re: commit in side stored procedure [message #314297 is a reply to message #314293] Wed, 16 April 2008 05:35 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Basically yes. Unless in circumstances where you can't.
Re: commit in side stored procedure [message #314300 is a reply to message #314297] Wed, 16 April 2008 05:38 Go to previous messageGo to next message
just.rahul
Messages: 9
Registered: November 2007
Junior Member
thanks thomasg
can you please tell me some of those circumstances
Re: commit in side stored procedure [message #314302 is a reply to message #314300] Wed, 16 April 2008 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Basically, the answer is NO unless you have very good reasons to do so.
If you don't know, then you don't.
When you will be in this situation, you will know you have to do it.

Regards
Michel
Re: commit in side stored procedure [message #314303 is a reply to message #314300] Wed, 16 April 2008 05:44 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
For example, you can not use such functions in selects statements then :

SQL> SET serverout ON;
SQL>
SQL> /* Test Table */
SQL> CREATE TABLE test_table (test_column VARCHAR2(10));

Table created.

SQL>
SQL> /* Test Function */
SQL> CREATE OR REPLACE FUNCTION test_func RETURN VARCHAR2 IS
  2
  3  BEGIN
  4    update test_table SET test_column = test_column;
  5    COMMIT;
  6    RETURN 'ACH, CRIVENS!!';
  7  END;
  8  /

Function created.

SQL>
SQL> /* This works */
SQL> BEGIN
  2     Dbms_Output.put_line(test_func);
  3  END;
  4  /
ACH, CRIVENS!!

PL/SQL procedure successfully completed.

SQL>
SQL> /* This doesn't work */
SQL> SELECT test_func FROM dual;
SELECT test_func FROM dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "PFK.TEST_FUNC", line 4


SQL>
SQL> DROP TABLE test_table;

Table dropped.


Re: commit in side stored procedure [message #314304 is a reply to message #314297] Wed, 16 April 2008 05:46 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I will give you one reason when you have to commit inside the stored proc. It's something like this.
Proc abc
is
begin
  insert --+ append
  ...
  commit;
  update ..
end;

If you don't issue a commit then the update will fail. Why check the oracle sql reference manual.

Regards

Raj
Re: commit in side stored procedure [message #314308 is a reply to message #314304] Wed, 16 April 2008 05:51 Go to previous messageGo to next message
just.rahul
Messages: 9
Registered: November 2007
Junior Member
thanks
Re: commit in side stored procedure [message #314313 is a reply to message #314304] Wed, 16 April 2008 05:53 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
It also depends on the kind of application.

In an application with user interaction the decision over commit/rollback should be on the client/user side.

When it's in some sort of batch processing where the ENTIRE logic of the program except for perhaps the command line to start it off is in PL/SQL stored procedures, then it makes more sense to put the commits/rollbacks and the error handling in the stored procedures itself.
Re: commit in side stored procedure [message #314314 is a reply to message #314313] Wed, 16 April 2008 05:56 Go to previous messageGo to next message
just.rahul
Messages: 9
Registered: November 2007
Junior Member
i want to ask one more question here what is the use of
PRAGMA AUTONOMOUS_TRANSACTION
Re: commit in side stored procedure [message #314315 is a reply to message #314314] Wed, 16 April 2008 05:59 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
search the oracle reference manual or google it. You will find loads of article on that.

Regards

Raj
Re: commit in side stored procedure [message #314336 is a reply to message #314303] Wed, 16 April 2008 06:48 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
RETURN 'ACH, CRIVENS!!';

You a Scotsman Thomas?
Re: commit in side stored procedure [message #314340 is a reply to message #314293] Wed, 16 April 2008 07:00 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
just.rahul wrote on Wed, 16 April 2008 05:32
can i commit implicitly inside a procedude,function or package?


It is the use of the word "implicitly" that I don't quite get...how do you commit implicitly? If you mean that something else explicitly does the commit for you under certain circumstances, such as exiting a client app normally, and you think of that as being implicit, then ok I guess I understand what you mean...
Re: commit in side stored procedure [message #314352 is a reply to message #314336] Wed, 16 April 2008 07:43 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
You a Scotsman Thomas?


No, just read Pratchett. Wink

I'm visiting Scotland in October, though.
Re: commit in side stored procedure [message #314427 is a reply to message #314352] Wed, 16 April 2008 11:53 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Ahhhh the Nac MacFeegle. Nice reference Smile Where do you plan on visiting? Are you doing a tour?
Re: commit in side stored procedure [message #314433 is a reply to message #314427] Wed, 16 April 2008 12:08 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yep. Very Happy
Previous Topic: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Next Topic: Displaying Count as 0 for no records
Goto Forum:
  


Current Time: Sun Dec 04 10:46:44 CST 2016

Total time taken to generate the page: 0.09520 seconds