Home

Description

The CREATE OR REPLACE rule states that when creating or modifying an existing PL/SQL object, the CREATE OR REPLACE statement should be used instead of the CREATE statement. This ensures that any existing object is replaced with the new version, rather than creating a duplicate object with the same name. This rule also applies to other types of objects, such as views, triggers, and stored procedures. By using the CREATE OR REPLACE statement, developers can ensure that their code is always up-to-date and that any existing objects are not duplicated.

Key Benefits

• Flexibility: CREATE OR REPLACE allows for more flexibility when making changes to existing objects, as it allows for the object to be modified without having to drop and recreate it.
• Time Saving: Using CREATE OR REPLACE instead of CREATE can save time, as it eliminates the need to drop and recreate the object.
• Error Prevention: CREATE OR REPLACE can help to prevent errors, as it allows for the object to be modified without having to drop and recreate it.

Non-compliant Code Example

CREATE TRIGGER trgCustomerAdded  --Non compliant code (Only create is used)
BEFORE INSERT ON CUSTOMERS FOR EACH ROW
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO CUSTOMERS VALUES(:new.pFirstName, :new.pLastName);
   COMMIT;  -- allowed only in autonomous triggers
END;

Compliant Code Example

CREATE OR REPLACE TRIGGER trgCustomerAdded --Compliant code (Create OR Replace is used instead of only Create)
BEFORE INSERT ON CUSTOMERS FOR EACH ROW
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO CUSTOMERS VALUES(:new.pFirstName, :new.pLastName);
   COMMIT;  -- allowed only in autonomous triggers
END;