CODEWITHSUNDEEP
oracle-database
chithra
chithra

Reputation: 77

oracle procedure to list table names and corresponding count

I am stucked with an issue. I have a table called gd_table_order which contains tablenames. And i need to get the table_name and count of each table to a target one. target is a view.

gd_table_order

I only gave an example with 2 columns, there are 10 columns like this.So in the procedure we need to enter this as a parameter.

And there will be corresponsding 10 views to be generate. below is a sample of 2 views.

V_CHECK_RECORDS_AUS

V_CHECK_RECORDS_BEL

V_CHECK_RECORDS_* is the output view name

Could you please help?

Sample Data

Upvotes: 0

Views: 158

Answers (1)

Popeye
Popeye

Reputation: 35900

I am not sure what is exact requirement here, But you can use the following approach to fetch the number of records from each table.

SQL> -- This is sample data
SQL> WITH SAMPLE_DATA(TNAME) AS
  2  (SELECT 'CUSTOMERS' FROM DUAL UNION ALL
  3  SELECT 'INTERVAL_TAB' FROM DUAL)
  4  -- Your query starts from here
  5  SELECT TABLE_NAME,
  6         TO_NUMBER(
  7         EXTRACTVALUE( XMLTYPE(
  8         DBMS_XMLGEN.GETXML('select count(*) c from ' || U.TABLE_NAME)
  9         ), '/ROWSET/ROW/C')) COUNT
 10    FROM USER_TABLES U JOIN SAMPLE_DATA S ON S.TNAME = U.TABLE_NAME;

TABLE_NAME           COUNT
--------------- ----------
CUSTOMERS                1
INTERVAL_TAB             0

SQL>

-- Update

You can generate the view as follows :

-- UPDATED THIS SECTION

CREATE OR REPLACE VIEW V_CHECK_RECORDS_AUS AS
SELECT TABLE_NAME,
       TO_NUMBER(
           EXTRACTVALUE( XMLTYPE(
                   DBMS_XMLGEN.GETXML('select count(*) c from ' 
                      || U.TABLE_NAME || ' WHERE oe_name=''BUL''')
               ), '/ROWSET/ROW/C')) NUM_ROWS
  FROM USER_TAB_COLUMNS U JOIN GD_TABLE_ORDER S ON S.TABLE_NAME_AUS = U.TABLE_NAME 
 WHERE U.COLUMN_NAME = 'OE_NAME';

In the same way you can generate other views.

-- Further Update

CREATE OR REPLACE VIEW V_CHECK_RECORDS_AUS AS
SELECT TABLE_NAME,
       CASE WHEN U.COLUMN_NAME IS NOT NULL THEN TO_NUMBER(
           EXTRACTVALUE( XMLTYPE(
                   DBMS_XMLGEN.GETXML('select count(*) c from ' 
                      || U.TABLE_NAME || ' WHERE ' || U.COLUMN_NAME || '=''BUL''')
               ), '/ROWSET/ROW/C')) 
         ELSE 0 END NUM_ROWS
  FROM GD_TABLE_ORDER S LEFT JOIN USER_TAB_COLUMNS U 
  ON S.TABLE_NAME_AUS = U.TABLE_NAME AND U.COLUMN_NAME = 'OE_NAME';

Upvotes: 1

Related Questions