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;
/
|