For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
Why does JDeveloper 12c remove @HandlerChain annotation when a project is opened? how to solve this issue?
Hi, @jackbox1098 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.
Hi, @jackbox1098 '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
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.
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
@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
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.
that have these specific columns "specific columns" (COL_A,COL_B,COL_C) the constants in script? example:
OWNER TABLE_NAME COLUMN_NAME STAX DEPT DEPTNO STAX DEPT DNAME STAX DEPT LOC
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!
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
@solomon-yakobson Thanks a lot! This should do it. I will test it in our system tomorrow. Feedback to follow.