CODEWITHSUNDEEP
databaseoracle-databaseplsql
Nate Silver
Nate Silver

Reputation: 13

building a select statement in oracle using values stored in variables

Is there a way to build a select statement in oracle using values stored in variables? For example can you do this:

 declare 
 tbl_var varchar2(10) := "map_set";

begin
select count(*) from tbl_var;
end;

Upvotes: 0

Views: 101

Answers (1)

Aramillo
Aramillo

Reputation: 3216

Yes there is, using execute immediate:

declare 
 tbl_var varchar2(10) := 'map_set';
 result number;

begin
execute immediate 'select count(*) from '||tbl_var into result; --save result into variable
dbms_output.put_line('Total rows:'||result); --print result
end;

Second way, you can create a function that receives table name as parameter and return the count:

create function get_count(tbl_var varchar2) return number is 

 result number;

begin
execute immediate 'select count(*) from '||tbl_var into result;
return result;
end;

After create the function you can query it like this:

select get_count('map_set') from dual;

Upvotes: 3

Related Questions