SQL Integrity

The following table describes the audit queries and resolutions for this area:

Query Description Resolution

SQL-01

SQL text without a base definition.

Run the following SQL:

DELETE FROM PSSQLTEXTDEFN
WHERE SQLID NOT IN
 (SELECT DISTINCT SQLID
  FROM PSSQLDEFN)

SQL-02

SQL definitions without SQL text.

Run the following SQL:

DELETE FROM PSSQLDEFN
WHERE SQLID NOT IN
 (SELECT DISTINCT SQLID
  FROM PSSQLTEXTDEFN)

SQL-03

SQL descriptions without a base definition.

Run the following SQL:

DELETE FROM PSSQLDESCR
WHERE SQLID NOT IN
 (SELECT DISTINCT SQLID
  FROM PSSQLDEFN)

SQL-04

SQL descriptions without associated SQL text.

Run the following SQL:

DELETE FROM PSSQLDESCR
WHERE SQLID NOT IN
 (SELECT DISTINCT SQLID
  FROM PSSQLTEXTDEFN)

SQL-05

AE SQL without SQL definitions.

This reveals Application Engine SQL Actions that do not contain any SQL code within them.

Open the Application Engine program and complete the entry of the SQL before attempting to run the program.

If the empty SQL actions are delivered by PeopleSoft, open an incident with My Oracle Support to report the corrupt AE program.

SQL-06

AE SQL that's not referenced.

This reveals an Application Engine SQL object that is not being referenced by an AE program. This indicates that the AE program is deleted but the associated SQL is not. The orphaned SQL does not cause issues other than consuming disk space.

If the orphaned SQL is delivered by PeopleSoft, open an incident with My Oracle Support to make sure that it is not a symptom of a larger problem, such as a corrupted AE application.

SQL-07

Record Views/Dynamic Views without SQL definitions.

Complete the entry of the record view or dynamic view before attempting to build or create the view. Each record should be opened, and the SQL should be entered as required.

SQL-08

View SQL that are not referenced by record or dynamic views.

Run the following SQL:

DELETE FROM PSSQLDEFN
WHERE SQLTYPE = 2 AND
 SQLID NOT IN
  (SELECT DISTINCT RECNAME
   FROM PSRECDEFN
   WHERE RECTYPE = 5 OR
   RECTYPE = 1)
DELETE FROM PSSQLDESCR
WHERE SQLTYPE = 2 AND
 SQLID NOT IN
  (SELECT DISTINCT RECNAME
   FROM PSRECDEFN
   WHERE RECTYPE = 5
   OR RECTYPE = 1)
DELETE FROM PSSQLTEXTDEFN
WHERE SQLTYPE = 2 AND
 SQLID NOT IN
  (SELECT DISTINCT RECNAME
   FROM PSRECDEFN
   WHERE RECTYPE = 5
   OR RECTYPE = 1)