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: <sfauchille_at_gmail.com>
Date: Mon, 13 Aug 2007 11:17:30 -0000
Message-ID: <1187003850.617910.294330@l70g2000hse.googlegroups.com>


On 11 août, 07:08, DA Morgan <damor..._at_psoug.org> wrote:
> William Robertson wrote:
> > 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.
>
> When Crystal calls Oracle stored procedures the parameters MUST be
> defined as IN OUT. Don't know why but that is their rule. At least
> it was in versions 9 and 10.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Masquer le texte des messages précédents -
>
> - Afficher le texte des messages précédents -

I'd tried what you said. First thing i tried :

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

But in Crystal when I do a "verify database", it send me an error

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

The verify database works but i can not bind my crystal's parameter to oracle's parameter

Finally, i will stay the the solution that works. To transform in Crystal the list into a string with comma separator, and in oracle, transform the string into a table.

Oracle 9i, and crystal report X

Thank you for your help

Stef Received on Mon Aug 13 2007 - 06:17:30 CDT

Original text of this message

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