Re: Problem with parameter passing

Originally posted by JéRôMe Vuibert
> Hello Hi everybody !
>
> I have a problem getting back out parameter values, when
> exceptions are
> raised.
>
> Here is my problem :
>
> Let say I have the following sql script
>
> -------------------------------------
> script
> -------------------------------------
>
> declare
> l_param VARCHAR2 := 'tata';
> l_status INTEGER;
> begin
> package_name.procedure_name(l_param , l_status);
> dbms_output.putline('l_status = ' || l_status);
>
> exception
> when others then
> dbms_output.putline('exception - l_status = ' ||
> l_status);
> end;
>
> and the following definition of the procedure (i didn't write all the
> package declaration, synonyms, grant and so on)
> -------------------------------------
> package, procedure definition
> -------------------------------------
>
> PROCEDURE procedure_name(l_p IN VARCHAR2, l_s OUT INTEGER);
> IS
> begin
> l_s := 0;
>
> if (l_p 'tata') then
> l_s := -1;
> raise_application_error(-20100, 'Different of tata');
> end if;
> end;
>
> ----
> The problem the following :
> 1. When i set l_param toto 'tata', I have the following message
> displayed :
> l_status = 0;
> 2; But when i set l_param to 'tutu', I have the following message
> displayed
> :
> exception - l_status =
> (there is no value after the = sign)
>
> Why the value of the parameter is not
> passed???????????????????????????????
>
> I have also noticed the follwing facts :
> - if procedure_name calls another procedure (other_procedure)
> which is
> declared on the same way (in parameters, and one out parameter for
> a status
> (let say the_status)),
> - the return value from other_procedure in the_status is correct
> - if i equals l_s to the_status, in procedure_name, l_s as the
> correct
> value
> - but in the script l_status does not contain value if an
> exception has
> been raised .
>
> ---------
>
> I know this is not a traditionnal way for exception programming,
> but we have
> to get some out parameters if an exxception is raised.
>
> Already a big thank for people who will give a solution at my problem.
>
> Sincerely.
>
> Jerome

I'm afraid you will have to adapt to the way PL/SQL works, rather than the way you think it SHOULD work! If you raise an exception, then values assigned to OUT parameters will NOT be returned to the calling program (as you just demonstrated).

Solution: you have a choice:

1. Don't raise an exception, use the status of -1 to indicate that an error occured
2. Raise and handle the exception, but don't expect to have been given a status value as well as an exception. If you need to differentiate between exception reasons, use different exceptions.

I'd go for 2: using call status flags is a C programmer's way of thinking!

--
Posted via http://dbforums.com