Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Posts
    10

    Unanswered: Problem with procedure that counts all the rows in all the tables

    When I run the following store procedure to count all the rows in all the tables I crash my sql Plus editor:
    CREATE OR REPLACE PROCEDURE TC_TABLEROWCOUNT(OWNER IN varchar2)
    IS
    row_count number;

    cursor get_tab is
    select table_name, num_rows
    from all_tables
    where owner='MAXDEV';

    begin

    dbms_output.put_line('Checking Record Counts for schema maxdev ');


    FOR get_tab_rec IN get_tab LOOP

    BEGIN


    EXECUTE IMMEDIATE 'select count(*) from '||get_tab_rec.table_name
    INTO row_count;




    EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('Error counting rows for table '
    ||get_tab_rec.table_name);

    END;

    END LOOP;

    END;
    /

    What am I doing wrong.

    Thanks for any help Tony

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    What type of error are you getting ???

    Gregg

  3. #3
    Join Date
    Feb 2004
    Posts
    10
    Thats the problem I am not getting an error, just a message in my task manager saying that SQL Plus editor is not responding.

    As I see it in the loop
    EXECUTE IMMEDIATE 'select count(*) from '||get_tab_rec.table_name
    INTO row_count;

    should retun the number of rows for each table. I have also tried using a varchar instead of a number.

    I am not sure what the problem could be I have tried looking up what problems EXECUTE IMMEDIATE could cause.

    Very confused Tony

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Have you set
    serveroutput on size 100000
    in your sql session ??? It may actually be working ...
    If you can, look at top sessions and see what the process is doing...

    Gregg

  5. #5
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    how many tables are you counting?

    it's not just that the process is taking a while, can you limit the cursor to just one table to test?
    There are 10 types of people in the world, those that know Binary and those that don't.

  6. #6
    Join Date
    Feb 2004
    Posts
    10
    Yes I have set it. if I take this statment out
    EXECUTE IMMEDIATE 'select count(*) from '||get_tab_rec.table_name
    INTO sql_stmt;
    The program will run outputting all the table names.
    There is something wrong with my execute statement.

    I have seen a version that has this in it.

    EXECUTE IMMEDIATE 'select count(*) from col1 from &&1..'||get_tab_rec.table_name
    INTO sql_stmt;

    But I have no idea what goes in &&1..', I thought it was the schema name in this case MAXDEV, when I put it in I hit the exception

    Tony

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    this works.
    Just add your error handling.
    The problem with the error handling was that it wouldn't let you debug your proc.

    PHP Code:
    CREATE OR REPLACE PROCEDURE TC_TABLEROWCOUNT (
        
    OWNER IN varchar2)
    IS

    row_count number
    ;
    vTable    varchar2(30);
    v_sqlstmt varchar2(100);

    cursor get_tab is
    select table_name
    num_rows
    from all_tables
    where owner
    ='MAXDEV';

    begin

    dbms_output
    .put_line('Checking Record Counts for schema maxdev ');

    FOR 
    get_tab_rec IN get_tab LOOP

        vTable 
    := get_tab_rec.table_name;

        
    v_sqlstmt := 'SELECT count(*) from '||vTable;

        
    EXECUTE IMMEDIATE v_sqlstmt into row_count;

    END LOOP;
    END;

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >EXECUTE IMMEDIATE 'select count(*) from col1 from &1..'||get_tab_rec.table_name INTO sql_stmt;
    The line above is FUBAR.
    1) the INTO clause should be before the "FROM" clause
    2) even if it is OK where it is it would need to be:

    ||get_tab_rec.table_name || ' INTO sql_stmt'

    My suggestion is to ALWAYS build the dynamic SQL as a complete string.
    Then use DBMS_OUTPUT to display the string.
    The cut & paste the results into SQL*Plus to ensure it is OK.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Feb 2004
    Posts
    10
    Thanks, problem now sorted out.

    Cheers everyone.

    Tony

Posting Permissions

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