Help with .Net calling procedure with IN statement and string variable

  • From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 29 Sep 2010 08:19:42 -0500

Ok guys, I have a developer that needs some help and for some reason I'm 
drawing a blank - perhaps because I'm not a VB .Net developer.

They have a screen where a user can select multiple values (all numeric) and 
then they want to execute a stored procedure from .Net with an "IN" statement.

So, here's what their variable is setup as:

v_tid varchar2(5000);

Then they're passing this in from .Net: 5000,5001,5002,5003

The code they're trying to execute in Oracle is:

Select ....
From ....
Where trip_id in (v_tid); /* (where v_tid is the comma delimited string of 
numbers) */

This returns the ORA-01722 invalid number error.

If I'm not mistaken this is because the value inside the () looks like 
('5000,5001,5002,5003') which is no good.  I tried wrapping the IN statement in 
a replace and replace tick marks with nothing but that still returns ORA-01722.

Since I know this is something that gets done fairly regularly, how can they 
accomplish passing a comma delimited string into a procedure?  The procedure 
will return a ref cursor of the results.


Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-663-1673
Email: chris.taylor@xxxxxxxxxxxxxxx<mailto:chris.taylor@xxxxxxxxxxxxxxx>

CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and 
may also be privileged. If you are not the named recipient, please notify the 
sender immediately and delete the contents of this message without disclosing 
the contents to anyone, using them for any purpose, or storing or copying the 
information on any medium.

Other related posts: