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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Sat Aug 11 2007 - 00:08:29 CDT
