New Rule: use CREATE OR REPLACE

[silviomarghitola]

** Language Usage / Stored Objects / General**
always use CREATE OR REPLACE instead of only CREATE

[PhilippSalvisberg]

from SonarSource:

"CREATE OR REPLACE" should be used instead of "CREATE"

When creating a function, procedure, package, package body, type, type body, trigger or library, it is a good practice replace the existing one to avoid errors.

Noncompliant Code Example

CREATE FUNCTION my_function RETURN PLS_INTEGER AS -- Noncompliant
BEGIN
  RETURN 42;
END;
/

Compliant Solution

CREATE OR REPLACE FUNCTION my_function RETURN PLS_INTEGER AS -- Compliant, no error even if the function already exists
BEGIN
  RETURN 42;
END;
/

[PhilippSalvisberg]

I tend to agree. The question is: does someone uses create without replace by purpose? E.g. to ensure the statement fails on second execution? Thoughts?

[S-Koell]

I would think that a create without a replace is so niche that this rule would make sense.
Because the persons knows what he is doing if he leaves out the "replace" and therefore can ignore that rule easily.
And if a "replace" is forgotten it can lead to problems.

I vote for this rule.

[PhilippSalvisberg]

Thanks, so let's add this rule.