1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Count(*) from table stored in variable plsql

Discussion in 'SQL PL/SQL' started by Sparamanga, Dec 3, 2016.

  1. Sparamanga

    Sparamanga Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    I am trying to write a PLSQL block that firstly..

    grabs the table name from all_tables where owner='rob1' and rownum =1

    I then want to assign this table_name to a variable.

    I then want to count the number of rows of this table and print to the screen.

    I have pasted my code below. Are you able to reference a variable withing a sql statement as i have?

    1. DECLARE
    2. TABLE_HOLDER VARCHAR2(200);
    3. COUNT_OF_ROWS NUMBER;

    4. BEGIN

    5. SELECT TABLE_NAME INTO TABLE_HOLDER FROM ALL_TABLES
    6. WHERE OWNER ='ROB1'AND ROWNUM=1;
    7. DBMS_OUTPUT.PUT_LINE(TABLE_HOLDER);

    8. SELECT COUNT(*)INTO COUNT_OF_ROWS FROM TABLE_HOLDER;
    9. DBMS_OUTPUT.PUT_LINE(COUNT_OF_ROWS);
    10. END;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,599
    Likes Received:
    363
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No. This requires dynamic SQL:

    Code (SQL):
    DECLARE

        TABLE_HOLDER VARCHAR2(200);
        COUNT_OF_ROWS NUMBER;
        SQLSTMT    VARCHAR2(4000);

    BEGIN

        SELECT TABLE_NAME INTO TABLE_HOLDER FROM ALL_TABLES
        WHERE OWNER ='ROB1'AND ROWNUM=1;
        DBMS_OUTPUT.PUT_LINE(TABLE_HOLDER);

        SQLSTMT:='SELECT COUNT(*) FROM '||TABLE_HOLDER;
        EXECUTE IMMEDIATE SQLSTMT INTO COUNT_OF_ROWS;
        DBMS_OUTPUT.PUT_LINE(TABLE_HOLDER||' has '||COUNT_OF_ROWS||' rows.');

    END;
    /
     
    Note that SQLSTMT is a straight 'select count(*)...' query and the INTO clause is moved to the EXECUTE IMMEDIATE command. Also you will see no output until you set SERVEROUTPUT ON with a non-zero size; I usually use this:

    set serveroutput on size 1000000

    to ensure I see all of the output.
     
    Sparamanga likes this.
  3. Sparamanga

    Sparamanga Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    Hi Zargon, thanks , that was very helpful.

    I actually need to get the row counts for all the views in the database and was using the above code initially to test out the concept.

    I have now written some PL/SQL to collect the count(*) from each view in the database and to store this in a table..

    DECLARE
    COUNT_OF_ROWS NUMBER;

    BEGIN
    FOR RRR IN (SELECT VIEW_NAME FROM ALL_VIEWS WHERE OWNER ='DM_REF_RSCE' ORDER BY VIEW_NAME)

    LOOP
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || RRR.VIEW_NAME INTO COUNT_OF_ROWS;

    INSERT INTO DM_REF_VIEWS_ROW_COUNTS (VIEW_NAME, ROW_COUNT, DATE_OF_QUERY)
    VALUES (RRR.VIEW_NAME, COUNT_OF_ROWS, SYSDATE);

    END LOOP;
    END;

    Next I wanted to either create a function or a stored procedure so that I, or someone else can just call this script to run and update the 'VIEWS_ROWS_COUNTS' and possibly then to select * from VIEWS_ROWS_COUNTS to display the updated table (though this is not as important).

    Should I create a stored procedure or a function?

    I tried to create a stored procedure but it didn't seem to work and I wasn't sure if I need to declare any variables with IN, OUT, IN OUT.

    I thought maybe it would just be a case of giving the script a name which can later be called?

    Thanks,

    Rob.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,599
    Likes Received:
    363
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Functions return values and you want to store/update values in another table so you should be considering a procedure and it should be written to both update existing values and insert new values if they exist. This owner can create a new view at any time and you want to be able to track the number of rows any new views contain. You also don't need to return the count into a variable since you can create a dynamic insert or update statement. Here's an example:

    Code (SQL):
    CREATE OR REPLACE PROCEDURE get_view_curr_row_cts IS

        SQLSTMT    VARCHAR2(4000);
        currct    NUMBER;=NULL;

        cursor get_all_views IS
        SELECT VIEW_NAME
        FROM ALL_VIEWS
        WHERE OWNER ='DM_REF_RSCE'
        ORDER BY VIEW_NAME;

    BEGIN

        FOR RRR IN get_all_views loop
            EXECUTE immediste 'select row_count from ref_views_row_counts where view_name = '||rrr.view_name INTO currct;
            IF currct IS NOT NULL THEN
                EXECUTE immediate 'update ref_views_row_counts set row_count = (select count(*) from '||rrr.view_name||'), date_of_query = sysdate where view_name = '||rrr.view_name;
            ELSE
                EXECUTE IMMEDIATE 'insert into ref_views_row_counts SELECT '''||rrr.view_name||''',COUNT(*), sysdate FROM ' || RRR.VIEW_NAME;
            END IF;
        END loop;

    END;
    /
     
    As I don't have a database currently to test this please create this procedure to see that it works.
     
  5. njuro

    njuro Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Poland
    Hi,
    There is a few bugs in code posted by Zargon.

    I've correct them and working code is:
    Code (SQL):
    CREATE OR REPLACE PROCEDURE get_view_curr_row_cts IS

        SQLSTMT    VARCHAR2(4000);
        currct    NUMBER:=NULL;

        cursor get_all_views IS
        SELECT VIEW_NAME
        FROM ALL_VIEWS
        WHERE OWNER ='DM_REF_RSCE'
        ORDER BY VIEW_NAME;

    BEGIN

        FOR RRR IN get_all_views LOOP
            EXECUTE IMMEDIATE 'select COUNT(*) from ref_views_row_counts where view_name = '''||rrr.view_name||'''' INTO currct;
            IF currct > 0 THEN
                EXECUTE IMMEDIATE 'update ref_views_row_counts set row_count = (select count(*) from '||rrr.view_name||'), date_of_query = sysdate where view_name = '''||rrr.view_name||'''';
            ELSE
                EXECUTE IMMEDIATE 'insert into ref_views_row_counts SELECT '''||rrr.view_name||''',COUNT(*), sysdate FROM ' || RRR.VIEW_NAME;
            END IF;
        END LOOP;
    COMMIT; -- OPTIONALLY
    END;
    /
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,599
    Likes Received:
    363
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    My conditions are correct; the Original poster wants to know 0 counts so those should remain; if the view does NOT exist in the table it needs to be inserted.