How to generate dynamic SQL statement with generic condition-CodePudding

I am trying to generate a dynamic SQL statement with a generic condition. Depending some conditions, it will be added different other conditions in where clause.

I was trying to have something like this:

declare
    v_sql varchar2(500);
    a number;
    v_dummy number := 1;
begin
    v_sql := 'delete tab_1 where v_dummy = 1 ';
    if a = 1 
       then v_sql := v_sql || ' and col_1 = 1';
       else v_sql := v_sql || ' and col_2 = 3';
    end if;
    execute immediate v_sql;
    dbms_output.put_line(v_sql);
end;

The error raised is:

ORA-00904: "V_DUMMY": invalid identifier

Can anyone, please, guide me how to handle this situation? The problem is with the definition of first condition (v_dummy = 1) that I need to add in order to use the "and" operand for the second condition.

Thank you,

CodePudding user response：

Use a bind variable:

declare
    v_sql varchar2(500);
    a number;
    v_dummy number := 1;
begin
    v_sql := 'delete tab_1 where :v_dummy = 1 ';
    if a = 1 
       then v_sql := v_sql || ' and col_1 = 1';
       else v_sql := v_sql || ' and col_2 = 3';
    end if;
    execute immediate v_sql USING v_dummy;
    dbms_output.put_line(v_sql);
end;
/

or since it evaluates to 1 = 1, you can omit it:

declare
    v_sql varchar2(500);
    a number;
begin
    v_sql := 'delete tab_1';
    if a = 1 
       then v_sql := v_sql || ' where col_1 = 1';
       else v_sql := v_sql || ' where col_2 = 3';
    end if;
    execute immediate v_sql;
    dbms_output.put_line(v_sql);
end;
/

CodePudding user response：

If there is a possibility that you need multiple conditions (not in your code) you can set initial condition simply putting col_1 = col_1. No need for dummy variable at all and leaves you options to add some more conditions:

declare
    v_sql varchar2(500);
    a number;
begin
    v_sql := 'delete tab_1 where col_1 = col_1 ';
    if a = 1 then 
        v_sql := v_sql || ' and col_1 = 1';
    else 
        v_sql := v_sql || ' and col_2 = 3';
    end if;
    execute immediate v_sql;
    dbms_output.put_line(v_sql);
end;