Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Posts
    1

    Unanswered: Getting count of rows in all tables

    Hi,
    I have written a script to get count of each tables of a schema.

    DECLARE CURSOR c1 IS
    select distinct table_name from all_tab_columns where OWNER='PLAY';
    countrows NUMBER;
    BEGIN
    FOR x IN c1 LOOP
    countrows := select count(*) from x.table_name;
    dbms_output.put_line(x.table_name || x.countrows);

    END LOOP;
    END;
    /
    When executing, I am getting an error which is as below:.Can anyone help me in the same?

    ERROR at line 6:
    ORA-06550: line 6, column 18:
    PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
    ( - + case mod new not null <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count current exists max min prior sql stddev sum variance
    execute forall merge time timestamp interval date
    <a string literal with character set specification>
    <a number> <a single-quoted SQL string> pipe
    <an alternatively-quoted string literal with character set specification>
    <an alternatively-quo
    ORA-06550: line 7, column 4:
    PLS-00103: Encountered the symbol "DBMS_OUTPUT"

    Thanks,
    P

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    visit Search and Download Oracle Database, Application Server, and Collaboration Suite Documentation
    Click on the appropriate version link.
    Spend some time Reading The Fine PL/SQL Reference Manual to learn valid syntax.

    http://asktom.oracle.com contains many fine coding examples.

    Good Luck!
    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.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    You could also consult the ALL_TABLES.NUM_ROWS column, if you don't need an up-to-the-date, accurate count. I believe this column is updated when statistics are built (or re-built) for a table.

Posting Permissions

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