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
16
17
18
19 | 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_employee_id constant employees.employee_id%type := in_employee_id;
co_increase_pct constant types_up.percentage := in_increase_pct;
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 co_increase_pct,co_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
17
18
19
20 | 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_employee_id constant employees.employee_id%type := in_employee_id;
co_increase_pct constant types_up.percentage := in_increase_pct;
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 co_increase_pct,co_employee_id
returning into out_new_salary;
end upd_salary;
end employee_api;
/
|