Examples of Oracle Common scripts

CREATE TABLE DBO. Index_policy_tbl
(
ID Number (Ten) is not NULL PRIMARY KEY,
policy_id number (10,0) default ( -1) is not NULL,
Alarm_count Number (Ten) default (0) not NULL
);

ALTER TABLE DBO. Index_policy_tbl ADD (c_name_1 VARCHAR2) not NULL, c_name_2 VARCHAR2 (up to () not NULL);

ALTER TABLE DBO. Index_policy_tbl RENAME COLUMN c_name_1 to C_name_1_new;

ALTER TABLE DBO. Index_policy_tbl MODIFY c_name_2 VARCHAR2 (n-CHAR);

CREATE TABLE DBO. Index_policy_tbl
(
ID Number (Ten) is not NULL PRIMARY KEY,
policy_id number (10,0) default ( -1) is not NULL,
Alarm_count Number (Ten) default (0) not NULL
);

CREATE SEQUENCE DBO. Index_policy_tbl_seq INCREMENT by 1 START with 1 nomaxvalue NOCACHE nocycle;

Create or replace
TRIGGER DBO. Index_policy_tbl_id_identity before INSERT on DBO. Index_policy_tbl
For each ROW
DECLARE
V_newval Number (Ten): = 0;
V_incval Number (10): = 0;
BEGIN
IF INSERTING AND:new.ID is NULL and then
SELECT DBO. Index_policy_tbl_seq. Nextval to V_newval from DUAL;
--If this is the first time this table has been inserted into (sequence = = 1)
If v_newval = 1 then
--get t He max Indentity value from the table
SELECT NVL (Max (ID), 0) to V_newval from DBO. INDEX_POLICY_TBL;
V_newval: = v_newval + 1;
--set the sequence to this value
LOOP
EXIT when v_incval>=v_newval;
SELECT DBO. Index_policy_tbl_seq.nextval to v_incval from dual;
END LOOP;
END IF;
--Save this to emulate @ @identity
Sqlserver_utilities.identity: = V_newval;
--Assign the value from the sequence to emulate the identity column
: new.id: = V_newval;
END IF;
END;

INSERT into DBO. Index_policy_tbl (policy_id, Alarm_count, C_name_1_new, c_name_2)
VALUES (1, ' A ', ' B ');
INSERT into DBO. Index_policy_tbl (policy_id, Alarm_count, C_name_1_new, c_name_2)
VALUES (101, 2, ' B ', ' C ');
INSERT into DBO. Index_policy_tbl (policy_id, Alarm_count, C_name_1_new, c_name_2)
VALUES (102, 3, ' C ', ' D ');
COMMIT;

SELECT * from DBO. INDEX_POLICY_TBL;

Drop TRIGGER DBO. index_policy_tbl_id_identity;
Drop SEQUENCE DBO. Index_policy_tbl_seq;
drop table DBO. INDEX_POLICY_TBL;

Examples of Oracle Common scripts