TABLE function with Dynamic SQL?

Question

Is it possible to use TABLE function in Dynamic SQL?

I get Invalid identifier error when I use table function with Table of Records as Input in EXECUTE IMMEDIATE.

I can't get the following SQLs to work.

EXECUTE IMMEDIATE 'SELECT COUNT(1) from TABLE(' || v_tab_type || ') WHERE ' || v_where_condn INTO v_cnt;

EXECUTE IMMEDIATE 'SELECT COUNT(1) from ' || TABLE(v_tab_type) || ') WHERE ' || v_where_condn INTO v_cnt;

v_tab_type is a collection populated through Oracle AQ messages.

Answer 1

You should be able to do something like

EXECUTE IMMEDIATE 
  'SELECT COUNT(*) ' ||
  '  FROM TABLE( :1 ) ' ||
  ' WHERE ' || some_predicate
  INTO v_cnt
 USING v_tab_type;

Hopefully, whatever you're doing with your predicate would also use bind variables appropriately, particularly if this is going to be called often.