1

I am working on online platform to make an ERD and to get the PL/SQL code of it but I get the following code (see below) but I am not sure if it is PL/SQL. I need to verify if this code is PL/SQL or not:

CREATE TABLE "CATEGORY" (
  "ID" NUMBER(10) PRIMARY KEY,
  "THLEVEL" NUMBER(10) NOT NULL
);

CREATE SEQUENCE "CATEGORY_SEQ" NOCACHE;

CREATE TRIGGER "CATEGORY_BI"
  BEFORE INSERT ON "CATEGORY"
  FOR EACH ROW
BEGIN
  IF :NEW."ID" IS NULL THEN
    SELECT "CATEGORY_SEQ".NEXTVAL INTO :NEW."ID" FROM DUAL;
  END IF;
END;;

CREATE TABLE "REPORT" (
  "ID" NUMBER(10) PRIMARY KEY,
  "CLIENT" CLOB NOT NULL,
  "VERSION" NUMBER(10) NOT NULL
);

CREATE SEQUENCE "REPORT_SEQ" NOCACHE;

CREATE TRIGGER "REPORT_BI"
  BEFORE INSERT ON "REPORT"
  FOR EACH ROW
BEGIN
  IF :NEW."ID" IS NULL THEN
    SELECT "REPORT_SEQ".NEXTVAL INTO :NEW."ID" FROM DUAL;
  END IF;
END;;

CREATE TABLE "ASSET" (
  "ID" NUMBER(10) PRIMARY KEY,
  "REPORT" NUMBER(10) NOT NULL,
  "IP" VARCHAR2(1000 CHAR) NOT NULL,
  "NAME" VARCHAR2(1000 CHAR)
);

CREATE INDEX "IDX_ASSET__REPORT" ON "ASSET" ("REPORT");

ALTER TABLE "ASSET" ADD CONSTRAINT "FK_ASSET__REPORT" FOREIGN KEY ("REPORT") REFERENCES "REPORT" ("ID");

CREATE SEQUENCE "ASSET_SEQ" NOCACHE;

CREATE TRIGGER "ASSET_BI"
  BEFORE INSERT ON "ASSET"
  FOR EACH ROW
BEGIN
  IF :NEW."ID" IS NULL THEN
    SELECT "ASSET_SEQ".NEXTVAL INTO :NEW."ID" FROM DUAL;
  END IF;
END;;

CREATE TABLE "SOLUTION" (
  "ID" NUMBER(10) PRIMARY KEY,
  "IMPLEVEL" VARCHAR2(1000 CHAR) NOT NULL,
  "DIFFICULTY" VARCHAR2(1000 CHAR) NOT NULL,
  "DESCRIPTION" CLOB NOT NULL
);

CREATE SEQUENCE "SOLUTION_SEQ" NOCACHE;

CREATE TRIGGER "SOLUTION_BI"
  BEFORE INSERT ON "SOLUTION"
  FOR EACH ROW
BEGIN
  IF :NEW."ID" IS NULL THEN
    SELECT "SOLUTION_SEQ".NEXTVAL INTO :NEW."ID" FROM DUAL;
  END IF;
END;;

CREATE TABLE "VULNERABILITY" (
  "ID" VARCHAR2(1000 CHAR) PRIMARY KEY,
  "ASSET" NUMBER(10) NOT NULL,
  "SOLUTION" NUMBER(10) NOT NULL,
  "CATEGORY" NUMBER(10) NOT NULL,
  "CVE" VARCHAR2(1000 CHAR),
  "DATE" TIMESTAMP NOT NULL,
  "LOCATION" CLOB NOT NULL
);

CREATE INDEX "IDX_VULNERABILITY__ASSET" ON "VULNERABILITY" ("ASSET");

CREATE INDEX "IDX_VULNERABILITY__CATEGORY" ON "VULNERABILITY" ("CATEGORY");

CREATE INDEX "IDX_VULNERABILITY__SOLUTION" ON "VULNERABILITY" ("SOLUTION");

ALTER TABLE "VULNERABILITY" ADD CONSTRAINT "FK_VULNERABILITY__ASSET" FOREIGN KEY ("ASSET") REFERENCES "ASSET" ("ID");

ALTER TABLE "VULNERABILITY" ADD CONSTRAINT "FK_VULNERABILITY__CATEGORY" FOREIGN KEY ("CATEGORY") REFERENCES "CATEGORY" ("ID");

ALTER TABLE "VULNERABILITY" ADD CONSTRAINT "FK_VULNERABILITY__SOLUTION" FOREIGN KEY ("SOLUTION") REFERENCES "SOLUTION" ("ID");

CREATE TABLE "EXPLOIT" (
  "ID" NUMBER(10) PRIMARY KEY,
  "VULNERABILITY" VARCHAR2(1000 CHAR) NOT NULL,
  "NAME" VARCHAR2(1000 CHAR) NOT NULL,
  "TYPE" CLOB NOT NULL,
  "DESCRIPTION" CLOB NOT NULL
);

CREATE INDEX "IDX_EXPLOIT__VULNERABILITY" ON "EXPLOIT" ("VULNERABILITY");

ALTER TABLE "EXPLOIT" ADD CONSTRAINT "FK_EXPLOIT__VULNERABILITY" FOREIGN KEY ("VULNERABILITY") REFERENCES "VULNERABILITY" ("ID");

CREATE SEQUENCE "EXPLOIT_SEQ" NOCACHE;

CREATE TRIGGER "EXPLOIT_BI"
  BEFORE INSERT ON "EXPLOIT"
  FOR EACH ROW
BEGIN
  IF :NEW."ID" IS NULL THEN
    SELECT "EXPLOIT_SEQ".NEXTVAL INTO :NEW."ID" FROM DUAL;
  END IF;
END;

So is this PL/SQL or is somethin else from Oracle? How this will sound in PL/SQL?

1
  • 3
    The ;; is wrong to terminate a PL/SQL block. See here for details: stackoverflow.com/questions/1079949/… But apart from that it looks like valid SQL (DDL) and PL/SQL (triggers) Commented Jul 5, 2016 at 8:12

4 Answers 4

1

Well that is PL/SQL and it will work in Oracle if you remove the double semi-colon you have on create trigger. https://dba.stackexchange.com/questions/1121/how-to-differentiate-between-sql-and-pl-sql

Sign up to request clarification or add additional context in comments.

Comments

1

That is NOT PL/SQL. That is DDL (Data Definition Language). Specifically it is Oracle DDL.

Comments

0

These are DDL statements in which we can create, alter tables ,PL/SQL is a Procedural language SQL in which we use DML Statements for any Process.

Comments

0

These are a set of DDL commands and not PL/SQL as such. However if you incorporate these commands in a nice stored procedure or package, it might be termed as PL/SQL (procedural part of it) but not currently in its current shape.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.