Comma separated string parameter in Oracle procedure passed from .NET code

Hi,

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)
IS
BEGIN
    OPEN MY_CURSOR FOR
        SELECT * FROM MyTable
        WHERE productId IN stringParam;
END MyProc;

3. The parameter stringParam is sendt from .NET code. Possible values for this parameter are for example:
'15'
'22,89'
'12,46,99'

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.
kathysmithAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johanntagleCommented:
You need to use a user defined pl/sql array, populate that based on the string passed to the procedure, then use it via "IN table(cast (array_name))".  Something like:

create or replace type g_array as table of varchar2(50);

Then in your stored proc you have
PROCEDURE MyProc.....
  p_list g_array;
  <etc>
BEGIN
  p_list := g_array('01','02','03'); /*you need to replace this part with a procedure to split the input string into the array*/
  OPEN CURSOR FOR
    SELECT *
      FROM MyTrable where productId in (select * from TABLE(CAST(p_list AS g_array)));
END;

johanntagleCommented:
You can get some ideas re how to split the string here:

http://glosoli.blogspot.com/2006/07/oracle-plsql-function-to-split-strings.html
PilouteCommented:
Hi,

Oracle is able to make some implicit conversions... So if you say the this works :

SELECT * FROM MyTable WHERE productId IN (11,12);

and this doesn't :

SELECT * FROM MyTable WHERE productId IN ('11,12');

the reason is pretty simple :

You are targetting a key that is either 11 or 12. How it is stored is not an issue, since oracle will easily convert a number in a string (numbers do not use quotes / strings use quotes), or a string into a number...

The second example doesn't work because you ask oracle to identify a key that is a five caracter string : '11,12'. This wont be converted into 11 OR 12 because you tell oracle ths whole string is a single unit.

If you want the string to be resolved to 11 OR 12, you have to set it as 2 separate strings (pay attention to the quotes) :

SELECT * FROM MyTable WHERE productId IN ('11','12');

Passing a string parameter is fine, but either you pass it without any quotes or you quote each element of the string...

Cheers,
P
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Robert SchuttSoftware EngineerCommented:
you could do it with your current parameter as well:

SELECT * FROM MyTable WHERE INSTR(','||stringParam||',', ','||productId||',', 1, 1) > 0;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
Modify the stored procedure to use dynamic sql.  Something like(typed in, untested):

OPEN MY_CURSOR FOR
       'SELECT * FROM MyTable ' ||
        ' WHERE productId IN ('|| stringParam|| ')';
kathysmithAuthor Commented:
Thank you very much to all of you guys for answers!

Since I've needed a quick fix in my real scenario, I decided to try first the code from robert_schutt:

SELECT * FROM MyTable WHERE INSTR(','||stringParam||',', ','||productId||',', 1, 1) > 0;

 I've also implemented this code in the real procedure, and the .NET code which executes this procedure works perfectly. Thanx Robert!
kathysmithAuthor Commented:
In my situation this was the easiest solution. I really have bad time now, and I needed to fix the bug in a procedure that caused the .NET application to fail. Robert's solution was the quickest to follow.
johanntagleCommented:
Robert: won't your solution will be slow once the table grows because you will be comparing all values of MyTable.productId to the entered parameter?  Note that any standard index on productId likely won't be used by your query.

Slightwv: won't this approach cause problems once you have many users because it doesn't use bind variables?

kathysmith: would also suggest that you also test joining your table with the casted table instead of having a subselect within the IN clause, especially if you expect many search parameters.  Many times a join is faster - but as Tom Kyte of asktom.oracle.com always says, you should test.
Robert SchuttSoftware EngineerCommented:
Johann, you're probably right; I should have said "as a quick fix you could try..." but as it turns out that's what Kathy wanted for the moment. If I can find some more time I'll see if there's clear evidence (in an execution plan maybe) that you wouldn't want to do this on a table with millions of rows, might be nice to know if only for future reference.
kathysmithAuthor Commented:
In reality, the table I'm working with will change very seldom. It's a table with about 60000 records.
There is no index on productId, and it will not be any index in the future. It's a very simple table, but I'm new beginner in PL/SQL, so it takes time to learn.

I'll test the each of your solution, guys, during the week-end. The problem now is that I'm under pressure to get things done here and I needed a quick solution. But I'm grateful to each of you.


johanntagleCommented:
Hi Kathy,

When you have time I suggest you do index productId, especially when you try the other solutions.  Since you said the table will seldom change, you will experience minimal overhead after the initial index creation, but a lot better performance when you query it.

I will stop monitoring this thread after this reply, so just post another question if you have problems implementing my suggested solution.  Thanks.

Johann
kathysmithAuthor Commented:
I promised to test each of your proposed solutions. First of all, in reality, MyTable:
- has about 60000 records
- will be seldom changed
- has index on ProductDescr field (see another question I posted earlier about this issue)
- is not and willl not be related to other tables
- productId won't be an index
- MyProc procedure has several other parameters
- there are maximum 20 users for the .NET application that gets data from this table
So, this is a very simple situation.

@slightwv: Hi again! The code you sent worked! The only problem is that I should need too many changes in the procedures.  But I can use the code some other time. Thanx!

@johanntagle: thank you, the link you sendt is an inspiration. I also found this one, and I'll try to use it when I'll re-write the procedure:
http://www.dbforums.com/oracle/998642-pl-sql-splitting-string-into-array.html

@robert_schutt: I'm using your solution, works perfectly!

The lesson: next time I should share points... Thank you so much for help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.