Execute Immediate with Non-Sql type

Question

I need to call a function dynamically (say handler() ) in PL/SQL function which returns a Nested Table.

Code:

BEGIN
... 
   v_function := 'handler'; //shown like this of simplicity.
   EXECUTE IMMEDIATE 'BEGIN :result := ' || v_function || '(...); END;'
     USING OUT v_error_msg;

... //process v_error_msg
END;

and the handler() specification:

TYPE t_error_msgs IS TABLE OF VARCHAR2(2000);

FUNCTION handle (...)
  RETURN t_error_msgs;

Issue is I get PL-00457:expressions have to be of SQL types while compiling as execute immediate wont allow non-sql types to be binded.

Is there any way around ?

Answer 1

It depends what you mean by 'workaround' The type will have to be declared at SQL level, not within a PL/SQL block (presumably a package in this case). This would work, for example:

CREATE OR REPLACE TYPE t_error_msgs AS TABLE OF VARCHAR2(2000)
/

CREATE OR REPLACE PACKAGE p42 AS
  FUNCTION handler RETURN t_error_msgs;
END p42;
/

CREATE OR REPLACE PACKAGE BODY p42 AS
  FUNCTION handler RETURN t_error_msgs IS
  BEGIN
    RETURN null; -- put real data here, obviously...
  END handler;
END p42;
/

DECLARE
  v_error_msg t_error_msgs;
  v_function varchar2(30);
BEGIN
  v_function := 'p42.handler';
  EXECUTE IMMEDIATE 'BEGIN :result := ' || v_function || '; END;'
    USING OUT v_error_msg;
END;
/

Alternatively you can reconsider whether you really need this to be dynamic. Presumably you're passing or somehow determining the function to call on the fly and populating v_function. If there's a relatively short list of possible values it might be simpler to have a case with individual static function calls.