Your organization wants you to write a stored procedure to pass the table name and get back the number of records that table contains. The SELECT statement must be created a dynamically, since you don’t know what table you are getting statistics from. You should write your function so that your client can display all of the tables’ name, plus the number of records contained each table.

-- Hands-On 07 (Using Native Dynamic SQL) 

SET ECHO ON
CLEAR SCR
-- Connect to sqlplus as the iself user.
--
pause

CONNECT iself/schooling
pause

CLEAR SCR
-- Set the pagesize to 55 and the linesize to 100.
--
pause

SET PAGESIZE 55 LINESIZE 100
pause

CLEAR SCR
-- Write a stored procedure, to pass the table name
-- as a parameter, and get back the number of records
-- that table contains.
--
pause


CREATE OR REPLACE FUNCTION get_total_recs (loc VARCHAR2) 
RETURN NUMBER IS 
Query_str VARCHAR2(1000); 
Num_of_recs NUMBER; 
BEGIN 
Query_str := 'SELECT COUNT(*) FROM ' || loc; 
EXECUTE IMMEDIATE query_str INTO num_of_recs; 
RETURN num_of_recs; 
END; 
/ 
-- Notice that the native dynamic SQL was used
-- in the stored procedure.
pause

CLEAR SCR
-- Test your function with a single table. 
--
pause

SELECT get_total_recs('emp') FROM dual
/
pause

CLEAR SCR
-- Test your function with multiple tables. 
--
pause

SELECT table_name as "Table Name",
get_total_recs(table_name) as "Number of Records"
FROM user_tables
/
pause

CLEAR SCR
-- Drop the get_total_recs function.
--
pause

DROP FUNCTION get_total_recs
/
pause

CLEAR SCR
-- Now, practice this Hands-On over and over
-- until you become a master at it.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck!
pause