Uploaded image for project: 'Rules Repository'
  1. Rules Repository
  2. RSPEC-2461

Blocks containing "EXECUTE IMMEDIATE" should trap all exceptions

    XMLWordPrintable

    Details

    • Type: Code Smell Detection
    • Status: Active
    • Resolution: Unresolved
    • Labels:
    • Message:
      Add an exception trap to this execution.
    • Default Severity:
      Critical
    • Impact:
      High
    • Likelihood:
      Low
    • Default Quality Profiles:
      Sonar way
    • Legacy Key:
      ExecuteImmediateTrapExceptionsCheck
    • Covered Languages:
      PL/SQL
    • Remediation Function:
      Constant/Issue
    • Constant Cost:
      1h

      Description

      Since the purpose of the EXECUTE IMMEDIATE statement is to execute dynamic SQL queries - which by definition can contain unexpected errors - properly handling exceptions becomes critical. Therefore, care should be taken to trap all possible exceptions.

      Noncompliant Code Example

      DECLARE
        result      VARCHAR2(42);
        column      VARCHAR2(42);
      BEGIN
        column := 'DUMMY_2';
        EXECUTE IMMEDIATE 'SELECT ' || column || ' FROM DUAL' INTO result; -- Non-Compliant
      END;
      /
      

      Compliant Solution

      SET SERVEROUTPUT ON
      
      DECLARE
        result      VARCHAR2(42);
        column      VARCHAR2(42);
      BEGIN
        column := 'DUMMY_2';
        EXECUTE IMMEDIATE 'SELECT ' || column || ' FROM DUAL' INTO result; -- Compliant
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE ('Execute immediate error: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
      END;
      /
      

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            ann.campbell.2 Ann Campbell
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated: