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
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
procedure trx_to_collection(
   p_appendix_id in px_mandate_appendix.id%TYPE
)
is
   k_trx_collection constant varchar2(10) := 'TRX_LINES';

   l_param_names             apex_application_global.vc_arr2;
   l_param_values            apex_application_global.vc_arr2;
begin
   l_param_names(l_param_names.count + 1) := 'APPENDIX_ID';
   l_param_values(l_param_names.count) := p_appendix_id;

   apex_collection.create_collection_from_query_b
   (
       p_collection_name => k_trx_collection
     , p_query           =>
           q'[select t.id, 'Y' include_flag, 'TRX' type
                from px_billing_transactions t
                where t.appendix_id = :APPENDIX_ID
                  and t.pending_invoice_flag = 'Y']'
     , p_names           => l_param_names
     , p_values          => l_param_values
   );
end;
/

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
procedure trx_to_collection(
   p_appendix_id in px_mandate_appendix.id%TYPE
)
is
   k_trx_collection constant varchar2(10) := 'TRX_LINES';

   k_sql constant types.big_string_type := 
           q'[select t.id, 'Y' include_flag, 'TRX' type
                from px_billing_transactions t
                where t.appendix_id = :APPENDIX_ID
                  and t.pending_invoice_flag = 'Y']';

   l_param_names             apex_application_global.vc_arr2;
   l_param_values            apex_application_global.vc_arr2;
begin
   l_param_names(l_param_names.count + 1) := 'APPENDIX_ID';
   l_param_values(l_param_names.count) := p_appendix_id;

   apex_collection.create_collection_from_query_b
   (
       p_collection_name => k_trx_collection
     , p_query           => k_sql
     , p_names           => l_param_names
     , p_values          => l_param_values
   );

end;
/