Create Table

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

/