PeopleCode Decoder

Updated Jan 5, 2010 - Fixed link to DECODEPC.sqr
Updated March 10, 2011 - Small change to add a couple more commands.
Updated October 1, 2011 - Additional updates, and moved the code to github

Several years ago, I came across an article title PeopleCode Secrets by Vijay Mukhi, Louis Fernandes and Sonal Kotecha. This provided a look behind the scenes of how PeopleSoft stores PeopleCode in the database. The article was never completed (at least not that I could find), but it did get me curious. DECODEPC.sqr was the result of the curiosity. I decided to try extending the information contained in the article and create an SQR that would be able to decode the PeopleCode BLOB.

This turned out to be more of a challenge than I though. PeopleCode is stored in a Long Raw field in the PSPCMPROG table... at least, up to 27,000 bytes of it. If the program goes over 27,000 bytes, it then flows over into an additional row, incrementing the PROGSEQ field by one. While SQR can read Long Raw fields well enough, it can only handle around 36,000 bytes. Since PeopleSoft only stores 27,000 bytes per field, this should be no problem then, right? Well, unfortunately, when SQR reads the Long Raw, it also converts it into a hexadecimal string. So, three bytes might end up looking like "A3 23 FF" (spaces are not actually returned, but there for clarity). These three bytes become a six byte string. So that 27,000 bytes, is actually more like 54,000 bytes. Yikes! OK... so I thought that maybe I could only deal with 32,000 byte 'chunks' of the data. Something like a substring function. Then, when I was done with that subset of the data, I could just go back a second time for the rest of the data. A fine idea, but ORACLE does not provide any easy way to substring a Long Raw. LOB fields, on the other hand, had a wealth of handy functions provided by Dbms_Lob. Since I couldn't figure out a way to cast the Long Raw into a LOB on the fly, I ended up created a work table (DLP_PCODE) which contained a BLOB field. I could then copy the Long Raw into the BLOB, converting it using the to_lob function. Nice, huh? Unfortunately the substring function that was delivered with Dbms_Lob could only return 2000 bytes at a time. This turned out to be not so much an issue... it just meant more slices. So now I have my PeopleCode program divided up into 2000 byte "slices", spread across multiple rows in 27000 bytes chunks. This is all handled by the NextByte and Get-Next-Segment functions in the SQR.

I won't go into a lot of detail about how PeopleCode is stored, since a lot of it is in the PeopleCode Secrets article. Fortunately not much has changed since the article was written (sometime pre version 8). The SQR also has many comments explaining things that I figured out that were not necessarily mentioned in the article. One thing that did change since the article, is that strings are now stored as double byte characters. In addition to string literals and comments, this includes almost all non-keywords and user defined names: variables, functions, methods, classes, etc. Currently, my program does not try to handle any extended characters... it simply ignores the second byte of the character.

My SQR is by no means complete. I still don't have all of the codes for the different PeopleCode elements. Indentions are still a mess. And I don't quite have the line feeds down right. But I was eager to share it with you all. As of this writing, I am posting my March 14th, 2005 version of the program. I will probably continue playing with it from time to time. If you figure out anything that is still missing, let me know. When the program encounters something it doesn't understand, it will simply stop. This has been the easiest way for me to determine what I am missing.

Also, try entering an "I" at the prompt. The "Interactive Mode" was a recent add-on... more to play with some ideas I had than anything else.