how to get count(*) value in local temp variable in dynamic sql (ORACLE PLSQL)
15,355
Solution 1
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;
Solution 2
Maybe different oracle version, but what worked for me was:
...
execute immediate 'select count(*) from ' || p_table_name into l_count;
...
Author by
Admin
Updated on July 29, 2022Comments
-
Admin almost 2 years
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 getcount(*)
value intotmp_cnt
variable?