Varying list using a pipelined function
Erick T, October 17, 2007 - 12:43 pm UTC
Instead dynamic code I have been used the joe garrepy string package
http://www.joegarrepy.com/str_package.htm http://plnet.org/ The use of the piplelined function (split_pipe) is as follows:
Select name
from employees
where employe_id in (SELECT to_number(string_value)
FROM TABLE(str.split_pipe('564,8,76,654,81'),','))
I hope this help you
Regards
Erick
Variable-Sized in Lists
Allan, October 18, 2007 - 8:59 am UTC
Also, for relatively small lists, you can put the values in a nested table, then pass the nested table into the query.
create TYPE t_varchar_1 IS TABLE OF VARCHAR2 (1)
/
DECLARE
nt_test t_varchar_1 := t_varchar_1 ('X', 'Y', 'Z');
BEGIN
FOR r_test IN (SELECT *
FROM DUAL
WHERE dummy IN (select * from table(nt_test))) LOOP
DBMS_OUTPUT.put_line (r_test.dummy);
END LOOP;
END;
Wrong syntax
Erick T, October 18, 2007 - 7:16 pm UTC
/*
This is the right syntax for the previous example
*/
CREATE OR REPLACE
TYPE apps.cust_col_str
AS TABLE OF VARCHAR(1)
/
-- End of DDL Script for Type APPS.CUST_COL_STR
DECLARE a cust_col_str:=cust_col_str('X','y','z');
BEGIN
FOR r_test IN (SELECT *
FROM DUAL
WHERE dummy IN (select * from table(cast (a AS cust_col_str)))) LOOP
DBMS_OUTPUT.put_line ('Found: '||r_test.dummy);
END LOOP;
END;
costing
Connor, October 22, 2007 - 3:05 am UTC
Just be careful with the TABLE() option when it comes to costing. I can't remember off hand, but Oracle assumes something like 8k rows coming back from the TABLE() which might impact your plans.
hth
connor
October 23, 2007 - 1:35 pm UTC
Also: consider the cardinality hint
Stew Ashton, October 23, 2007 - 4:40 pm UTC
With this hint you can tell the optimizer how many rows to expect from a query or subquery when statistics are unavailable.
Might be useful with the global temporary table, or with the "varying in list" solution:
http://asktom.oracle.com/~tkyte/cardinality.html
Helena Marková, October 24, 2007 - 3:42 am UTC
cardinality
Tom, November 10, 2009 - 2:39 pm UTC
November 11, 2009 - 3:56 pm UTC