Advice 513

SQL Navigator
1

An example of code

FUNCTION b11
RETURN PLS_INTEGER
IS
a BOOLEAN;
BEGIN
a := TRUE;
RETURN 2;
EXCEPTION
WHEN OTHERS
/*ADVICE(9): This function exception handler does not contain a RETURN
statement [513] */
THEN
IF a
THEN
RETURN NULL;
ELSE
RETURN 1;
END IF;
END b11;

So the ADVICE should be another on

with more than one RETURN statement in the
executable section …maybe the 512?

2

Filipe,

I think this advice is flagged because good coding style would place only one (1) return statement in each coding block, as follows (sorry for the bad indentation, it looked good in SqlNav ;-)):

FUNCTION b11
RETURN PLS_INTEGER
IS
a BOOLEAN;
lc_ret NUMBER;
BEGIN
a := TRUE;
lc_ret := 2;
RETURN lc_ret;
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF a THEN
lc_ret := NULL;
ELSE
lc_ret := 1;
END IF;

  RETURN lc_ret;
END;

END b11;

3

Hi Dominique,

Thanks for your replay. I was curious about the ADVICE 513 in Filipe’s example but didn’t come up with any idea yet. Now I can still get the 513 with you code. I think this should be an error of the advisor. I will log CR for this.

FUNCTION b11
/* Formatted on 16-Jan-2009 18:32:55 (QP5 v5.120) */
/*ADVICE(1): Function has no parameters [514] */
RETURN PLS_INTEGER IS
a BOOLEAN;
lc_ret NUMBER;
/*ADVICE(6): NUMBER has no precision [315] */
BEGIN
a := TRUE;
lc_ret := 2;
RETURN lc_ret;
EXCEPTION
WHEN OTHERS THEN
/*ADVICE(13): This function exception handler does not contain a RETURN
statement [513] */
BEGIN
IF a THEN
lc_ret := NULL;
ELSE
lc_ret := 1;
END IF;

RETURN lc_ret;
END;
END b11;

4

It doesn’t raise the advice if I put RETURN outside of the BEGIN/END of WHEN OTHERS. Does this make sense?
FUNCTION b11
/* Formatted on 16-Jan-2009 18:39:42 (QP5 v5.120) */
/*ADVICE(1): Function has no parameters [514] */
RETURN PLS_INTEGER IS
a BOOLEAN;
lc_ret NUMBER;
/*ADVICE(6): NUMBER has no precision [315] */
BEGIN
a := TRUE;
lc_ret := 2;
RETURN lc_ret;
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF a THEN
lc_ret := NULL;
ELSE
lc_ret := 1;
END IF;
END;

RETURN lc_ret;
END b11;

5

1- With my code :

As I told above an Advice 512 (several returns) was to be expected but not the 513…

I would like to have there a 512 advice!

2- Dominique code:
Still get the 513 but would avoid a possible 512

3- Vicent Code:
That makes sense: the formatter (in error) is looking only that the last line in the exception should be a return and not realy checking the exit points.

6

Filipe, I get you piont now. Yes Advice 512 should be there. I will create CR for this.