Home » SQL & PL/SQL » SQL & PL/SQL » Commit/rollback in functions inside package body
 Commit/rollback in functions inside package body [message #11464] |
Sun, 28 March 2004 12:17  |
Fil
Messages: 7
Registered: July 2003
|
Junior Member |
|
|
Hi, this is really a newbie question I guess...
I have a function inside a package body that has to insert data in several tables.
I'm really a newbie, so maybe there's more than one error in my code.
As far I understood, procedures and functions inside packages don't need commit (at least this is what happens to me in my environment): it seems there's a commit either I explicit call it or not.
But now I'd like to have a way to manage the rollback/commit feature...explicitly...
I'm posting from home (the real code is at office) but if I remember the code -more or less- is:
========================
function fx_insert_in_tbls (
parameters IN...
) RETURN NUMBER
AS
nextTABLE1pk_ NUMBER(10,0);
nextTABLE2pk_ NUMBER(10,0);
nextTABLE3pk_ NUMBER(10,0);
BEGIN
--table1
select SQtable1.NEXTVAL INTO fx_insert_in_tbls.nextTABLE1pk_
INSERT into TABLE1
� ( pk,
���� name,
���� descr )
VALUES
� ( fx_insert_in_tbls.nextTABLE1pk_,
��� some-var-in,
��� someother-var-in);
IF SQLCODE != 0 THEN
��� ROLLBACK;
��� RETURN -1;
END IF;
--table2
select SQtable2.NEXTVAL INTO fx_insert_in_tbls.nextTABLE2pk_
INSERT into TABLE2
� ( pk,
���� name,
���� descr )
VALUES
� ( fx_insert_in_tbls.nextTABLE2pk_,
��� some-var-in,
��� someother-var-in);
IF SQLCODE != 0 THEN
��� ROLLBACK;
��� RETURN -2;
END IF;
--table3
select SQtable3.NEXTVAL INTO fx_insert_in_tbls.nextTABLE3pk_
INSERT into TABLE3
� ( pk,
���� name,
���� descr )
VALUES
� ( fx_insert_in_tbls.nextTABLE3pk_,
��� some-var-in,
��� someother-var-in);
IF SQLCODE != 0 THEN
��� ROLLBACK;
��� RETURN -3;
END IF;
��
�� --if I'm here then everything should ahve gone the right way...
�� COMMIT;
�� RETURN fx_insert_in_tbls.nextTABLE1pk_
END fx_insert_in_tbls;
========================
Oracle compiles tha package w/out errors, but...Is this the right solution?
Please, consider I'm that new to Oracle that I don't even know what to ask about this code: any suggestion, tips, fix... about how to get my target in a better way is really appreciated.
Thank you
Filippo
|
|
|
|
| Re: Commit/rollback in functions inside package body [message #11465 is a reply to message #11464] |
Sun, 28 March 2004 13:14  |
 |
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If you exit normally or perform any DDL, then Oracle commits at that point. If there is an abnormal exit or an error, then Oracle does a rollback at that point. Otherwise, Oracle waits for you to tell it whether to commit or rollback. Procedures, functions, and packages are no exception. A commit can be issued inside the procedure or outside the procedure after it is executed. There are various methods of handling various errors, including using exceptions and raise_application_error. There are various exceptions and "when others" can be used to catch anything remaining. Sometimes, however, this can obscure the problem, so it may be preferable to just let it return an erorr message. However, if you are calling it from some application, you won't want to leave anything unhandled, but may comment out the exception when debugging. Please see the brief example below for some common exception handling.
BEGIN
-- your code
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR
(-20001, 'An attempt was made to enter a duplicate value, where only one such value is allowed.');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR
(-20002, 'some message');
END;
/
You can also use savepoints, so that you can rollback to any particular savepoint after the last commit. It is sometimes better to have the whole thing commit or the whole thing fail, rather than try to figure out which pieces committed. You can find information and examples of exceptions, raise_application_error and savepoints in the Oracle on-line documentation. You can click on the link below for a handy menu for searching that documentation. It will require that you register for Oracle Technology Network (OTN) but it is free.
|
|
|
|
|
|
| Re: Commit/rollback in functions inside package body [message #11480 is a reply to message #11473] |
Mon, 29 March 2004 03:47 |
|
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If Oracle encounters something (an error) that raises an exception during any portion of the code, it goes immediately to the exception block, executes what is there and then exits. A rollback is not necessary, because it is done automatically.
You are not using raise_application_error properly. The proper format is:
raise_application_error (-20001, 'some message');
where the first parameter is -20001 or -20002 and so on, within a short range of numbers, where -20001 is one end of the range, and the second parameter is text.
I forgot to mention that, if you are going to commit within a function called from SQL, you will need to use pragma autonomous_transaction.
I believe you can only either use raise_application_error or return a value, not both. But you can certainly include the value in the message of your raise_application_error or output it separately with dbms_output or both.
If you do not include an exception block, Oracle will still give you an error message. In fact, it will tell you the correct line of the code, instead of just the line that the exception block is on.
There are also circumstances where the function won't even execute, so it will never reach the exception block, such as an attempt to pass a parameter of the wrong data type.
Please see the example belows for how all of this works, with and without an exception block.
scott@ORA92> -- without exception block:
scott@ORA92> CREATE OR REPLACE FUNCTION test_function
2 (p_deptno IN dept.deptno%TYPE)
3 RETURN NUMBER
4 AS
5 PRAGMA AUTONOMOUS_TRANSACTION;
6 BEGIN
7 INSERT INTO dept (deptno) VALUES (p_deptno);
8 COMMIT;
9 RETURN 1;
10 END test_function;
11 /
Function created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> SELECT * FROM dept
2 /
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
scott@ORA92> -- insert of legitimate deptno succeeds:
scott@ORA92> SELECT test_function (99) FROM DUAL
2 /
TEST_FUNCTION(99)
-----------------
1
scott@ORA92> -- attempted insert of duplicate deptno fails:
scott@ORA92> SELECT test_function (40) FROM DUAL
2 /
SELECT test_function (40) FROM DUAL
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated
ORA-06512: at "SCOTT.TEST_FUNCTION", line 7
scott@ORA92> -- attempt to pass parameter of wrong datatype fails:
scott@ORA92> SELECT test_function ('abc') FROM DUAL
2 /
SELECT test_function ('abc') FROM DUAL
*
ERROR at line 1:
ORA-01722: invalid number
scott@ORA92> DELETE FROM DEPT WHERE DEPTNO = 99
2 /
1 row deleted.
scott@ORA92> -- with exception block:
scott@ORA92> CREATE OR REPLACE FUNCTION test_function
2 (p_deptno IN dept.deptno%TYPE)
3 RETURN NUMBER
4 AS
5 PRAGMA AUTONOMOUS_TRANSACTION;
6 BEGIN
7 INSERT INTO dept (deptno) VALUES (p_deptno);
8 COMMIT;
9 RETURN 1;
10 EXCEPTION
11 WHEN OTHERS THEN
12 RAISE_APPLICATION_ERROR
13 (-20002,
14 '2 The following error occurred: '
15 || CHR(10) || sqlcode || ' ' || sqlerrm);
16 END test_function;
17 /
Function created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> SELECT * FROM dept
2 /
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
scott@ORA92> -- insert of legitimate deptno succeeds:
scott@ORA92> SELECT test_function (99) FROM DUAL
2 /
TEST_FUNCTION(99)
-----------------
1
scott@ORA92> -- attempted insert of duplicate deptno fails:
scott@ORA92> SELECT test_function (40) FROM DUAL
2 /
SELECT test_function (40) FROM DUAL
*
ERROR at line 1:
ORA-20002: 2 The following error occurred:
-1 ORA-00001: unique constraint (SCOTT.PK_DEPT) violated
ORA-06512: at "SCOTT.TEST_FUNCTION", line 12
scott@ORA92> -- attempt to pass parameter of wrong datatype fails:
scott@ORA92> SELECT test_function ('abc') FROM DUAL
2 /
SELECT test_function ('abc') FROM DUAL
*
ERROR at line 1:
ORA-01722: invalid number
scott@ORA92> DELETE FROM DEPT WHERE DEPTNO = 99
2 /
1 row deleted.
|
|
|
|
|
|
|
|
| Re: Commit/rollback in functions inside package body [message #11526 is a reply to message #11519] |
Tue, 30 March 2004 20:42 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
See RAISE_APPLICATION_ERROR.
Set the optional third parameter to TRUE if you are re-raising an exception and you want to keep the error stack (SQLERRM). The default FALSE is useful when you are using RAISE_APPLICATION_ERROR to raise a new exception and there is no existing error stack.
Since the TRUE setting keeps the existing error stack you don't need to concatenate SQLERRM explicitly.
|
|
|
|
Goto Forum:
Current Time: Mon Apr 28 02:30:37 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
