Forum Stats

  • 3,840,087 Users
  • 2,262,565 Discussions
  • 7,901,148 Comments

Discussions

Categories

Check tables with specific columns

user545194
user545194 Member Posts: 608 Bronze Badge
in SQL & PL/SQL

Hi,

"Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.11.0.0.0"

We need to select all tables with specific columns and add them to an audit table.

Problem: When querying the dba_tab_cols againt the col_audit_tab only the matching tables are returned. We need to find all tables that do not yet exist in the col_audit_tab and insert them.

We have attached some test data.

Thanks for your help!

test_data.zip


Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,612 Red Diamond
    edited Jun 20, 2022 3:29PM Answer ✓

    We need to add all table names to the audit table that are in the data dictionary. No deletions, only inserts.

    So if column was dropped you'll still have it in audit table? Anyway, something like:

    MERGE
  INTO COL_AUDIT_TAB T
  USING(
        SELECT  OWNER,
                TABLE_NAME,
                MAX(
                    CASE
                      WHEN COLUMN_NAME = 'COL_A' THEN 'COL_A'
                    END
                   ) COL_A,
                MAX(
                    CASE
                      WHEN COLUMN_NAME = 'COL_B' THEN 'COL_B'
                    END
                   ) COL_B,
                MAX(
                    CASE
                      WHEN COLUMN_NAME = 'COL_C' THEN 'COL_C'
                    END
                   ) COL_C
          FROM  DBA_TAB_COLS
          WHERE COLUMN_NAME IN ('COL_A','COL_B','COL_C')
          GROUP BY OWNER,
                   TABLE_NAME
       ) S
  ON (
      T.OWNER = S.OWNER,
      T.TABLE_NAME = S.TABLE_NAME
     )
  WHEN NOT MATCHED
    THEN
      INSERT
        VALUES(
               S.OWNER,
               S.TABLE_NAME
               S.COL_A,
               S.COL_B,
               S.COL_C
              )
/

    SY.

    P.S. Added ON clause I initially missed

    user545194
    · Share on TwitterShare on Facebook

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,267 Red Diamond

    Hi, @user545194

    When querying the dba_tab_cols againt the col_audit_tab only the matching tables are returned. We need to find all tables that do not yet exist in the col_audit_tab and insert them.

    To find rows that are in one table but not another, you can use MINUS, NOT EXISTS, NOT IN or outer join.

    We have attached some test data.

    Post the sample data, the exact results you want from that sample data, and an explanation of how you get those results from that data right in this space. Do not use attachments. Not everyone who wants to help you can or will open attahements.

    · Share on TwitterShare on Facebook
  • Stax
    Stax Member Posts: 138 Bronze Badge

    Hi, @user545194

    'COL_A','COL_B','COL_C' is constant?

    SELECT

      tc.table_name,

      MAX(

        CASE

          WHEN tc.column_name = 'COL_A' THEN

            tc.column_name

          ELSE

            NULL

        END

      ) AS col_a,

      MAX(

        CASE

          WHEN tc.column_name = 'COL_B' THEN

            tc.column_name

          ELSE

            NULL

        END

      ) AS col_b,

      MAX(

        CASE

          WHEN tc.column_name = 'COL_C' THEN

            tc.column_name

          ELSE

            NULL

        END

      ) AS col_c

    FROM

      user_tab_cols tc

      --RIGHT JOIN myuser.col_audit_tab  at ON tc.table_name = at.table_name

    WHERE

      tc.table_name<>'COL_AUDIT_TAB' -- and owner='MYUSER'

      and tc.column_name in ('COL_A','COL_B','COL_C')

      and not exists (

        select 1 

        from col_audit_tab at

        where tc.table_name = at.table_name and (tc.column_name=at.COL_A or tc.column_name=at.COL_B or tc.column_name=at.COL_C)

        )

    GROUP By

      tc.table_name

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,612 Red Diamond

    OP, if I got it right, is looking for both ways changes - additions to audit table and removal of what is no longer valid from audit table. Therefore it should be MERGE into audit table using full outer join between DBA_TAB_COLS and audit table. That is if OP wants to store results in a table. I'd look into creating a view as full outer join between DBA_TAB_COLS and audit table.

    SY.

    · Share on TwitterShare on Facebook
  • user545194
    user545194 Member Posts: 608 Bronze Badge

    Thanks to all for your feedback!

    @Stax Thanks for the script. Still not returning the table names that are in the data dictionary, but not in the audit table.

    @Solomon Yakobson We need to add all table names to the audit table that are in the data dictionary. No deletions, only inserts.

    Tables with the mentioned columns above must be saved in the col_audit_tab; i.e.

    -> Audit table columns: Table_name col_a col_b col_c

    -> Entries: Key_tab col_a col_b col_c

    Key_tab2 col_a null col_c

    · Share on TwitterShare on Facebook
  • Stax
    Stax Member Posts: 138 Bronze Badge

    @Stax Thanks for the script. Still not returning the table names that are in the data dictionary, but not in the audit table.

    I do not understand what is needed, you can example

    ps

    there is no information about KEY_COL_TAB2.COL_B in col_audit_tab, so the script returned it

    · Share on TwitterShare on Facebook
  • user545194
    user545194 Member Posts: 608 Bronze Badge

    Our goal is to query the dba_tab_cols and save all tables in the audit table that have these specific columns. The table names here are only examples.

    We can use different table and column names i.e. Labels, Handbooks, Services. ID_CLIENT1, ID_CLIENT2, ID_CLIENT3 to make it clearer.

    · Share on TwitterShare on Facebook
  • Stax
    Stax Member Posts: 138 Bronze Badge

    that have these specific columns

    "specific columns" (COL_A,COL_B,COL_C) the constants in script?

    example:

    1) exist the table DEPT, not present in  in the audit table

    OWNER	TABLE_NAME	COLUMN_NAME
