Do a IN in stored procedure with variable

Question

I'm developing a stored procedure and i'm stuck in a step. In some point i have this:

stmt := stmt || ' AND Risk.Code IN (:4) ';

How can i make risk.code do a in when :4 is a text variable that contains the list of values "1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20"

bind variables are not applicable for `IN` clause this way. It should be like `IN (:1,:2,:3)`. You can insert these values into a nestd tables, and `CAST` it as a table. [Example](http://stackoverflow.com/questions/21695220/oracle-dynamic-query-with-in-clause-using-cursor/21695311#21695311). You can also try out [NUMBER_TABLE](http://docs.oracle.com/cd/E11882_01/appdev.112/e10577/d_sql.htm) - along with `DBMS_SQL` as shown [here](http://stackoverflow.com/questions/21335493/can-somebody-explain-dbms-sql-number-table/21336827#21336827) — Maheswaran Ravisankar, Jun 18 '14 at 16:56
Tom Kyte has documented an approach to this type of problem, http://tkyte.blogspot.com/2006/06/varying-in-lists.html. — Patrick Bacon, Jun 18 '14 at 17:19

score 0 · Accepted Answer · answered Jun 18 '14 at 17:08

0

Found it,

stmt := stmt || ' AND Risk.Code IN ('|| P_RISK ||') ';

answered Jun 18 '14 at 17:08

Luis

2,665
8
44
70

That is risky because of SQL Injection. – user1944408 Jun 19 '14 at 15:10

score 0 · Answer 2 · answered Jun 19 '14 at 15:38

First you need to convert comma separated values into an varray and then use

stmt := stmt || ' AND Risk.Code IN (select column_value from TABLE(v_my_data))';

One technique: https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement

Do a IN in stored procedure with variable

2 Answers2