Re: Help with .Net calling procedure with IN statement and string variable
- From: Gokul Kumar Gopal <gokulkumar.gopal@xxxxxxxxx>
- To: ChrisDavid.Taylor@xxxxxxxxxxxxxxx
- Date: Wed, 29 Sep 2010 15:34:15 +0200
I once solved this using subquery factoring..
with temp as
(
select
substr('&1',start_pos,decode(pos,0,9999,pos) - start_pos) str
from
(
select
pos,
next_pos+1 start_pos
from
(
select
rn,
pos,
nvl(lag(pos) over (order by rn),0) next_pos
from
(
select
rownum rn,
instr('&1',',',1,level) pos from dual
connect by level <= (length('&1') - length(replace('&1',',',''))) + 1
)
)
)
)
select * from temp;
Not sure if there are better ways in SQL. Another way to do this would
be to use a function to return values.
Rgds,
Gokul
On Wed, Sep 29, 2010 at 3:19 PM, Taylor, Chris David
<ChrisDavid.Taylor@xxxxxxxxxxxxxxx> wrote:
> 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.
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
