英文:
How to log the usage of an Oracle function
问题
在我的Oracle数据库中，我有一个名为MYFCT的函数，已经过时。它的新版本被编写为MYFCT_NEW，对于相同的输入参数返回不同的输出。
我们相应地重写了我们的代码库，但我想知道是否仍然存在一些未记录的用法。
是否可能记录函数的使用情况？类似于触发器CREATE TRIGGER BEFORE EXECUTE ...来记录调用MYFCT的所有会话。
目前，我只是想删除执行该函数的授权，然后看看是否会出现错误，但这似乎有点严厉。
英文:
In my Oracle database, I have a function MYFCT that is obsolete. A new version of it has been writen as MYFCT_NEW, that returns different output for the same input parameters.
We re-wrote our codebase accordingly, but I wonder if there is still some undocumented usage of it.
Is it possible to log the usage of a function ? Something like a trigger CREATE TRIGGER BEFORE EXECUTE ... to log all sessions that call MYFCT.
For the moment, I only imagine removing the grant to execute that function and see if errors pop up, but it seems a bit harsh.
答案1
得分: 1
你可以将一个日志记录过程包含到旧函数中，就像这样：
SQL> create table fun_log (who varchar2(30), when date);
表已创建。
SQL> create or replace procedure p_log is
2 pragma autonomous_transaction;
3 begin
4 insert into fun_log(who, when) values (user, sysdate);
5 commit;
6 end;
7 /
过程已创建。
这是你的“旧”函数；包括第3行（即调用 p_log 过程）：
SQL> create or replace function f_test return number is
2 begin
3 p_log;
4 return 1;
5 end;
6 /
函数已创建。
然后检查 fun_log 表中是否有任何行：
SQL> select f_test from dual;
F_TEST
----------
1
SQL> select * from fun_log;
WHO WHEN
------------------------------ -------------------
SCOTT 06.07.2023 10:03:12
SQL>
如果你愿意，你可以在 fun_log 表上创建一个触发器，每当插入新行时，它将向你发送一封电子邮件（这样你就不必手动检查了）。
英文:
You could include a logging procedure into the old function. Something like this:
SQL> create table fun_log (who varchar2(30), when date);
Table created.
SQL> create or replace procedure p_log is
2 pragma autonomous_transaction;
3 begin
4 insert into fun_log(who, when) values (user, sysdate);
5 commit;
6 end;
7 /
Procedure created.
This is your "old" function; include line #3 (i.e. call the p_log procedure):
SQL> create or replace function f_test return number is
2 begin
3 p_log;
4 return 1;
5 end;
6 /
Function created.
Then check whether there are any rows in fun_log table:
SQL> select f_test from dual;
F_TEST
----------
1
SQL> select * from fun_log;
WHO WHEN
------------------------------ -------------------
SCOTT 06.07.2023 10:03:12
SQL>
If you want, you can create a trigger on fun_log which will send you an e-mail whenever a new row is being inserted (so that you wouldn't have to check it manually).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论