I’ve had a few requests from people to document how I use the  PeopleCodeTools syntax highlighter in Sublime Text. Here’s a quick overview.

Firstly, the source code is freely available on github, for those that would like to contribute to it. And for those unfamiliar with installing this plugin using Sublime Text, all you need to do is first install Package Control, and then install the ‘PeopleCodeTools’ plugin from Package Control.

The plugin should work for PeopleCode methods (in Application Classes), Functions and Event PeopleCode when using ctrl/cmd + r to bring up the Goto Symbol prompt. Here’s an example of searching for Event PeopleCode on a PeopleCode dump:

Event PeopleCode

Here’s another example of searching for method PeopleCode:

Method PeopleCode

The extensions of the PeopleCode files need to end in either .ppl or .pcode.

I’ve also created syntax highlighters for .tracesql files. Although bear in mind that .tracesql files can sometimes generate code with unmatched quotes resulting in messed up syntax highlighting:

Messed up syntax highlighting

For this specific issue I’ve created a tool to tidy up .tracesql files (also available in the plugin). To run the tidy tool, run the “PeopleCode Tools: Tidy PeopleCode Trace” tool from the command palette:

tidy

The syntax highlighting should now look okay:

Tidied syntax highlighting

I’ve noticed that the syntax highlighting plugin slows the opening time for very large files (i.e. 50 MB or greater) due to the fact that the plugin uses regular expressions to create scopes. For this reason, I usually open up a PeopleCode dump at the start of my day and peruse it when needed. I’ve also noticed that the tidy command can take up to 60 seconds on 15MB tracesql files, so please be patient.

Also, when searching for text within large files, I’d recommend disabling the ‘Highlight matches’ option when searching large files:

Disable Highlight matchesIf ‘Highlight matches’ is not disabled, the search will try to instantly match every single character you type. I’m yet to figure out a way to toggle this functionality on and off, so I have to manually de-select it for now.

I’ve also noticed that if you are using the BracketHighlighter plugin, you’ll need to disable it by running ‘BracketHighlighter: Toggle Global Enable’ from the command line. If not, the response times for pretty much anything you do will be quite slow.

That’s all for now. Hope you guys find this plugin useful!

I’ve added another tool to the PeopleCodeTools Sublime Text plugin that allows you to extract the call stack from a PeopleCode trace. You can find it on the PeopleCodeTools repository on github along with the syntax highlighters for PeopleCode and PeopleSoft trace files.

For those unfamiliar with installing this plugin, please see my previous post: PeopleCodeTools – Syntax Highlighting for Sublime Text

This tool only applies to PeopleSoft Trace Files that have at a minimum the following trace parameters:

4044 Trace

To extract the call stack, open the .tracesql file (generated with the trace flags) in Sublime Text  and then run the “PeopleCode Tools: Extract PeopleCode Call Stack” command from the command palette:

Extract PeopleCode Call Stack - Run

The call stack of the trace file should then be extracted and formatted:

Extract PeopleCode Call Stack

I find this tool particularly useful when looking through large trace files and trying to understand where and in what context a particular line of code is invoked. For the most part, this tool works pretty well. I’ve been trying to fix bugs as I discover them. If you’ve installed the PeopleCodeTools plugin using Package Control, you should automatically receive these updates.

 

Update: I have changed this post based on Graham’s recent comments regarding Trace Magic since the trace settings that I recommended were not actually compatible with Trace Magic. Please see the following link for information regarding Trace Magic:

https://communities.oracle.com/portal/server.pt?open=514&objID=224&mode=2&threadid=382561

However, when manually reading a trace file (e.g. in Sublime Text, Notepad++, etc.) I would recommend the following trace settings:

-TRACE 135 -TOOLSTRACESQL 31 -TOOLSTRACEPC 4044

I still find these settings useful for non-performance related debugging issues.

I was running a PeopleSoft Application Engine process that I suspected was hogging the CPU on a Windows environment. I wanted to see whether this process was in fact the cause of the CPU intensive activities on the database.

