Skip to content

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)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
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)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
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;
/