Skip to content

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
7
declare
   l_next_val employees.employee_id%type;
begin
   execute immediate 'select employees_seq.nextval from dual'
      into l_next_val;
end;
/

Example (good)

1
2
3
4
5
6
7
8
declare
   l_next_val employees.employee_id%type;
   co_sql     constant types_up.big_string_type :=
      'select employees_seq.nextval from dual';
begin
   execute immediate co_sql into l_next_val;
end;
/