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

oracle, plsql, plsqldeveloper

Solution

You can achieve it 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 into l_count;
  return l_count;
end count_rows;

Problem

I want to get `count(*)` value in dynamic plsql statement. 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 while writing the dynamic sql stament? or any other way to get `count(*)` value into `tmp_cnt` variable?

Original source