PeopleTools | Understanding and resolving a ‘no matching buffer’ error
A. A Usual Suspect
‘No matching buffer’ errors are something that most developers have to contend with at some stage in their development career. Although the error sounds scary and a bit cryptic, the underlying cause of a ‘no matching buffer’ error is fairly straightforward.
A relational database such as Oracle defines records in terms of their relations with one other (indeed, that is why Oracle is called a ‘relational database’). This is done by via the use of ‘key fields’, either primary keys or foreign keys. The ‘no matching buffer’ occurs when one of these core integrity rules is breached:
To explain this further, let’s consider an example. In our database, we have a record called DEPT_TBL. This consists of two keys – SETID and DEPT_ID. We then have a second record called EMPLOYEE_TBL. This has three keys – SETID, DEPT_ID and EMPLID. Every employee has to belong to at least one department, but a department doesn’t always have to have employees. This gives rise to a ‘parent-child’ relationship. DEPT_TBL is considered the ‘parent’ while EMPLOYEE_TBL is consider the ‘child’. The child table copies the key structure of its parent, with the addition of one or more fields to make the key unique.
If we load this structure on to a PeopleSoft page, the DEPT_TBL would most likely be at Level 1 in the ‘component buffer’, while the EMPLOYEE_TBL would sit at Level 2. As the system loads all records on to the page, it is naturally expecting the parent-child relationship to be maintained. Every record at Level 2 must have a corresponding Level 1. However, in some cases – perhaps due to an SQL delete performed directly on the database – the parent record no longer exists but the child record remains. The system loads the Level 2 record into the component, but then errors when it discovers that there is ‘no matching buffer’ at level 1 for that record.
Compared to other types of system messages, ‘no matching buffer’ errors are relatively easy to troubleshoot. Next, we consider the traditional way to troubleshoot these sorts of errors, along with an unusual alternative, one that takes advantage of the Ctrl-J functionality.
B. SQL Tracing
Tracing has already been covered in another BareFoot PeopleSoft post, so please refer to the link below for further details on how to set up a trace. In the case of ‘no matching buffer’ errors, we need to perform an SQL trace on the component in question, and then analyse the log file afterwards to determine the record causing the problem.
As soon as you have the trace file, the easiest way to find the problem is to start at the bottom of the file, and then do a ‘backwards’ search for the word ‘Rollback’. Hopefully you should find a ‘Rollback’ right at the bottom of the file. Then, analyse the statements immediately before the Rollback. These SQLs should give a clue as to where the problem lies. At the very least, you should get a sense of which records are causing the problem.
Once you know the names of the parent and child records – perhaps from the trace, or perhaps from having prior knowledge of the component – you can then write SQL such as this to track down the ‘orphaned’ child records:
select * from ps_child_table a where not exists (select 'x' from ps_parent_table b where b.key1 = a.key1 and b.key2 = a.key2);
Once you’ve discovered the offending data, you can either re-insert the parent record or delete the orphaned child records. Either way, the component buffer will be happy.
However, you may have a more difficult exercise ahead of you: trying to work out exactly why the parent record was deleted. This subject is beyond the scope of the current post, but you will most likely have to analyse all parts of the system that use the record, trying to work out what has caused the parent record to go missing.
C. Ctrl-J Workaround?
Finally, there might be another way to perform a quick troubleshoot of a ‘no matching buffer’ error… and this one is a little bit strange. I should also add that this technique may not work in all cases. In fact, I’ve just tried it now in a couple of different browsers (Internet Explorer and Chrome), and in both cases, the ‘trick’ failed to work properly. However it has definitely worked in the past, so I’m still going to mention it for the sake of completion.
Just about every developer is aware that if you click Ctrl-J on a PeopleSoft page, you will see a list of basic information about the page that you’re on:
When the ‘no matching buffer’ error appears, dismiss the error and then click Ctrl-J to go into the information screen. Then click Ctrl-J to go back to the original component. The error message will no longer appear, and instead you will see a component partially filled-in with data. By looking through the scroll areas and the grids, you will hopefully come across a child scroll containing data, but with a missing parent record.
As I said, it’s a hit-and-miss kind of technique though. It hasn’t worked on the examples I’ve tried in the course of writing this post. Feel free to give it a go whenever you come across a ‘no matching buffer’ error. (And feel free to use the ‘Contacts’ link on BareFoot PeopleSoft to let me know how you got on.)
Tip 045: Introduction to Tracing