Enviar una matriz de valores al procedimiento de Oracle para usar en la cláusula WHERE IN

Tengo un procedimiento almacenado en Oracle como se muestra a continuación:

CREATE PROCEDURE MY_TEST_PROC( CUR OUT SYS_REFCURSOR, PARAM_THAT_WILL_BE _USED_INSIDE_WHERE_IN ) AS BEGIN OPEN CUR FOR SELECT * FROM MY_TABLE WHERE COL1 IN (here I want to put values received from C#) END; 

En el lado de la aplicación ASP.NET, tengo un elemento seleccionado con varias opciones. Quiero utilizar estos elementos de lista en mi cláusula WHERE. Sé que puedo tener un parámetro de entrada VARCHAR2 en mi proceso almacenado, hacer una cadena separada por comas de los elementos de la lista, enviarla al procedimiento. Hay dos preocupaciones con ir de esta manera:

  1. Hago que mi sitio web sea vulnerable a las inyecciones de SQL
  2. En mi proceso almacenado tengo que usar el patrón EXECUTE (‘SELECT …’) que me gustaría evitar.

¿Cómo puedo enviar estos elementos de la lista al procedimiento almacenado y usarlos dentro de la cláusula WHERE IN? Estoy usando ODP.NET y he oído hablar de UDT pero no sé cómo usarlo.

Puede agregar este parámetro de entrada separado por comas como varchar () y usar la siguiente instrucción where:

 where (','||PARAM_THAT_WILL_BE||',' like '%,'||COL1||',%') 

por ejemplo, si PARAM_THAT_WILL_BE='2,3,4,5' y col1=3 obtenemos:

 where (',2,3,4,5,' like '%,3,%') 

y es VERDADERO si el valor COL1 está en esta lista. Aquí no usa una consulta dinámica para evitar preocupaciones 1) y 2).

Una forma podría ser usar un VARRAY para el parámetro PARAM_THAT_WILL_BE _USED_INSIDE_WHERE_IN y usarlo como se describe aquí
Sin embargo, no estoy seguro de cómo llamarlo desde c #.

Otra forma es usar varchar2 con un csv como lo indicó en su pregunta, pero sin sql dynamic, como este:

 CREATE PROCEDURE MY_TEST_PROC( CUR OUT SYS_REFCURSOR, PARAM_THAT_WILL_BE varchar2) AS BEGIN OPEN CUR FOR SELECT * FROM MY_TABLE WHERE COL1 IN ( select regexp_substr(PARAM_THAT_WILL_BE, '[^,]+',1,level) p from dual t connect by level <= regexp_count(PARAM_THAT_WILL_BE, ',') + 1 ) END; 

Para este escenario, utilicé esto

CREATE PROCEDURE MY_TEST_PROC(CUR OUT SYS_REFCURSOR,A in VARCHAR2 ) AS BEGIN OPEN CUR FOR SELECT * FROM MY_TABLE WHERE COL1 IN (SELECT REGEXP_SUBSTR(**A**,'[^,]+', 1, LEVEL) FROM DUAL CONNECT BY REGEXP_SUBSTR(**A**, '[^,]+', 1, LEVEL) IS NOT NULL) END;

El valor A debe contener qutoes abierto y cerrado (‘). EX: ‘512,456,4564’ si es un valor ‘512’ como este