Compiler Hints

[aotte]

Hi,

You said you are always on the look out for new compiler hints to add, well. rummaged through all the books (no need to re-invent the wheel), and all my checklists, and came up with the list below.

Some are rather extreme, so you might want to build switches for these. And I didn't phrase things to prevent holy wars, so some of them might cause some replies

Flexibility and maintainablity:
===============================
Hard-coded variable length, scale, or precision

Any hard coded value that isn't part of a constant or subtype declaration

SELECT *

INSERT INTO [TABLE] VALUES

FUNCTIONS with OUT or IN OUT parameters (Switchable setting, if your standard is to only return a boolean for success or failure)

Coding clarity:
===============
Module END statements without module name
Package, Function, Procedure, Loop
(Beautifier possibilities)

Missing Block, Loop, and Goto (Whatever your beliefs on Goto's is) labels

Repeated conditions in "nested" IFs structure, to prevent for example:
  IF v_salary > c_mid_range AND v_company_car = c_no THEN
     <whatever>
  ELSIF v_salary > c_mid_range AND v_company_car <> c_no THEN
     <something>
  ELSIF v_salary <= c_mid_range AND v_company_bike = c_no THEN
     <et cetera>
  ELSE
     <whachammacallit>
  END IF

which would be clearer, coded as

  IF v_salary > c_mid_range THEN
     IF v_company car = c_no THEN
        <whatever>
     ELSE
        <something>
     END IF
  ELSIF v_company_bike = c_no THEN
     <et cetera>
  ELSE
     <whachammacallit>
  END IF

Exceeding a given limit of AND, OR and NOT in IF statements
(This might give way to a code analyzer addition to PL/SQl Developer, i.e. code compexity score based on number of decision points)

NULL return value on FUNCTIONs returning a BOOLEAN

Hard to find bugs:
==================
Implicit data type conversions/comparisons:
E.g. TRIM(SYSDATE) instead of TRUNC(SYSDATE)
     ENTRY_DATE = '03/04/03'

Any variable delared in the package specification, instead of the package body

Multiple EXITs in LOOPs, RETURNs in FUNCTION bodies

LOOPs without EXIT

FUNCTIONs with possible execution path without RETURN

DELETE WITHOUT WHERE (Already on the list, I believe)

UPDATE WITHOUT WHERE (Already on the list, I believe)

EXITs in FOR or WHILE loops

Declare FOR loop index as variable (from Steven Feuerstein's Oracle PL/SQL Best Practices)

 

[Marco Kalter]

Thanks! We'll consider them for a future 5.1.x update.

------------------
Marco Kalter
Allround Automations

 

[SvenHeuer]

one more

update table set FieldA=FieldA where blah

I searched today two hours for this stupid code (it should be ... FieldA=fFieldA ..).

Greetings
Sven

 

[aotte]

Two more:

1) Database names preceed variable name, i.e. the below will delete ALL rows.

DECLARE
ename VARCHAR2(10) := 'KING';
BEGIN
DELETE FROM emp WHERE ename = ename;

2) Really stupid, but when you have been working on the code for hours, you tend to become impervious of the obvious, especially if it is a nested select:

BETWEEN 3 AND 1

(Yeah, I know, no magic numbers! ;-) Still would be nice to have a compiler hint for it.)

------------------
Hakuna Matata,

Arnoud.

 

[vopipari]

Here is another:

assignments of hard coded values to variables:

declare
c_name constant varchar2(5) := '938872093';
begin
...

only generates a runtime error. Would be nice to get a compile error, but would settle for a warning/hint...

Thanks