clob html 转义,sql - 将PL / SQL语句存储在CLOB值中，转义符不起作用 - 堆栈内存溢出...

我有此语句，它在CLOB内插入一些信息以及完整的触发器。 但是，我收到此错误：ORA-01756：带quoted string not properly terminated

我在触发器的每个引号前面都添加了转义字符(')。 但是我不明白为什么我总是收到这个错误。

这是表创建查询：

CREATE TABLE Template (id number(10) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 2), triggerEvent clob NOT NULL, triggerCode clob NOT NULL, TemplateTypeid number(10) NOT NULL, PRIMARY KEY (id));

这是整个插入查询：

drop table template CASCADE CONSTRAINTS PURGE;

drop table TemplateType CASCADE CONSTRAINTS PURGE;

drop table BusinessRuleType CASCADE CONSTRAINTS PURGE;

drop table Operator CASCADE CONSTRAINTS PURGE;

drop table Category CASCADE CONSTRAINTS PURGE;

drop table BusinessRule CASCADE CONSTRAINTS PURGE;

drop table TargetDatabase CASCADE CONSTRAINTS PURGE;

drop table BusinessRuleType_Operator CASCADE CONSTRAINTS PURGE;

drop table DatabaseType CASCADE CONSTRAINTS PURGE;

drop table RuleValues CASCADE CONSTRAINTS PURGE;

drop sequence ARNG_seq;

CREATE SEQUENCE ARNG_seq;

CREATE TABLE Template (id number(10) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 2), triggerEvent clob NOT NULL, triggerCode clob NOT NULL, TemplateTypeid number(10) NOT NULL, PRIMARY KEY (id));

CREATE TABLE TemplateType (id number(10) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 2), category varchar2(255) NOT NULL, DatabaseTypeid number(10) NOT NULL, PRIMARY KEY (id));

CREATE TABLE BusinessRuleType (id number(10) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 2), name varchar2(255), Categoryid number(10) NOT NULL, Templateid number(10) NOT NULL, PRIMARY KEY (id));

CREATE TABLE Operator (id number(10) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 5), operatorType varchar2(255) NOT NULL, name varchar2(255) NOT NULL, value varchar2(255) NOT NULL, PRIMARY KEY (id));

CREATE TABLE Category (id number(10) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 2), name varchar2(255) NOT NULL, PRIMARY KEY (id));

CREATE TABLE BusinessRule (id number(10) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 2), generatedCode clob, name varchar2(255) NOT NULL, errorMessage varchar2(255) NOT NULL, validationFailureSeverity varchar2(50) NOT NULL, targetColumn varchar2(255) NOT NULL, targetTable varchar2(255) NOT NULL, BusinessRuleTypeid number(10) NOT NULL, SavedTargetDatabaseid number(10) NOT NULL, Operatorid number(10) NOT NULL, PRIMARY KEY (id));

CREATE TABLE TargetDatabase (id number(10) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 2), name varchar2(255) NOT NULL, url varchar2(255) NOT NULL, username varchar2(255) NOT NULL, password varchar2(255) NOT NULL, DatabaseTypeid number(10) NOT NULL, PRIMARY KEY (id));

CREATE TABLE BusinessRuleType_Operator (BusinessRuleTypeid number(10) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 2), Operatorid number(10) NOT NULL, PRIMARY KEY (BusinessRuleTypeid, Operatorid));

CREATE TABLE DatabaseType (id number(10) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 2), databaseType varchar2(255) NOT NULL, PRIMARY KEY (id));

CREATE TABLE RuleValues (id number(10) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 2), name varchar2(255) NOT NULL, stringValue varchar2(255), intValue number(10), clobValue clob, BusinessRuleid number(10) NOT NULL, PRIMARY KEY (id));

ALTER TABLE BusinessRuleType ADD CONSTRAINT FKBusinessRu863439 FOREIGN KEY (Categoryid) REFERENCES Category (id);

ALTER TABLE BusinessRule ADD CONSTRAINT FKBusinessRu277804 FOREIGN KEY (BusinessRuleTypeid) REFERENCES BusinessRuleType (id);

ALTER TABLE BusinessRuleType ADD CONSTRAINT FKBusinessRu443116 FOREIGN KEY (Templateid) REFERENCES Template (id);

ALTER TABLE Template ADD CONSTRAINT FKTemplate586491 FOREIGN KEY (TemplateTypeid) REFERENCES TemplateType (id);

