Problem with parameter passing

From: Jérôme VUIBERT <jerome.vuibert_at_fleximage.fr>
Date: Fri, 11 Jul 2003 12:04:03 +0200
Message-ID: <3f0e8b89$0$5402$626a54ce@news.free.fr>

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 Received on Fri Jul 11 2003 - 05:04:03 CDT