jump to navigation

SQL Developer Hints and Tips February 17, 2009

Posted by Duncan in SQL.
comments closed

I’ve put together an overview of Oracle’s SQL Developer product – at least the main features from a PeopleSoft developer’s perspective – plus some productivity tips and configuration tweaks to make it a better user experience.

It’s probably a bit big to be a blog post so I’ve put it on a tab at the top, and at this link:


Creating an entirely read-only user in PeopleSoft August 28, 2008

Posted by Duncan in Oracle, PeopleSoft, PeopleTools, Security, SQL.
comments closed

On big projects it is quite likely that large numbers of developers have access to a many environments. Occasionally they can have access to environment which is quite important, for instance one that the customer is using for training or testing.

To reduce the likelihood of developers accidentally deleting some data that they shouldn’t it would be quite normal to remove their access to the environment altogether. However if they need access for troubleshooting purposes then (at least on projects I’ve seen) it’s quite normal for developers to be told “OK, you can have access, but be careful not to do anything destructive”. Occasionally – as with everything – things can go wrong. Either someone forgets which environment they’re in, or does something with unintended consequences. An alternative to the “just be careful” approach would be to create an entirely read-only user profile (i.e. one that has display only privileges to every component system-wide).

A read-only user profile is shown in screenshot below, where no fields are editable and the save button is inactivated:

Also, on Run Control pages the ‘Run’ button is inactive. It’s going to be pretty difficult to alter data in this environment.

Here’s how to do it quickly and easily …


Substringing and Oracle SQL – Basic Trick August 18, 2008

Posted by Duncan in Oracle, SQL.
comments closed

This may well be something that you already know, but it was totally new to me.  It was one of those magic moments where you stumble across something so elementary you wonder:

a) how you haven’t found out about it before

b) how much time it’ll save in the future

This is related to taking only a portion of a string using Oracle SQL, and in particular the right hand side of a string.  In other programming languages I’ve been spoilt with the RIGHT(x,num_chars) command, which we don’t have in Oracle.

In the past I’ve made do using something like:


if I wanted to take the 4 right-most characters from a string.  This gets cumbersome pretty quickly when you have multiple substrings etc.

Today – to my delight – I discovered that you can include negative numbers and Oracle will count back from the right hand side.  So this will take the 4 right-most characters from a string:


PeopleCode records: PSPCMNAME and PSPCMPROG August 11, 2008

Posted by Duncan in PeopleSoft, PeopleTools, SQL.
comments closed

A widely known tip and a new (at least for me) discovery:

Widely known tip

Most techies who’ve looked under the covers will be aware of PSPCMPROG. It’s the underlying table where PeopleCode is storeed. This isn’t immediately useful however as the actual code itself is stored in the PROGTXT field in binary so it’s not easily accessible. This isn’t an insurmountable issue however as there are a couple of routines to decode the field (an SQR and a Java version, both by David L Price). I’ve not used either method personally, but a colleague has used the SQR version with great success.

For me, this field isn’t quite the most useful on PSPCMPROG. You may have noticed that if you update the PeopleCode on a record, the record properties aren’t updated to reflect the change – I guess because the record definition itself hasn’t changed (although strangely Component and Page PeopleCode do update the timestamp on the corresponding Component/Page definition – so there’s a bit of an inconsistency there). So how do you check when and by whom a piece of Record PeopleCode was last updated? If you check the LASTUPDDTTM and LASTUPDOPRID fields on the PSPCMPROG record via SQL then it’s all recorded there. This snippet of knowledge has saved me (or at least expedited troubleshooting by showing me the correct person to ask about the change) innumerable times.

Lesser known tip

I’ve not really had much reason to look at the PSPCMNAME record in the past, however a colleague and I checked it recently and discovered the RECNAME and REFNAME fields. Every time you create some PeopleCode your code is parsed and a line inserted into PSPCMNAME for every reference to a Tools object. I assume that this is the record searched when using ‘Find Definition References’ within App Designer. However, now we know the SQL table behind this we can therefore query this table to quickly find out objects affected by code in a specified project, for example.

Both the company I currently work for and my previous employer had utilities to export project details to file, printing out settings and details from Tools objects to speed-up the documentation process (and we know how much techies love documenting!). Now we can add to that utility any objects that are affected but which aren’t included in the project.

SQL Best Practises in less than 20 minutes January 29, 2008

Posted by Duncan in Oracle, PeopleSoft, SQL.
comments closed

A post on the blog of H.Tonguç Yılmaz drew my attention to a great video tutorial by Stephane Faroult on “SQL Best Practises in less than 20 minutes”.


Stephane clearly knows his topic, but also possesses a wonderful dry wit. His comical example of the developer’s shopping algorithm and the “DBA doing the parameter dance” to tune it had me chuckling away and has made sure that the point won’t be forgotten.

Materialized Views January 10, 2008

Posted by Duncan in Oracle, PeopleSoft, SQL.
comments closed

