One of the most common reasons people appear to be using dynamic SQL is to handle the case when the user can specify one, two or any amount of values to be searched on. For example, the user may select (say) two names from a LOV on people, and so the where-clause part of the query would look like:
where SURNAME in ( 'SMITH', 'WILLIAMS')
but of course if they had selected FOUR names from the LOV, then the where-clause part of the query would look like:
where SURNAME in ( 'SMITH', 'WILLIAMS','DIO','MALMSTEEN')
So throughout forms applications, the way people tend to handle this is with dynamic piece of SQL along the lines of: l_sql := 'select * from persons where surname in ('|| lv_list_of_names ||')';
which we then process with EXEC_SQL or similar. As we all now know, its a bad thing to do on performance grounds (no bind variables = no performance). But its also a pain to code correctly - we have to carefully build up the list of names into a comma separated list, we have to check for surnames that contains quotes, etc etc. And its a big security hole as well
You might be thinking that surely it can't be possible to process an arbritrary number of in-list items with the same single SQL ? Well, yes it can. All it takes a little knowledge of the object-relational options within Oracle.
Firstly, we created a nested table type (you'd only need to do this once for the whole database) to hold a list of strings that have been (say) picked from a LOV
SQL> create or replace type varchar2_list as table of varchar2 (255); 2 / Type created.
Then we can create a simple function which takes a comma-separated list of predicate values and convert them to the varchar2_list type.
SQL> create or replace 2 function in_list( p_string in varchar2 ) return varchar2_list as 3 l_string long default p_string || ','; 4 l_data varchar2_list := varchar2_list(); 5 n number; 6 begin 7 loop 8 exit when l_string is null; 9 n := instr( l_string, ',' ); 10 l_data.extend; 11 l_data(l_data.count) := ltrim( rtrim( substr( l_string, 1, n-1 )) ); 12 l_string := substr( l_string, n+1 ); 13 end loop; 14 return l_data; 15 end; 16 / Function created.
And here is where things get very sweet...Oracle can re-cast the results of such a function into a relational structure on the fly. For example, we can take the string 'SMITH,JONES,BROWN' and automatically treat it as if they were rows in a table
SQL> select *
2 from table(cast(in_list('SMITH,JONES,BROWN') as varchar2_list));
COLUMN_VALUE
--------------------------
SMITH
JONES
BROWN
"Big deal" I hear you say. But once you can convert a string to a table, then you can of course, use it within normal SQL. For example, lets build a string of people's surnames (in the variable "list_of_people"). We can then query the NOMINAL table for just those people using static SQL
SQL> declare
2 list_of_people varchar2(100) := 'SMITH,WILLIAMS,DIO,MALMSTEEN';
3 begin
4 for i in ( select id, date_created
5 from person
6 where upper(surname) in (
7 select *
8 from table(cast(in_list(list_of_people) as varchar2_list))) ) loop
9 dbms_output.put_line('ID = '||i.id);
10 dbms_output.put_line('DATE_CREATED = '||i.date_created);
11 end loop;
12 end;
13 /
ID = 9000001
DATE_CREATED = 01/OCT/03
ID = 9000002
DATE_CREATED = 01/OCT/03
ID = 9000003
DATE_CREATED = 01/OCT/03
ID = 9000004
DATE_CREATED = 01/OCT/03
ID = 9000010
DATE_CREATED = 01/OCT/03
ID = 9000023
DATE_CREATED = 02/OCT/03
ID = 9000026
DATE_CREATED = 02/OCT/03
PL/SQL procedure successfully completed.
Notice that the SQL in red above does not contain any literals - it
doesn't matter what the list of people being sought is, we can re-use the
same SQL over and over. No dynamic SQL needed, no literal strings needed.
In fact, anything that you can convert into the nested table type
varchar2_list, can be used. So (for example), its trivial to change our
demo so that rather than passing a string list of names, you could pass
any SQL statement to generate the list of surnames that we are interested
in. All we need do it alter our IN_LIST function to loop through a ref
cursor as opposed to a string.
SQL> create or replace 2 function in_list( p_string in varchar2 ) return varchar2_list as 3 type rc is ref cursor; 4 l_cursor rc; 5 l_tmp long; 6 l_data varchar2_list := varchar2_list(); 7 begin 8 open l_cursor for p_string; 9 loop 10 fetch l_cursor into l_tmp; 11 exit when l_cursor%notfound; 12 l_data.extend; 13 l_data(l_data.count) := l_tmp; 14 end loop; 15 close l_cursor; 16 return l_data; 17 end; 18 / Function created.
Now my "list_of_people" variable can be a SQL statement, and my loop still works just fine.
SQL> declare
2 list_of_people varchar2(100) := 'select surname from nominal '||
3 ' where surname like ''S%''';
4 begin
5 for i in ( select id, date_created
6 from person
7 where upper(surname) in (
8 select *
9 from table(cast(in_list(list_of_people) as varchar2_list))) ) loop
10 dbms_output.put_line('ID = '||i.id);
11 dbms_output.put_line('DATE_CREATED = '||i.date_created);
12 end loop;
13 end;
14 /
ID = 9000001
DATE_CREATED = 01/OCT/03
ID = 9000010
DATE_CREATED = 01/OCT/03
ID = 9000021
DATE_CREATED = 02/OCT/03
ID = 9000022
DATE_CREATED = 02/OCT/03
ID = 9000023
DATE_CREATED = 02/OCT/03
ID = 9000024
DATE_CREATED = 02/OCT/03
ID = 9000025
DATE_CREATED = 02/OCT/03
ID = 9000026
DATE_CREATED = 02/OCT/03
ID = 9000027
DATE_CREATED = 02/OCT/03
ID = 9000074
DATE_CREATED = 07/OCT/03
PL/SQL procedure successfully completed.
There are a myriad of uses for collections and object types in PL/SQL. You'll find all the details on using object types within PL/SQL within the PL/SQL reference and the Application Developer - Object Relational reference.