i have code this:
declare p_vara varchar2; p_varb varchar2; p_varc varchar2; begin insert my_insertable_table select a.id,b.id,c.id table_a a, table_b b, table_c c a.id not null , a.id = b.id , c.id = 'cat' end; now based on the variable make conditional parts of query called based on variable.
declare p_vara varchar2; p_varb varchar2; p_varc varchar2; begin insert my_insertable_table select a.id, -- show if p_vara = 'yes' b.id, -- show if p_varb = 'yes' c.id -- show if p_varc = 'yes' table_a a, -- use if p_vara = 'yes' table_b b, -- use if p_varb = 'yes' table_c c -- use if p_varc = 'yes' a.id not null -- use if p_vara = 'yes' , a.id = b.id -- use if p_vara = 'yes' , p_varb = 'yes' , c.id = 'cat' -- use if p_varc = 'yes' end; so example if variables set this:
p_vara = 'yes' p_varb = 'no' p_varc = 'no' then query should this:
select a.id table_a a.id null;
as written, requirements not appear complete. if 3 variables yes, example, full statement have 3 table joins 1 join condition you'd generate cartesian product table_c. if p_vara = 'yes' , p_varc = 'yes', you'd have 2 tables joined no join condition you'd again have cartesian product. seems unlikely me want generate cartesian product.
in general, can build sql statement in string variable , pass execute immediate. if have 25 boolean variables, implies code generate total of 33.55 million distinct sql statements. verifying none of paths generate statement syntax errors non-trivial. combined fact resorting dynamic sql makes code harder read, maintain, , support in addition creating opportunities performance , security issues, tend push on design contemplates complex describing.
that said, (i'm not building where clause out trust jist)
declare l_vara boolean; l_varb boolean; l_varc boolean; l_sql_stmt varchar(4000); begin l_sql_stmt := 'insert my_insertable_table( col1, col2, col3 ) '; l_sql_stmt := l_sql_stmt || ' select ' || (case when l_vara ' a.id, ' else ' null, ' end) || (case when l_varb ' b.id, ' else ' null, ' end) || (case when l_varc ' c.id, ' else ' null, ' end); l_sql_stmt := rtrim( l_sql_stmt, ',' ); -- remove trailing comma l_sql_stmt := l_sql_stmt || ' '; if( l_vara ) l_sql_stmt := l_sql_stmt || ' table_a a, '; end if; if( l_varb ) l_sql_stmt := l_sql_stmt || ' table_b b, '; end if; if( l_varc ) l_sql_stmt := l_sql_stmt || ' table_c c, '; end if; -- again remove trailing comma l_sql_stmt := rtrim( l_sql_stmt, ',' ); <<build out clause similarly>> -- log sql statement can debug when fails insert some_log_table( sql_stmt ) values( l_sql_stmt ); execute immediate l_sql_stmt; end; 