dynamically binding the tablename

[Karthika]

How to dynamically bind the table name in stored procedure.

For example

procedure sample(tblname varchar2 in)as
row_count number(2);
begin
select count(*) into row_count from tblname;
end sample;

[jkoopmann]

bind variables are used for predicates in the where clause such like :
create or replace procedure sample(tblname varchar2 ) is
row_count number;
begin
execute immediate 'select count(*) from user_tables where table_name = :1' into row_count using tblname;
dbms_output.put_line('result : ' ||row_count);
end sample;

if you want to dynamically provide a table name you need to build a sql string and execute it like this:

create or replace procedure sample(tblname varchar2 ) is
row_count number;
begin
execute immediate 'select count(*) from '||tblname into row_count;
dbms_output.put_line('result : ' ||row_count);
end sample;