|
|
|
|
Re: commit in side stored procedure [message #314303 is a reply to message #314300] |
Wed, 16 April 2008 05:44   |
ThomasG
Messages: 3212 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   |
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 #314313 is a reply to message #314304] |
Wed, 16 April 2008 05:53   |
ThomasG
Messages: 3212 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 #314340 is a reply to message #314293] |
Wed, 16 April 2008 07:00   |
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...
|
|
|
|
|
|