During Release the Scripts are executed in the Following Order

  1. Schema – DDL Scripts – (Table Alter Scripts)
  2. Content Script – Rows addition and Deletion
  3. Packages (Stored Procedures and Function)- DML Scripts – (CRUD)
SELECT  'Equal' FROM DUAL
 WHERE  NVL(NULL,'') = '';

Output

SELECT  'Equal' FROM DUAL
 WHERE  NVL(NULL,'~') = '~';

Output
Empty

While Passing Parameters to Procedure its should be

SELECT  'Equal' FROM DUAL
 WHERE  NVL(col_name, '~') = NVL(p_param, NVL(col_name, '~'));

Whenever a table is created in Oracle its shld be done by Four step process

  1. Table Creation
  2. Adding Constraints(Primary Key, Foreign Key)
  3. Adding Synonyms
  4. Giving Grants
  5. Alter Queries

Table Creation

create table TABLE_NAME_1
( COLUMN_NAME_1   varchar2(50),
  COLUMN_NAME_2   NUMBER(1),
  COLUMN_NAME_3   DATE not null,
  COLUMN_NAME_4   NUMBER(38)
 );

Constraint
Query 1

alter table TABLE_NAME_2 
  add constraint FK_COLUMN_NAME_1 foreign key (COLUMN_NAME_1) references TABLE_NAME_1 (COLUMN_NAME_1);

Query 2

alter table TABLE_NAME_3 add constraint fk_column_1 
foreign key (COLUMN_1, COLUMN_2) references TABLE_NAME_2 (COLUMN_1, COLUMN_2);

Synonym

 CREATE PUBLIC SYNONYM TABLE_NAME_1 FOR OWNER_NAME.TABLE_NAME_1;
 CREATE PUBLIC SYNONYM SEQUENCE_NAME_SEQ FOR OWNER_NAME.SEQUENCE_NAME_SEQ;
 GRANT ALL ON SEQUENCE_NAME_SEQ TO ADMIN,PART,ALL_DEVELOPERS;

Grants

 GRANT ALL ON TABLE_NAME_1 TO ADMIN,PART,ALL_DEVELOPERS;
 GRANT INSERT,UPDATE,DELETE,SELECT ON TABLE_NAME_1 TO ADMIN,PART,ALL_DEVELOPERS;

Other Queries

 ALTER TABLE TABLE_NAME_1 COLUMN_NAME_1 NOT NULL;
 ALTER TABLE TABLE_NAME_1 MODIFY COLUMN_NAME_1 varchar2(50);
 ALTER TABLE TABLE_NAME_1 DROP COLUMN COLUMN_NAME_1;
 ALTER TABLE TABLE_NAME_1 DROP CONSTRAINT COLUMN_NAME_1;

Things to Consider

While creating Primary key(No Null Values allowed like unique key) as a combination of two or more columns you should take in to consideration only the non null columns as part of unique key.

 ALTER TABLE TABLE_NAME
   ADD CONSTRAINT PK_TABLE_NAME PRIMARY KEY (COLUMN_NAME_1, COLUMN_NAME_2, COLUMN_NAME_3)
  USING INDEX TABLESPACE INDX;

COLUMN_NAME_1, COLUMN_NAME_2, COLUMN_NAME_3 should be a Non Null column.

When deleting a Table to run new script the following should be done.

DROP TABLE TABLE_NAME;
DROP SEQUENCE SEQUENCE_NAME;
DROP PUBLIC SYNONYM SYNONYM_NAME;

In case you are deleting multiple Table the same thing should be done with tables grouped together as one below.

DROP TABLE TABLE_NAME;
.
.
.

DROP SEQUENCE SEQUENCE_NAME;
.
.
.

DROP PUBLIC SYNONYM SYNONYM_NAME;
.
.
.
.

The Child Table referencing the Parent table should be deleted first.

 DROP TABLE CHILD_TABLE_NAME;
 DROP TABLE PARENT_TABLE_NAME;

Checking if the table column is referenced some where by Child Table in form of Primary Key

SELECT TABLE_NAME AS "CHILD_TABLE"
       ,CONSTRAINT_NAME
  FROM ALL_CONSTRAINTS T
 WHERE R_OWNER = 'OWNER_NAME'
   AND CONSTRAINT_TYPE = 'R'
   AND R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME
                               FROM ALL_CONSTRAINTS
                              WHERE CONSTRAINT_TYPE IN ('P', 'U')
                                AND TABLE_NAME = 'TABLE_NAME'
                                AND OWNER = 'OWNER_NAME')
 ORDER BY TABLE_NAME
         ,CONSTRAINT_NAME;

Sequence

CREATE SEQUENCE TEST_SEQ
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;

Audit Trigger

CREATE OR REPLACE TRIGGER TRIGGER_NAME
BEFORE INSERT OR UPDATE ON TABLE_NAME
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
  IF INSERTING THEN
    :NEW.CRE_USER_UID := USERUID;
    :NEW.CRE_TIMESTAMP := SYSDATE;
  ELSIF UPDATING THEN
    :NEW.UPD_USER_UID := USERUID;
    :NEW.UPD_TIMESTAMP := SYSDATE;
  END IF;
/
END TRIGGER_NAME;

Note the / in the End of Trigger