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?

What is the exact error that you are getting when running the code? Also, your SQL string should not have a ; at the end when using EXECUTE IMMEDIATE. Try changing the end of your SQL string from where rnk=1; to where rnk=1. — EJ Egyed
– EJ Egyed, Commented Nov 3, 2020 at 14:15
Also, can you give an example of what is being output from your DBMS_OUTPUT? — EJ Egyed
– EJ Egyed, Commented Nov 3, 2020 at 14:18
@EJEgyed, ';' is not a problem(i forgot remove it from there, it's not in my code). i added dbms-output in question for you) — Марсель Абдуллин
– Марсель Абдуллин, Commented Nov 3, 2020 at 14:31
When binding :1, does what your binding actually have the PARTITION BY keywords? or is it just a column name? — EJ Egyed
– EJ Egyed, Commented Nov 3, 2020 at 14:48

Marmite Bomber · Accepted Answer · 2020-11-03 17:46:42Z

2

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.

edited Nov 3, 2020 at 17:46

answered Nov 3, 2020 at 15:08

Marmite Bomber

21.3k4 gold badges31 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Марсель Абдуллин Over a year ago

you want to say that I can't solve the problem with that architecture?

EJ Egyed Over a year ago

If you provide the table structures with some sample data and the output you are expecting, we may be able to help provide a more efficient solution to query/insert the data you are looking for.

Marmite Bomber Over a year ago

@МарсельАбдуллин I added a solution proposal

Марсель Абдуллин Over a year ago

@Marmit Bomber, But I have a different formulas(for example: sum(rashody) over(partition by gosh) and sum(rashody) over (partition by gosh) / count(nomer) over(partition by gosh). And I want to store it in one variable

Марсель Абдуллин Over a year ago

I solve the problem with adding formula1 and formula2: formula1 || ‘over(‘ || partition_column || ‘)’ || ‘/‘ || formula2 || ‘over(‘ || partition_column || ‘) as fact’. Notice that ‘)’ || ‘/‘ is different then ‘)/‘ in second variant you get an error. When I don’t need to divide I put ‘count(distinct 1)’ in formula 2

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related