Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

dynamic define variables in sqlplus

970575Oct 25 2012 — edited Oct 25 2012
I 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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 22 2012
Added on Oct 25 2012
3 comments
1,211 views