Wednesday, March 23, 2005

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.

27 comments:

Derek said...

Interesting post. I think I'll have to read it a couple times before I totally get a grasp of it. I came across your blog from the PeopleSoft Fans User Group. Do you plan to post more PeopleSoft stuff or is that just how you started your blog?

David L. Price said...

This whole keeping a blog thing is still pretty new to me. I still haven't developed the discipline to update it as regularly as I should. I have a ton of other things I want to say and post about, so hopefully I will have more soon. I will definitely have more PeopleSoft stuff. I have years of this kind of stuff just sitting around.

Anonymous said...

Thanks! Been trying to extract information on the PCode part of Ap Engines, and my program would abend when I hit a long PCode. Had figured out some of the "clear text" stuff, and written a routine to print the hex values when what was there wasn't clear text -- but had not yet figured out how to access a large "long raw" field. Haven't yet verified that your code will do what I want, but it's a good start and I'll surely learn a lot.

Anonymous said...

Hey.. great.. keep posting more information ]

great help to begin with

Arshi said...

Hi,
Really great piece of information.
Keep going.
Please share if you have some updated information on the same

Anonymous said...
This comment has been removed by a blog administrator.
Derek said...

Hello!

I've been playing around with the decoder SQR and it's great! One issue I'm having is with decoding the actual numbers that are in PeopleCode.

For example:
&MSGBOXTITLE = MsgGetText(10302, 3, "Cancel");

The "10302" and "3" in the code seem to not get decoded correctly. In the evaluate of the #tVal variable, they come out to a 17 but I can't figure out what to do with it from there. Have you found this to be an issue as well?

David L. Price said...

I have never seen an issue like that before. I have a newer version of the Decode PC SQR that added some logic to handle number. It appears that in older code, PeopleSoft handled numbers differently. I would be curious if that resolved your issue. I will post and updated version of the program in the next day or so.

Derek said...

That would be great David, thanks. It doesn't look like that 17 pops up for all numbers in the various types of PeopleCode. Some like Page PeopleCode decode the numbers in the MsgGetText functions correctly. It looks like this is specific to Menu PC and Record PCode.

Anonymous said...

decodepc.java is decodepc.sqr re-written into Java with expanded functionality. Uses JDBC connection to PeopleSoft. There's no need to use Application Designer to read an Application Engine program.

http://www.passportgeek.com/?q=forum/52

Larry said...

Do you have any tool which can do debug in App Server SOA? I read an article on http://peoplesoftsupport.blogspot.com and found about fusion middleware which is future for PeopleSoft.

yare said...

GeoCities is down now, could you please upload your decodePC.sqr somewhere else ??

Thank you very much in advance...

psftfan said...

Hi David, I'm interested to have a look at your decodepc.sqr. But it looks like the link on geocities is no longer available. Any possibility to post it again?

David L. Price said...

Since Geocities closed their doors, the links to the SQRs have been broken. I am now 'hosting' the SQRs on Dropbox, and have updated the links accordingly. Sorry for the issue.

yare said...

Thank you very much, really appreciate that.

David Vandiver said...

Link to DecodePC.sqr seems to be broken again.

David Vandiver said...

The link to the SQR is not working again. Thanks

David L. Price said...

Seems to be working for me. Maybe it was some issue with Dropbox when you tried. Is it still not working?

David Vandiver said...

May be a block on the network. I'll try again on my home network.

BTW, what copyright restrictions do you have on the code? I am building an Application Engine Analyzer in SQR and would like to include the DECODEPC code. The new SQR will be on a free site called SQRTricks.com.

Asgar said...

Hai..

Check out the following peoplecode segment with decodePC.java, it is not decrypting number..

If None( ENTRY_REASN_TBL.DESCRSHORT) Then
ENTRY_REASN_TBL.DESCRSHORT = Substring( ENTRY_REASN_TBL.DESCR, 1, 10);
End-If

Regards,
Asgar

Anonymous said...

Awesome, that’s exactly what I was scanning for! You just spared me alot of searching around

Anonymous said...

Impressive that you wrote this with something as crappy as SQR.

It has inspired me to write a new PCode decoder; check out project Decode PeopleCode on SourceForge.

Anonymous said...

waiting for next post

Anonymous said...

Hey There. I found your blog using msn. This is a very well written article. I will be sure to bookmark it and come back to read more of your useful information. Thanks for the post. I'll definitely return.

Javier Mansilla said...

Hi, my name es Javier, i`m from argentina.
I build "Mansijav List Project" in Visual Basic 6.0. This tools export project content and detail of same object.
I make a function peoplecode decoder, was a hard work but was funny too.
If you have some question about some exa code.... tell me, i will help you if i know de answer.
Best regards.
Javier

PD: i found the same "Peoplesoft Secret" article in the begining..... it is offline now. Do you have any copy of the original article?

PD2: sorry for my english.....

David L. Price said...

You can find an archive of the original article on archive.org. Here is a link to the part about PeopleCode: http://web.archive.org/web/20031007194458/http://www.vijaymukhi.com/pcode/chap5/chap5.htm

Gobikkannan said...

Hi I'm Gobi ,

For my requirement I need to extract the data which is inside the prgtxt field @ pspcmprog table. After that extraction I need to append some blob things with that structure for mask the data.

After reading this article I'm thinking my plan regarding the extraction and append will not work.

Is there anyway to achieve this.