Hi,
I had following piece of code.
CREATE OR REPLACE procedure xbl_pro_jay is
begin
EXECUTE IMMEDIATE 'TRUNCATE TABLE xbl_jay';
end xbl_pro_jay;
CREATE OR REPLACE FUNCTION xbl_fun_jay
RETURN number
IS
BEGIN
xbl_pro_jay;
RETURN 1;
exception
when others
then return 10;
END;
On executing " SELECT xbl_fun_jay FROM dual; "
I got the result as 10 (i.e the procedure failed)
Again I changed the code as below and it resulted 1 (the procedure completed successfully).
CREATE OR REPLACE procedure xbl_pro_jay
PRAGMA AUTONOMOUS_TRANSACTION;
is
begin
EXECUTE IMMEDIATE 'TRUNCATE TABLE xbl_jay';
end xbl_pro_jay;
CREATE OR REPLACE FUNCTION xbl_fun_jay
RETURN number
IS
BEGIN
xbl_pro_jay;
RETURN 1;
exception
when others
then return 10;
END;
Can anyone pls tell why the AUTONOMOUS_TRANSACTION let the procedure work ...?
Thanks.