Path: text.usenetserver.com!out02a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!e16g2000pri.googlegroups.com!not-for-mail
From:  Aravindh <knaravindh81@gmail.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Stored procedure parameter
Date: Wed, 08 Aug 2007 10:06:01 -0000
Organization: http://groups.google.com
Lines: 99
Message-ID: <1186567561.635643.158160@e16g2000pri.googlegroups.com>
References: <1186565589.969375.297460@22g2000hsm.googlegroups.com>
   <1186567453.720898.115940@i38g2000prf.googlegroups.com>
NNTP-Posting-Host: 203.99.195.64
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
X-Trace: posting.google.com 1186567562 20942 127.0.0.1 (8 Aug 2007 10:06:02 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 8 Aug 2007 10:06:02 +0000 (UTC)
In-Reply-To: <1186567453.720898.115940@i38g2000prf.googlegroups.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322),gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.0 CTSINTCOISA6, 1.1 CTSINTCOISA7
Complaints-To: groups-abuse@google.com
Injection-Info: e16g2000pri.googlegroups.com; posting-host=203.99.195.64;
   posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0
Xref: usenetserver.com comp.databases.oracle.server:432629
X-Received-Date: Wed, 08 Aug 2007 10:43:04 EDT (text.usenetserver.com)

On Aug 8, 3:04 pm, Aravindh <knaravind...@gmail.com> wrote:
> On Aug 8, 2:33 pm, "sfauchi...@gmail.com" <sfauchi...@gmail.com>
> wrote:
>
>
>
>
>
> > Hello,
>
> > Sorry for my english, i'll try to explain my problem
>
> > I'm working with Crystal Report X and i call a oracle stored
> > procedure.
>
> > One of the parameter (ListOfPoint) of crystal is a multi value (id of
> > a table)
>
> > In the Stored procedure i'd like to get this parameter to do something
> > like that :
>
> > CREATE OR REPLACE  PROCEDURE MyPrc
> > (ListOfPoint in ?????? ) AS
>
> > BEGIN
>
> > SELECT col1,col2 FROM MyTable WHERE Col3 in (ListOfPoint)
>
> > END;
>
> > Do you have an idea??
>
> > I tried this :
>
> > - in crystal, convert the list of parameter ListofPoint into string by
> > a join to get a "1,2,3"
> > - in the stored procedure declare the ListOfPoint into varchar2 but
> > the select do not work because Col3 is a number
>
> Hi ,
>
> >From what you state I understand that multiple values need to be
>
> passed in the input in the same variable. So what you can probably do
> is :-
> You can have a input variable name as M_multipleinput and declare it
> as varchar.
> You can input the values separated by a comma (INPUT1,INPUT2,INPUT3
> etc...)
> Then you can use a parsing logic to separate the input. The parsing
> logic can be something like this..
> The separated inputs can be inserted into a GLOBAL temporary table and
> the select can be modified to fetch from the Global temporary table.
>
>  temp_reportidcollection := reportidcollection;
>    temp_reportidcollection1 := reportidcollection;
>    delimiter := ',';
>
>    WHILE (INSTR (temp_reportidcollection1, delimiter, 1, 1) > 0)
>    LOOP
>       INSERT INTO CSXCOLLECTIONTMP
>            VALUES (TO_NUMBER (TRIM (SUBSTR (temp_reportidcollection1,
>                                             1,
>                                               INSTR
> (temp_reportidcollection1,
>                                                      delimiter,
>                                                      1,
>                                                      1
>                                                     )
>                                             - 1
>                                            )
>                                    )
>                              ));
>
>       temp_reportidcollection1 :=
>          SUBSTR (temp_reportidcollection1,
>                  INSTR (temp_reportidcollection1, delimiter, 1, 1) +
> 1,
>                  LENGTH (temp_reportidcollection1)
>                 );
>    END LOOP;
>
> SELECT col1,col2 FROM MyTable WHERE Col3 in ( select * from
> CSXCOLLECTIONTMP)
>
> Please get back if you still have any questions. Thanks
>
> Regards
> KN Aravindh- Hide quoted text -
>
> - Show quoted text -

Hi ,

As stated by Sybrandb you can also use Dynamic Execute Immediate.

Thanks
KN Aravindh