Re: when is a variable not a variable

From: Chris Halioris <halioris_chris_nonlilly_at_lilly.com>
Date: 1997/01/29
Message-ID: <32EFB8C3.4AE6@lilly.com>#1/1

John Steinbach wrote:
>
> 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

Not sure if the last message made it through...here goes again.

That is because v_name is not a table.
You could replace that select statement with dynamic SQL to query the appropriate table. Code might look like this:

PROCEDURE count_tables IS

   v_name user_tables.table_name%TYPE;
   v_count NUMBER;
   rslt INTEGER;
   CURSOR c_tables IS

      SELECT table_name
        FROM user_tables
       WHERE tabelspace_name = 'tablespacename';

   cursor_handle INTEGER := DBMS_SQL.OPEN_CURSOR; BEGIN
   FOR x IN c_tables LOOP

      v_name := x.table_name;
      DBMS_SQL.PARSE(cursor_handle, 'SELECT count(*) FROM ' || v_name, 
DBMS_SQL.V7);
      DBMS_SQL.DEFINE_COLUMN(cursor_handle, 1, v_count);
      rslt := DBMS_SQL.EXECUTE_AND_FETCH(cursor_handle);
      DBMS_SQL.COLUMN_VALUE(cursor_handle, 1, v_count);
      INSERT INTO table_count(table_name,count)
      VALUES(v_name,v_count);
      --
      -- If you just want it displayed on the screen instead of
      -- writing to a table, you could do the following
      DBMS_OUTPUT.PUT_LINE(v_name || ': ' || to_char(v_count));

   END LOOP;
   DBMS_SQL.CLOSE_CURSOR(cursor_handle); END; Chris Halioris
Tactics, Inc. Received on Wed Jan 29 1997 - 00:00:00 CST