Skip to content

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
35
36
37
38
39
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;
      co_wait           constant pls_integer     := in_wait;
   begin
      -- Receive next message and unpack for each column. 
      l_status := sys.dbms_pipe.receive_message(
                     pipename => co_dflt_pipe_name
                    ,timeout  => co_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
35
36
37
38
39
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;
      co_wait           constant pls_integer     := in_wait;
   begin
      -- Receive next message and unpack for each column. 
      l_status := sys.dbms_pipe.receive_message(
                     pipename => co_dflt_pipe_name
                    ,timeout  => co_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;
/