Currently I have in my pl/sql code following statements:
-- vList looks like '1,2,3,4'
vStatement := 'SELECT NAME FROM T_USER WHERE ID IN ( ' || vList || ' ) ';
Execute Immediate vStatement BULK COLLECT INTO tNames;
I think that concatenating of query if bad practice, so I want to make this query without using stings. What is the way to rewrite this ?
P.S. maybe people here can point out why concatenation of queries is bad, because i don't have enough reasons to prove that this style is bad.
my guess is that you took some steps previously to get vList id's into a delimited string (you don't say how vList was populated ). Why not keep as one query?
begin
...
select name
bulk collect into tNames
from t_user
where id in (select id from some_table where ...);
...
Context switching when run many times can be painful, but to me the worst part is that you are blindly accepting parameter input to be a list of numbers, when it could be anything really. It could (innocently) be '1,2,X', and you'll get a runtime error "invalid number". Or worse, it could be a SQL injection attack. Its bad practice in general (dynamic sql does have its place), but definitely NOT how you're using it.
Try something like this:
create or replace type t_num_tab as table of number;
create or replace procedure test_proc(i_list in t_num_tab) as
type t_name_tab is table of varchar2(100);
l_names t_name_tab;
begin
-- get names
select name
bulk collect into l_names
from user_table
where id in (select * from table(i_list));
-- do something with l_names
dbms_output.put_line('Name count: ' || l_names.count);
end;
You can create an object type if you need something more complicated than a list of numbers.
It's not just that concatenation is slow. It's that dynamic queries in plsql are REALLY slow. Here's a good writeup of both the how and why to do this:
Ask Tom: How can I do a variable "in list"
