How to generate a sql code that creates a view from others views?

Question

I have a "General" view which an union of several "sub" views. I would like to be able to generate the code of the creation of the "General" view everytime a new "sub" view (NEW_SUB_VIEW) is added to the schema. The code for the "General" view is like this:

CREATE OR REPLACE FORCE EDITIONABLE VIEW "DV2_OBIDMT"."F_GENERAL_DATA_QLTY" 
("SRC_SYS_ID", "SOFT_RULE_NAME", "ENTITY_NAME", "DATE_", "PASSES", "FAILS") 
AS 
( select distinct 
SRC.SRC_SYS_ID,
EN.SOFT_RULE_NAME,
EN.ENTITY_NAME,
(to_date(to_char(SRC.LDTS,'DD-MM-YY'))) as date_,
sum(case when SRC.QLTY_TEST= 'Pass' then 1 else 0 end) as Passes, 
sum(case when SRC.QLTY_TEST= 'Fail' then 1 else 0 end) as Fails
from "DV2_OBIADM".IM_LUT_ENTITY_SOFT_RULES EN, SAT_CNTRCT_OTH_GCP_DQ SRC
WHERE EN.ENTITY_NAME = 'SAT_CNTRCT_OTH_GCP_DQ'
GROUP BY SRC.SRC_SYS_ID, EN.ENTITY_NAME, EN.SOFT_RULE_NAME,
(to_date(to_char(SRC.LDTS,'DD-MM-YY')))


UNION 

select distinct 
SRC.SRC_SYS_ID,
EN.SOFT_RULE_NAME,
EN.ENTITY_NAME,
(to_date(to_char(SRC.LDTS,'DD-MM-YY'))) as date_,
sum(case when SRC.QLTY_TEST= 'Pass' then 1 else 0 end) as Passes, 
sum(case when SRC.QLTY_TEST= 'Fail' then 1 else 0 end) as Fails
from "DV2_OBIADM".IM_LUT_ENTITY_SOFT_RULES EN, SAT_CNTRCT_N4_DQ SRC
WHERE EN.ENTITY_NAME = 'SAT_CNTRCT_N4_DQ'
GROUP BY SRC.SRC_SYS_ID, EN.ENTITY_NAME, EN.SOFT_RULE_NAME,
(to_date(to_char(SRC.LDTS,'DD-MM-YY')))

I would like that the sql code generated would add an UNION with the select of the new sub view like this:

CREATE OR REPLACE FORCE EDITIONABLE VIEW "DV2_OBIDMT"."F_GENERAL_DATA_QLTY" 
("SRC_SYS_ID", "SOFT_RULE_NAME", "ENTITY_NAME", "DATE_", "PASSES", "FAILS") 
AS 
( select distinct 
SRC.SRC_SYS_ID,
EN.SOFT_RULE_NAME,
EN.ENTITY_NAME,
(to_date(to_char(SRC.LDTS,'DD-MM-YY'))) as date_,
sum(case when SRC.QLTY_TEST= 'Pass' then 1 else 0 end) as Passes, 
sum(case when SRC.QLTY_TEST= 'Fail' then 1 else 0 end) as Fails
from "DV2_OBIADM".IM_LUT_ENTITY_SOFT_RULES EN, SAT_CNTRCT_OTH_GCP_DQ SRC
WHERE EN.ENTITY_NAME = 'SAT_CNTRCT_OTH_GCP_DQ'
GROUP BY SRC.SRC_SYS_ID, EN.ENTITY_NAME, EN.SOFT_RULE_NAME,
(to_date(to_char(SRC.LDTS,'DD-MM-YY')))


UNION 

select distinct 
SRC.SRC_SYS_ID,
EN.SOFT_RULE_NAME,
EN.ENTITY_NAME,
(to_date(to_char(SRC.LDTS,'DD-MM-YY'))) as date_,
sum(case when SRC.QLTY_TEST= 'Pass' then 1 else 0 end) as Passes, 
sum(case when SRC.QLTY_TEST= 'Fail' then 1 else 0 end) as Fails
from "DV2_OBIADM".IM_LUT_ENTITY_SOFT_RULES EN, SAT_CNTRCT_N4_DQ SRC
WHERE EN.ENTITY_NAME = 'SAT_CNTRCT_N4_DQ'
GROUP BY SRC.SRC_SYS_ID, EN.ENTITY_NAME, EN.SOFT_RULE_NAME,
(to_date(to_char(SRC.LDTS,'DD-MM-YY')))

UNION 

select distinct 
SRC.SRC_SYS_ID,
EN.SOFT_RULE_NAME,
EN.ENTITY_NAME,
(to_date(to_char(SRC.LDTS,'DD-MM-YY'))) as date_,
sum(case when SRC.QLTY_TEST= 'Pass' then 1 else 0 end) as Passes, 
sum(case when SRC.QLTY_TEST= 'Fail' then 1 else 0 end) as Fails
from "DV2_OBIADM".IM_LUT_ENTITY_SOFT_RULES EN, **NEW_SUB_VIEW**SRC
WHERE EN.ENTITY_NAME = '**NEW_SUB_VIEW**'
GROUP BY SRC.SRC_SYS_ID, EN.ENTITY_NAME, EN.SOFT_RULE_NAME,
(to_date(to_char(SRC.LDTS,'DD-MM-YY')))

I hope that was clear, Please help me with I'm not really that ggod in SQL, I don't know if I should add a trigger or create a procedure!

Thank you!

Answer 1

Your requirements are not very clear. Anyway, this trigger below will create new VIEW automatically every time a new view is added to your schema.

CREATE OR REPLACE TYPE VARCHAR_TABLE_TYPE AS TABLE OF VARCHAR2(100);

CREATE OR REPLACE TRIGGER T_NEW_VIEW 
    AFTER CREATE ON SCHEMA
    WHEN (ora_dict_obj_type = 'VIEW')
DECLARE 
    sqlcmd CLOB;
    entities VARCHAR_TABLE_TYPE;
BEGIN    

    IF UPPER(ora_dict_obj_name) = 'F_GENERAL_DATA_QLTY' THEN
        -- avoid recursive calling of this trigger!!!
        RETURN;     
    END IF;

    SELECT view_Name
    BULK COLLECT INTO entities 
    FROM NEW_SUB_VIEWS;

    -- "General view" components:
    entities := entities MULTISET UNION DISTINCT VARCHAR_TABLE_TYPE('SAT_CNTRCT_OTH_GCP_DQ', 'SAT_CNTRCT_N4_DQ');

    sqlcmd := 'CREATE OR REPLACE FORCE VIEW DV2_OBIDMT.F_GENERAL_DATA_QLTY '||CHR(13);
    sqlcmd := sqlcmd ||'  (SRC_SYS_ID, SOFT_RULE_NAME, ENTITY_NAME, DATE_, PASSES, FAILS) AS '||CHR(13);
    FOR i IN entities.FIRST..entities.LAST LOOP
        IF i > 1 THEN
            sqlcmd := sqlcmd ||'UNION '||CHR(13);
        END IF;
        sqlcmd := sqlcmd ||'SELECT SRC.SRC_SYS_ID, EN.SOFT_RULE_NAME, EN.ENTITY_NAME, TRUNC(SRC.LDTS) as date_, '||CHR(13)||
            'SUM(CASE WHEN SRC.QLTY_TEST= ''Pass'' THEN 1 ELSE 0 END) as Passes, '||CHR(13)||
            'SUM(CASE WHEN SRC.QLTY_TEST= ''Fail'' THEN 1 ELSE 0 END) as Fails '||CHR(13);
        sqlcmd := sqlcmd || 'FROM DV2_OBIADM.IM_LUT_ENTITY_SOFT_RULES EN '||CHR(13);
        sqlcmd := sqlcmd || '   CROSS JOIN '||entities(i)||' SRC '||CHR(13);
        sqlcmd := sqlcmd || 'WHERE EN.ENTITY_NAME = '''||entities(i)||''''||CHR(13);
        sqlcmd := sqlcmd || 'GROUP BY SRC.SRC_SYS_ID, EN.ENTITY_NAME, EN.SOFT_RULE_NAME, TRUNC(SRC.LDTS) '||CHR(13);
    END LOOP;   
    DBMS_OUTPUT.PUT_LINE(sqlcmd); --> Just to verify and detect errors
    EXECUTE IMMEDIATE sqlcmd;

END;
/

However, I think you should review your over-all design, it might be not the best one.

Answer 2

So if I understand you correctly the only part that is changing in your "subviews" is the condition

WHERE EN.ENTITY_NAME = '**NEW_SUB_VIEW**'

if that is the case, why not implement your "general view" as

select distinct 
SRC.SRC_SYS_ID,
EN.SOFT_RULE_NAME,
EN.ENTITY_NAME,
(to_date(to_char(SRC.LDTS,'DD-MM-YY'))) as date_,
sum(case when SRC.QLTY_TEST= 'Pass' then 1 else 0 end) as Passes, 
sum(case when SRC.QLTY_TEST= 'Fail' then 1 else 0 end) as Fails
from "DV2_OBIADM".IM_LUT_ENTITY_SOFT_RULES EN, SAT_CNTRCT_N4_DQ SRC
WHERE EN.ENTITY_NAME in (select view_name from my_current_views) 
GROUP BY SRC.SRC_SYS_ID, EN.ENTITY_NAME, EN.SOFT_RULE_NAME,
(to_date(to_char(SRC.LDTS,'DD-MM-YY')))

And maintain a Table called my_current_views

create table my_current_views (
 view_name as varchar2(100)
)

And whenever you want your "general view" to change you insert new record into my_current_views table.