Create or replace Function test_function(p_Parameter_ID number)
Return number is
l_Variable number;
Begin
 Select output into l_Variable from test_table where  id=p_Parameter_ID;
 Exception when others then
  Insert into exception_Table values('No Data Found',    p_Parameter_ID);		  
  return l_Variable;	   	     
End test_function;	

14551, 00000, "cannot perform a DML operation inside a query "
// *Cause:  DML operation like insert, update, delete or select-for-update
//          cannot be performed inside a query or under a PDML slave.
// *Action: Ensure that the offending DML operation is not performed or
//          use an autonomous transaction to perform the DML operation within
//          the query or PDML slave.

SQL> create table tab1 (a number);

Table created.

SQL> 
SQL> create or replace function fun1 return number is
  2  begin
  3     insert into tab1 values(1);
  4     return (1);
  5  end;
  6  /

Function created.

SQL> 
SQL> select * from tab1;

no rows selected

SQL> 
SQL> declare
  2     v1 number;
  3  begin
  4     v1 := fun1;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from tab1;

         A
----------
         1

SQL> select fun1 from dual;
select fun1 from dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "MICHEL.FUN1", line 3

SQL> create table tab1 (a number);

Table created.

SQL> 
SQL> select * from tab1;

no rows selected

SQL> 
SQL> create or replace function fun1 return number is
  2     pragma autonomous_transaction;
  3  begin
  4     insert into tab1 values(1);
  5     commit;
  6     return (1);
  7  end;
  8  /

Function created.

SQL> 
SQL> select fun1 from dual;

      FUN1
----------
         1

1 row selected.

SQL> 
SQL> select * from tab1;

         A
----------
         1

1 row selected.

SQL> 

CREATE PROCEDURE p IS
   x INTEGER;
BEGIN
   NULL;
EXCEPTION
   WHEN OTHER THEN
      "log_package"."error"
      (msg=>'error happened'
      ,details=>'dynamic content '||x
      );
      RAISE; -- always re-raise exceptions
      -- but since you log exception you can catch it and forget about re-raising
END;

CREATE PACKAGE "log_package" IS
BEGIN
   PROCEDURE "error"(msg VARCHAR2, details CLOB);
END "log_package";

CREATE PACKAGE BODY "log_package" IS
BEGIN
   PROCEDURE INSERT_LOG IS
      PRAGMA AUTONOMOUS TRANSACTIONS
   BEGIN
      INSERT INTO my_table VALUES (...); -- would be the best to use a cursor for this... do not specify time - instead skip such column here and create it in the table with default set to sysdate or current_timestamp or ...
      -- you might also log traceback...
   END INSERT_LOG;
   PROCEDURE "error"(msg VARCHAR2, details CLOB) IS
   BEGIN
      INSERT_LOG(msg,details,sqlcode,sqlerrm,'error');
   EXCEPTION
      WHEN OTHERS THEN
         RAISE;
         -- either re-raise or handle it... but that procedure should never fail or you are in trouble
   END "error";
END "log_package";

PROCEDURE p ... IS BEGIN
EXCEPTION WHEN OTHERS THEN
my_log_procedure;RAISE;
END p;

SQL> conn scott/tiger
Connected.
SQL> set echo on
SQL> @faq
SQL> create or replace package faq_tst
  2  as
  3    procedure entry_point;
  4  end faq_tst;
  5  /

Package created.

SQL> create or replace package body faq_tst
  2  as
  3    procedure inner_proc_2
  4    is
  5    begin
  6      raise no_data_found;
  7    exception
  8      when others
  9      then
 10        raise;
 11    end;
 12
 13    procedure inner_proc_1
 14    is
 15    begin
 16      inner_proc_2;
 17    exception
 18      when others
 19      then
 20        raise;
 21    end;
 22
 23    procedure entry_point
 24    is
 25    begin
 26      inner_proc_1;
 27    end;
 28  end faq_tst;
 29  /

Package body created.

