G-6020: Try to use output bind arguments in the RETURNING INTO clause of dynamic DML statements rather than the USING clause.

Minor

Maintainability

Reason

When a dynamic INSERT, UPDATE, or DELETE statement has a RETURNING clause, output bind arguments can go in the RETURNING INTO clause or in the USING clause.

You should use the RETURNING INTO clause for values returned from a DML operation. Reserve OUT and IN OUT bind variables for dynamic PL/SQL blocks that return values in PL/SQL variables.

Example (bad)

CREATE OR REPLACE PACKAGE BODY employee_api IS
   PROCEDURE upd_salary (in_employee_id  IN     employees.employee_id%TYPE
                        ,in_increase_pct IN     types_up.percentage
                        ,out_new_salary     OUT employees.salary%TYPE)
   IS
      co_sql_stmt CONSTANT types_up.big_string_type := '
           UPDATE employees SET salary = salary + (salary / 100 * :1) 
            WHERE employee_id = :2
        RETURNING salary INTO :3';
   BEGIN
     EXECUTE IMMEDIATE co_sql_stmt
           USING in_increase_pct, in_employee_id, OUT out_new_salary;
   END upd_salary;
END employee_api;
/

Example (good)

CREATE OR REPLACE PACKAGE BODY employee_api IS
   PROCEDURE upd_salary (in_employee_id  IN     employees.employee_id%TYPE
                        ,in_increase_pct IN     types_up.percentage
                        ,out_new_salary     OUT employees.salary%TYPE)
   IS
      co_sql_stmt CONSTANT types_up.big_string_type := 
          'UPDATE employees SET salary = salary + (salary / 100 * :1) 
            WHERE employee_id = :2
        RETURNING salary INTO :3';
   BEGIN
     EXECUTE IMMEDIATE co_sql_stmt
           USING in_increase_pct, in_employee_id
           RETURNING INTO out_new_salary;
   END upd_salary;
END employee_api;
/