A materialized view (aka Snapshot) is a sort of ‘summary table’, the use of which allows you to reduce the processing time and complexity of some queries.

It’s a view where the data is defined via a SQL statement, but the resulting dataset is actually stored in the database (which can then be indexed, analysed etc). Depending upon the parameters chosen Oracle can keep the data in your view in sync with that in the tables upon which it is based. They were originally designed for replication (i.e. holding local copies of remote tables) but they’ve been adapted for performance tuning and reporting use.


Shared Pool December 13, 2007

Posted by Duncan in Oracle, PeopleSoft, SQL.
comments closed

Oracle Databases contain a ‘shared pool’, which is a repository for recently run SQL. If you attempt to execute the same piece of SQL repeatedly you’ll probably notice that the execution time decreases, this is because of how Oracle parses the SQL (i.e. it’s a soft parse, not a hard parse) and the execution plan is already calculated for the SQL. I’ve spoken about this before in an App Engine context here.

This cache (or to use the correct term the ‘Shared Pool’) is all very useful, but what if you’re trying to tune a piece of SQL? You want to get a ‘natural’ idea of how long it’ll take to run, not the artificially quick result you’ll get if it’s in the shared pool. (Execution Time is of course not the only measure to consider when tuning, examining the execution plan is critically important.)

We need to flush any mentions of our SQL from the shared pool. DBAs can clear it by issuing the ‘ALTER SYSTEM FLUSH SHARED_POOL’ command, but us un-privileged developers don’t have that luxury (and neither would we want to clear the entire cache).

Instead, re-calculating the statistics on a table means that “all shared SQL areas that contain statements referencing the analyzed schema object are flushed from the shared pool”.

A fuller (and slightly more expertly worded) explanation is available here:


Portal Navigation December 9, 2007

Posted by Duncan in Oracle, PeopleSoft, SQL.
comments closed

I would imagine many of you are familiar with the PSPRSMDEFN table as it is tremendously useful. I would gamble most have a SQL snippet tucked away somewhere that will query it to find the PIA navigation to any given component. However Jim Marion has posted some SQL on his blog that is the tidiest I’ve seen to date (it’s Oracle specific BTW).

Most SQL snippets use multiple joins to the table, I’ve seen one before that used connect_by_prior, but this one is the smallest script yet.

Thanks Jim!


this slightly more succinct version may be even better.
select distinct rtrim(reverse
         (portal_label), ' > ')), ' > ') path
  from psprsmdefn
 where portal_name = 'EMPLOYEE'
   and portal_prntobjname = 'PORTAL_ROOT_OBJECT'
 start with portal_uri_seg2 = :1
connect by prior
         portal_prntobjname = portal_objname

An Alternative way of writing Effdt clauses October 7, 2007

Posted by Duncan in Oracle, PeopleSoft, SQL.
comments closed

I’ve covered the RANK analytic function before but neglected to mention a useful application for it, and that is for rewriting Effdt and Effseq clauses more efficiently.

The usual way to return the job row (this could be any table, I’m just picking job as a common example) that’s effective for each Employee at a specific date is probably something similar to this:

SELECT emplid, empl_rcd, effdt, effseq
  FROM ps_job j
 WHERE j.effdt =
    (SELECT MAX (j1.effdt)
       FROM ps_job j1
      WHERE j1.emplid = j.emplid
        AND j1.empl_rcd = j.empl_rcd
        AND j1.effdt <= '10/AUG/2007')
   AND j.effseq =
    (SELECT MAX (j2.effseq)
       FROM ps_job j2
      WHERE j2.emplid = j.emplid
        AND j2.empl_rcd = j.empl_rcd
        AND j2.effdt = j.effdt)

Execution Cost = 3010, time = 1 sec.

SELECT emplid, empl_rcd, effdt, effseq
  FROM (
SELECT emplid, empl_rcd, effdt, effseq,
       (PARTITION BY emplid, empl_rcd
            ORDER BY effdt DESC
                   , effseq DESC) rnk
           FROM ps_job
          WHERE effdt <= '10/AUG/2007') j
 WHERE rnk = 1

Execution Cost = 665, time = 78 msecs.

Not only is the code shorter and easier, the database execution cost is less than a quarter of the ‘standard’ method and the execution time is less than a tenth.

Explain plans and costs are obviously variable depending upon your environment, the tables and indexes and the volume of data; however it should be clear from the example above that there are sometimes quicker ways than the ‘standard’ method.

Lag and Lead September 21, 2007

Posted by Duncan in Oracle, PeopleSoft, SQL.
comments closed

I’ve previously looked at the RANK and DENSE_RANK analytic functions, so thought I’d expand a little by mentioning LAG (and LEAD).

Analytic Functions are extensions to Oracle SQL that enable common tasks to be accomplished faster than using large blocks of SQL or PL/SQL. They are apparently ‘under review by the ANSI SQL committee for inclusion in the SQL specification’, although whether this will ever happen or not is anyones guess.

The LAG function solves (at least for those of us on Oracle) the oft-encountered issue of retrieving data from a prior row.