SQL> declare
  2    l_text varchar2(32000);
  3  begin
  4    faq_tst.entry_point;
  5  exception
  6    when others
  7    then
  8      dbms_output.put_line(substr(sqlerrm, 1, 256));
  9      dbms_output.put_line('Error stack:');
 10      l_text := dbms_utility.format_error_stack;
 11      while length(l_text) > 0
 12      loop
 13        dbms_output.put_line(substr(l_text, 1, 256));
 14        l_text := substr(l_text, 256);
 15      end loop;
 16      dbms_output.put_line('Call stack:');
 17      l_text := dbms_utility.format_call_stack;
 18      while length(l_text) > 0
 19      loop
 20        dbms_output.put_line(substr(l_text, 1, 256));
 21        l_text := substr(l_text, 256);
 22      end loop;
 23  end;
 24  /
ORA-01403: no data found
Error stack:
ORA-01403: no data found

Call stack:
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
6860DCA0        17  anonymous block


PL/SQL procedure successfully completed.

try
{
   nothing;
}
catch (Exception e)
{
   if(!can_handle(e))
   {
      throw(e);
   }
}

  WHEN OTHERS
  THEN
    RAISE;

 CREATE OR REPLACE PROCEDURE proc1 IS
    BEGIN
       DBMS_OUTPUT.put_line ('running proc1');
       RAISE NO_DATA_FOUND;
    END;
    /

    CREATE OR REPLACE PROCEDURE proc2 IS
       l_str VARCHAR2 (30) := 'calling proc1';
    BEGIN
       DBMS_OUTPUT.put_line (l_str);
       proc1;
    END;
    /
    CREATE OR REPLACE PROCEDURE proc3 IS
    BEGIN
       DBMS_OUTPUT.put_line ('calling proc2');
       proc2;
    EXCEPTION
       WHEN OTHERS
       THEN
          DBMS_OUTPUT.put_line ('Error stack at top level:');
          DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
    END;
    /

    SQL> SET SERVEROUTPUT ON
    SQL> BEGIN
      2     DBMS_OUTPUT.put_line ('Proc3 -> Proc2 -> Proc1 backtrace');
      3     proc3;
      4  END;
      5  /
    Proc3 -> Proc2 -> Proc1 backtrace
    calling proc2
    calling proc1
    running proc1
    Error stack at top level:
    ORA-06512: at "SCOTT.PROC1", line 4
    ORA-06512: at "SCOTT.PROC2", line 5
    ORA-06512: at "SCOTT.PROC3", line 4

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> set serverout on
SQL>  declare
  2     l_text varchar2(32000);
  3   begin
  4     faq_tst.entry_point;
  5   exception
  6     when others
  7     then
  8       dbms_output.put_line(substr(sqlerrm, 1, 256));
  9       DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
 10       dbms_output.put_line('Error stack:');
 11       l_text := dbms_utility.format_error_stack;
 12       while length(l_text) > 0
 13       loop
 14         dbms_output.put_line(substr(l_text, 1, 256));
 15         l_text := substr(l_text, 256);
 16       end loop;
 17       dbms_output.put_line('Call stack:');
 18       l_text := dbms_utility.format_call_stack;
 19       while length(l_text) > 0
 20       loop
 21         dbms_output.put_line(substr(l_text, 1, 256));
 22         l_text := substr(l_text, 256);
 23       end loop;
 24   end;
 25   /
ORA-01403: no data found
ORA-06512: at "AYUSH.FAQ_TST", line 20
ORA-06512: at "AYUSH.FAQ_TST", line 26
ORA-06512: at line 4

Error stack:
ORA-01403: no data found

Call stack:
----- PL/SQL Call Stack -----
  object      line  object
  handle    number
name
254C7BB4        18  anonymous block


PL/SQL procedure successfully completed.

ORA-01403: no data found
ORA-06512: at "AYUSH.FAQ_TST", line 20
ORA-06512: at "AYUSH.FAQ_TST", line 26
ORA-06512: at line 4

SQL> set echo on
SQL> @faq
SQL> create or replace package faq_tst
  2  as
  3    procedure entry_point;
  4  end faq_tst;
  5  /

Package created.

