Re: Stored procedure parameter

From: sybrandb <sybrandb_at_gmail.com>
Date: Wed, 08 Aug 2007 06:36:42 -0700
Message-ID: <1186580202.603035.266810@l70g2000hse.googlegroups.com>

On Aug 8, 1:04 pm, "sfauchi..._at_gmail.com" <sfauchi..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Ok, I didn't get my message through, or I asked too much (to spend some effort in resolving your own problem) so here it is

ops$tkyte_at_dev8i> create or replace

     function in_list( p_string in varchar2 ) return myTableType   2 as

  3      l_string        long default p_string || ',';
  4      l_data          myTableType := myTableType();
  5      n               number;

  6 begin
  7 loop

  8        exit when l_string is null;
  9        n := instr( l_string, ',' );
10         l_data.extend;
11         l_data(l_data.count) :=
                 ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
12         l_string := substr( l_string, n+1 );

13 end loop;
14
15 return l_data;
16 end;
17 /

Function created.

ops$tkyte_at_dev8i> select *
  2 from THE

        ( select cast( in_list('abc, xyz, 012') as
                              mytableType ) from dual ) a

  3 /

COLUMN_VALUE

---

abc
xyz
012

ops$tkyte_at_dev8i> select * from all_users where username in   2 ( select *
  3 from THE ( select cast( in_list('OPS$TKYTE, SYS, SYSTEM')

                         as mytableType ) from dual ) )

  4 /

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
OPS$TKYTE                           23761 02-MAY-00
SYS                                     0 20-APR-99
SYSTEM                                  5 20-APR-99

 This is from http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425

It is THAT simple.

--
Sybrand Bakker
Senior Oracle DBA

Received on Wed Aug 08 2007 - 08:36:42 CDT