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

Question

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?

score 11 · Answer 1 · answered Jan 30 '15 at 14:22

11

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

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

answered Jan 30 '15 at 14:22

Miguel

111
1
2

Note possible sql injection risk – Ruskin Jan 30 '15 at 14:32
1

This works for me on 12c while the accepted answer returns ORA-06547: RETURNING clause must be used with INSERT, UPDATE, or DELETE statements. – Matthew James Briggs Oct 14 '18 at 19:00

Codo · Accepted Answer · 2020-03-31T08:09:51.160

9

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;

edited Mar 31 '20 at 08:09

answered Jul 23 '12 at 07:46

Codo

75,595
17
168
206

This does not work in Oracle. ORA-06547: RETURNING clause must be used with INSERT, UPDATE, or DELETE statements. – Brent Easton Mar 30 '20 at 23:28

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

2 Answers2