Interestingly, I discovered that on a Windows environment, an Oracle instance is composed of one oracle.exe process with many different threads. Each thread represents either a background process (PMON, SMON, etc.) or a foreground user session. So, when you are running Oracle server on a Windows environment and encounter a situation where the server CPUs are nearing 100% utilisation, Task Manager only reveals that oracle.exe is taking up the CPU time. There are no details as to which specific thread or session is responsible for the high load.

Fortunately, I managed to find out which thread was responsible for the CPU hogging by downloading a tool (ProcessExplorer by sysinternals) that supports displaying threads within processes. You can download that here.

Process Explorer shows all the processes that are currently running. After double-clicking on the oracle.exe process, I was able to find the TID (Thread ID) that had the highest CPU utilisation. I then ran the following query in SQL Developer, substituting :1 for the TID that had the highest CPU utilisation.

[sourcecode language=”sql”]
select proc.spid TID, sess.username, sess.osuser,
sess.status, sess.sid, sess.program
from v$process proc, v$session sess, v$bgprocess bg
where sess.paddr = proc.addr
and bg.paddr(+) = proc.addr
and proc.spid in (:1);
[/sourcecode]

This query can be modified to include more columns to be displayed from v$session, v$process and v$bgprocess depending on your specific preferences.

I was also able to identify the specific SQL that the thread was running, by executing the following SQL and substituting :1 for the TID identified earlier:

[sourcecode language=”sql”]select sqlarea.*
from v$process proc,
v$session sess,
v$sqlarea sqlarea
where proc.addr = sess.paddr
and sess.sql_hash_value = sqlarea.hash_value
and proc.spid in (:1);
[/sourcecode]

I was interested to see a clean view of what the bind variables were for the currently executing SQL statement. I used the following query:

[sourcecode language=”sql”]
select * from v$sql_bind_capture
where sql_id = ‘<ENTER SQL_ID HERE>’;
[/sourcecode]

I then created indexes on columns in the SQL statements that I thought were problematic.

It’s also worth noting that if you really need to, you can kill the session by issuing the following command:

[sourcecode language=”sql”]ALTER SYSTEM KILL SESSION;[/sourcecode]

I wanted to utilise the following setup:
1. Base VM (Eg. Base) that is used as a template
2. Copies of that VM (eg. JayVM, JayVM2)

Since Base VM was not registered in VirtualBox, I was successfully able to create JayVM by copying the vmdk files (Windows_XP.vmdk) from Base VM to a new folder C:\hosted images\JayVM. However, I soon encountered the following error when copying the vmdk files of Base VM into new folder c:\hosted images\JayVM2 in order to create JayVM2:

Failed to open the hard disk C:\hosted images\JayVM2\Windows_XP.vmdk.

Cannot register the hard disk 'C:\hosted images\JayVM2\Windows_XP.vmdk' {00c11b02-c807-443d-b10d-dfffe0ae1b96} because a hard disk 'C:\hosted images\JayVM2\Windows_XP.vmdk' with UUID {00c11b02-c807-443d-b10d-dfffe0ae1b96} already exists.

Result Code: E_INVALIDARG (0x80070057)
Component: VirtualBox
Interface: IVirtualBox {c28be65f-1a8f-43b4-81f1-eb60cb516e66}

I’m guessing I encountered this because I didn’t use the clone functionality in VirtualBox. But I didn’t really want to use the clone functionality because it would require me to always keep a copy of Base VM on my hard drive, and my VMs take up a lot of disk space (sometimes 45 GB). Instead I zip up Base VM and keep it on a separate server.

I managed to resolve this issue by navigating to my VirtualBox install directory (C:\Program Files\Oracle\VirtualBox) and running the following command to change the UUID:
vboxmanage internalcommands sethduuid “C:\hosted images\JayVM2\Windows_XP.vmdk”
This command changed the UUID for the VM, allowing me to be able to run both VMs (JayVM and JayVM2) on the same host.

Here’s a bat file I created to help me do this:

I encountered the following error when trying to launch Peoplesoft applications such as Application Designer and Data Mover on my Windows 7 (64-bit) virtual machine:

