Oracle 从SQL识别PL/SQL函数调用

你好，
首先，感谢您所做的出色工作-我 (希望其他许多人) 非常感谢。

我想问一下是否有一种自动的方法来识别从SQL调用的PL/SQL函数。
我想准备在12c中使用PRAGMA UDF的 “候选人” 列表，但是我不知道如何搜索它们。

提前谢谢，
亲切的问候

在一般情况下，不，我不知道找到这个的方法。除了抓取v $ 视图。

如果您在12.2上，则可以使用PL/Scope检查PL/SQL中调用其他PL/SQL的SQL。

alter session set plscope_settings='identifiers:all, statements:all'
/
create or replace function f ( p int ) 
  return int as
begin
  return p + 1;
end f;
/

create or replace procedure p ( param int ) as
  val int;
begin
  select f(param)
  into   val
  from   dual;
  
  dbms_output.put_line(val);
end p;
/

exec p(1);

with my_prog_unit as (
  select user owner, 'P' object_name from dual
), full_set as (
  select ai.usage,
         ai.usage_id,
         ai.usage_context_id,
         ai.type,
         ai.name
  from   all_identifiers ai, my_prog_unit
  where  ai.object_name = my_prog_unit.object_name
  and    ai.owner = my_prog_unit.owner
  union all
  select st.type,
         st.usage_id,
         st.usage_context_id,
         'TYPE',
         'NAME'
  from   all_statements st, my_prog_unit
  where  st.object_name = my_prog_unit.object_name
  and    st.owner = my_prog_unit.owner
), dml_statements as (
  select st.owner, st.object_name, st.line, st.usage_id, st.type
  from   all_statements st, my_prog_unit
  where  st.object_name = my_prog_unit.object_name
  and st.owner = my_prog_unit.owner
  and st.type in ('SELECT', 'UPDATE', 'DELETE')
)
  select st.owner,
         st.object_name,
         st.line,
         st.type,
         s.text
  from   dml_statements st, all_source s
  where ('CALL', 'FUNCTION') in (
    select fs.usage, fs.type
    from   full_set fs
    connect by prior fs.usage_id = fs.usage_context_id
    start with fs.usage_id = st.usage_id
  )
  and st.line = s.line
  and st.object_name = s.name
  and st.owner = s.owner;

OWNER   OBJECT_NAME   LINE   TYPE     TEXT                 
CHRIS   P                  4 SELECT     select f(param)
复制

HT给Steven Feuerstein上面的SQL语句。他更多地谈论使用PL/Scope在PL/SQL中查找SQL中的PL/SQL:https://stevenfeuersteinonplsql.blogspot.co.uk/2017/06/more-122-plscope-magic-find-sql.html