Thursday, 8 September 2011

Use NDS with a comma-delimited list of values

Native dynamic SQL or NDS allows me to implement a dynamic IN clause very simply, as shown below. I simply concatenate my comma delimited list into the SELECT statement and open it using a cursor variable. I then return that cursor variable to the calling program, which can then fetch the individual rows identified by the WHERE clause.
FUNCTION in_clause.nds_list (list_in IN VARCHAR2)
RETURN sys_refcursor
IS
retval sys_refcursor;
BEGIN
OPEN retval FOR
'SELECT * FROM otn_question WHERE id IN ('
|| list_in
|| ')';

RETURN retval;
END nds_list;
Notes on this implementation:
• SYS_REFCURSOR is a pre-defined weak REF CURSOR type available in Oracle Database 9i Release 2 and above. You must use a weak type when working with dynamic SQL.
• Notice that for each distinct list of values, I have a different physical SQL statement. Consequently, each execution of this statement will require a hard parse of that statement, which could have a noticeable impact when a large number of users all try to hard parse at the same time.
• I am concatenating an entire chunk of the SQL statement into the WHERE clause. By doing so, I leave myself open to the possibility of code injection: a clever (and malicious) user might pass code in the list_in argument that causes unintended activity to occur. A good way to avoid this problem is to work with bind variables rather than a string list, which is possible using DBMS_SQL.
• You will only be able to use this approach if you have less than 1001 items in the list.

No comments:

Post a Comment