ALTER TABLE BusinessRuleType_Operator ADD CONSTRAINT FKBusinessRu447733 FOREIGN KEY (BusinessRuleTypeid) REFERENCES BusinessRuleType (id);

ALTER TABLE BusinessRuleType_Operator ADD CONSTRAINT FKBusinessRu382431 FOREIGN KEY (Operatorid) REFERENCES Operator (id);

ALTER TABLE BusinessRule ADD CONSTRAINT FKBusinessRu873463 FOREIGN KEY (SavedTargetDatabaseid) REFERENCES TargetDatabase (id);

ALTER TABLE TemplateType ADD CONSTRAINT FKTemplateTy236989 FOREIGN KEY (DatabaseTypeid) REFERENCES DatabaseType (id);

ALTER TABLE TargetDatabase ADD CONSTRAINT FKTargetData209438 FOREIGN KEY (DatabaseTypeid) REFERENCES DatabaseType (id);

ALTER TABLE BusinessRule ADD CONSTRAINT FKBusinessRu447639 FOREIGN KEY (Operatorid) REFERENCES Operator (id);

ALTER TABLE RuleValues ADD CONSTRAINT FKRuleValues255277 FOREIGN KEY (BusinessRuleid) REFERENCES BusinessRule (id);

INSERT INTO Category (id, name) VALUES (1, 'Static');

INSERT INTO Category (id, name) VALUES (2, 'Dynamic');

INSERT INTO Operator (id, operatortype, name, value) VALUES (1, 'Compare', 'Equals', '=');

INSERT INTO Operator (id, operatortype, name, value) VALUES (2, 'Compare', 'Not Equals', '!=');

INSERT INTO Operator (id, operatortype, name, value) VALUES (3, 'Range', 'Between', 'BETWEEN');

INSERT INTO Operator (id, operatortype, name, value) VALUES (4, 'Range', 'Not Between', 'NOTBETWEEN');

INSERT INTO DatabaseType (id, databaseType) VALUES (1, 'Oracle');

INSERT INTO TargetDatabase (id, name, url, username, password, DatabaseTypeid) VALUES (1, 'School Database', 'XXXX', 'XXX', 'XX', 1);

INSERT INTO TemplateType (id, category, DatabaseTypeid) VALUES (1, 'Constraint', 1);

INSERT INTO Template (id, triggerevent, triggercode, templatetypeid) VALUES (1, 'insert, update <> | <

BEFORE DELETE OR INSERT OR UPDATE

ON {attribute_table}

FOR EACH ROW

DECLARE

L_OPER VARCHAR2(3);

L_ERROR_STACK VARCHAR2(4000);

BEGIN

IF INSERTING

THEN

L_OPER := 'INS';

ELSIF UPDATING

THEN

L_OPER := 'UPD';

ELSIF DELETING

THEN

L_OPER := 'DEL';

END IF;

DECLARE

L_PASSED BOOLEAN := TRUE;

BEGIN

IF L_OPER IN ('INS', 'UPD')

THEN

IF '{operand}' = '=' THEN

L_PASSED := :NEW.{attribute_column} >= {range_min} AND :NEW.{attribute_column} <= {range_max};

ELSE

L_PASSED := :NEW.{attribute_column} < {range_min} OR :NEW.{attribute_column} > {range_max};

END IF;

IF NOT L_PASSED

THEN

raise_application_error(-20000, {error});

END IF;

END IF;

END;

END;]' , 1 );

INSERT INTO BusinessRuleType (id, name, categoryid, templateid) VALUES (1, 'Attribute Range Rule', 1, 1);

INSERT INTO BusinessRule (id, name, errorMessage, validationFailureSeverity, targetColumn, targetTable, BusinessRuleTypeid, SavedTargetDatabaseid, Operatorid) VALUES (1, 'BRG_NMR_CNS_RNG_01', 'Zit niet in de range', 'Informational Warning', 'cijfer', 'rapport', 1, 1, 1);

INSERT INTO RuleValues (id, name, intValue, BusinessRuleid) VALUES (1, 'MinValue', 1, 1);

INSERT INTO RuleValues (id, name, intValue, BusinessRuleid) VALUES (2, 'MaxValue', 10, 1);

INSERT INTO BusinessRuleType_operator (businessruletypeid, operatorid) VALUES (1,1);

我需要将整个触发器作为CLOB保存在数据库中的原因是因为我的Java后端可以使用它。