Re: Stored procedure parameter

On 8 août, 12:06, Aravindh <knaravind..._at_gmail.com> wrote:
> On Aug 8, 3:04 pm, Aravindh <knaravind..._at_gmail.com> wrote:
>
>
>
>
>
> > On Aug 8, 2:33 pm, "sfauchi..._at_gmail.com" <sfauchi..._at_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- Masquer le texte des messages précédents -
>
> - Afficher le texte des messages précédents -

Thanks for your help but I didn't find the answer. The exemple you gave me is to convert to string, but for me, I have in my variable "ListOfPoint" that i receive from crystal '1,2,3' and when i try to put it in the SELECT col1,col2 FROM MyTable WHERE Col3 in (ListOfPoint) i have an error because Col3 need to be number. I mean by the substitution the select become :SELECT col1,col2 FROM MyTable WHERE Col3 in ('1,2,3') but it should be SELECT col1,col2 FROM MyTable WHERE Col3 in (1,2,3)
I'm not sure you understand my english (sorry)!!!!!

Do i really have to do the conversion of my multi values parameter into string with comma separator in Crystal, or oracle can get directly the list into an array (for exemple) eg:

CREATE OR REPLACE PROCEDURE MyPrc
(ListOfPoint in Number_Array ) AS
BEGIN
SELECT col1,col2 FROM MyTable WHERE Col3 in (ListOfPoint) END; Thanks Received on Wed Aug 08 2007 - 06:04:19 CDT