DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Error(31,30): PLS-00302: component 'TABLE_NAME_V' must be declared

  1. #1
    Join Date
    Jan 2014
    Posts
    3

    Error(31,30): PLS-00302: component 'TABLE_NAME_V' must be declared

    Hi People,

    I am writing a procedure for work but I can't seem to locate the above error. I have it declared in the correct place but yet the compiler complains about the variables in the sql_stmnt below.
    There are actually two errors owner_v and table_name_v in that sql_stmnt.

    Can someone look this over to see where my mistake is? I would appreciate it.

    thanks

    Error(31,30): PLS-00302: component 'TABLE_NAME_V' must be declared


    create or replace
    PROCEDURE SCHEMA_CNT IS


    owner_v varchar2(100);

    table_name_v varchar2(100);

    sql_stmnt varchar2(4000);

    cnt_val number;

    CURSOR c1 IS select owner, table_name into owner_v, table_name_v from dba_tables
    WHERE owner NOT IN ('SYS','SYSTEM')
    GROUP BY owner, table_name;



    BEGIN

    FOR c1_rec IN c1 LOOP

    sql_stmnt := 'select count(*) from '||c1_rec.owner_v||'.'||c1_rec.table_name_v;

    EXECUTE IMMEDIATE sql_stmnt INTO cnt_val;

    DBMS_OUTPUT.PUT_LINE(c1_rec.table_name_v||' has '||cnt_val||' number of rows');

    END LOOP;
    END;

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    It helps to format the text. How does this work?

    Code:
    CREATE OR REPLACE PROCEDURE SCHEMA_CNT 
    IS
       owner_v      VARCHAR2(100);
       table_name_v VARCHAR2(100);
       sql_stmnt    VARCHAR2(4000);
       cnt_val      NUMBER;
       CURSOR c1
           IS SELECT owner,   table_name
                INTO owner_v, table_name_v
                FROM dba_tables
               WHERE owner NOT IN ('SYS','SYSTEM')
               GROUP BY owner, table_name;
    BEGIN
      FOR c1_rec IN c1
      LOOP
        sql_stmnt := 'select count(*) cnt INTO :into_bind from '||c1_rec.owner_v||'.'||c1_rec.table_name_v;
        EXECUTE IMMEDIATE sql_stmnt RETURN cnt_val;
        DBMS_OUTPUT.PUT_LINE(c1_rec.table_name_v||' has '||cnt_val||' number of rows');
      END LOOP;
    END;
    /

  3. #3
    Join Date
    Jan 2014
    Posts
    3
    Good evening,

    It still complains. Here is the complaint.

    Error(17,37): PLS-00103: Encountered the symbol "CNT_VAL" when expecting one of the following: into bulk The symbol "into" was substituted for "CNT_VAL" to continue.




    The compiler is saying from the message above cnt_val is to be replaced with INTO.

    Regards,

  4. #4
    Join Date
    Jan 2014
    Posts
    3
    Hi Everyone,

    This works. See below:

    table row count by owner query using dynamic sql

    CREATE THE PROCEDURE:

    create or replace
    PROCEDURE SCHEMA_CNT
    IS
    cursor c1 is select owner, table_name from dba_tables
    where owner not in ('SYS','SYSTEM','DBSNMP','STREAMADMIN',
    'OUTLN','MGMT_VIEW','MDSYS','ORDSYS','EXFSYS','WMSYS','APPQOSSYS',
    'OWBSYS_AUDIT','ORDDATA','CTXSYS','ANONYMOUS','SYSMAN','XDB','ORDPLUGINS',
    'OWBSYS','SI_INFORMTN_SCHEMA','OLAPSYS','ORACLE_OCM','XS','BI','PM',
    'MDDATA','IX','SH','DIP','OE','REPADMIN','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR',
    'SPATIAL_WFS_ADMIN_USR','APEX_030200')
    ORDER BY owner, table_name;

    sql_stmnt varchar2(4000);
    cnt_val number;

    begin

    for c1_rec in c1 loop

    sql_stmnt := 'select count(*) from '||c1_rec.owner||'.'||c1_rec.table_name;
    execute immediate sql_stmnt into cnt_val;

    dbms_output.put_line(c1_rec.owner||' '||c1_rec.table_name||' '||cnt_val);

    end loop;

    end;
    /


    SET THE ENVIRONMENT:

    SET SERVEROUTPUT ON;

    begin

    DBMS_OUTPUT.ENABLE(10000000);

    end;
    /




    SAVE THE OUTPUT:

    Spool the output to a file.

    Sqlplus >spool schema_cnt.txt

    The output will be found in the oracle home directory as schema_cnt.txt

    Issue the command at the sql prompt:


    Sql> EXECUTE SCHEMA_CNT1;

    PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.


    Stop the spooling:

    Sqlplus > spool off;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width