STAX	DEPT		DEPTNO
STAX	DEPT		DNAME
STAX	DEPT		LOC

    2) exist the table Key_tab2 and present in col_audit_tab (COL_B not in the audit table)

    that should return the script?

    · Share on TwitterShare on Facebook
  • user545194
    user545194 Member Posts: 608 Bronze Badge

    The output (COL_AUDIT_TAB) should be like this:

    OWNER TABLE_NAME ID_CLIENT1 ID_CLIENT2 ID_CLIENT3

    USER1 LABELS ID_CLIENT1 NULL ID_CLIENT3

    USER1 SERVICES NULL ID_CLIENT2 NULL

    Important is that all tables are saved that have at least 1 of these columns.

    Thanks!

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,612 Red Diamond
    edited Jun 20, 2022 3:29PM Answer ✓

    We need to add all table names to the audit table that are in the data dictionary. No deletions, only inserts.

    So if column was dropped you'll still have it in audit table? Anyway, something like:

    MERGE
  INTO COL_AUDIT_TAB T
  USING(
        SELECT  OWNER,
                TABLE_NAME,
                MAX(
                    CASE
                      WHEN COLUMN_NAME = 'COL_A' THEN 'COL_A'
                    END
                   ) COL_A,
                MAX(
                    CASE
                      WHEN COLUMN_NAME = 'COL_B' THEN 'COL_B'
                    END
                   ) COL_B,
                MAX(
                    CASE
                      WHEN COLUMN_NAME = 'COL_C' THEN 'COL_C'
                    END
                   ) COL_C
          FROM  DBA_TAB_COLS
          WHERE COLUMN_NAME IN ('COL_A','COL_B','COL_C')
          GROUP BY OWNER,
                   TABLE_NAME
       ) S
  ON (
      T.OWNER = S.OWNER,
      T.TABLE_NAME = S.TABLE_NAME
     )
  WHEN NOT MATCHED
    THEN
      INSERT
        VALUES(
               S.OWNER,
               S.TABLE_NAME
               S.COL_A,
               S.COL_B,
               S.COL_C
              )
/

    SY.

    P.S. Added ON clause I initially missed

    user545194
    · Share on TwitterShare on Facebook
  • user545194
    user545194 Member Posts: 608 Bronze Badge

    @Solomon Yakobson Thanks a lot! This should do it. I will test it in our system tomorrow. Feedback to follow.

    · Share on TwitterShare on Facebook