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;
Add a comment
|
1 Answer
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;
8 Comments
Nate Silver
Maybe I am doing this wrong, but I don't get anything in the Data Grid in Toad. But when I run: select count(*) from map_set; I get a count value returned.
Aramillo
you don't see anything because this is plsql code, datagrid is for sql. If you want to see results, you must use
dbms_output.put_line and check DBMS Output in toad. Check editAramillo
Check the second way, this will show result in the grid
Nate Silver
Ok great! this certainly works ;) I have a follow up question, don't know if I should open a separate thread for it.
Nate Silver
Will 'execute immediate' work for functions as well, I tried it but get an invalid sql statement.
|