iSelfSchooling - This procedure is an example of how to use the DBMS_SQL package

This procedure is an example of how to use the DBMS_SQL package. You pass the table name and run the procedure. The procedure will return the number of rows in the table.

CREATE OR REPLACE PROCEDURE my_count (p_table in VARCHAR2)
is
    v_cursor NUMBER;
    v_sql VARCHAR2(200);
    v_result NUMBER;
    v_count NUMBER;
BEGIN
    -- Build a query
    v_sql := 'SELECT count(*) numofrec FROM ' || p_table;

    -- Open the cursor.
    v_cursor := DBMS_SQL.OPEN_CURSOR;

    -- Parse the query.
    DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.v7);

    -- Set up the columns
    DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_count);

    -- Execute the query
    v_result := DBMS_SQL.EXECUTE(v_cursor);

    -- Print the output headers
    DBMS_OUTPUT.NEW_LINE;
    DBMS_OUTPUT.NEW_LINE;

    DBMS_OUTPUT.PUT ('Number of Rows in your ' || p_table || ' is: ');

    -- Fetch the rows
    LOOP
        EXIT WHEN DBMS_SQL.FETCH_ROWS (v_cursor) = 0;
        DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_count);
    END LOOP;

    -- Print the number of rows.
    DBMS_OUTPUT.PUT_LINE(v_count);

    -- Close the cursor.
    DBMS_SQL.CLOSE_CURSOR(v_cursor);

END my_count;
/

How to execute the procedure.

Before executing the procedure. Set the serveroutput parameter
to ON, then execute the procedure.

SET SERVEROUTPUT ON
EXECUtE my_count('user_tables');

Or you can use binding variable to execute the procedure.

-- define bind variable. (this is an example of how to define a bind variable)
VARIABLE v_mytable VARCHAR2(100)

-- Assign the table name.
BEGIN
:    v_mytable := 'user_tables';
END;
/

-- Check your define bind variable.
PRINT v_mytable
--or--
PRINT :v_mytable

-- Execute the my_count procedure using the bind variable.
EXECUTE my_count(:v_mytable);