Tech Talk

LIGHTLY TECHNICAL OBSERVATIONS ON PEOPLESOFT, SQR, AND RELATED AND UNRELATED TOPICS

Archives

To search for a particular text string within all PeopleCode, we can go to Edit | Find In... and select "entire database". But that scan can take a very long time. A text file containing all of the code would be much easier and faster to search.

I have previously described a method for exporting all PeopleCode as text. Unfortunately, that no longer seems to work. Recently when I've done this it has ended early with an "insufficient memory" error. It could be that there is so much more code now that App Designer just can't handle it. (Your mileage may vary and I'd be interested to know if you can still do this.)

Fortunately—sort of—there is an alternative method. The human-readable version of PeopleCode is stored in table PSPCMTXT. So I wrote a quick SQR to dump the contents of this table along with headers giving the name of each program.

But not all code seems to be in that table. Out of 102,000 PeopleCode programs (in HR/HCM 9.1) it is missing about 2,500. Even more strangely, some code that is not in the table in one database is there in other databases. I have not figured out the pattern but it may involve migrations in some way. Well, 97.5% is better than nothing.

Here is how you can check PSPCMTXT (the text-format table) against PSPCMPROG (the table containing the code that is actually executed):

   SELECT COUNT(*)
   FROM   PSPCMPROG  A
   WHERE  NOT EXISTS (SELECT 1
                      FROM   PSPCMTXT
                      WHERE  OBJECTVALUE1 = A.OBJECTVALUE1
                      AND    OBJECTVALUE2 = A.OBJECTVALUE2
                      AND    OBJECTVALUE3 = A.OBJECTVALUE3
                      AND    OBJECTVALUE4 = A.OBJECTVALUE4
                      AND    OBJECTVALUE5 = A.OBJECTVALUE5
                      AND    OBJECTVALUE6 = A.OBJECTVALUE6
                      AND    OBJECTVALUE7 = A.OBJECTVALUE7)

That is the count of the missing programs. Compare that against SELECT COUNT(*) FROM PSPCMPROG, which is all of the programs.