I have a list of tables, i need to pass those table based on column name.
For example if my table has CLASSID it will go block 2, if table has OBJID it will go block 1, if table has KOPPKT it will go block 3, but if any table has CLASSID and OBJID both, it should go block 2 not block 1.
Similarly if any table has KOPPKT and OBJID it should go block 1 not block 3. Here i am facing issue to send tables into proper block.
The code i am trying to achieve but not getting the desired output.
desc TABLE1;
Name Null Type
------- -------- ----------
CLASSID NOT NULL NUMBER(10)
KOPPKT NOT NULL NUMBER(10)
ID NOT NULL NUMBER(10)
desc TABLE2;
Name Null Type
------- -------- ----------
CLASSID NOT NULL NUMBER(10)
OBJID NOT NULL NUMBER(10)
desc TABLE3;
Name Null Type
------- -------- ----------
KOPPKT NOT NULL NUMBER(10)
OBJID NOT NULL NUMBER(10)
Here Table1 and table2 should go block 2 as it has CLASSID, table3 should go block 1 as it has OBJID
set serveroutput on
declare
v_exporttable VARCHAR2(100):='TABLE1';
v_name varchar2(100);
begin
FOR rec IN
(
select distinct column_name from all_tab_cols
where table_name = v_exporttable
) loop
if (rec.column_name ='OBJID') then
DBMS_OUTPUT.PUT_LINE('Table has OBJID'); /* block 1 */
elsif (rec.column_name ='CLASSID') then
DBMS_OUTPUT.PUT_LINE('Table has Classid'); /* block 2 */
elsif (rec.column_name = 'KOPPKT') then
DBMS_OUTPUT.PUT_LINE('Table has KOPPKT'); /* block 3 */
end if;
end loop;
end;
/
Passing Table1 first output i got. which means it goes both block 2 and block 3.but i want to pass only block2
PL/SQL procedure successfully completed.
Table has Classid
Table has KOPPKT
Similarly if I pass Table 2 I am getting this. which means it goes both block 1 and block 2.but i want to pass only block2
PL/SQL procedure successfully completed.
Table has OBJID
Table has CLASSID
Similarly if I pass Table 3 I am getting this. which means it goes both block 1 and block 3.but i want to pass only block2
PL/SQL procedure successfully completed.
Table has OBJID
Table has KOPPKT
I have tried with below condition also still it not working
(rec.column_name ='OBJID' and rec.column_name <>'CLASSID')
Advertisement
Answer
Find all the columns of interest for your table with LISTAGG
and then check for individual combinations in CASE
statement. Put condition on CLASSID
before condition on CLASSID
and something:
with tab_cols as (
select table_name,
listagg(column_name, '|') within group(order by 1) as cols
from all_tab_cols
where table_name in (<your list>)
and column_name in ('CLASSID', 'OBJID', 'KOPPKT')
group by table_name
)
select table_name,
case
when instr(cols, 'CLASSID') > 0 then 2
when instr(cols, 'OBJID') > 0 then 1
when instr(cols, 'KOPPKT') > 0 then 3
end as block_number
from tab_cols