Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Stored procedure parameter

Re: Stored procedure parameter

From: Aravindh <knaravindh81_at_gmail.com>
Date: Wed, 08 Aug 2007 10:04:13 -0000
Message-ID: <1186567453.720898.115940@i38g2000prf.googlegroups.com>


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 Received on Wed Aug 08 2007 - 05:04:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US