Hi folks,
The provided solution certainly works in a single-user system but can bring false positives in more complex environments.
How about that helper SQL*Plus script show_last_func.sql:
col create_func_stmt old_v create_func_stmt nopri
col func_name for a30 old_v func_name
#0 select q'!
#del 10 last
#l
#a !' create_func_stmt from dual
/
select regexp_substr('&create_func_stmt.', 'function\s+(\w+\.)?(\w+)', 1, 1, 'i', 2) func_name
from dual;
select created, last_ddl_time
from obj
where object_name = upper('&func_name.');
The listing of a sample run is below:
SQL> @D:\temp\create_func_test.sql
SQL> set ver off echo on timi off sqlp "SQL> " ti off
SQL>
SQL> create or replace function f_wo_owner return int
2 is
3 begin
4 null;
5 end;
6 /
Function created.
SQL>
SQL> @@show_last_func.sql
SQL> col create_func_stmt old_v create_func_stmt nopri
SQL> col func_name for a30 old_v func_name
SQL>
SQL> #0 select q'!
SQL> #del 10 last
SP2-0622: Starting line number must be less than ending line number
SQL> #l
1 select q'!
2 create or replace function f_wo_owner return int
3 is
4 begin
5 null;
6* end;
SQL> #a !' create_func_stmt from dual
6* end;!' create_func_stmt from dual
SQL> /
SQL>
SQL> select regexp_substr('&create_func_stmt.', 'function\s+(\w+\.)?(\w+)', 1, 1, 'i', 2) func_name
2 from dual;
FUNC_NAME
------------------------------
f_wo_owner
SQL>
SQL> select created, last_ddl_time
2 from obj
3 where object_name = upper('&func_name.');
CREATED LAST_DDL_TIME
------------------- -------------------
10.03.2019 16:24:47 10.03.2019 16:24:47
SQL>
SQL> create or replace function &_user..f_with_owner return int
2 is
3 begin
4 null;
5 end;
6 /
Function created.
SQL>
SQL> @@show_last_func.sql
SQL> col create_func_stmt old_v create_func_stmt nopri
SQL> col func_name for a30 old_v func_name
SQL>
SQL> #0 select q'!
SQL> #del 10 last
SP2-0622: Starting line number must be less than ending line number
SQL> #l
1 select q'!
2 create or replace function &_user..f_with_owner return int
3 is
4 begin
5 null;
6* end;
SQL> #a !' create_func_stmt from dual
6* end;!' create_func_stmt from dual
SQL> /
SQL>
SQL> select regexp_substr('&create_func_stmt.', 'function\s+(\w+\.)?(\w+)', 1, 1, 'i', 2) func_name
2 from dual;
FUNC_NAME
------------------------------
f_with_owner
SQL>
SQL> select created, last_ddl_time
2 from obj
3 where object_name = upper('&func_name.');
CREATED LAST_DDL_TIME
------------------- -------------------
10.03.2019 16:37:21 10.03.2019 16:37:21
SQL>
SQL> create
2 or
3 replace
4 function
5 f_multiline
6 return int
7 is
8 begin
9 null;
10 end;
11 /
Function created.
SQL>
SQL> @@show_last_func.sql
SQL> col create_func_stmt old_v create_func_stmt nopri
SQL> col func_name for a30 old_v func_name
SQL>
SQL> #0 select q'!
SQL> #del 10 last
SQL> #l
1 select q'!
2 create
3 or
4 replace
5 function
6 f_multiline
7 return int
8 is
9* begin
SQL> #a !' create_func_stmt from dual
9* begin!' create_func_stmt from dual
SQL> /
SQL>
SQL> select regexp_substr('&create_func_stmt.', 'function\s+(\w+\.)?(\w+)', 1, 1, 'i', 2) func_name
2 from dual;
FUNC_NAME
------------------------------
f_multiline
SQL>
SQL> select created, last_ddl_time
2 from obj
3 where object_name = upper('&func_name.');
CREATED LAST_DDL_TIME
------------------- -------------------
10.03.2019 16:23:31 10.03.2019 17:05:25
SQL>
SQL> create or replace
2 FuNcTiOn f_mixed_case return int
3 is
4 begin
5 null;
6 end;
7 /
Function created.
SQL>
SQL> @@show_last_func.sql
SQL> col create_func_stmt old_v create_func_stmt nopri
SQL> col func_name for a30 old_v func_name
SQL>
SQL> #0 select q'!
SQL> #del 10 last
SP2-0622: Starting line number must be less than ending line number
SQL> #l
1 select q'!
2 create or replace
3 FuNcTiOn f_mixed_case return int
4 is
5 begin
6 null;
7* end;
SQL> #a !' create_func_stmt from dual
7* end;!' create_func_stmt from dual
SQL> /
SQL>
SQL> select regexp_substr('&create_func_stmt.', 'function\s+(\w+\.)?(\w+)', 1, 1, 'i', 2) func_name
2 from dual;
FUNC_NAME
------------------------------
f_mixed_case
SQL>
SQL> select created, last_ddl_time
2 from obj
3 where object_name = upper('&func_name.');
CREATED LAST_DDL_TIME
------------------- -------------------
10.03.2019 16:27:16 10.03.2019 16:27:16
SQL>
There is just a nasty SP2-0622 error that can be ignored - I have tried to handle functions where the function's name would be on 5th-6th line. The script is capable of handling some "long" functions as well.