Oracle Live SQL - Script: Sample Configuration table

Statement 1

create table DSI_SUMMARY_CONFIG 
( 
  CONFIG_ID                     NUMBER, 
  OPERATOR                      VARCHAR2(50), 
  SUMMARY_NAME                  VARCHAR2(50), 
  TABLE_NAME                    VARCHAR2(500), 
  MATERIALIZED_VIEW             CHAR, 
  ENABLED                       CHAR, 
  LAG                           NUMBER, 
  MISC_PROCESS_PARAMS           VARCHAR2(200), 
  DEFAULT_REPROCESS             NUMBER, 
  RETENTION_PERIOD              NUMBER, 
  DEL_QUERY                     VARCHAR2(2000), 
  INS_QUERY                     VARCHAR2(2000), 
  EXECUTION_ORDER               NUMBER 
  )

Table created.

Statement 2

  INSERT INTO DSI_SUMMARY_CONFIG (onfig_id,operator,summary_name,table_name,materialized_view,enabled,lag,misc_process_params,default_reprocess,Retention_period,del_query,ins_query) 
  with x as ( 
     select 
'100','VODAFONE','DATA USAGE','DSI_VF_DATA_USAGE','N','Y',0,null,9,365,'DELETE SUMMARIES.DSI_VF_DATA_USAGE WHERE #DATE_FILTER#','insert  into summaries.DSI_VF_DATA_USAGE 
SELECT 
    trunc(DATESTARTOFCHARGING) 
     sum(DATABYTES / 1024 / 1024)         DATAMB, 
 FROM SERVER.cdrdb s 
     WHERE s.RECORDTYPE = 13 
 and #DATE_FILTER# 
    group by trunc(DATESTARTOFCHARGING)',1 from dual union all 
select '104','VODAFONE','RECHARGE BTS','DSI_VF_RECHARGE_BTS','N','Y',0,null,1,365,'delete summaries.dsi_VF_recharge_bts 
where #DATE_FILTER#','insert into summaries.dsi_VF_recharge_bts 
select ACC_NBR, 
       CREATED_DATE, 
       prod_spec_name, 
       SUBMIT_AMOUNT, 
       NETWORK, 
       BASE_STATION, 
       AREA, 
       REGION 
from summaries.dsi_vf_prepaid_recharge 
       LEFT join bi.cust_cell_top on ACC_NBR = A_MSISDN 
where #DATE_FILTER#' ,2 from dual union all 
select '106','VODAFONE','PREPAID RECHARGE','DSI_VF_PREPAID_RECHARGE','N','Y',0,null,1,365,'delete summaries.dsi_VF_recharge_bts 
where #DATE_FILTER#','insert into summaries.dsi_VF_recharge_bts 
select ACC_NBR, 
       CREATED_DATE, 
       prod_spec_name, 
       SUBMIT_AMOUNT, 
       NETWORK, 
       BASE_STATION, 
       AREA, 
       REGION 
from summaries.dsi_vf_prepaid_recharge 
       LEFT join bi.cust_cell_top on ACC_NBR = A_MSISDN 
where #DATE_FILTER#' ,2 from dual) 
select * from  x

ORA-00913: too many values

More Details: https://docs.oracle.com/error-help/db/ora-00913

Statement 3

  INSERT INTO DSI_SUMMARY_CONFIG (onfig_id,operator,summary_name,table_name,materialized_view,enabled,lag,misc_process_params,default_reprocess,Retention_period,del_query,ins_query,EXECUTION_ORDER) 
  with x as ( 
     select 
'100','VODAFONE','DATA USAGE','DSI_VF_DATA_USAGE','N','Y',0,null,9,365,'DELETE SUMMARIES.DSI_VF_DATA_USAGE WHERE #DATE_FILTER#','insert  into summaries.DSI_VF_DATA_USAGE 
SELECT 
    trunc(DATESTARTOFCHARGING) 
     sum(DATABYTES / 1024 / 1024)         DATAMB, 
 FROM SERVER.cdrdb s 
     WHERE s.RECORDTYPE = 13 
 and #DATE_FILTER# 
    group by trunc(DATESTARTOFCHARGING)',1 from dual union all 
select '104','VODAFONE','RECHARGE BTS','DSI_VF_RECHARGE_BTS','N','Y',0,null,1,365,'delete summaries.dsi_VF_recharge_bts 
where #DATE_FILTER#','insert into summaries.dsi_VF_recharge_bts 
select ACC_NBR, 
       CREATED_DATE, 
       prod_spec_name, 
       SUBMIT_AMOUNT, 
       NETWORK, 
       BASE_STATION, 
       AREA, 
       REGION 
from summaries.dsi_vf_prepaid_recharge 
       LEFT join bi.cust_cell_top on ACC_NBR = A_MSISDN 
where #DATE_FILTER#' ,2 from dual union all 
select '106','VODAFONE','PREPAID RECHARGE','DSI_VF_PREPAID_RECHARGE','N','Y',0,null,1,365,'delete summaries.dsi_VF_recharge_bts 
where #DATE_FILTER#','insert into summaries.dsi_VF_recharge_bts 
select ACC_NBR, 
       CREATED_DATE, 
       prod_spec_name, 
       SUBMIT_AMOUNT, 
       NETWORK, 
       BASE_STATION, 
       AREA, 
       REGION 
from summaries.dsi_vf_prepaid_recharge 
       LEFT join bi.cust_cell_top on ACC_NBR = A_MSISDN 
where #DATE_FILTER#' ,2 from dual) 
select * from  x

ORA-00904: "ONFIG_ID": invalid identifier

More Details: https://docs.oracle.com/error-help/db/ora-00904

Statement 4

  INSERT INTO DSI_SUMMARY_CONFIG (config_id,operator,summary_name,table_name,materialized_view,enabled,lag,misc_process_params,default_reprocess,Retention_period,del_query,ins_query,EXECUTION_ORDER) 
  with x as ( 
     select 
'100','VODAFONE','DATA USAGE','DSI_VF_DATA_USAGE','N','Y',0,null,9,365,'DELETE SUMMARIES.DSI_VF_DATA_USAGE WHERE #DATE_FILTER#','insert  into summaries.DSI_VF_DATA_USAGE 
SELECT 
    trunc(DATESTARTOFCHARGING) 
     sum(DATABYTES / 1024 / 1024)         DATAMB, 
 FROM SERVER.cdrdb s 
     WHERE s.RECORDTYPE = 13 
 and #DATE_FILTER# 
    group by trunc(DATESTARTOFCHARGING)',1 from dual union all 
select '104','VODAFONE','RECHARGE BTS','DSI_VF_RECHARGE_BTS','N','Y',0,null,1,365,'delete summaries.dsi_VF_recharge_bts 
where #DATE_FILTER#','insert into summaries.dsi_VF_recharge_bts 
select ACC_NBR, 
       CREATED_DATE, 
       prod_spec_name, 
       SUBMIT_AMOUNT, 
       NETWORK, 
       BASE_STATION, 
       AREA, 
       REGION 
from summaries.dsi_vf_prepaid_recharge 
       LEFT join bi.cust_cell_top on ACC_NBR = A_MSISDN 
where #DATE_FILTER#' ,2 from dual union all 
select '106','VODAFONE','PREPAID RECHARGE','DSI_VF_PREPAID_RECHARGE','N','Y',0,null,1,365,'delete summaries.dsi_VF_recharge_bts 
where #DATE_FILTER#','insert into summaries.dsi_VF_recharge_bts 
select ACC_NBR, 
       CREATED_DATE, 
       prod_spec_name, 
       SUBMIT_AMOUNT, 
       NETWORK, 
       BASE_STATION, 
       AREA, 
       REGION 
from summaries.dsi_vf_prepaid_recharge 
       LEFT join bi.cust_cell_top on ACC_NBR = A_MSISDN 
where #DATE_FILTER#' ,2 from dual) 
select * from  x

3 row(s) inserted.

Statement 5

Select * from dsi_summary_config


CONFIG_ID OPERATOR SUMMARY_NAME TABLE_NAME MATERIALIZED_VIEW ENABLED LAG MISC_PROCESS_PARAMS DEFAULT_REPROCESS RETENTION_PERIOD DEL_QUERY INS_QUERY EXECUTION_ORDER
100 VODAFONE DATA USAGE DSI_VF_DATA_USAGE N Y 0  - 9 365 DELETE SUMMARIES.DSI_VF_DATA_USAGE WHERE #DATE_FILTER# insert  into summaries.DSI_VF_DATA_USAGE 
SELECT 
    trunc(DATESTARTOFCHARGING) 
     sum(DATABYTES / 1024 / 1024)         DATAMB, 
 FROM SERVER.cdrdb s 
     WHERE s.RECORDTYPE = 13 
 and #DATE_FILTER# 
    group by trunc(DATESTARTOFCHARGING) 1
104 VODAFONE RECHARGE BTS DSI_VF_RECHARGE_BTS N Y 0  - 1 365 delete summaries.dsi_VF_recharge_bts 
where #DATE_FILTER# insert into summaries.dsi_VF_recharge_bts 
select ACC_NBR, 
       CREATED_DATE, 
       prod_spec_name, 
       SUBMIT_AMOUNT, 
       NETWORK, 
       BASE_STATION, 
       AREA, 
       REGION 
from summaries.dsi_vf_prepaid_recharge 
       LEFT join bi.cust_cell_top on ACC_NBR = A_MSISDN 
where #DATE_FILTER# 2
106 VODAFONE PREPAID RECHARGE DSI_VF_PREPAID_RECHARGE N Y 0  - 1 365 delete summaries.dsi_VF_recharge_bts 
where #DATE_FILTER# insert into summaries.dsi_VF_recharge_bts 
select ACC_NBR, 
       CREATED_DATE, 
       prod_spec_name, 
       SUBMIT_AMOUNT, 
       NETWORK, 
       BASE_STATION, 
       AREA, 
       REGION 
from summaries.dsi_vf_prepaid_recharge 
       LEFT join bi.cust_cell_top on ACC_NBR = A_MSISDN 
where #DATE_FILTER# 2

3 rows selected.

CONFIG_ID	OPERATOR	SUMMARY_NAME	TABLE_NAME	MATERIALIZED_VIEW	ENABLED	MISC_PROCESS_PARAMS	DEFAULT_REPROCESS	RETENTION_PERIOD	DEL_QUERY	INS_QUERY	EXECUTION_ORDER
100	VODAFONE	DATA USAGE	DSI_VF_DATA_USAGE	N	Y	-	9	365	DELETE SUMMARIES.DSI_VF_DATA_USAGE WHERE #DATE_FILTER#	insert into summaries.DSI_VF_DATA_USAGE SELECT trunc(DATESTARTOFCHARGING) sum(DATABYTES / 1024 / 1024) DATAMB, FROM SERVER.cdrdb s WHERE s.RECORDTYPE = 13 and #DATE_FILTER# group by trunc(DATESTARTOFCHARGING)	1
104	VODAFONE	RECHARGE BTS	DSI_VF_RECHARGE_BTS	N	Y	-	1	365	delete summaries.dsi_VF_recharge_bts where #DATE_FILTER#	insert into summaries.dsi_VF_recharge_bts select ACC_NBR, CREATED_DATE, prod_spec_name, SUBMIT_AMOUNT, NETWORK, BASE_STATION, AREA, REGION from summaries.dsi_vf_prepaid_recharge LEFT join bi.cust_cell_top on ACC_NBR = A_MSISDN where #DATE_FILTER#	2
106	VODAFONE	PREPAID RECHARGE	DSI_VF_PREPAID_RECHARGE	N	Y	-	1	365	delete summaries.dsi_VF_recharge_bts where #DATE_FILTER#	insert into summaries.dsi_VF_recharge_bts select ACC_NBR, CREATED_DATE, prod_spec_name, SUBMIT_AMOUNT, NETWORK, BASE_STATION, AREA, REGION from summaries.dsi_vf_prepaid_recharge LEFT join bi.cust_cell_top on ACC_NBR = A_MSISDN where #DATE_FILTER#	2

Sample Configuration table

Additional Information