Dynamically Executing Schema Functions And Procedures In Oracle

Oracle has powerful commands to execute SQL commands dynamically. It is possible to use DBMS_SQL package or EXECUTE IMMEDIATE command. I demonstrate a simple example to show how to call a function or procedure in other schema dynamically.
Below there is a function get_hello which defined in SYS schema. I call this function in HR schema by giving name of schema and function name

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE FUNCTION get_hello(lang IN VARCHAR2) RETURN VARCHAR2 IS
  2  BEGIN
  3    IF upper(lang) = 'EN' THEN
  4      RETURN 'Hello';
  5    ELSIF upper(lang) = 'TR' THEN
  6      RETURN 'Merhaba';
  7    ELSE
  8      RETURN 'Hello';
  9    END IF;
 10  END;
 11  /
Function created
SQL>
SQL> DECLARE
  2    lang VARCHAR2(2) := 'Tr';
  3  BEGIN
  4    dbms_output.put_line(get_hello(lang));
  5  END;
  6  /
Merhaba
PL/SQL procedure successfully completed
SQL> grant execute on get_hello to hr;
Grant succeeded
SQL> conn hr/hr
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    lang VARCHAR2(2) := 'Tr';
  3  BEGIN
  4    dbms_output.put_line(sys.get_hello(lang));
  5  END;
  6  /
Merhaba
PL/SQL procedure successfully completed
SQL>
SQL> DECLARE
  2    lang  VARCHAR2(2) := 'Tr';
  3    hello VARCHAR2(16);
  4  BEGIN
  5    EXECUTE IMMEDIATE 'select sys.get_hello(:lang) from dual'
  6      INTO hello
  7      USING lang;
  8    dbms_output.put_line(hello);
  9  END;
 10  /
Merhaba
PL/SQL procedure successfully completed
SQL>
SQL> CREATE OR REPLACE FUNCTION generic_get_hello(lang          IN VARCHAR2,
  2                                               schema_name   IN VARCHAR2,
  3                                               function_name IN VARCHAR2)
  4    RETURN VARCHAR2 IS
  5    res VARCHAR2(16);
  6  BEGIN
  7    EXECUTE IMMEDIATE 'select ' || schema_name || '.' || function_name ||
  8                      '(:lang) from dual'
  9      INTO res
 10      USING lang;
 11    RETURN res;
 12  END;
 13  /
Function created
SQL>
SQL> BEGIN
  2    dbms_output.put_line(generic_get_hello('en','sys','get_hello') );
  3  END;
  4  /
Hello
PL/SQL procedure successfully completed
SQL>