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.

If `v_tab_type` is a local variable with a known type, why are you trying to use dynamic SQL? — Justin Cave, Apr 09 '14 at 03:27

score 0 · Accepted Answer · answered Apr 09 '14 at 04:59

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.

TABLE function with Dynamic SQL?

1 Answers1