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;
/