G-7125

🆓

Warning

Always use CREATE OR REPLACE instead of CREATE alone.

Rule Details

Severity Level: Major
Quality Area: Maintainability
Fixing times: 1 mins
Local-easy
Database Support: OracleDB (v7.3.4 +)

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

Non-Compliant Example

create package body employee_api is
   function employee_by_id(in_employee_id in integer) -- NOSONAR G-7460: 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;
/

Issues

Line	Column	Message
1	1	Missing OR REPLACE in CREATE.

Compliant Solution - ★★★★★

create or replace package body employee_api is
   function employee_by_id(in_employee_id in integer) -- NOSONAR G-7460: 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;
/

References

same as plsql:CreateOrReplaceCheck
same as Trivadis G-7125