I need your help. The simplified description of my problem is here:
1. I have an Oracle table, MyTable. One of its fields is productId, of type VARCHAR2(10). This field contains f.ex 15. Other example is 06. You'll never find other type of strings here.
2. I've wrote the following procedure (simplified):
PROCEDURE MyProc(stringParam IN VARCHAR2, MyCursor OUT T_CURSOR)
OPEN MY_CURSOR FOR
SELECT * FROM MyTable
WHERE productId IN stringParam;
3. The parameter stringParam is sendt from .NET code. Possible values for this parameter are for example:
This means that stringParam might sometimes be a comma separated string.
4. MyProc works correct if myParam consist of one sequence, for example '15'. It works correct both from my .NET application and in Toad for Oracle.
5. The problem is that MyProc doesn't work correct if myParam is a comma separated string (for example '12,46,99'). "Doesn't work correct" means I get 0 records back, even MyTable contains productId having the values 12 or 46 or 99.
I have tested several selects, and here are the results:
-- the following works
SELECT * FROM MyTable WHERE productId IN ('02');
SELECT * FROM MyTable WHERE productId IN (2);
SELECT * FROM MyTable WHERE productId IN (11,12);
-- the following doesn't work
SELECT * FROM MyTable WHERE productId IN ('11,12');
QUESTION: myParam is passed as a comma separated string from .NET code. How should I changed MyProc so that it works correctly? I'm trying to find out if there are some arrays in PL/SQL, but I'm not sure if this is the right direction.