Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 962
Vijay Kolte Avatar asked Jul 23 '12 07:07

Vijay Kolte


2 Answers

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

...
execute immediate 'select count(*) from ' || p_table_name into l_count;
...
like image 77
Miguel Avatar answered Nov 07 '22 23:11

Miguel


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;
like image 9
Codo Avatar answered Nov 07 '22 23:11

Codo