everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

Manuscript: 10

 

 

Topics:  Hands-On 07

 

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.

 

   
   

Manuscript

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

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

CONNECT oracle/learning
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

 

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.