Expand and Fill the missing dates with last not null value

Input and Expected Output:

Data Setup:

create table tx_data(dt date, amnt number);

insert into tx_data values(to_date('20-JUL-2019','DD-MON-YYYY'),100);
insert into tx_data values(to_date('25-JUL-2019','DD-MON-YYYY'),200);
insert into tx_data values(to_date('30-JUL-2019','DD-MON-YYYY'),300);

commit;

Query:

with d1 as (
select min(dt) mi,max(dt) mx, max(dt)-min(dt) num
from tx_data),
dt_range as (select level-1+mi dt
from d1
connect by level <=num+1)
select dt_range.dt,
       last_value(amnt ignore nulls) over(order by dt_range.dt) amnt
from dt_range left outer join tx_data
on (dt_range.dt = tx_data.dt)

order by 1;

sql to get combination of all the given values

create table t1(c varchar2(1));

insert into t1 values('A');
insert into t1 values('B');
insert into t1 values('C');

create table t2(c varchar2(1));

insert into t2 values('D');
insert into t2 values('E');
insert into t2 values('F');

commit;

----------------------------------------------------

with t as (select c from t1 union select c from t2)
select a1.c||'-'||a2.c output
from t a1, t a2
where a1.c < a2.c;

----------------------------------------------------

with t as (select c from t1 union select c from t2)
select distinct least(a1.c,a2.c), greatest(a1.c,a2.c) output
from t a1, t a2
where a1.c <> a2.c
order by 1;

----------------------------------------------------

oracle compound trigger

What is compound trigger?

A compound trigger combines the following triggers into one single trigger:

• Before statement trigger
• Before row trigger
• After row trigger
• After statement trigger

Advantages of “Compound trigger”?

• Consolidating the individual triggers into single compound trigger
• Global variables to share across timing events
• Effectively load the changes to another table using bulk collect
• To avoid mutating table error

CREATE OR REPLACE TRIGGER emp_comp_trigger
  FOR INSERT OR UPDATE OR DELETE ON EMP COMPOUND TRIGGER

  g_variable VARCHAR2(50) := 'DEMO ON COMPOUND TRIGGER';

  BEFORE STATEMENT IS
    l_variable VARCHAR2(50) := ' BEFORE STATEMENT ';
  BEGIN
    dbms_output.put_line(l_variable||g_variable);
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
    l_variable VARCHAR2(50) := ' BEFORE EACH ROW ';
  BEGIN
    dbms_output.put_line(l_variable||g_variable);
  END BEFORE EACH ROW;

  AFTER EACH ROW IS
    l_variable VARCHAR2(50) := ' AFTER EACH ROW ';
  BEGIN
    dbms_output.put_line(l_variable||g_variable);
  END AFTER EACH ROW;

  AFTER STATEMENT IS
    l_variable VARCHAR2(50) := ' AFTER STATEMENT ';
  BEGIN
    dbms_output.put_line(l_variable||g_variable);
  END AFTER STATEMENT;

END emp_comp_trigger;
/


CREATE OR REPLACE TRIGGER emp_comp_trigger
  FOR INSERT OR UPDATE OR DELETE ON EMP COMPOUND TRIGGER

  g_variable VARCHAR2(50) := 'DEMO ON COMPOUND TRIGGER';

  BEFORE STATEMENT IS
l_variable VARCHAR2(50) := ' BEFORE STATEMENT ';
  BEGIN
if INSERTING then
dbms_output.put_line(l_variable||g_variable||' INSERT ');
elsif UPDATING then
dbms_output.put_line(l_variable||g_variable||' UPDATE ');
elsif DELETING then
dbms_output.put_line(l_variable||g_variable||' DELETE ');
end if;
  END BEFORE STATEMENT;

END emp_comp_trigger;
/