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):
FROM PSPCMPROG A
WHERE NOT EXISTS (SELECT 1
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.