G-7125
🆓Warning
Always use CREATE OR REPLACE instead of CREATE alone.
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) -- dbLinter ignore(G-7460) non-deterministic function
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 statement. |
Compliant Solution - ★★★★★
create or replace package body employee_api is
function employee_by_id(in_employee_id in integer) -- dbLinter ignore(G-7460) non-deterministic function
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;
/Parameters
Use parameters to customize the rule to your needs.
| Parameter | Description | Default Value |
|---|---|---|
| DisableAllQuickFix | Comma-separated list of rules for which a quick fix should not be applied to all the problems in a file. | Core G-3130 |
References
- same as Trivadis G-7125
- same as plsql:CreateOrReplaceCheck