Oracle dynamic SQL: UDF within execute immediate

Question

I am trying to make some of my code dynamic. While typing the question how to use UDFs in dynamic SQL, I figured out the answer:

One can call the UDF from outside!

This works:

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

This did not work:

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

But this works:

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

I am using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0

In case you have an idea, how to skip the call from outside, feel free to let me know.

Many greeetings, Peter

"*This did not work*" is not at all helpful. What doesn't work? Does it give any error?..and for what specific reason do you want it be dynamic? — Kaushik Nayak, Jun 12 '19 at 13:42
Have you created a synonym for your UDF? Is the UDF in the same package? — JohnHC, Jun 12 '19 at 14:00
the UDF is in the same package as the dynamic sql. Error message was that it could not find the function. I guess this was because the dynamic sql did not have the package as its scope but isolates itself soemhow. — Peter Frey, Jun 12 '19 at 14:13

Tad Harrison · Answer 1 · 2019-06-13T12:45:41.607

Check your grants and make sure you either include explicit schema owner in the quoted call, use a synonym, or connect directly as schema owner.

Remember that stored procedures execute normally with the privileges of the code creator, so you should make certain that the username you use to run the execute immediate has direct grant (not via a role) access to execute the function.

This works fine in Oracle 12c when logged in as schema owner:

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;

Returns:

King
Blake
Clark
...

EDIT:

Based on the additional information that function and calling procedure are in the same package, it is likely that the problem is merely naming and scope.

When using execute immediate, the statement is parsed and executed at runtime, by Oracle's SQL engine, with very limited context of the surrounding code. In short, the payload of execute immediate doesn't know it's running in a package.

Here's a demo that should clear things up a bit.

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;
/

Here's the results:

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>

For test 1, the SQL engine is looking for something called "public_func" and can't find it. This make sense because you could have two packages that each have something called "public_func" in them. The SQL engine does not know that it is being invoked from within a package.

Test 2 is what you did, and it works as expected.

For test 3, a function is called that exists only within the package body. Normally, other procedures in the package can see private functions, but since this is interpreted at runtime and the SQL engine doesn't know it's being called within the scope of the package, this call fails as well.

@Marmite Bomber, the first "this works" one is a standalone function (assuming the OP tried a standalone function then a package). — Tad Harrison, Jun 12 '19 at 16:57
@tad:No, all the codes belongs to the same package. I run the code from outside via: exec my_package.my_func... and my func is i.e "update some_table....;" — Peter Frey, Jun 13 '19 at 07:51
@tad:I am new to pl sql: How do I make sure I have all permissions I need? — Peter Frey, Jun 13 '19 at 07:54
@Peter Frey, it's probably not a permissions problem--it's a scope problem. I have edited my answer based on your comment that the code is in the same package. To answer your question about dealing with permissions problems (not necessarily relevant here), I recommend doing *everything* with the schema owner account first, to prove the code works, and then trying to get it to work with a different account. This will help suss out the permissions and explicit schema name prefixes you may need to add. Note that production apps usually don't connect as schema owner directly. — Tad Harrison, Jun 13 '19 at 12:51

Oracle dynamic SQL: UDF within execute immediate

1 Answers1