CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Serge
Serge

Reputation: 626

passing in table name as plsql parameter

I want to write a function to return the row count of a table whose name is passed in as a variable. Here's my code:

create or replace function get_table_count (table_name IN varchar2)
  return number
is
  tbl_nm varchar(100) := table_name;
  table_count number;
begin
  select count(*)
  into table_count
  from tbl_nm;
  dbms_output.put_line(table_count);
  return table_count;
end;

I get this error:

FUNCTION GET_TABLE_COUNT compiled
Errors: check compiler log
Error(7,5): PL/SQL: SQL Statement ignored
Error(9,8): PL/SQL: ORA-00942: table or view does not exist

I understand that tbl_nm is being interpreted as a value and not a reference and I'm not sure how to escape that.

Upvotes: 4

Views: 19670

Answers (1)

Dmitriy
Dmitriy

Reputation: 5565

You can use dynamic SQL:

create or replace function get_table_count (table_name IN varchar2)
  return number
is
  table_count number;
begin
  execute immediate 'select count(*) from ' || table_name into table_count;
  dbms_output.put_line(table_count);
  return table_count;
end;

There is also an indirect way to get number of rows (using system views):

create or replace function get_table_count (table_name IN varchar2)
  return number
is
  table_count number;
begin
  select num_rows
    into table_count
    from user_tables
   where table_name = table_name;

  return table_count;
end;

The second way works only if you had gathered statistics on table before invoking this function.

Upvotes: 10

Related Questions