Skip to Main Content
  • Questions

  • How to print function name in sqlplus along with creation time.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ashutosh.

Asked: February 19, 2019 - 8:46 am UTC

Last updated: March 12, 2019 - 4:59 am UTC

Version: 11.1

Viewed 1000+ times

You Asked

Hi,

I need advise on below query.


Whenever we created any procedure/function from sqlplus command prompt then Function created message shown in sqlplus.

E:g

SQL> @C:\abc.fnc;

Function created.

1:Can we print Function/Procedure name along with Function /Procedure created message.
2:Can we print creation time along with Function /Procedure created message.

If any command exists in sqlplus for above requirement. Please let me know or Please suggest any alternative to achieve this.

Regards,
Ashutosh



and Connor said...

To my knowledge there is nothing native in SQL*Plus to do this. Options could be:

1) echo+time

SQL> @c:\tmp\my_func.sql

Function created.

SQL> set time on
11:56:00 SQL> set echo on
11:56:02 SQL> @c:\tmp\my_func.sql
11:56:06 SQL> create or replace
11:56:06   2  function my_func return int is
11:56:06   3  begin
11:56:06   4    return 1;
11:56:06   5  end;
11:56:06   6  /

Function created.


so you can see the time at which the script run, plus the actual DDL that is going on.

2) A helper script that you run after each DDL

SQL> @c:\tmp\my_func.sql

Function created.

SQL> select object_name, to_char(last_ddl_time,'dd/mm hh24:mi:ss') ddl
  2  from   user_objects
  3  order by last_ddl_time desc nulls last
  4  fetch first 1 row only;

OBJECT_NAME                              DDL
---------------------------------------- --------------
MY_FUNC                                  20/02 11:55:54



Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great help

Ashutosh Tripathi, February 20, 2019 - 10:53 am UTC

Hi Connor,

Thank you so much and It is helpful.

Regards,
Ashutosh



Chris Saxon
February 20, 2019 - 11:49 am UTC

You're welcome

a few SQL*Plus commands

Mikhail Velikikh, March 10, 2019 - 5:31 pm UTC

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.
Connor McDonald
March 12, 2019 - 4:59 am UTC

Nice stuff.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library