Oracle Diagnostic Queries
It’s always a challenge when you want to build your own Oracle SQL Tools. I was asked how you could synchronize multiple cursors into a single source. The answer is quite simple, you write an Oracle object type to represent a record structure, an Oracle list of the record structure, and a stored function to return the list of the record structure.
For this example, you create the following table_struct
object type and a table_list
collection type:
/* Drop the types from most to least dependent. */ DROP TYPE table_list; DROP TYPE table_struct; /* Create the record type structure. */ CREATE OR REPLACE TYPE table_struct IS OBJECT ( table_name VARCHAR2(30) , column_cnt NUMBER , row_cnt NUMBER ); / /* Create the collection of a record type structure. */ CREATE OR REPLACE TYPE table_list IS TABLE OF table_struct; / |
The following listing
function now reads all table names from the user_tables
view. A subordinate cursor reads the user_tab_columns
view for the number of columns in a table. A Native Dynamic SQL (NDS) cursor counts the number of rows in each tables found in the .
/* Create the listing function. */ CREATE OR REPLACE FUNCTION listing RETURN table_list IS /* Variable list. */ lv_column_cnt NUMBER; lv_row_cnt NUMBER; /* Declare a statement variable. */ stmt VARCHAR2(200); /* Declare a system reference cursor variable. */ lv_refcursor SYS_REFCURSOR; lv_table_cnt NUMBER; /* Declare an output variable. */ lv_list TABLE_LIST := table_list(); /* Declare a table list cursor that excludes APEX tables. */ CURSOR c IS SELECT table_name FROM user_tables WHERE table_name NOT IN ('DEPT','EMP','APEX$_ACL','APEX$_WS_WEBPG_SECTIONS','APEX$_WS_ROWS' ,'APEX$_WS_HISTORY','APEX$_WS_NOTES','APEX$_WS_LINKS' ,'APEX$_WS_TAGS','APEX$_WS_FILES','APEX$_WS_WEBPG_SECTION_HISTORY' ,'DEMO_USERS','DEMO_CUSTOMERS','DEMO_ORDERS','DEMO_PRODUCT_INFO' ,'DEMO_ORDER_ITEMS','DEMO_STATES'); /* Declare a column count. */ CURSOR cnt ( cv_table_name VARCHAR2 ) IS SELECT table_name , COUNT(column_id) AS cnt_columns FROM user_tab_columns WHERE table_name = cv_table_name GROUP BY table_name; BEGIN /* Read through the data set of non-environment variables. */ FOR i IN c LOOP /* Count the columns of a table. */ FOR j IN cnt(i.table_name) LOOP lv_column_cnt := j.cnt_columns; END LOOP; /* Declare a statement. */ stmt := 'SELECT COUNT(*) AS column_cnt FROM '||i.table_name; /* Open the cursor and write set to collection. */ OPEN lv_refcursor FOR stmt; LOOP FETCH lv_refcursor INTO lv_table_cnt; EXIT WHEN lv_refcursor%NOTFOUND; lv_list.EXTEND; lv_list(lv_list.COUNT) := table_struct( table_name => i.table_name , column_cnt => lv_column_cnt , row_cnt => lv_table_cnt ); END LOOP; END LOOP; RETURN lv_list; END; / |
The following query pulls the processed data set as the function’s result:
COL table_name FORMAT A20 HEADING "Table Name" COL column_cnt FORMAT 9,999 HEADING "Column #" COL row_cnt FORMAT 9,999 HEADING "Row #" SELECT table_name , column_cnt , row_cnt FROM TABLE(listing); |
It returns the following result set:
Table Name Column # Row # -------------------- -------- ------ SYSTEM_USER 11 5 COMMON_LOOKUP 10 49 MEMBER 9 10 CONTACT 10 18 ADDRESS 10 18 STREET_ADDRESS 8 28 TELEPHONE 11 18 RENTAL 8 4,694 ITEM 14 93 RENTAL_ITEM 9 4,703 PRICE 11 558 TRANSACTION 12 4,694 CALENDAR 9 300 AIRPORT 9 6 ACCOUNT_LIST 8 200 15 rows selected. |
As always, I hope this helps those trying to work with the Oracle database.