Re: Stored procedure parameter

On Aug 10, 2:38 pm, "sfauchi..._at_gmail.com" <sfauchi..._at_gmail.com> wrote:
> On 10 août, 14:16, William Robertson <williamr2..._at_googlemail.com>
> wrote:
>
>
>
> > On Aug 10, 9:54 am, "sfauchi..._at_gmail.com" <sfauchi..._at_gmail.com>
> > wrote:
>
> > > On 9 août, 17:25, DA Morgan <damor..._at_psoug.org> wrote:
>
> > > > Florian Reiser wrote:
> > > > > Hello sfauchille,
>
> > > > > in the stored procedure you first have to parse your string and convert
> > > > > the elements back to numbers again.
>
> > > > > With kind regards
> > > > > Florian Reiser
> > > > >http://www.ra-bc.de
>
> > > > No you don't. You can use CAST. Here's one example:
>
> > > > conn scott/tiger
>
> > > > CREATE OR REPLACE TYPE InStrTab IS TABLE OF VARCHAR2(20);
> > > > /
>
> > > > set serveroutput on
>
> > > > DECLARE
> > > > i PLS_INTEGER;
> > > > x InStrTab := InStrTab('10','30');
> > > > BEGIN
> > > > SELECT COUNT(*)
> > > > INTO i
> > > > FROM emp
> > > > WHERE deptno IN (
> > > > SELECT column_value
> > > > FROM TABLE(CAST(x AS InStrTab)));
>
> > > > dbms_output.put_line(i);
> > > > END;
> > > > /
>
> > > > --
> > > > Daniel A. Morgan
> > > > University of Washington
> > > > damor..._at_x.washington.edu (replace x with u to respond)
> > > > Puget Sound Oracle Users Groupwww.psoug.org
>
> > > Thank you, but the problem is to pass an array from crystal report to
> > > oracle
>
> > What happened when you tried my suggestion? Given that nobody here has
> > Crystal reports, what does the call to the procedure look like?
>
> > btw you need TABLE() or MEMBER OF depending on your Oracle version,
> > which I think you forgot to mention. Explicit casting is not generally
> > needed in versions above 8i.- Masquer le texte des messages précédents -
>
> > - Afficher le texte des messages précédents -
>
> Yes I tried what you said :
> CREATE OR REPLACE PROCEDURE MyProc
> x InStrTab := InStrTab('10','30');
> AS
> BEGIN
> ...
> END
>
> But when i do this, in crystal report i can't bind (i'm not sure if
> that's the correct word in english) my parameter to this stored proc's
> parameter and I don't know why.

That example is missing some brackets so I'm not sure what I'm looking at. I would have expected the procedure to look like

CREATE OR REPLACE PROCEDURE MyProc (p_points IN instrtab) AS ...

I was really wondering what the call looked like in Crystal Reports. And your Oracle version, unless it's a secret.

I'm not sure where Daniel is going with the IN OUT thing but I'm sure it'll be worth it. Received on Fri Aug 10 2007 - 19:13:07 CDT