Re: Stored procedure parameter

From: <sfauchille_at_gmail.com>
Date: Wed, 08 Aug 2007 15:00:02 -0000
Message-ID: <1186585202.678033.293910@22g2000hsm.googlegroups.com>

On 8 août, 15:36, sybrandb <sybra..._at_gmail.com> wrote:
> 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 fromhttp://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2...
>
> It is THAT simple.
>
> --
> Sybrand Bakker
> Senior Oracle DBA- Masquer le texte des messages précédents -
>
> - Afficher le texte des messages précédents -

thanks a lot for your answer, it works.
I had to to some modification in the in_list function to get numbers:

 create or replace type myTableTypeNumber as table of number;

create or replace function in_list (p_string in varchar2) return myTableTypeNumber
as

	l_string long default p_string || ',';
	l_data myTableTypeNumber := myTableTypeNumber();
	n number;

begin
	loop
		exit when l_string is null;
		n := instr(l_string,',');
		l_data.extend;
		l_data(l_data.count) := TO_NUMBER(ltrim(rtrim(substr(l_string,
1,n-1))));
		l_string := substr(l_string,n+1);
	end loop;
	return l_data;

end;
/

Strange that i have to convert an array in a string (crystal) and after convert in string into a table (oracle) for make it work.

Thank you again

Stephan Received on Wed Aug 08 2007 - 10:00:02 CDT