Skip to Main Content

SQLcl

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

23.3.1.0: False positives by CODESCAN

Peter L.Feb 1 2024

The source below triggers the following warnings with CODESCAN, that I assume are false positives.

Would you agree? Did I make a mistake? Thanks!

I ignore these warnings to keep the code simple:
-ignore G-5010,G-7430,G-4380,G-4320,G-4370

*** 10 distinct warnings
Warning (5,29): G-2610: Never use self-defined weak ref cursor types
Warning (16,7): G-7420: Always make the RETURN statement the last statement of your function
Warning (29,25): G-5060: Avoid unhandled exceptions
Warning (36,54): G-2140: Never initialize variables with NULL
Warning (41,5): G-7160: Always explicitly state parameter mode
Warning (41,5): G-7330: Always assign values to OUT parameters
Warning (51,12): G-3145: Avoid using SELECT * directly from a table or view
Warning (57,25): G-3180: Always specify column names instead of positional references in ORDER BY clauses
Warning (70,5): G-4140: Avoid executing any statements between a SQL operation and the usage of an implicit cursor attribute
Warning (94,9): G-4375: Always use EXIT WHEN instead of an IF statement to exit from a loop

This is the test source with explanations under the reported warnings.

CREATE OR REPLACE PACKAGE BODY test
AS
  -- G-2610: Never use self-defined weak ref cursor types
  --   Looks strong enough to me
  TYPE strong_ref_cursor IS REF CURSOR RETURN dual%ROWTYPE;


  FUNCTION test_function( in_param IN BOOLEAN )
    RETURN PLS_INTEGER
  AS
  BEGIN
    IF( in_param ) THEN
      -- G-7420: Always make the RETURN statement the last statement of your function
      --   This is not correct, there is actually a RETURN statement at the end.
      --   This one is reported as expected: G-7430: Try to use no more than one RETURN statement within a function
      RETURN 1;
    END IF;

    RETURN 0;
  END test_function;


  PROCEDURE test_exc
  AS
    l_dummy dual.dummy%TYPE;
  BEGIN
    -- G-5060: Avoid unhandled exceptions
    --   NO_DATA_FOUND and TOO_MANY_ROWS do not happen with such an aggregation query
    SELECT MIN( dummy ) INTO l_dummy FROM dual;
  END test_exc;


  PROCEDURE test_param(
    -- G-2140: Never initialize variables with NULL
    --   Not a variable, but a parameter
    in_dummy_with_default IN dual.dummy%TYPE DEFAULT NULL,
    -- G-7160: Always explicitly state parameter mode
    --   NOCOPY seems to disrupt this
    -- G-7330: Always assign values to OUT parameters
    --   Is assigned in SELECT
    io_dual IN OUT NOCOPY dual%ROWTYPE,
    -- G-7330: Always assign values to OUT parameters
    --   It is actually assigned. This one might be tricky.
    --   Only reported if previous parameter is removed (only distinct errors shown)
    out_lob OUT CLOB
  )
  AS
  BEGIN
    -- G-3145: Avoid using SELECT * directly from a table or view
    --   Make an exception when selecting into a ROWTYPE?
    SELECT *
      INTO io_dual
      FROM dual
      WHERE dummy = in_dummy_with_default
      -- G-3180: Always specify column names instead of positional references in ORDER BY clauses
      --   More complex in reality, but this is not a positional reference
      ORDER BY dummy || 1;

    sys.dbms_lob.createtemporary( out_lob, FALSE );

  END test_param;


  PROCEDURE test_cursor_attribute
  AS
    l_dummy dual.dummy%TYPE;
  BEGIN
    -- G-4140: Avoid executing any statements between a SQL operation and the usage of an implicit cursor attribute
    --   There is another statement right before SQL%FOUND
    SELECT dummy INTO l_dummy FROM dual;

    sys.dbms_output.put_line( 'Test' );

    SELECT dummy INTO l_dummy FROM dual;

    IF( SQL%FOUND ) THEN
      NULL;
    END IF;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END test_cursor_attribute;


  PROCEDURE test_loop
  AS
  BEGIN
    FOR cur IN ( SELECT dummy FROM dual ) LOOP
      some_package.some_method( cur.dummy );
      IF( TRUE ) THEN
        -- G-4375: Always use EXIT WHEN instead of an IF statement to exit from a loop
        --   This is actually an EXIT WHEN
        EXIT WHEN 1 = 1;
      END IF;
    END LOOP;
  END test_loop;

END test;
/
Comments
Post Details
Added on Feb 1 2024
0 comments
127 views