G-7170: Avoid using an IN OUT parameter as IN or OUT only.
Major
Efficiency, Maintainability
Unsupported in db* CODECOP Validators
We cannot determine the usage of an in out parameter in a reliable way, especially when other units are involved which are maintained in another file.
Reason
Avoid using parameter mode in out unless you actually use the parameter both as input and output. If the code body only reads from the parameter, use in; if the code body only assigns to the parameter, use out. If at the beginning of a project you expect a parameter to be both input and output and therefore choose in out just in case, but later development shows the parameter actually is only in or out, you should change the parameter mode accordingly.
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
26
27
28
29
30
31
32
33
34 | create or replace package body employee_up is
procedure rcv_emp(io_first_name in out employees.first_name%type
,io_last_name in out employees.last_name%type
,io_email in out employees.email%type
,io_phone_number in out employees.phone_number%type
,io_hire_date in out employees.hire_date%type
,io_job_id in out employees.job_id%type
,io_salary in out employees.salary%type
,io_commission_pct in out employees.commission_pct%type
,io_manager_id in out employees.manager_id%type
,io_department_id in out employees.department_id%type
,in_wait in integer) is
l_status pls_integer;
co_dflt_pipe_name constant string(30 char) := 'MyPipe';
co_ok constant pls_integer := 1;
begin
-- Receive next message and unpack for each column.
l_status := sys.dbms_pipe.receive_message(pipename => co_dflt_pipe_name
,timeout => in_wait);
if l_status = co_ok then
sys.dbms_pipe.unpack_message(io_first_name);
sys.dbms_pipe.unpack_message(io_last_name);
sys.dbms_pipe.unpack_message(io_email);
sys.dbms_pipe.unpack_message(io_phone_number);
sys.dbms_pipe.unpack_message(io_hire_date);
sys.dbms_pipe.unpack_message(io_job_id);
sys.dbms_pipe.unpack_message(io_salary);
sys.dbms_pipe.unpack_message(io_commission_pct);
sys.dbms_pipe.unpack_message(io_manager_id);
sys.dbms_pipe.unpack_message(io_department_id);
end if;
end rcv_emp;
end employee_up;
/
|
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
29
30
31
32
33
34 | create or replace package body employee_up is
procedure rcv_emp(out_first_name out employees.first_name%type
,out_last_name out employees.last_name%type
,out_email out employees.email%type
,out_phone_number out employees.phone_number%type
,out_hire_date out employees.hire_date%type
,out_job_id out employees.job_id%type
,out_salary out employees.salary%type
,out_commission_pct out employees.commission_pct%type
,out_manager_id out employees.manager_id%type
,out_department_id out employees.department_id%type
,in_wait in integer) is
l_status pls_integer;
co_dflt_pipe_name constant string(30 char) := 'MyPipe';
co_ok constant pls_integer := 1;
begin
-- Receive next message and unpack for each column.
l_status := sys.dbms_pipe.receive_message(pipename => co_dflt_pipe_name
,timeout => in_wait);
if l_status = co_ok then
sys.dbms_pipe.unpack_message(out_first_name);
sys.dbms_pipe.unpack_message(out_last_name);
sys.dbms_pipe.unpack_message(out_email);
sys.dbms_pipe.unpack_message(out_phone_number);
sys.dbms_pipe.unpack_message(out_hire_date);
sys.dbms_pipe.unpack_message(out_job_id);
sys.dbms_pipe.unpack_message(out_salary);
sys.dbms_pipe.unpack_message(out_commission_pct);
sys.dbms_pipe.unpack_message(out_manager_id);
sys.dbms_pipe.unpack_message(out_department_id);
end if;
end rcv_emp;
end employee_up;
/
|