DECLARE
CURSOR TABCURSOR IS
SELECT COUNT(*) AS COUNTROW FROM USER_OBJECTS WHERE OBJECT_TYPE='TABLE' AND OBJECT_NAME='CS30_CSPURVIEW';
V_SQL VARCHAR2(2000);
BEGIN
FOR TAB IN TABCURSOR LOOP
IF TAB.COUNTROW!=0 THEN
V_SQL:='drop TABLE CS30_CSPURVIEW';
EXECUTE IMMEDIATE V_SQL;
END IF;
END LOOP;
END;
/
CREATE TABLE CS30_CSPURVIEW
(
PURVIEWID NUMBER(10,0) NOT NULL ,
CSID VARCHAR2 (50) NOT NULL ,
STARTIP VARCHAR2 (15) NOT NULL ,
ENDIP VARCHAR2 (15) NOT NULL ,
STARTIPDESC VARCHAR2 (12) DEFAULT '' NOT NULL ,
ENDIPDESC VARCHAR2 (12) DEFAULT '' NOT NULL ,
CONSTRAINT PK_CS30_CSPURVIEW PRIMARY KEY ( PURVIEWID ) ENABLE
);
CREATE INDEX IX_CS30_CSPURVIEW_CSID ON CS30_CSPURVIEW ( CSID );
CREATE INDEX IX_CS30_CSPURVIEW_STARTIPDESC ON CS30_CSPURVIEW ( STARTIPDESC );
CREATE INDEX IX_CS30_CSPURVIEW_ENDIPDESC ON CS30_CSPURVIEW ( ENDIPDESC );
/
DECLARE
CURSOR TABCURSOR IS
SELECT COUNT(*) AS COUNTROW FROM USER_OBJECTS WHERE OBJECT_TYPE='SEQUENCE' AND OBJECT_NAME='CS30_CSPURVIEW_SEQ';
V_SQL VARCHAR2(2000);
BEGIN
FOR TAB IN TABCURSOR LOOP
IF TAB.COUNTROW!=0 THEN
V_SQL:='drop sequence CS30_CSPURVIEW_SEQ';
EXECUTE IMMEDIATE V_SQL;
END IF;
END LOOP;
V_SQL:='create sequence CS30_CSPURVIEW_SEQ increment by 1';
EXECUTE IMMEDIATE V_SQL;
V_SQL:='CREATE OR REPLACE TRIGGER TRG_CS30_CSPURVIEW
BEFORE INSERT ON CS30_CSPURVIEW
FOR EACH ROW
BEGIN
SELECT CS30_CSPURVIEW_SEQ.NEXTVAL INTO :NEW.PurviewID FROM DUAL;
END;';
EXECUTE IMMEDIATE V_SQL;
END;
/
INSERT INTO .... ....;
COMMIT;
drop table "ECM"."CS30_CSPURVIEW" cascade constraints
/