dynamic define variables in sqlplus
970575Oct 25 2012 — edited Oct 25 2012I have requirement, in which user will define 'n' number of variables in param.dat file as below
define filter_cond1='emp.emp_id is not null'
define filter_cond2='emp.dept_id is not null'
.
.
.
define filter_cond<n>='emp.emp_name is not null'
I am supposed to write one sql script which will create select query dynamically as per filter condition defined in param file.
While writing script i m facing difficulty to know how many defined variables like "filter_cond"<n> are present in
param.dat file
Also, with below script i m not able to get exact value of define variables using execute immediate
Please suggest code to perform above described task
-- script
set serveroutput on
define filter_cond1='emp.emp_id is not null'
define filter_cond2='emp.dept_id is not null'
declare
l_filter_define_var_nm varchar2(32);
l_filter_cond varchar2(4000);
l_sql varchar2(4000);
i number := 1;
begin
l_filter_define_var_nm := 'filter_cond' || i;
l_sql := 'select ''&' || l_filter_define_var_nm || ''' from dual';
dbms_output.put_line(' l_sql ' || l_sql);
EXECUTE IMMEDIATE l_sql into l_filter_cond;
dbms_output.put_line(l_filter_define_var_nm || ' is ' || l_filter_cond);
i:=2;
l_filter_define_var_nm := 'filter_cond' || i;
l_sql := 'select ''&' || l_filter_define_var_nm || ''' from dual';
dbms_output.put_line(' l_sql ' || l_sql);
EXECUTE IMMEDIATE l_sql into l_filter_cond;
dbms_output.put_line(l_filter_define_var_nm || ' is ' || l_filter_cond);
exception
when others
then
dbms_output.put_line('exception ' || sqlerrm || dbms_utility.format_error_backtrace);
end;
/
-- Actual output
l_sql select '&filter_cond1' from dual
filter_cond1 is &filter_cond1
l_sql select '&filter_cond2' from dual
filter_cond1 is &filter_cond2
-- Expected output
l_sql select '&filter_cond1' from dual
filter_cond1 is emp.emp_id is not null
l_sql select '&filter_cond2' from dual
filter_cond1 is emp.dept_id is not null
Thank you.