Oracle dynamic SQL:UDF 中的立即执行

Question

我正在尝试使我的一些代码动态化。在输入如何在动态SQL中使用UDF的问题时，我想出了答案：

可以从外面打电话给UDF！

这有效：

Update my_table
Set col1 = get_some_value(col2,col2)
Where 1 = 1;

这不起作用：

Execute Immediate '
Update my_table
Set col1 = get_some_value(col2,col3)
Where 1 = 1
';

但这有效：

Execute Immediate '
Update my_table
Set col1 = my_package_name.get_some_value(col2,col3)
Where 1 = 1
';

我正在使用 Oracle 数据库 12c 企业版版本 12.1.0.2.0

如果您有想法，如何跳过外面的电话，请随时告诉我。

许多格里廷， 彼得

Answer 1

检查授权，并确保在带引号的调用中包含显式架构所有者、使用同义词或直接以架构所有者身份连接。

请记住，存储过程以代码创建者的权限正常执行，因此应确保用于立即运行执行的用户名具有执行函数的直接授予(而不是通过角色)访问权限。

当以架构所有者身份登录时，这在 Oracle 12c 中工作正常：

create function myfunc(p_text in varchar2) return varchar2 is
begin
return initcap(p_text);
end;
/
begin
execute immediate 'update emp set ename = myfunc(ename)';
end;
/
select ename from emp;

返回：

King
Blake
Clark
...

编辑：

根据函数和调用过程位于同一包中的附加信息，问题可能只是命名和作用域。

当使用execute immediate时，该语句由Oracle的SQL引擎在运行时解析和执行，周围代码的上下文非常有限。 简而言之，execute immediate的有效负载不知道它在包中运行。

这是一个应该澄清一些事情的演示。

create or replace package mytest as
function public_func(p_text in varchar2) return varchar2;
procedure demo;
end;
/
create or replace package body mytest as
-------------------------------------------------------------------------------
function public_func(p_text in varchar2) return varchar2 is
begin
return initcap(p_text);
end;
-------------------------------------------------------------------------------
function private_func(p_text in varchar2) return varchar2 is
begin
return lower(p_text);
end;
-------------------------------------------------------------------------------
procedure demo is
begin
-- Test 1 should fail because the function name is not fully qualified
begin
execute immediate 'update emp set ename = public_func(ename)';
exception when others then
dbms_output.put_line('Test1: ' || SQLERRM);
end;
-- Test 2 should pass
begin
execute immediate 'update emp set ename = mytest.public_func(ename)';
exception when others then
dbms_output.put_line('Test2: ' || SQLERRM);
end;
-- Test 3 should fail because the private function is not visible
begin
execute immediate 'update emp set ename = mytest.private_func(ename)';
exception when others then
dbms_output.put_line('Test3: ' || SQLERRM);
end;
end;
end;
/

结果如下：

SQL> set serveroutput on;
SQL> begin
2    mytest.demo;
3  end;
4  /
Test1: ORA-00904: "PUBLIC_FUNC": invalid identifier
Test3: ORA-00904: "MYTEST"."PRIVATE_FUNC": invalid identifier
PL/SQL procedure successfully completed.
SQL> 

对于测试 1，SQL 引擎正在查找名为"public_func"的内容，但找不到它。这是有道理的，因为您可以有两个包，每个包中都有称为"public_func"的东西。SQL 引擎不知道它正在从包中调用。

测试 2 是您所做的，它按预期工作。

对于测试 3，将调用仅存在于包正文中的函数。通常，包中的其他过程可以看到私有函数，但由于这是在运行时解释的，并且 SQL 引擎不知道它在包的范围内被调用，因此此调用也会失败。