My machine had Oracle Server 10.2.0.2 (64-bit) installed. I discovered that while PeopleTools 8.5x has been certified to run on 64-bit Windows platforms, the program itself is 32-bit. Thus, any library it needs to load also needs to be 32-bit. In this case, the Oracle server lib ‘PSORA’ must be the 32-bit version rather than the 64-bit version. I managed to work around this by installing the 32-bit Oracle Client (10.2.0.2) version on the system.

After rebooting, I noticed some other weird errors. I ended up removing my ORACLE_HOME environment variable altogether and then modifying the Add to PATH variable in psappsrv.cfg to point to both the Client and the Server ORACLE_HOME environment variables, and everything started working again.

I was trying to debug an Application Engine and noticed that the debugger had hung. I restarted Application Designer and re-ran the debugger for the Application Engine. Since there was only one instance assigned to each temporary table, I noticed that the previous failed run had not released the temporary tables. I managed to work around this by deleting from the following tables:

The following are the SQL Statements that I used:

If you’re sure that nobody else is using the system you can just run the following:

Note: TRUNCATE does not require a commit as it is a DDL statement.

If you are performing an impact analysis using Find Definition References, and are changing Record-Field PeopleCode, then you first need to determine whether the underlying Record for the Record-Field is a derived record. If the record IS a derived record, then you can use Find Definition References on the Record-Field. However, if the record IS NOT a derived record, then you need to use Find Definition References on the Record as all of the fields of the record will be stored within the component buffer, and thus certain PeopleCode events (FieldDefault, FieldFormula, RowInit, SaveEdit, SavePreChange, Workflow, SavePostChange) on those fields will execute.

Example: When a Record is not a derived record, and ANY field of that record is added to a page (eg. perhaps in a Grid or a Scroll Area), then the above-mentioned Record-Field PeopleCode Events will ALWAYS execute for ALL fields.

I wasted a good couple of hours trying to figure out why my application engine programs would no longer run in Application Designer (two-tier). I have concluded that it is due to a PeopleTools bug in Application Designer. Something was causing the App Engine program to run in an infinite loop. I had to manually close the psae.exe process, and observed that the log file couldn’t even execute the first step of MAIN:

PeopleTools 8.51.03 - Application Engine
Copyright (c) 1988-2011 Oracle and/or its affiliates.
All Rights Reserved

2011-04-05-14.48.24.218016  SearchArchive [1155] Exception logged: RC=3.
2011-04-05-14.48.24.218016  AeCursCheckOutCallState [1119] Exception logged: RC=3.
2011-04-05-14.48.24.218016  GetNextStateRecord [68] Exception logged: RC=3.
2011-04-05-14.48.24.218016  GetNextStateRecord [68] Exception logged: RC=3.
2011-04-05-14.48.24.218032  GetNextStateRecord [68] Exception logged: RC=3.
14.48.25 Tracing Application Engine program JAY_AE Jays Test AE (C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\PS\PS\AE_0405144824_2188.AET)
Application Engine Debugger - enter command or type ? for help.

JAY_AE.MAIN.Step01>
Invalid command - please try again or type ? for help

JAY_AE.MAIN.Step01>
Invalid command - please try again or type ? for help

JAY_AE.MAIN.Step01>
Invalid command - please try again or type ? for help

JAY_AE.MAIN.Step01>
Invalid command - please try again or type ? for help

This was really frustrating as it was working all along before. The only way I could get it working again was to deselect the “Debug” checkbox in Configuration Manager. You can do this by performing the following steps:
1. Open Configuration Manager
2. Navigate to the “Profile” tab
3. Click the “Edit” button for the Profile
4. Click on the “Process Scheduler” tab
5. Deselect the “Debug” checkbox on the Application Engine frame

Deselect the "Debug" checkbox on the "Process Scheduler" tab

This is my current workaround, but it means that I’m unable to debug non-PeopleCode Application Engine steps (unless I run psae.exe manually…but then I can’t debug PeopleCode steps!). If anyone knows how to fix this problem, then I’d be grateful if you could let me know :S.