Thread: SELECT statement...

How do I use a varchar variable to specify a tablename in a SELECT statement???
e.g. i want to write a procedure which accept table name as input and print the number of records in it;

create or replace procedure a(tblName in varchar) is
result long;
begin
select count(*) into result from tblName;--error on this line
dbms_output.put_line(result);
end a;

simply try to use this

Code:

create or replace procedure a(tblName in varchar2) is
result varchar2(30);
begin
select count(*) into result from tblName
dbms_output.put_line('The table contains '|| result ||' rows.');
end a;

hello nitin

u know execute immediate method.its a method used in native dynamic sql.native dynamic sql means at the run time we will get the query.here the query which will give at run time will execute at run time not at compile time.

for u r asked procedure also same thing here we r getting table name at run time so below example will work for u.

create or replace procedure cnt_proc(tbl in varchar2)
is
str varchar2(200):='select count(*) from '||tbl;
n number;
begin
execute immediate str into n;
dbms_output.put_line('no of rows='||n);
end;
/