How do I insert a dynamic SQL oracle variable in a string?

Question

I have a dynamic query:

declare

p_sql clob;

begin

​for naprav in (select naprav_name from naprav)

​ ​ ​loop​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​

​ ​ ​ ​ ​ ​ for pokaz in (select * from pokaz)

​ ​ ​ ​ ​ ​ ​loop

​ ​ ​ ​ ​ ​ ​ ​ ​for group_level in (select * from group_level)

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​loop

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ p_sql := '

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ insert into from_cost_lab_with_love

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ with cte as (

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ select

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ' ||​ pokaz.i_id_pokaz || ' as i_id_pokaz,

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ' ||​ pokaz.pokaz_measure || ' as pokaz_measure,

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ' ||​ pokaz.pokaz_describe || ' as "Описание показателя",

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ' ||​ naprav.naprav_name || ' as "Блок данных",

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ' ||​ group_level.level_name || ' as level_name,

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ' ||​ group_level.tb_id || ' as tb_id,

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ' ||​ group_level.gosb_id || ' as gosb_id,

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ' ||​ group_level.org_id || ' as org_id,

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ' ||​ pokaz.pokaz_formula || ' as fact_amt,' --показатель

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ||​ pokaz.pokaz_formula || '  as fc_amt_1,

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ row_number() over(' || group_level.partition_column || ' order by BE) as rnk

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ 0 as plan_amt,

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ TO_DATE(CONCAT(CONCAT(FINANSOVYJ_GOD,CONCAT(''0'',KVARTAL*3 - 3)),''01''), ''yyyymmdd'') as period_begin,​ --начало квартала

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ LAST_DAY(TO_DATE(CONCAT(CONCAT(FINANSOVYJ_GOD,CONCAT(''0'',KVARTAL*3)),''01''), ''yyyymmdd'')) as period_end, --конец квартала

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ LAST_DAY(TO_DATE(CONCAT(CONCAT(FINANSOVYJ_GOD,CONCAT(''0'',MESJATS)), ''01''), ''yyyymmdd'')) as rep_date, --дата измерений

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ TO_DATE(CURRENT_DATE, ''dd.mm.yy'') as load_date,

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ 0 as execution

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ from erp_trips

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ )

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ select *

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ from cte

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ where rnk=1;';

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ dbms_output.put_line(p_sql);

​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​execute immediate p_sql using group_level.partition_column;

​ ​ ​ ​ ​ ​ ​ ​end loop;

​ ​ ​ ​ ​end loop;

​ ​ ​end loop;

end;

It has a field pokaz. pokaz_formula, this field is taken from the pokaz table, one of the values of this field is pokaz. pokaz_formula:

sum(distinct RASHODY) over(:1)/count(distinct KOD_TSZ) over(:1)

dbms_output:

insert into from_cost_lab_with_love

              with cte as (

                     select

                     '99business_trips_01' as i_id_pokaz,

                     'руб.' as pokaz_measure,

                     'Командировочные расходы, руб.' as "Описание показателя",

                     'Командировки' as "Блок данных",

                     'gosb' as level_name,

                     BE/100 as tb_id,

                     GOSB as gosb_id,

                     '' as org_id,

                     sum(distinct RASHODY) over(:1) as fact_amt,sum(distinct RASHODY) over(:1) as fc_amt_1,

                           row_number() over(partition by BE, GOSB order by BE) as rnk,

                           0 as plan_amt,

                           TO_DATE(CONCAT(CONCAT(FINANSOVYJ_GOD,CONCAT('0',KVARTAL*3 - 3)),'01'), 'yyyymmdd') as period_begin,  --начало квартала

                           LAST_DAY(TO_DATE(CONCAT(CONCAT(FINANSOVYJ_GOD,CONCAT('0',KVARTAL*3)),'01'), 'yyyymmdd')) as period_end, --конец квартала

                           LAST_DAY(TO_DATE(CONCAT(CONCAT(FINANSOVYJ_GOD,CONCAT('0',MESJATS)), '01'), 'yyyymmdd')) as rep_date, --дата измерений

                           TO_DATE(CURRENT_DATE, 'dd.mm.yy') as load_date,

                           0 as execution

                     from erp_trips

              )

              select *

              from cte

              where rnk=1 and gosb_id <> 'ПЦП'

              order by tb_id, gosb_id

And when this value is substituted in the loop in the p_sql variable, the value :1 should be replaced with the value from the group_level table column, as in my code. But the compiler swears (missing right parenthesis) how can I solve this problem?

Answer 1

In the same way as you can't bind a table name you can't also bind parts of an analytic function.

Example - this fails with ORA-00903: invalid table name

begin
 execute immediate 'insert into tab1(col) select col from :1' using 'TAB2';
end;
/

Same is valid for you trying to bind partition by clause

begin
 execute immediate 'insert into tab1(col) select max(col) over (:1)  from tab2' using 'partition by col2';
end;
/

This fails as you observe with ORA-00907: missing right parenthesis

What you can do but is for sure what you do not want is to bind a value in the PARTITION BY clause

begin
 execute immediate 'insert into tab1(col) select max(col) over (partition by :1)  from tab2' using 'col2';
end;
/

This works, but behaves in the same way as if you would ommit the partition by clause, because it corresponds to partition by 'col2', i.e. partitioning on a fixed value results in one partition of all rows from the table.

So probaly the solution you are looking for is to concatenate the partition by clause into the insert statement:

 execute immediate 'insert into tab1(col) select max(col) over (' || 
    group_level.partition_column || /* concatenate the partition clause in the SQL */
 ')  from tab2';

which will produce something like this

insert into tab1(col) select max(col) over (partition by col2)  from tab2

You should always checking for the SQL injection while doing so.