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

  I no longer count how many times I have explained this to developers ... Just tell them to replace what is between parentheses after the IN by the subquery below.

variable v_tid varchar2(100)

begin

   :v_tid := '5000,5001,5002,5003';

end;

/

select substr(tid,

              instr(tid, ',', 1, rn) + 1,

              instr(tid, ',', 1, rn + 1)

• instr(tid, ',', 1, rn) - 1) id

from (select rownum rn, tid

      from (select ',' || trim(',' from :v_tid) || ',' tid

            from dual)

      connect by rownum < length(tid) - length(replace(tid, ',', '')))

/

Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>

On 09/29/2010 03:19 PM, Taylor, Chris David 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_at_ingrambarge.com <mailto:chris.taylor_at_ingrambarge.com>
>
>
>
> *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.*
>
>
>

--
http://www.freelists.org/webpage/oracle-l