G-6020: Try to use output bind arguments in the RETURNING INTO clause of dynamic DML statements rather than the USING clause.
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;
/
|