PeopleTools | App Designer crash when attempting to build records
A. Bug Attack
At one of our project sites, we once struck a very odd problem in App Designer. None of our records were capable of being built. When we attempted to build a record (either a table or a view), an error would flash up at the bottom of the screen, and then App Designer would abruptly terminate. Because the error was displayed so quickly, no one could read what was actually said. Furthermore, no details were saved in the ‘build’ log files.
Eventually we tracked down the problem. It was related to public synonyms in our Dev environment. These were incorrectly set up against the wrong database owner.
B. Public Synonyms
But first things first: what exactly is a ‘public synonym’? As the name suggests, it’s simply an alternative way to refer to a database object. It also makes life easier for database users as they don’t need to know the owner of any one object. If synonyms did not exist, all users would have to specify the owner name and the table name in their database queries:
select 'x' from owner_name.ps_table_name
To provide a short-cut to the table name alone, a public synonym can be created. Any time that ‘ps_table_name’ is entered into a query on its own, the database will know from the public synonyms that ‘ps_table_name’ is really ‘owner_name.ps_table_name’. The public synonym therefore hides a level of complexity from the database users, making life easier for everyone (as well as cutting down on the amount of typing required).
To see what public synonyms exist in a database, run the following SQL statement:
select * from all_synonyms;
Getting back to our App Designer build problem, some of the core PeopleTools tables in our Dev database (such as PSSTATUS, PSOPRDEFN, and PSACCESSPRFL) had incorrect owner names in the public synonym table. Instead of the owner being ‘PSCSM’, it was set to ‘PSFSM’ (apparently the result of an upgrade script). So when any user tried to do a Build in App Designer, the system was trying to do a check on the PSSTATUS table. As the system normally does with any database script, it firstly went to the synonyms table, found the synonym, but then discovered that the PSFSM.PSSTATUS table did not exist. This confused App Designer to such a degree that it ended up terminating straightaway.
C. The Solution
The solution was to delete all the incorrect public synonyms are recreate them from scratch with the correct database owner. Although this was quite a strange problem, it provided a useful exercise to understanding more about public synonyms. In the normal course of affairs, most users are not even aware they exist.