|
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.
By:
John Kazerooni
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);
Good Luck!
|