identifying columns in all the tables with a specific value

oracle 11g

I have many tables that ends with FCT (lets take two tables ABC_FCT, XYZ_FCT) owned by my_schema.


All the tables have the following 4 columns predominatly: REG_ID, USR_ID, CAT_ID, SPR_ID

Now I need a generic sql which gives count of the above 4 ids where the value is -1 and print it as follows:

Tab_name    Col_name    Cnt
ABC_FCT       reg_id          10
ABC_FCT       usr_id           5
ABC_FCT       cat_id           6
ABC_FCT       spr_id           11
XYZ_FCT       reg_id          10
XYZ_FCT       usr_id           5
XYZ_FCT       cat_id           6
XYZ_FCT       spr_id          11
..
..
..
..
Can this be done with generic dynamic sql instead of calculating for every table..

Please let me know if you need any more information..

Thanks
gs79Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
>>where the value is -1
I didn't understand this bit, how will a count of rows result in -1 ???
is the requirement fully stated?
gs79Author Commented:
sorry i think i was not clear

select count(*) from abc_fct where reg_id = -1
select count(*) from abc_fct where usr_id = -1

this way I want to dynamically find the counts for all the tables ending with %FCT for the 4 columns (reg_id, usr_id,cat_id, spr_id

Is there a way to do this with dynamic sql..

thanks
ianmills2002Commented:
I came up with a 2 step process

Create a function that can run dynamic SQL like below...

CREATE OR REPLACE FUNCTION my_get_count (p_table VARCHAR2, p_column VARCHAR2) 
    RETURN NUMBER IS
    query_str VARCHAR2(1000);
    num_count NUMBER;
BEGIN
    query_str := 'SELECT COUNT(*) FROM ' || p_table
        || ' WHERE ' || p_column || ' = -1';
        
    EXECUTE IMMEDIATE query_str
        INTO num_count;
        
    RETURN num_count;
END;

Open in new window



... Then use this function in a select statement.
select 
 x.table_name
,X.COLUMN_NAME
,my_get_count (x.table_name, X.COLUMN_NAME)
from all_tab_columns X
where table_name like '%FCT'
--and owner = '' -- you may need this to limit to your schema only
and X.COLUMN_NAME in ('REG_ID', 'USR_ID','CAT_ID', 'SPR_ID')

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

PortletPaulEE Topic AdvisorCommented:
I think you need to ensure all the relevant fields are present, hence I suggest use of user_tab_columns for this. This selection logic can be borrowed for other approaches of course. Try the following
BEGIN
    FOR i IN (
                SELECT
                TABLE_NAME
                FROM (
                      SELECT
                      DISTINCT TABLE_NAME
                      , COLUMN_NAME
                      , row_number() over (partition BY TABLE_NAME ORDER BY COLUMN_NAME) num_of
                      FROM user_tab_columns
                      WHERE COLUMN_NAME = 'REG_ID'
                      OR COLUMN_NAME = 'USR_ID'
                      OR COLUMN_NAME = 'CAT_ID'
                      OR COLUMN_NAME = 'SPR_ID'
                     )
                WHERE num_of = 4
              )
    LOOP
        EXECUTE IMMEDIATE 'select count(*) from ' || i.TABLE_NAME || ' where reg_id = -1';
    END LOOP;
END;

Open in new window

edit: oh, if you have views and don't want those included then annd this inner join to user_tab_columns
inner join user_tables ut on utc.table_name = ut.table_name

-- think this will exclude views (been a while)
PortletPaulEE Topic AdvisorCommented:
with the suggested inner join...
BEGIN
    FOR i IN (
                SELECT
                TABLE_NAME
                FROM (
                      SELECT
                      DISTINCT utc.TABLE_NAME
                      , utc.COLUMN_NAME
                      , row_number() over (partition BY utc.TABLE_NAME ORDER BY utc.COLUMN_NAME) num_of
                      FROM user_tab_columns utc
                      INNER JOIN user_tables ut ON utc.TABLE_NAME = ut.TABLE_NAME
                      WHERE COLUMN_NAME = 'REG_ID'
                      OR COLUMN_NAME = 'USR_ID'
                      OR COLUMN_NAME = 'CAT_ID'
                      OR COLUMN_NAME = 'SPR_ID'
                     )
                WHERE num_of = 4
              )
    LOOP
        EXECUTE IMMEDIATE 'select count(*) from ' || i.TABLE_NAME || ' where reg_id = -1';
    END LOOP;
END;

Open in new window

PortletPaulEE Topic AdvisorCommented:
humble apologies, 3rd and final attempt. This should output the table_name, which you probably need:
BEGIN
    FOR i IN (
                SELECT
                TABLE_NAME
                FROM (
                      SELECT
                      DISTINCT utc.TABLE_NAME
                      , utc.COLUMN_NAME
                      , row_number() over (partition BY utc.TABLE_NAME ORDER BY utc.COLUMN_NAME) num_of
                      FROM user_tab_columns utc
                      INNER JOIN user_table ut ON utc.TABLE_NAME = ut.TABLE_NAME
                      WHERE utc.COLUMN_NAME = 'REG_ID'
                      OR utc.COLUMN_NAME = 'USR_ID'
                      OR utc.COLUMN_NAME = 'CAT_ID'
                      OR utc.COLUMN_NAME = 'SPR_ID'
                     )
                WHERE num_of = 4
              )
    LOOP
        EXECUTE IMMEDIATE 'select ''' || i.TABLE_NAME ||''' as table_name, count(*) as count_of from ' || i.TABLE_NAME || ' where reg_id = -1';
    END LOOP;
END;

Open in new window

sdstuberCommented:
SELECT table_name,
       column_name,
       TO_NUMBER(
           EXTRACTVALUE(
               DBMS_XMLGEN.getxmltype(
                   'select count(*) X from ' || table_name || ' where ' || column_name || ' = -1'
               ),
               '/ROWSET/ROW/X'
           )
       )
           COUNT
  FROM all_tab_cols
 WHERE owner = 'MY_SCHEMA'
   AND table_name LIKE '%FCT'
   AND column_name IN ('REG_ID', 'USR_ID', 'CAT_ID', 'SPR_ID')
ORDER BY table_name, column_name;
PortletPaulEE Topic AdvisorCommented:
ah, I see I have it wrong, the count is per field - duh! Oh well.
PortletPaulEE Topic AdvisorCommented:
hats off to you sdstuber!!

I offer up a variant inspired by xmlgen:
SELECT
  TABLE_NAME
, TO_NUMBER(
    EXTRACTVALUE(
      DBMS_XMLGEN.getxmltype(
        'select count(distinct REG_ID || USR_ID || CAT_ID || SPR_ID) X from ' || table_name || ' where REG_ID = -1'
      ),
      '/ROWSET/ROW/X'
    )
  )
  as DISTINCT_COUNT_OF
FROM (
  SELECT
  DISTINCT utc.TABLE_NAME
  , utc.COLUMN_NAME
  , row_number() over (partition BY utc.TABLE_NAME ORDER BY utc.COLUMN_NAME) num_of
  FROM user_tab_columns utc
  INNER JOIN user_tables ut ON utc.TABLE_NAME = ut.TABLE_NAME
  WHERE utc.COLUMN_NAME = 'REG_ID'
  OR utc.COLUMN_NAME = 'USR_ID'
  OR utc.COLUMN_NAME = 'CAT_ID'
  OR utc.COLUMN_NAME = 'SPR_ID'
)
WHERE num_of = 4

Open in new window

PortletPaulEE Topic AdvisorCommented:
woo hooo, this xmlgen is too enticing - apologies, but one last one. For an output like:
TABLE_NAME DISTINCT4 REG_ID USR_ID CAT_ID SPR_ID
SELECT
  TABLE_NAME
, TO_NUMBER(
    EXTRACTVALUE(
      DBMS_XMLGEN.getxmltype(
        'select count(distinct REG_ID || USR_ID || CAT_ID || SPR_ID) DISTINCT4 from ' || table_name 
      ),
      '/ROWSET/ROW/DISTINCT4'
    )
  )
  as DISTINCT_COUNT_OF
, TO_NUMBER(
     EXTRACTVALUE(
        DBMS_XMLGEN.getxmltype(
             'select count(*) REG_ID from ' || table_name || ' where REG_ID = -1'
         ),
         '/ROWSET/ROW/REG_ID'
     )
  )
  AS REG_ID
, TO_NUMBER(
     EXTRACTVALUE(
        DBMS_XMLGEN.getxmltype(
             'select count(*) USR_ID from ' || table_name || ' where USR_ID = -1'
         ),
         '/ROWSET/ROW/USR_ID'
     )
  )
  AS USR_ID
, TO_NUMBER(
     EXTRACTVALUE(
        DBMS_XMLGEN.getxmltype(
             'select count(*) CAT_ID from ' || table_name || ' where CAT_ID = -1'
         ),
         '/ROWSET/ROW/CAT_ID'
     )
  )
  AS CAT_ID
, TO_NUMBER(
     EXTRACTVALUE(
        DBMS_XMLGEN.getxmltype(
             'select count(*) SPR_ID from ' || table_name || ' where SPR_ID = -1'
         ),
         '/ROWSET/ROW/SPR_ID'
     )
  )
  AS SPR_ID
FROM (
      SELECT
      DISTINCT utc.TABLE_NAME
      , utc.COLUMN_NAME
      , row_number() over (partition BY utc.TABLE_NAME ORDER BY utc.COLUMN_NAME) num_of
      FROM user_tab_columns utc
      INNER JOIN user_tables ut ON utc.TABLE_NAME = ut.TABLE_NAME
      WHERE utc.COLUMN_NAME = 'REG_ID'
      OR utc.COLUMN_NAME = 'USR_ID'
      OR utc.COLUMN_NAME = 'CAT_ID'
      OR utc.COLUMN_NAME = 'SPR_ID'
)
WHERE num_of = 4

Open in new window

no points pl.
gs79Author Commented:
I tried all the solutions and all the solution works fine. Just curious which would be faster..calling function or using xmlgen type..

Please advice..

Thanks
ianmills2002Commented:
I personally don't know. I have never used the DBMS_XMLGEN.getxmltype() function. I would try a sample if data and test both methods to see which is faster.

Without testing, I would guess the DBMS_XMLGEN.getxmltype() function because it is precompiled.

In my experience, some efficiency techniques work fine for most situations, but there is always a database design or query that breaks the normal rules of efficient coding.
PortletPaulEE Topic AdvisorCommented:
execute immediate is a bit 'kludgy' really, you prepare a string that is to be a query, then execute each query - so that approach is performing a lot of individual queries.

My guess is the DBMS_XMLGEN will outshine the (more old fashioned) execute immediate.
I don't get many opportunities to use that function so I don't think of it as a first choice - but will attempt to do so in the future.

the DBMS_XMLGEN is still performing quite a number of individual queries then parsing each to access a specific node in the xml output, but those XML functions are quite fast.

Perhaps you could do explain plans as a way to assess the relative merits?
sdstuberCommented:
DBMS_XMLGEN is expensive,  for one, you do still need to parse and execute (effectively doing execute immediate anyway) plus the results are turned into xml, then the xml is parsed to pull out the values you really need.

However, a "run sql everywhere" type action is inherently an expensive operation so the added weight of dbms_xmlgen is probably negligible; then again, the added weight of looping in your own pl/sql is probably negligible as well.

I use dbms_xmlgen simply because a sql result set is generally easier to work with.
I do not use it for efficiency.

If you must get this information quickly, then time them, use autotrace or other session statistic measures and determine for yourself on your real data which is most efficient.
gs79Author Commented:
@ianmills

Is there a way to get the total count as well in same function call.. I tried passing the primary key column..and PK indicator as a parameter to function and modified the function as below to get a total couunt if the pased column is PK..But since many of the tables do not have a PK defined, for some of the tables the total count came blank..

CREATE OR REPLACE FUNCTION my_get_count (p_table VARCHAR2, p_column VARCHAR2, PK_IND)
    RETURN NUMBER IS
    query_str VARCHAR2(1000);
    num_count NUMBER;
BEGIN
if PK_IND = 'NP' then
    query_str := 'SELECT COUNT(*) FROM ' || p_table
        || ' WHERE ' || p_column || ' = -1';
elsif PK_IND = 'P'
   query_str := 'SELECT COUNT(*) FROM ' || p_table
 end if;
       
    EXECUTE IMMEDIATE query_str
        INTO num_count;
       
    RETURN num_count;
END;

I joined with all_constraints and all_constraint_cols to assign a PK_IND for a column..

Please let me know if you have any thoughts

Thanks
sdstuberCommented:
to get a total count, how about something like this...


select table_name, column_name, cnt,  sum(cnt) over() total_count from
(SELECT table_name,
       column_name,
       TO_NUMBER(
           EXTRACTVALUE(
               DBMS_XMLGEN.getxmltype(
                   'select count(*) X from ' || table_name || ' where ' || column_name || ' = -1'
               ),
               '/ROWSET/ROW/X'
           )
       )
           cnt
  FROM all_tab_cols
 WHERE owner = 'MY_SCHEMA'
   AND table_name LIKE '%FCT'
   AND column_name IN ('REG_ID', 'USR_ID', 'CAT_ID', 'SPR_ID')
ORDER BY table_name, column_name)
gs79Author Commented:
@sdstuber

Thank you for your response. I think i was not clear. I am not looking for total of the columns with -1. I want to get the total count of the whole table as shown below

Tab_name    Col_name    Cnt
ABC_FCT       total             5500
ABC_FCT       reg_id          10
ABC_FCT       usr_id           5
ABC_FCT       cat_id           6
ABC_FCT       spr_id           11
XYZ_FCT       total              6000
XYZ_FCT       reg_id          10
XYZ_FCT       usr_id           5
XYZ_FCT       cat_id           6
XYZ_FCT       spr_id          11

Though total is not the name of a column in the table, i want to display the 'total' under column name so that I can pivot this.

Hence I passed the PK of the table to the function so that I could get the total count. Though I am successful getting what I want in the above format, some of the tables do not have PK and hence total is blank..

On the other note I will run both methods, calling function and xmlgen to measure the performance..

thanks
PortletPaulEE Topic AdvisorCommented:
>>some of the tables do not have PK and hence total is blank..
just count(*) if all you want is total rows, no need to specify a field.
gs79Author Commented:
I figured this out..Thanks for the great answers..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.