Re: when is a variable not a variable

John Steinbach <bach_at_panix.com> wrote in article <5cma2o$874_at_panix.com>...
> PROCEDURE count_tables IS
> v_name CHAR(20);
> v_count INTEGER;
> CURSOR c_tables IS
> SELECT table_name
> FROM user_tables
> WHERE tabelspace_name = 'tablespacename';
> BEGIN
>
> FOR x IN c_tables LOOP
> v_name := x.table_name;
> SELECT COUNT(*) INTO v_count
> FROM v_name;
>
> INSERT INTO table_count(table_name,count)
> VALUES(v_name,v_count);
> ENND LOOP;
> END;
>
> The following code does not work. I was tring to get a row count of all
 my
> table spaces without hardcoding the table names.
>
> Any Thoughts?
>
> John STeinbach
> Bartlett Tree Experts
>

Hi!

Placing a 'bind' variable at that position (FROM) is not SQL, only static table-names (or viewnames) or
subqueries are allowed there.
In other words you can't have a dynamic tablename in the from-clause. Therefore you have to program a way of dynamically selecting the count from a a table, which i've modified your procedure to do:

PROCEDURE calc(ts in varchar2) IS
v_count INTEGER;
gencursor integer;
dummy integer;
CURSOR c_tables IS
  SELECT table_name
  FROM user_tables
  WHERE tablespace_name = ts;
BEGIN            FOR x IN c_tables LOOP
    gencursor := dbms_sql.open_cursor;
    dbms_sql.parse(gencursor,
      'select count(*) from '||x.table_name, dbms_sql.native);     dbms_sql.define_column(gencursor, 1, v_count);     if dbms_sql.execute_and_fetch(gencursor, TRUE) > 0     then

      dbms_sql.column_value(gencursor, 1, v_count);
      dbms_output.put_line(x.table_name || ' #Rows: ' || to_char(v_count));
	

    end if;
    dbms_sql.close_cursor(gencursor);
  END LOOP;    END;

---
After creating the procedure you call it with (from SQLplus):
set serveroutput on
exec calc('YOUR TABLESPACE');