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

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

Re: Stored procedure parameter

From: William Robertson <williamr2019_at_googlemail.com>
Date: Wed, 08 Aug 2007 23:05:51 -0700
Message-ID: <1186639551.946032.323650@d55g2000hsg.googlegroups.com>


On Aug 8, 10:01 am, sfauchi..._at_gmail.com wrote:
> On 8 août, 10:57, 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

Ideally you would pass in a collection, although I don't know how easy Crystal Reports makes this. Alternatively you might be able to define a constructor expression, so that instead of

myproc(p_list)

it becomes

my_proc( my_collection_type(1,3,456,99) )

http://www.williamrobertson.net/documents/comma_separated.html
http://www.oracle-developer.net/display.php?id=301
http://tkyte.blogspot.com/2006/06/varying-in-lists.html
Received on Thu Aug 09 2007 - 01:05:51 CDT

Original text of this message

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