Skip to content

G-7125: Always use CREATE OR REPLACE instead of CREATE alone.

Major

Maintainability

Reason

Using create alone makes your scripts give an error if the program unit already exists, which makes the script not repeatable. It is good practice to use create or replace to avoid such errors.

Example (bad)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
create package body employee_api is
   function employee_by_id(in_employee_id in integer) -- NOSONAR: non-deterministic
      return employees%rowtype is
      co_employee_id constant employees.employee_id%type := in_employee_id;
      r_employee     employees%rowtype;
   begin
      select *
        into r_employee
        from employees
       where employee_id = co_employee_id;

      return r_employee;
   exception
      when no_data_found then
         null;
      when too_many_rows then
         raise;
   end employee_by_id;
end employee_api;
/

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
create or replace package body employee_api is
   function employee_by_id(in_employee_id in integer) -- NOSONAR: non-deterministic
      return employees%rowtype is
      co_employee_id constant employees.employee_id%type := in_employee_id;
      r_employee     employees%rowtype;
   begin
      select *
        into r_employee
        from employees
       where employee_id = co_employee_id;

      return r_employee;
   exception
      when no_data_found then
         null;
      when too_many_rows then
         raise;
   end employee_by_id;
end employee_api;
/