SQL> create or replace package body faq_tst
  2  as
  3    procedure inner_proc_2
  4    is
  5    begin
  6      raise no_data_found;
  7    exception
  8      when others
  9      then
 10        raise;
 11    end;
 12
 13    procedure inner_proc_1
 14    is
 15    begin
 16      inner_proc_2;
 17    exception
 18      when others
 19      then
 20        raise;
 21    end;
 22
 23    procedure entry_point
 24    is
 25    begin
 26      inner_proc_1;
 27    end;
 28  end faq_tst;
 29  /

Package body created.
SQL> set serverout on
SQL>  declare
  2     l_text varchar2(32000);
  3   begin
  4     faq_tst.entry_point;
  5   exception
  6     when others
  7     then
  8       dbms_output.put_line(substr(sqlerrm, 1, 256));
  9       DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
 10       dbms_output.put_line('Error stack:');
 11       l_text := dbms_utility.format_error_stack;
 12       while length(l_text) > 0
 13       loop
 14         dbms_output.put_line(substr(l_text, 1, 256));
 15         l_text := substr(l_text, 256);
 16       end loop;
 17       dbms_output.put_line('Call stack:');
 18       l_text := dbms_utility.format_call_stack;
 19       while length(l_text) > 0
 20       loop
 21         dbms_output.put_line(substr(l_text, 1, 256));
 22         l_text := substr(l_text, 256);
 23       end loop;
 24   end;
 25   /
ORA-01403: no data found
ORA-06512: at "AYUSH.FAQ_TST", line 20
ORA-06512: at "AYUSH.FAQ_TST", line 26
ORA-06512: at line 4

Error stack:
ORA-01403: no data found

Call stack:
----- PL/SQL Call Stack -----
  object      line  object
  handle    number
name
254C7BB4        18  anonymous block


PL/SQL procedure successfully completed.

SQL> CREATE OR REPLACE PROCEDURE proc1 IS
  2  BEGIN
  3     DBMS_OUTPUT.put_line ('running proc1');
  4     RAISE NO_DATA_FOUND;
  5  EXCEPTION
  6     WHEN OTHERS THEN
  7        DBMS_OUTPUT.put_line (
  8           'Error stack in block where raised:');
  9        DBMS_OUTPUT.put_line  (
 10           DBMS_UTILITY.format_error_backtrace);
 11        RAISE;
 12  END;
 13  /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE proc2
  2  IS
  3     l_str VARCHAR2 (30) := 'calling proc1';
  4  BEGIN
  5     DBMS_OUTPUT.put_line (l_str);
  6     proc1;
  7  END;
  8  /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE proc3 IS
  2  BEGIN
  3     DBMS_OUTPUT.put_line ('calling proc2');
  4     proc2;
  5  EXCEPTION
  6     WHEN OTHERS
  7     THEN
  8        DBMS_OUTPUT.put_line ('Error stack at top level:');
  9        DBMS_OUTPUT.put_line  (DBMS_UTILITY.format_error_backtrace);
 10        DBMS_OUTPUT.put_line  (DBMS_UTILITY.format_error_backtrace);
 11  END;
 12  /

Procedure created.

SQL>  exec proc3
calling proc2
calling proc1
running proc1
Error stack in block where raised:
ORA-06512: at "AYUSH.PROC1", line 4

Error stack at top level:
ORA-06512: at "AYUSH.PROC1", line 11
ORA-06512: at "AYUSH.PROC2", line
6
ORA-06512: at "AYUSH.PROC3", line 4

ORA-06512: at "AYUSH.PROC1", line 11
ORA-06512: at "AYUSH.PROC2", line
6
ORA-06512: at "AYUSH.PROC3", line 4


PL/SQL procedure successfully completed.

create global temporary table tmp_table_dummy (val number);

create or replace function dummy_fun return number is
  pragma autonomous_transaction;
  s number;
begin
  -- A complex query, requiring a long time to execute
  insert into tmp_table_dummy
  select 4 from dual
  union all
  select 3 from dual;
  
  -- Further processing, where the saved data is read more than once
  select sum(val) into s from tmp_table_dummy;
  commit;
  return s;
end dummy_fun;

select * from (select dummy_fun from dual);