以下是脚本:
CREATE OR REPLACE FUNCTION CLOA.DT_ADDUSEROBJECT
RETURN NUMBER IS NEWOBJECTID NUMBER;
BEGIN
/* GET THE NEXT VALUE FROM THE SEQUENCE FOR THE OBJECT ID */
SELECT MICROSOFTSEQDTPROPERTIES.NEXTVAL INTO NEWOBJECTID FROM DUAL;
/* ADD THE PARENT NODE FOR THE NEW OBJECT */
INSERT INTO MICROSOFTDTPROPERTIES ( ID, OBJECTID, PROPERTY ) --microsoftdtproperties
VALUES ( NEWOBJECTID, NEWOBJECTID, 'DtgSchemaOBJECT' );
RETURN( NEWOBJECTID );
END DT_ADDUSEROBJECT;
-------------------------
CREATE OR REPLACE PROCEDURE CLOA.DT_DROPUSEROBJECTBYID
( PARAM_ID IN NUMBER )AS
BEGIN
DELETE FROM MICROSOFTDTPROPERTIES WHERE OBJECTID = PARAM_ID;
END DT_DROPUSEROBJECTBYID;
--------------------------
CREATE OR REPLACE PROCEDURE CLOA.DT_SETPROPERTYBYID
( PARAM_ID IN NUMBER, PARAM_PROPERTY IN VARCHAR2,PARAM_VALUE IN VARCHAR2,PARAM_LVALUE IN LONG RAW )
AS
BEGIN
DECLARE X NUMBER(40);
BEGIN
SELECT COUNT(*) INTO X
FROM MICROSOFTDTPROPERTIES WHERE OBJECTID=PARAM_ID AND PROPERTY=PARAM_PROPERTY;
IF X = 0 THEN
INSERT INTO MICROSOFTDTPROPERTIES (ID, PROPERTY, OBJECTID, VALUE, LVALUE, VERSION)
VALUES (MICROSOFTSEQDTPROPERTIES.NEXTVAL, PARAM_PROPERTY, PARAM_ID, PARAM_VALUE, PARAM_LVALUE, 0);
ELSE
UPDATE MICROSOFTDTPROPERTIES SET VALUE=PARAM_VALUE, LVALUE=PARAM_LVALUE, VERSION=VERSION+1
WHERE OBJECTID=PARAM_ID AND PROPERTY=PARAM_PROPERTY;
END IF;
END;
END DT_SETPROPERTYBYID; --DT_SETPROPERTYBYID
CREATE OR REPLACE FUNCTION CLOA.DT_ADDUSEROBJECT
RETURN NUMBER IS NEWOBJECTID NUMBER;
BEGIN
/* GET THE NEXT VALUE FROM THE SEQUENCE FOR THE OBJECT ID */
SELECT MICROSOFTSEQDTPROPERTIES.NEXTVAL INTO NEWOBJECTID FROM DUAL;
/* ADD THE PARENT NODE FOR THE NEW OBJECT */
INSERT INTO MICROSOFTDTPROPERTIES ( ID, OBJECTID, PROPERTY ) --microsoftdtproperties
VALUES ( NEWOBJECTID, NEWOBJECTID, 'DtgSchemaOBJECT' );
RETURN( NEWOBJECTID );
END DT_ADDUSEROBJECT;
-------------------------
CREATE OR REPLACE PROCEDURE CLOA.DT_DROPUSEROBJECTBYID
( PARAM_ID IN NUMBER )AS
BEGIN
DELETE FROM MICROSOFTDTPROPERTIES WHERE OBJECTID = PARAM_ID;
END DT_DROPUSEROBJECTBYID;
--------------------------
CREATE OR REPLACE PROCEDURE CLOA.DT_SETPROPERTYBYID
( PARAM_ID IN NUMBER, PARAM_PROPERTY IN VARCHAR2,PARAM_VALUE IN VARCHAR2,PARAM_LVALUE IN LONG RAW )
AS
BEGIN
DECLARE X NUMBER(40);
BEGIN
SELECT COUNT(*) INTO X
FROM MICROSOFTDTPROPERTIES WHERE OBJECTID=PARAM_ID AND PROPERTY=PARAM_PROPERTY;
IF X = 0 THEN
INSERT INTO MICROSOFTDTPROPERTIES (ID, PROPERTY, OBJECTID, VALUE, LVALUE, VERSION)
VALUES (MICROSOFTSEQDTPROPERTIES.NEXTVAL, PARAM_PROPERTY, PARAM_ID, PARAM_VALUE, PARAM_LVALUE, 0);
ELSE
UPDATE MICROSOFTDTPROPERTIES SET VALUE=PARAM_VALUE, LVALUE=PARAM_LVALUE, VERSION=VERSION+1
WHERE OBJECTID=PARAM_ID AND PROPERTY=PARAM_PROPERTY;
END IF;
END;
END DT_SETPROPERTYBYID; --DT_SETPROPERTYBYID
cloa.dt_adduserobject 已被编译但有错误
1. MICROSOFTSEQDTPROPERTIES 这个序列是否存在
2. MICROSOFTDTPROPERTIES 这个表是否存在,且表结构是否与插入信息相符合