G-6010: Always use a character variable to execute dynamic SQL.

Major

Maintainability, Testability

Reason

Having the executed statement in a variable makes it easier to debug your code (e.g. by logging the statement that failed).

Example (bad)

1
2
3
4
5
6
declare
   l_next_val employee.employee_id%type;
begin
   execute immediate 'select employee_seq.nextval from dual' into l_next_val;
end;
/

Example (good)

1
2
3
4
5
6
7
8
declare
   l_next_val employee.employee_id%type;
   k_sql constant types.big_string_type := 
       'select employee_seq.nextval from dual';
begin
   execute immediate k_sql into l_next_val;
end;
/