who can i exit procedure or function in oracle before the end statment
ahmed eitah
who can i exit procedure or function in a package in oracle before the end statment for example
PROCEDURE hsabintegrated(awale in varchar2,TANYe in varchar2,talte in varchar2,
rab3e in varchar2,DALEEL in NUMBER,asmhsabe in varchar2)is
var_hsab hsab%rowtype;
begin
IF DALEEL = 0 THEN
exit PROCEDURE; ---> ___>>___>>>>>>>>>>/* how can i do this */
ELSIF DALEEL = 1 THEN
select * into var_hsab FROM hsab where asmhsab = asmhsabe;
else
raise_application_error(-20203,'daleel is either 0 or 1 check daleel');
end if;
COMMIT;
end hsabintegrated;
how can i exit this PROCEDURE before the end. what statment i need that equals exit PROCEDURE or function in other languages.
thanks in advance
ahmad
ddf
You need to declare an exception, raise it at the appropriate place
then handle the exception properly:
PROCEDURE hsabintegrated(awale in varchar2,TANYe in varchar2,talte in
varchar2,
rab3e in varchar2,DALEEL in NUMBER,asmhsabe in varchar2)is
var_hsab hsab%rowtype;
my_ex exception;
wrong_val exception;
pragma exception_init(my_ex, -20001);
pragma exception_init(wrong_val, -20203);
begin
IF DALEEL = 0 THEN
raise my_ex;
ELSIF DALEEL = 1 THEN
select * into var_hsab FROM hsab where asmhsab = asmhsabe;
else
raise wrong_val
end if;
COMMIT;
exception
when my_ex then
null;
when wrong_val then
raise_application_error(-20203,'daleel is either 0
or 1 check daleel');
when others then
-- Output desired error message
dbms_output.put_line('-20999: Sumthin'' bad happened
-- error stack follows');
-- Output actual line number of error source
dbms_output.put(dbms_utility.format_error_backtrace);
-- Output the actual error number and message
dbms_output.put_line(dbms_utility.format_error_stack);
end hsabintegrated;
/
Of course exiting in the middle of a procedure is, well, wrong as you
should code for what you want to process and then set up the code to
ignore what you do not want to process:
PROCEDURE hsabintegrated(awale in varchar2,TANYe in varchar2,talte in
varchar2,
rab3e in varchar2,DALEEL in NUMBER,asmhsabe in varchar2)is
var_hsab hsab%rowtype;
begin
IF DALEEL = 1 THEN
select * into var_hsab FROM hsab where asmhsab = asmhsabe;
end if;
exception
when others then
-- Output desired error message
dbms_output.put_line('-20999: Sumthin'' bad happened
-- error stack follows');
-- Output actual line number of error source
dbms_output.put(dbms_utility.format_error_backtrace);
-- Output the actual error number and message
dbms_output.put_line(dbms_utility.format_error_stack);
end hsabintegrated;
/
Input of 0 is essentially ignored and processing passes through to the
end of the procedure with no work performed.
David Fitzjarrell
Shahzad Yousuf
rab3e in varchar2,DALEEL in NUMBER,asmhsabe in varchar2)is
var_hsab hsab%rowtype;
begin
select * into var_hsab FROM hsab where asmhsab = asmhsabe;
COMMIT;
end hsabintegrated;
Shahzad Yousuf
ddf
> Shahzad Yousuf- Hide quoted text -
>
> - Show quoted text -
Where is your exception handler? I don't see one coded and that
leaves any errors unhandled making it difficult to troubleshoot the
code or the input. And I wrote the same code, with properly handled
exceptions, before you posted your 'answer'. I would do you a great
service to read the entire thread before responding.
David Fitzjarrell