PL/SQL Syntax problems

Question

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?

The `;;` is wrong to terminate a PL/SQL block. See here for details: http://stackoverflow.com/questions/1079949/when-do-i-need-to-use-a-semicolon-vs-a-slash-in-oracle-sql/10207695#10207695 But apart from that it looks like valid SQL (DDL) and PL/SQL (triggers) — , Jul 05 '16 at 08:12

score 1 · Accepted Answer · edited Apr 13 '17 at 12:42

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

edited Apr 13 '17 at 12:42

Community

1
1

answered Jul 05 '16 at 08:10

vercelli

4,717
2
13
15

1

`CREATE TRIGGER` **is** PL/SQL – Jul 05 '16 at 08:11

score 0 · Answer 2 · answered Sep 14 '18 at 16:46

0

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

answered Sep 14 '18 at 16:46

Stephen Caggiano

156
4

score 0 · Answer 3 · answered Aug 01 '21 at 07:11

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.

answered Aug 01 '21 at 07:11

Engr Waseem Abbas

1
1

score 0 · Answer 4 · edited Aug 01 '21 at 14:00

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.

edited Aug 01 '21 at 14:00

Dharman

30,962
25
85
135

answered Aug 01 '21 at 13:55

Shashank Tandon

1
1

PL/SQL Syntax problems

4 Answers4