Oracle 7 procedures for verifying the referential integrity of an existing Oracle database whose schema is generated from the OPM schema shown in section 3 are contained in file Example_check.ORA7:
CREATE OR REPLACE PROCEDURE C_CLASSES
AS
BEGIN
DECLARE "_cnt1" INTEGER;
BEGIN
SELECT COUNT(*)
INTO "_cnt1"
FROM CLASSES
WHERE CLASSES."_dbid" IS NOT NULL
AND NOT EXISTS ( SELECT * FROM DATABASES
WHERE DATABASES."_dbid" = CLASSES."_dbid" );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column "_dbid"'
|| ' of table CLASSES');
END IF;
END;
END C_CLASSES;
/
CREATE OR REPLACE PROCEDURE C_COURSE
AS
BEGIN
DECLARE "_cnt1" INTEGER;
BEGIN
SELECT COUNT(*)
INTO "_cnt1"
FROM COURSE
WHERE NOT EXISTS ( SELECT * FROM OBJECTS
WHERE OBJECTS."_oid" = COURSE."_oid" );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column "_oid"'
|| ' of table COURSE');
END IF;
SELECT COUNT(*)
INTO "_cnt1"
FROM COURSE
WHERE COURSE."_defaultVersion" IS NOT NULL
AND NOT EXISTS ( SELECT * FROM "_vCOURSE"
WHERE "_vCOURSE"."_oid" = COURSE."_oid"
AND "_vCOURSE"."_version" = COURSE."_defaultVersion" );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column "_defaultVersion"'
|| ' of table COURSE');
END IF;
END;
END C_COURSE;
/
CREATE OR REPLACE PROCEDURE C_COURSE_references
AS
BEGIN
DECLARE "_cnt1" INTEGER;
BEGIN
SELECT COUNT(*)
INTO "_cnt1"
FROM COURSE_references
WHERE NOT EXISTS ( SELECT * FROM "_vCOURSE"
WHERE "_vCOURSE"."_oid" = COURSE_references."_oid"
AND "_vCOURSE"."_version" = COURSE_references."_version" );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column "_version"'
|| ' of table COURSE_references');
END IF;
SELECT COUNT(*)
INTO "_cnt1"
FROM COURSE_references
WHERE NOT EXISTS ( SELECT * FROM REFERENCE
WHERE REFERENCE."_oid" = COURSE_references.references );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column references'
|| ' of table COURSE_references');
END IF;
END;
END C_COURSE_references;
/
CREATE OR REPLACE PROCEDURE C_DEPARTMENT
AS
BEGIN
DECLARE "_cnt1" INTEGER;
BEGIN
SELECT COUNT(*)
INTO "_cnt1"
FROM DEPARTMENT
WHERE NOT EXISTS ( SELECT * FROM OBJECTS
WHERE OBJECTS."_oid" = DEPARTMENT."_oid" );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column "_oid"'
|| ' of table DEPARTMENT');
END IF;
SELECT COUNT(*)
INTO "_cnt1"
FROM DEPARTMENT
WHERE NOT EXISTS ( SELECT * FROM DepartmentNames
WHERE DepartmentNames."_code" = DEPARTMENT.name );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column name'
|| ' of table DEPARTMENT');
END IF;
END;
END C_DEPARTMENT;
/
CREATE OR REPLACE PROCEDURE C_FACULTY
AS
BEGIN
DECLARE "_cnt1" INTEGER;
BEGIN
SELECT COUNT(*)
INTO "_cnt1"
FROM FACULTY
WHERE NOT EXISTS ( SELECT * FROM PERSON
WHERE PERSON."_oid" = FACULTY."_oid" );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column "_oid"'
|| ' of table FACULTY');
END IF;
SELECT COUNT(*)
INTO "_cnt1"
FROM FACULTY
WHERE FACULTY."_defaultVersion" IS NOT NULL
AND NOT EXISTS ( SELECT * FROM "_vFACULTY"
WHERE "_vFACULTY"."_oid" = FACULTY."_oid"
AND "_vFACULTY"."_version" = FACULTY."_defaultVersion" );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column "_defaultVersion"'
|| ' of table FACULTY');
END IF;
SELECT COUNT(*)
INTO "_cnt1"
FROM FACULTY
WHERE FACULTY.department IS NOT NULL
AND NOT EXISTS ( SELECT * FROM DEPARTMENT
WHERE DEPARTMENT."_oid" = FACULTY.department );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column department'
|| ' of table FACULTY');
END IF;
SELECT COUNT(*)
INTO "_cnt1"
FROM FACULTY, STUDENT
WHERE STUDENT."_oid" = FACULTY."_oid";
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20005,
'Common instances in disjoint relations'
|| ' FACULTY and STUDENT');
END IF;
END;
END C_FACULTY;
/
CREATE OR REPLACE PROCEDURE C_PERSON
AS
BEGIN
DECLARE "_cnt1" INTEGER;
BEGIN
SELECT COUNT(*)
INTO "_cnt1"
FROM PERSON
WHERE NOT EXISTS ( SELECT * FROM OBJECTS
WHERE OBJECTS."_oid" = PERSON."_oid" );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column "_oid"'
|| ' of table PERSON');
END IF;
END;
END C_PERSON;
/
CREATE OR REPLACE PROCEDURE C_REFERENCE
AS
BEGIN
DECLARE "_cnt1" INTEGER;
BEGIN
SELECT COUNT(*)
INTO "_cnt1"
FROM REFERENCE
WHERE NOT EXISTS ( SELECT * FROM OBJECTS
WHERE OBJECTS."_oid" = REFERENCE."_oid" );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column "_oid"'
|| ' of table REFERENCE');
END IF;
END;
END C_REFERENCE;
/
CREATE OR REPLACE PROCEDURE C_SCLASSES
AS
BEGIN
DECLARE "_cnt1" INTEGER;
BEGIN
SELECT COUNT(*)
INTO "_cnt1"
FROM SCLASSES
WHERE NOT EXISTS ( SELECT * FROM CLASSES
WHERE CLASSES."_cid" = SCLASSES."_class" );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column "_class"'
|| ' of table SCLASSES');
END IF;
SELECT COUNT(*)
INTO "_cnt1"
FROM SCLASSES
WHERE SCLASSES."_subclass" IS NOT NULL
AND NOT EXISTS ( SELECT * FROM CLASSES
WHERE CLASSES."_cid" = SCLASSES."_subclass" );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column "_subclass"'
|| ' of table SCLASSES');
END IF;
END;
END C_SCLASSES;
/
CREATE OR REPLACE PROCEDURE C_STUDENT
AS
BEGIN
DECLARE "_cnt1" INTEGER;
BEGIN
SELECT COUNT(*)
INTO "_cnt1"
FROM STUDENT
WHERE NOT EXISTS ( SELECT * FROM PERSON
WHERE PERSON."_oid" = STUDENT."_oid" );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column "_oid"'
|| ' of table STUDENT');
END IF;
SELECT COUNT(*)
INTO "_cnt1"
FROM STUDENT
WHERE STUDENT."_defaultVersion" IS NOT NULL
AND NOT EXISTS ( SELECT * FROM "_vSTUDENT"
WHERE "_vSTUDENT"."_oid" = STUDENT."_oid"
AND "_vSTUDENT"."_version" = STUDENT."_defaultVersion" );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column "_defaultVersion"'
|| ' of table STUDENT');
END IF;
SELECT COUNT(*)
INTO "_cnt1"
FROM STUDENT, FACULTY
WHERE FACULTY."_oid" = STUDENT."_oid";
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20005,
'Common instances in disjoint relations'
|| ' STUDENT and FACULTY');
END IF;
END;
END C_STUDENT;
/
CREATE OR REPLACE PROCEDURE C_STUDENT_attends
AS
BEGIN
DECLARE "_cnt1" INTEGER;
BEGIN
SELECT COUNT(*)
INTO "_cnt1"
FROM STUDENT_attends
WHERE NOT EXISTS ( SELECT * FROM "_vSTUDENT"
WHERE "_vSTUDENT"."_oid" = STUDENT_attends."_oid"
AND "_vSTUDENT"."_version" = STUDENT_attends."_version" );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column "_version"'
|| ' of table STUDENT_attends');
END IF;
SELECT COUNT(*)
INTO "_cnt1"
FROM STUDENT_attends
WHERE STUDENT_attends.course IS NOT NULL
AND STUDENT_attends."_vcourse" IS NOT NULL
AND NOT EXISTS ( SELECT * FROM "_vCOURSE"
WHERE "_vCOURSE"."_oid" = STUDENT_attends.course
AND "_vCOURSE"."_version" = STUDENT_attends."_vcourse" );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column "_vcourse"'
|| ' of table STUDENT_attends');
END IF;
SELECT COUNT(*)
INTO "_cnt1"
FROM STUDENT_attends
WHERE STUDENT_attends.inDepartment IS NOT NULL
AND NOT EXISTS ( SELECT * FROM DEPARTMENT
WHERE DEPARTMENT."_oid" = STUDENT_attends.inDepartment );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column inDepartment'
|| ' of table STUDENT_attends');
END IF;
END;
END C_STUDENT_attends;
/
CREATE OR REPLACE PROCEDURE C__vCOURSE
AS
BEGIN
DECLARE "_cnt1" INTEGER;
BEGIN
SELECT COUNT(*)
INTO "_cnt1"
FROM "_vCOURSE"
WHERE NOT EXISTS ( SELECT * FROM COURSE
WHERE COURSE."_oid" = "_vCOURSE"."_oid" );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column "_oid"'
|| ' of table "_vCOURSE"');
END IF;
END;
END C__vCOURSE;
/
CREATE OR REPLACE PROCEDURE C__vFACULTY
AS
BEGIN
DECLARE "_cnt1" INTEGER;
BEGIN
SELECT COUNT(*)
INTO "_cnt1"
FROM "_vFACULTY"
WHERE NOT EXISTS ( SELECT * FROM FACULTY
WHERE FACULTY."_oid" = "_vFACULTY"."_oid" );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column "_oid"'
|| ' of table "_vFACULTY"');
END IF;
END;
END C__vFACULTY;
/
CREATE OR REPLACE PROCEDURE C__vSTUDENT
AS
BEGIN
DECLARE "_cnt1" INTEGER;
BEGIN
SELECT COUNT(*)
INTO "_cnt1"
FROM "_vSTUDENT"
WHERE NOT EXISTS ( SELECT * FROM STUDENT
WHERE STUDENT."_oid" = "_vSTUDENT"."_oid" );
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20003,
'Missing references in column "_oid"'
|| ' of table "_vSTUDENT"');
END IF;
END;
END C__vSTUDENT;
/
CREATE OR REPLACE PROCEDURE C_FACULTY_rank
AS
BEGIN
DECLARE "_cnt1" INTEGER;
BEGIN
SELECT COUNT(*)
INTO "_cnt1"
FROM FACULTY
WHERE rank NOT IN ('Professor', 'Associate Professor',
'Assistant Professor');
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20004,
'Domain definition violation in'
|| ' rank of relation FACULTY');
END IF;
END;
END C_FACULTY_rank;
/
CREATE OR REPLACE PROCEDURE C__vFACULTY_salary
AS
BEGIN
DECLARE "_cnt1" INTEGER;
BEGIN
SELECT COUNT(*)
INTO "_cnt1"
FROM "_vFACULTY"
WHERE salary NOT BETWEEN 30000 AND 150000;
IF ( "_cnt1" != 0 )
THEN
raise_application_error(-20004,
'Domain definition violation in'
|| ' salary of relation "_vFACULTY"');
END IF;
END;
END C__vFACULTY_salary;
/
CREATE OR REPLACE PROCEDURE CHECK_ALL
AS
BEGIN
C_CLASSES;
C_COURSE;
C_COURSE_references;
C_DEPARTMENT;
C_FACULTY;
C_PERSON;
C_REFERENCE;
C_SCLASSES;
C_STUDENT;
C_STUDENT_attends;
C__vCOURSE;
C__vFACULTY;
C__vSTUDENT;
C_FACULTY_rank;
C__vFACULTY_salary;
END CHECK_ALL;
/