How to get count (*) value in temp local variable in dynamic sql (ORACLE PLSQL)

Question

I want to get the count(*) value in the dynamic expression plsql. We can write static stmt as:

 select count(*) into tmp_cnt from table_info where nbr_entry='0123456789'; 

but how to get tmp_cnt value when writing dynamic sql stament? or any other way to get the count(*) value in the tmp_cnt variable?

Answer 1

You can achieve this with EXECUTE IMMEDIATE ... RETURNING INTO:

 function count_rows(p_table_name varchar2) return number is l_count number; begin execute immediate 'select count(*) from ' || p_table_name returning into l_count; return l_count; end count_rows; 

Answer 2

Maybe a different version of the oracle, but what worked for me was:

 ... execute immediate 'select count(*) from ' || p_table_name into l_count; ...