Oracle 11G Dynamically generated insert SQL

Question

I have a task where I have to compare all the columns in a schema with the trend of same column. For this I have written a query which generated insert statements in bulk. The total num of insert statements will exceed 50K+. The sql generation query that I have written is as below:

select ' insert into global.PM_COUNTER_AUDIT  select  ''' || column_name ||
       '''  COUNTER_NAME,   pre.country,pre.city, pre.stddev_col_name , pre.cnt_wk,post.counter_last_day,var_wk from (select  country, city,stddev(nvl(' || column_name ||
       ',0)) stddev_col_name,  variance(nvl(' || column_name || ',0)) var_wk, avg(nvl(' || column_name || ',0)) cnt_wk, count(*) from  ' || owner || '.' || table_name ||
       ' where datetime>=trunc(sysdate)-14  and  upper(trim(tO_char(datetime,''DAY''))) NOT IN (''FRIDAY'',''SATURDAY'') group by country, city)pre left outer join (select country, city,sum(nvl(' ||
       column_name || ',0)) counter_last_day  from  ' || owner || '.' || table_name ||
       ' where datetime>=trunc(sysdate)-1 group by country, city )post  on  pre.country=post.country and pre.city=post.city where counter_last_day not between pre.cnt_wk-(3*(pre.stddev_col_name))  and pre.cnt_wk+(3*(pre.stddev_col_name)) '
from   all_tab_cols
where  owner = 'HUAWEI_EUTRAN_PM'
and    upper(table_name) in ('TABLE_X', 'TABLE_Y', table_z)
and    nullable = 'Y'

Now I want to execute all these generated statement using cursor or immediate execute but I am unable to accomplish this. Kindly advise how can I do it.

TIA

Answer 1

Cleaning up your code a bit you can do something like:

begin
  for r in (
    select 'insert into table_name
select ''' || COLUMN_NAME || ''' counter_name, pre.country, pre.city,
  pre.stddev_col_name, pre.cnt_wk, post.counter_last_day, pre.var_wk
from (
  select country, city, stddev(nvl(' || COLUMN_NAME || ', 0)) stddev_col_name, 
    variance(nvl(' || COLUMN_NAME || ', 0)) var_wk,
    avg(nvl(' || COLUMN_NAME || ',0)) cnt_wk,
    count(*)
  from ' || OWNER || '.' || TABLE_NAME ||'
  where datetime >= trunc(sysdate) - 14
  and to_char(datetime, ''FMDAY'', ''NLS_DATE_LANGUAGE=ENGLISH'') NOT IN (''FRIDAY'', ''SATURDAY'')
  group by country, city
) pre
left outer join (
  select country, city, sum(nvl(' || COLUMN_NAME || ', 0)) counter_last_day 
  from ' || OWNER || '.' || TABLE_NAME || '
  where datetime >= trunc(sysdate) - 1
  group by country, city
) post
on pre.country = post.country
and pre.city = post.city
where counter_last_day not between pre.cnt_wk-(3*(pre.stddev_col_name))
and pre.cnt_wk+(3*(pre.stddev_col_name))' as stmt
from all_tab_cols where owner = 'HEXA'
and table_name in ('Z_STATS', 'Y_STATS', 'X_STATS')
and nullable = 'Y'
and rownum <= 10
  ) loop

    dbms_output.put_line(r.stmt);
    execute immediate r.stmt;

  end loop;
end;
/

I've taken out a few unnecessary bit and simplified the day check, while adding an explicit language to use for that part.

It will generate and run statements like:

insert into table_name
select 'ABC' counter_name, pre.country, pre.city,
  pre.stddev_col_name, pre.cnt_wk, post.counter_last_day, pre.var_wk
from (
  select country, city, stddev(nvl(ABC, 0)) stddev_col_name, 
    variance(nvl(ABC, 0)) var_wk,
    avg(nvl(ABC,0)) cnt_wk,
    count(*)
  from HEXA.Z_STATS
  where datetime >= trunc(sysdate) - 14
  and to_char(datetime, 'FMDAY', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('FRIDAY', 'SATURDAY')
  group by country, city
) pre
left outer join (
  select country, city, sum(nvl(ABC, 0)) counter_last_day 
  from HEXA.Z_STATS
  where datetime >= trunc(sysdate) - 1
  group by country, city
) post
on pre.country = post.country
and pre.city = post.city
where counter_last_day not between pre.cnt_wk-(3*(pre.stddev_col_name))
and pre.cnt_wk+(3*(pre.stddev_col_name))

... where z_stats has a nullable column called abc.

db<>fiddle (with user instead of HEXA so it runs).

One of the thing I took out was was UPPER(TABLE_NAME); that's only needed if your table names are mixed case, created with quoted identifiers. If they are then other references in the code would have to quote those, so it's probably noise. Something you might want to add is a data type check so you only pick up numeric columns.

Answer 2

Beside your statement is hardly to read it is also pointless. I assume you try to run a procedure similar to this:

DECLARE
    sqlstr VARCHAR2(10000);
    
    CURSOR SourceTables IS
    SELECT OWNER, TABLE_NAME, COLUMN_NAME
    FROM ALL_TAB_COLS 
    WHERE OWNER = 'HEXA' 
        AND TABLE_NAME IN ('Z_STATS','Y_STATS','X_STATS') 
        AND NULLABLE = 'Y' 
        AND ROWNUM <= 10;
    
BEGIN

    FOR aTable IN SourceTables LOOP
        sqlstr := 
            'INSERT INTO table_name 
            WITH pre AS (
                SELECT country, city,
                    STDDEV(NVL( '||aTable.COLUMN_NAME||',0)) stddev_col_name,  
                    VARIANCE(NVL('||aTable.COLUMN_NAME||',0)) var_wk, 
                    AVG(NVL('||aTable.COLUMN_NAME||',0)) cnt_wk, 
                    COUNT(*) AS N
                FROM '||aTable.OWNER||'.'||aTable.TABLE_NAME||'
                WHERE datetime >= TRUNC(SYSDATE)-14 
                    AND TO_CHAR(datetime,''DY'', ''NLS_DATE_LANGUAGE = American'') NOT IN (''FRI'',''SAT'')
                GROUP BY country, city),
            post AS (
                SELECT country, city,
                    SUM(NVL('||aTable.OLUMN_NAME||',0)) counter_last_day  
                FROM  '||aTable.OWNER||'.'||aTable.TABLE_NAME||' 
                WHERE datetime >= TRUNC(SYSDATE)-1 
                GROUP BY country, city)
            SELECT '||aTable.COLUMN_NAME||' COUNTER_NAME, 
                pre.country,
                pre.city, 
                pre.stddev_col_name, 
                pre.cnt_wk,
                post.counter_last_day,
                var_wk
            FROM pre
                LEFT OUTER JOIN post ON  pre.country = post.country AND pre.city = post.city 
            WHERE counter_last_day NOT BETWEEN pre.cnt_wk-(3*(pre.stddev_col_name)) AND pre.cnt_wk+(3*(pre.stddev_col_name))';

        EXECUTE IMMEDIATE sqlstr;
    END LOOP;
END;

The query does not make much sense and most likely the PL/SQL will not work on the fly but I hope you get an idea how it could be written.

You may have a look at windowing clause. It should be possible to run the query without sub-queries, resp. CTE. Could be similar to this one:

SELECT
    country, 
    city,
    STDDEV(COLUMN_NAME) OVER (PARTITION BY  country, city ORDER BY datetime RANGE INTERVAL '14' DAY PRECEDING) stddev_col_name,  
    VARIANCE(COLUMN_NAME) OVER (PARTITION BY  country, city ORDER BY datetime RANGE INTERVAL '14' DAY PRECEDING) var_wk, 
    AVG(COLUMN_NAME) OVER (PARTITION BY country, city ORDER BY datetime RANGE INTERVAL '14' DAY PRECEDING) nt_wk, 
    SUM(COLUMN_NAME) OVER (PARTITION BY country, city ORDER BY datetime RANGE INTERVAL '1' DAY PRECEDING) counter_last_day, 
    COUNT(*) AS N
FROM TABLE_NAME
WHERE datetime >= TRUNC(SYSDATE)-14 
    AND TO_CHAR(datetime,'DY', 'NLS_DATE_LANGUAGE = American') NOT IN ('FRI','SAT');