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