5 Replies Latest reply: Dec 10, 2012 1:26 AM by 899401 RSS

    create sp for count

    899401
      Hi,
      i have 20 tables in schema and wanted to take count using store procedure
      how to create a sp for taking count of multiple tables

      thanks in advance
        • 1. Re: create sp for count
          moreajays
          Hi,

          create or replace
          function get_rows( p_tname in varchar2 ) return number
          as
          l_columnValue number default NULL;
          begin
          execute immediate
          'select count(*)
          from ' || p_tname INTO l_columnValue;

          return l_columnValue;
          end;
          /

          select user, table_name,
          get_rows( user||'.'||table_name) cnt
          from user_tables
          /

          Thanks,
          Ajay More
          http://moreajays.blogspot.com
          • 2. Re: create sp for count
            hitgon
            SET SERVEROUTPUT ON;
            spool '/home/oracle/monstreaming/db1_count.log'
            DECLARE
            V_CNT INT:=0;
            STMT VARCHAR(4000);
            BEGIN

            FOR CNT IN (SELECT TNAME FROM TAB ORDER BY TNAME)
            LOOP

            DBMS_OUTPUT.PUT_LINE('SELECT COUNT(1) FROM '|| CNT.TNAME);

            STMT := 'SELECT COUNT(1) FROM '|| CNT.TNAME;

            EXECUTE IMMEDIATE STMT INTO V_CNT;

            DBMS_OUTPUT.PUT_LINE('COUNT:==> '|| V_CNT);

            END LOOP;

            END;
            /

            spool off;
            exit;
            • 3. Re: create sp for count
              Niket Kumar
              .....

              Edited by: Niket Kumar on Dec 7, 2012 3:07 PM
              • 4. Re: create sp for count
                Girish Sharma
                If you are getting then ORA-00942 then see below demo :
                SQL> show user;
                USER is "SYS"
                
                create or replace procedure table_counts(name_of_schema in varchar2)
                  is
                  tab_counts number;
                  begin
                  select count(*) into tab_counts from sys.dba_tables where owner=name_of_schema;
                  dbms_output.put_line('Total Number of tables in ' || name_of_schema || ' is ' || tab_counts);
                end;
                /
                
                Procedure created.
                
                SQL> exec table_counts('SCOTT');
                Total Number of tables in SCOTT is 26
                
                PL/SQL procedure successfully completed.
                
                SQL> exec table_counts('HR');
                Total Number of tables in HR is 8
                
                PL/SQL procedure successfully completed.
                
                SQL> connect scott/tiger
                Connected.
                SQL> select granted_role from user_role_privs;
                
                GRANTED_ROLE
                ------------------------------
                CONNECT
                DBA
                EXP_FULL_DATABASE
                RESOURCE
                
                SQL> create or replace procedure table_counts(name_of_schema in varchar2)
                  2  is
                  3  tab_counts number;
                  4  begin
                  5  select count(*) into tab_counts from sys.dba_tables where owner=name_of_schema;
                  6  dbms_output.put_line('Total Number of tables in ' || name_of_schema || ' is ' || tab_counts);
                  7  end;
                  8  /
                
                Warning: Procedure created with compilation errors.
                
                SQL> show errors;
                Errors for PROCEDURE TABLE_COUNTS:
                
                LINE/COL ERROR
                -------- -----------------------------------------------------------------
                5/1      PL/SQL: SQL Statement ignored
                5/42     PL/SQL: ORA-00942: table or view does not exist
                
                Scott user is having DBA role, but still he is not able to query on dba_tables view.  He needs explicit
                select privilege on dba_tables. So..
                SQL> connect sys/girish as sysdba
                Connected.
                SQL> grant select on dba_tables to scott;
                
                Grant succeeded.
                
                SQL> connect scott/tiger
                Connected.
                SQL> create or replace procedure table_counts(name_of_schema in varchar2)
                  2  is
                  3  tab_counts number;
                  4  begin
                  5  select count(*) into tab_counts from sys.dba_tables where owner=name_of_schema;
                  6  dbms_output.put_line('Total Number of tables in ' || name_of_schema || ' is ' || tab_counts);
                  7  end;
                  8  /
                
                Procedure created.
                
                SQL> exec table_counts('HR');
                
                PL/SQL procedure successfully completed.
                
                SQL> set serveroutput on;
                SQL> exec table_counts('HR');
                Total Number of tables in HR is 8
                
                PL/SQL procedure successfully completed.
                
                SQL>
                Regards
                Girish Sharma
                • 5. Re: create sp for count
                  899401
                  thanks