Use DBMS_SQL and bind variables

1 create or replace FUNCTION dbms_sql_list ( list_in IN VARCHAR2 )
2 RETURN otn_question_nt
3 IS
4 l_query VARCHAR2 ( 32767 ) := 'SELECT * FROM otn_question WHERE id IN (';
5 l_cur PLS_INTEGER := DBMS_SQL.open_cursor;
6 l_feedback PLS_INTEGER;
7 l_ids pky_nt := pky_nt ( );
8 l_row PLS_INTEGER;
9 l_onerow otn_question%ROWTYPE;
10 retval otn_question_nt := otn_question_nt ( );
11 BEGIN
12 -- Parse the delimited list to the collection.
13 string_to_list ( list_in, l_ids );
14
15 -- Build the list of placeholders.
16 FOR l_row IN 1 .. l_ids.COUNT
17 LOOP
18 l_query := l_query || ':bv' || l_row || ',';
19 END LOOP;
20
21 l_query := RTRIM ( l_query, ',' ) || ')';
22 -- Define the columns to be queried.
23 DBMS_SQL.parse ( l_cur, l_query, DBMS_SQL.native );
24 DBMS_SQL.define_column ( l_cur, 1, 1 );
25 DBMS_SQL.define_column ( l_cur, 2, 'a', 100 );
26 DBMS_SQL.define_column ( l_cur, 3, 'a', 2000 );
27
28 -- Bind each variable in the provided list.
29 FOR l_row IN 1 .. l_ids.COUNT
30 LOOP
31 DBMS_SQL.bind_variable ( l_cur, ':bv' || l_row, l_ids ( l_row ));
32 END LOOP;
33
34 -- Execute and then fetch each row.
35 l_feedback := DBMS_SQL.EXECUTE ( l_cur );
36
37 LOOP
38 l_feedback := DBMS_SQL.fetch_rows ( l_cur );
39 EXIT WHEN l_feedback = 0;
40 -- Retrieve column values and move them to the nested table.
41 DBMS_SQL.COLUMN_VALUE ( l_cur, 1, l_onerow.ID );
42 DBMS_SQL.COLUMN_VALUE ( l_cur, 2, l_onerow.title );
43 DBMS_SQL.COLUMN_VALUE ( l_cur, 3, l_onerow.description );
44 retval.EXTEND;
45 retval ( retval.LAST ) :=
46 otn_question_ot ( l_onerow.ID, l_onerow.title
47 , l_onerow.description );
48 END LOOP;
49
50 DBMS_SQL.close_cursor ( l_cur );
51 RETURN retval;
52* END dbms_sql_list;
Here is an explanation of the more interesting parts of the code:
Line(s) Significance
4-5 Set up the starting point for the query.
16-19 Add a placeholder to the IN list for each value in the list provided in the parameter list.
23-26 Parse the query and then define each column being queried. You will need to change this for the specific query you execute.
29-32 Bind each variable value into the dynamic cursor.
38-47 For each row fetched, retrieve the individual column values, construct an object and place that object into the collection that is being returned by the function.
Notes on this implementation:
• It is the most voluminous and complicated implementation.
• In Oracle Database 10g, you do not pay any noticeable penalty for using DBMS_SQL (compared to earlier versions); Oracle has reduced the overhead of context switches, which has greatly improved the efficiency of this package.
• You are still constrained to a maximum of 1000 items.
• This approach uses bind variables, so passing different values for IDs will not automatically force a hard parse (as would happen with the NDS implementation). If and when, however, you pass different number of values, you will have a physically distinct SQL statement and a hard parse will then occur.