Re: Help with .Net calling procedure with IN statement and string variable
- From: Rajaram Subramanian <rajaram.subramanian@xxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>, ChrisDavid.Taylor@xxxxxxxxxxxxxxx
- Date: Wed, 29 Sep 2010 13:38:16 +0000 (GMT)
Check this link.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:110612348061
Regards
Raj
--- On Wed, 29/9/10, Taylor, Chris David <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
wrote:
From: Taylor, Chris David <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
Subject: Help with .Net calling procedure with IN statement and string variable
To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
Date: Wednesday, 29 September, 2010, 13:19
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.
Thanks,
Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-663-1673
Email: 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:
