During Release the Scripts are executed in the Following Order
- Schema – DDL Scripts – (Table Alter Scripts)
- Content Script – Rows addition and Deletion
- Packages (Stored Procedures and Function)- DML Scripts – (CRUD)
During Release the Scripts are executed in the Following Order
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
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