Database

Alter Audit

The term alter audit in PeopleSoft refers to running an alter build script in application designer on a number of records (tables) to check if there are any differences between the PeopleSoft definitions and what exists in your database.

To perform a comprehensive alter audit of your current environment:

insert-tables-into-project.png

alter-audit-build-settings.png

alter-audit-build-settings-alter-tab.png

Analyze Tables

You can use the ANALYZE TABLE command to store table statistics for Oracle (and many other DBMS) execution plans. A simple version of this script involves estimating statistics using the command:

analyze table 'TABLE_NAME' estimate statistics

Adding Sequence Numbers

To add a new sequence number based on existing key structures where the sequence number increments based on those keys the following query logic applies. This would be used when there is data already present and PeopleCode will not be triggered to set the sequence number.

How this query works is to group results based on keys and then find the count of rows with a rowid greater than or less than its rowid.

UPDATE RECORD_NAME T1
  SET T1.SEQNUM_FIELD= (
    SELECT Count(*)
    FROM RECORD_NAME T2
    WHERE
      T1.KEY_FIELD_1 = T2.KEY_FIELD_1 
      AND  T1.KEY_FIELD_2 =T2. KEY_FIELD_2
      AND  T2.ROWID >= T1.ROWID 
    GROUP BY T2.KEY_FIELD_1, T2.KEY_FIELD_2 
) 

Approximate Database Size

The following SQL is for an Oracle Database and it attempts to approximate the size of a PeopleSoft database using the user extents table. Basically, it looks at size of all tables and indexes (through table spaces) in the PeopleSoft database. I emphasize that this is just an approximate size and will be less than the true size.

Note, you will need to change the <OWNER> parameters to the owner of your PeopleSoft database (e.g. SYSADM). Each query takes a while to run, give them at least a few minutes.

This query gives you the total size of all tables:

select
    SUM(BYTES)/(1024*1024) TOTAL_SIZE_OF_TABLES_IN_MB
from 
    USER_EXTENTS UE
where 
    UE.SEGMENT_TYPE = 'TABLE'
    and UE.TABLESPACE_NAME in (
        select distinct TABLESPACE_NAME 
        from ALL_TABLES
        where OWNER = 'SYSADM'
        and TABLE_NAME like 'PS%'
    )
;   

While this query gives you the total size of all indexes:

select
    SUM(BYTES)/(1024*1024) TOTAL_SIZE_OF_INDEXS_IN_MB
from 
    USER_EXTENTS UE
where 
    UE.SEGMENT_TYPE = 'INDEX'
    and UE.TABLESPACE_NAME in (
        select distinct TABLESPACE_NAME
        from ALL_INDEXES
        where TABLE_OWNER = 'SYSADM'
        and TABLE_NAME like 'PS%'
    )
;

Add the two sizes together to get your approximate database size.

Audit Records

If you are creating an audit record, there are normally three fields that need to go at the start of the audit record (remember to use the AUDIT_ prefix). These three fields will uniquely identify your audit data. They are (in the order they should be put on the audit record):

  1. AUDIT_OPRID
  2. AUDIT_STAMP
  3. AUDIT_ACTN

The audit stamp field is a date/time field. However in order for it to be correctly populated, make sure that you set it to Auto-Update so that it is automatically set to the correct date/time stamp.

If you are using the same audit record for multiple records then add the field AUDIT_RECNAME. Ensure that this is system maintained and this will tell you which record the audit action relates to.

Non Key-Preserved Table ORA-01779

You may get the following Oracle error when building a component with multiple scroll levels:

ORA-01779 cannot modify a column which maps to a non key-preserved table

What this is telling you is that PeopleSoft is trying to automatically write (insert/update) into a table that it should not be able to.

More than likely, this will be a view.

Auto Generate a Select Statement

The following SQL will automatically generate a formatted select statement with all the fields of the record you specify. Note that this only works in Oracle:

select
   'select '
   || substr(SYS_CONNECT_BY_PATH(chr(13) || chr(9) || FIELDNAME, ','), 2)
   || chr(13)
   || 'from '
   || (case when substr(RECNAME, 1, 2) != 'PS' then 'PS_' || RECNAME else RECNAME end)
   || ';' as SELECT_STATEMENT
from
   (
   select
     RECNAME,
     FIELDNAME,
     FIELDNUM,
     count(*) OVER ( partition by RECNAME ) as FIELDCOUNT,
     ROW_NUMBER () OVER ( partition by RECNAME order by FIELDNUM) FIELDSEQUENCE
   from
     PSRECFIELDDB
   where
     RECNAME = '<RECNAME>')
where
   FIELDCOUNT=FIELDSEQUENCE
start with
   FIELDSEQUENCE = 1
connect by prior
   FIELDSEQUENCE+1=FIELDSEQUENCE
and prior
   RECNAME=RECNAME
order by FIELDNUM
;

Remember to replace <RECNAME> with the appropriate PeopleSoft record (e.g. PSUSEREMAIL. This script should correctly prefix your record name with PS_ where appropriate.

Note the use of the record RECFIELDDB which includes all sub-record files so this will work on records that use sub-records such as EMPLOYEES. Thanks to Jonathan Kearney for this advice.

If you use the example of PSUSEREMAIL the output of the query will look like this:

select
	EMAILID,
	EMAILTYPE,
	OPRID,
	PRIMARY_EMAIL
from PSUSEREMAIL;

Note that if you run this in a query tool such as SQLTools++ or TOAD, then you may only see the single word select in your query output - the rest of the text is there, it is just not visible because of the new line characters. Simply copy and paste the output to an editor window and it should display.

If you want to prefix your columns with an alias and give your record an alias use the following version and replace <RECNAME> and <ALIAS> with the appropriate values.

For example, PSUSEREMAIL as record and A as the alias.

select
   'select '
   || substr(SYS_CONNECT_BY_PATH(chr(13) || chr(9) || ALIAS || '.' || FIELDNAME, ','), 2)
   || chr(13)
   || 'from '
   || (case when substr(RECNAME, 1, 2) != 'PS' then 'PS_' || RECNAME else RECNAME end) 
   || ' ' || ALIAS
   || ';' as SELECT_STATEMENT
from
   (
   select
     RECNAME,
     '<ALIAS>' as ALIAS,
     FIELDNAME,
     FIELDNUM,
     count(*) OVER ( partition by RECNAME ) as FIELDCOUNT,
     ROW_NUMBER () OVER ( partition by RECNAME order by FIELDNUM) FIELDSEQUENCE
   from
     PSRECFIELD
   where
     RECNAME = '<RECNAME>')
where
   FIELDCOUNT=FIELDSEQUENCE
start with
   FIELDSEQUENCE = 1
connect by prior
   FIELDSEQUENCE+1=FIELDSEQUENCE
and prior
   RECNAME=RECNAME
order by FIELDNUM
;

Auto Numbering

There are a number of ID fields in PeopleSoft that are auto-numbered. A good example is the EMPLID (employee ID) field. The field EMPLID_LAST_EMPL stores the last system allocated EMPLID. This field can be found in the PS_INSTALLATION table. This means the next time a person (EMPLID) is created, they will get the value in in the EMPLID_LAST_EMPL field of the PS_INSTALLATION table + 1.

Getting the Next Number

Now you might think that if you wanted the next available ID for such a field, you would need to write some PeopleCode to increment the value by 1. However, this isn't necessary as there are two delivered functions to do this for you:

Note that the function GetNextNumberWithGaps is now deprecated.

What's the difference?

GetNextNumber examples:

GetNextNumber(PS_INSTALLATION.EMPLID_LAST_EMPL, 9999999999)
GetNextNumber("PS_INSTALLATION", "EMPLID_LAST_EMP", 9999999999)

The value 9999999999 is the maximum value of the EMPLID_LAST_EMPL field which has a length of 10.

GetNextNumberWithGapsCommit examples:

GetNextNumberWithGapsCommit(PS_INSTALLATION.EMPLID_LAST_EMPL, 9999999999, 1)

The extra parameter with a value of 1 means the increment value (in this case increment by 1).

Finding where sequence numbers are stored.

Breadcrumbs not updated from Pagelet Link

Sometimes, clicking the hyperlink in a pagelet to another component, takes you to the component but the breadcrumbs (in PeopleTools 8.5) are not updated. This occurs if the target component has the additional parameter, NAVSTACK=Clear. The NAVSTACK=Clear parameter is used for context sensitive return to links.

In addition to this, the NAVSTACK=Clear parameter has the undocumented feature of not updating breadcrumbs. To see which content references have this parameter, use the following SQL:

select * 
from PSPRSMDEFN 
where PORTAL_URLTEXT like '%NAVSTACK=Clear%';

Note that NAVSTACK=Clear is mainly used in PeopleSoft HRMS.

Changing your Oracle Session Schema

Use the following code to alter your session's current schema:

ALTER SESSION SET current_schema = SYSADM

This will change the current schema to SYSADM when you are logged in as a different schema.

Generate Oracle Public Synonyms

The following SQL can be used to generate a script to create public synonyms (in Oracle). This example uses the SYSADM user but it can be adapted to suit:

select
  'create public synonym ' || table_name || ' for SYSADM.' || table_name || ';'
from ALL_TABLES
where owner = 'SYSADM';

SQL Account Is Locked ORA-28000

If you ever get the following message:

SQL Access Manager
SQL error. Stmt #: 2 Error Position: 0 Return 2800 - ORA-28000: the account is locked.

This means that the system account used by PeopleSoft (typically SYSADM in Oracle) has been locked. If you (can) try to login to the environment using SYSADM through a SQL tool you would get the same error. This error occurred when I was attempting to do a compare report in application designer but could happen at any time.

Ask your DBA to unlock the account, as they will need to login with SYS access to an Oracle database.

Custom Sorting with Decode

When trying to sort based on values that do not fit the standard ascending and descending sort logic provide by your database engine you can use the following decode() function to sort based on custom value ordering.

By ordering your value list in a decode function you can sort based on the integer values you assign to each value.

SELECT X.FIELDVALUE, X.XLATLONGNAME
FROM PSXLATITEM X
WHERE X.FIELDNAME = 'NOR_IMPRINT_TYPE' 
ORDER BY decode( X.FIELDVALUE, 'S', 1, 'N', 2, 'O', 3 )

The result is then:

FIELDVALUE XLATLONGNAME
S Standard
N Optional
O Optional, additional charge

Last Refresh of Environment

Sometimes you'll need to known when an environment was last refreshed and may not have the details of this. One way to approximate this using the PeopleTools tables is to look at the process request table.

Specifically you can use a query like this to check the first process that was run:

select * from PSPRCSRQST 
where DBNAME = '{your database}'  
order by RUNDTTM asc;

It may only be an approximation as it relies on at least one process running after the refresh (typically a system process) which may or may not have happened on the same day as the refresh. Even so it, gives you a timeframe to work with.

Case When Exists SQL

The Case-When-Exists expression in Oracle is really handy. Here's an example of how to use it in a sub-select to return a status. This SQL checks for a match between the PS_PERSON and PSOPRDEFN records to determine the person status. The idea is that if the operator is not in PS_PERSON then they are not a true person in PeopleSoft.

Please be aware that this SQL will only work if:

select
    O.OPRID,
    O.EMPLID,
    case when exists (
        select 1
        from PS_PERSON P
        where P.EMPLID = O.EMPLID
    ) then 'Person' else 'Not a Person' end as PERSON_STATUS
from PSOPRDEFN O;

Enumerate Rows in SQL

The following SQL is an example of how to enumerate (number) rows in SQL. This SQL will only work on Oracle databases.

select
    ROW_NUMBER () OVER (partition by ROLEUSER order by ROLENAME) as SEQNO,
    ROLEUSER,
    ROLENAME
from PSROLEUSER
order by ROLEUSER;

In this example, the roles for each user will be enumerated.

So for example, the results for the user PS would be something like this (roles 1, 2, 3 … n)

SEQNO ROLEUSER ROLENAME
1 PS ADHOCUSR
2 PS AM Administrator
3 PS AppServer Administrator
n PS Rolename

This SQL is very useful if you need to insert rows with a sequence number or effective sequence number into a table.

Conditional Counting In SQL

If you ever want to conditionally count the number of times a particular condition occurs in SQL, you can do it in Oracle using the case and count functions. Here's a simple example which counts the number of males/females stored in PS_PERSONAL_DATA.

select
    count(case when SEX = 'M' then 1 end) as MALES,
    count(case when SEX = 'F' then 1 end) as FEMALES
from PS_PERSONAL_DATA

All that is happening is that the case statement returns a 1 for every instance where the gender is M or F and the count, counts each returned value of 1 giving a summary like this:

MALES FEMALES
10004 20421

The conditions in the case statement can be a lot more complex and can include sub-queries.

Field Requires Unsupported Conversion

While performing an alter on a table, I received the following error in the SQL Build log (PSBUILD.LOG):

Error: PS_EXAMPLE_REC - Field EXAMPLE_FIELD requires an unsupported conversion.  Record skipped. (76,29)

In this scenario, I had updated a field on a record after incorrectly making it a character field instead of a signed number. The conversion was from character (e.g. '1') to signed number (e.g. 1). However, the alter could not do this as indicated by the error.

Because this was a mistake in the field definition, I ended up dropping the field from the record definition, and performing the alter which dropped the column from the table. Then, I added the field back to the record definition and performed the alter which worked.

This works fine, if you have the luxury of dropping the data in the field ...

Oracle Partition By

Partition By is a construct in Oracle that groups data in a select clause rather than by using group by

Here's a PeopleSoft example. This SQL partitions mail addresses by EMPLID, EFFDT, EFF_STATUS and ADDR_TYPE and returns the maximum dated row for each partition.

select *
from
(
    select EMPLID, EFFDT, EFF_STATUS, ADDR_TYPE,
         max(EFFDT) OVER (partition by EFF_STATUS, ADDR_TYPE) as MAX_DATE
        from PS_ADDRESSES
        where ADDR_TYPE = 'MAIL'
) B
where EFFDT = B.MAX_DATE
and EFF_STATUS = B.EFF_STATUS;

Paritioning data is really useful for grouping data prior to manipulation (as opposed to group by which does grouping after manipulation). This is because group by is for grouping results while partition by is actually partition by the data and returning the appropriate partitioned data as the results.

Date End Field

In PeopleSoft the DATE_END field is defined as a date field. If the date 12/31/2009 is selected for this field it will show up like 2009-12-31 00:00:00. On 12/31/09 if you are comparing this field to SYSDATE via SQL your results might not return as expected since SYSDATE would return 2009-12-31 10:42:18 (i.e. the timestamp as well). To get around this you can use the trunc() function to to output trunc(SYSDATE) in a format that compares nicely with your data.

SELECT EOEP_RULE_ID, DATE_BEGIN, DATE_END 
FROM PS_EOEP_FORMULA 
WHERE DATE_BEGIN <= trunc(SYSDATE) AND DATE_END >= trunc(SYSDATE)
EOEP_RULE_ID DATE_BEGIN DATE_END
10001_05_EXTENDED 2009-01-01 00:00:00 2009-12-31 00:00:00
10001_05_EXTENDED 2009-01-01 00:00:00 2009-12-31 00:00:00
10001_05_EXTENDED 2009-01-01 00:00:00 2009-12-31 00:00:00
10001_05_EXTENDED 2009-01-01 00:00:00 2009-12-31 00:00:00

Kill an Oracle Database Session

If you are logged into the system with enough rights (e.g. SYSADM) you can kill database sessions that are not ACTIVE. To do so, first identify the appropriate session ID (sid) and serial number using this query:

SELECT s.sid,
       s.serial#,
       s.osuser,
       s.program,
       s.machine,       
       s.status,
       s.state
FROM   v$session s
WHERE  OSUSER = ':UserID'
AND    STATUS <> 'ACTIVE';

Replace User ID with your OS username.

To kill the session use the following command:

alter session kill session 'sid,serial#' immediate;

Note that the IMMEDIATE keyword is only if you want the session to be killed instantly. That is you know it should be killed. You will need to enter the sid and the serial number to kill the session correctly.

Oracle Database Links

Use the following code to create a database link:

The values you need to plugin are:

NOTE: You need to create a new connection to test the database link - it won't work in the same connection used to create the database link. If you get a TNS error, ensure that TNSNAMES.ORA is up to date on the database server you are creating the link on.

PL/SQL for Long Data Type Fields

On Oracle databases, you can't use standard SQL insert statements on fields that have a type of LONG. In order to work with data in tables with these types of fields, you'll need to use PL/SQL. Here's a very simple example of inserting into the message catalog from another database (database connection):

BEGIN
FOR ROW IN
       (SELECT  MESSAGE_SET_NBR,
                MESSAGE_NBR,
                MESSAGE_TEXT,
                MSG_SEVERITY,
                LAST_UPDATE_DTTM,
                DESCRLONG
        FROM    SYSADM.PSMSGCATDEFN@REMOTE_DB
        WHERE   MESSAGE_SET_NBR = '12345'
    )
    LOOP
    INSERT INTO SYSADM.PSMSGCATDEFN
    VALUES (    
                ROW.MESSAGE_SET_NBR,
                ROW.MESSAGE_NBR,
                ROW.MESSAGE_TEXT,
                ROW.MSG_SEVERITY,
                ROW.LAST_UPDATE_DTTM,
                ROW.DESCRLONG
           );
END LOOP;
END;
/
NOTE: this isn't a problem with newer PeopleTools/Application versions where such fields have now changed to a data type of CLOB.

No Tablespaces

After completing an PeopleSoft installation, I found that I had missed a step as there were no table spaces available to save a new record definition into.

You can check this in application designer using the following steps:

Another way to check this is through:

PeopleTools > Utilities > Administration > Tablespace Utilities:

no-tablespaces-defined.png

No tablespaces! Behind the scenes, the table that needs to be populated is PSTBLSPCCAT.

The step that I had missed as part of the installation was to run the SQR, setspace.sqr.

Start sqrw.exe (found under PS_HOME\bin\sqr\<DB>\binw\). Specify the appropriate parameters and run.

running-setspace-sqr.png

NOTE: Replace PS_HOME above with the appropriate location for your PeopleSoft installation.

Unique Constraint Errors

Unique constraint errors are a part of life as a PeopleSoft developer. The most common way to troubleshoot them is to set up a SQL trace to determine the point of failure. Then its a matter of determining the bind variables that were passed to the insert statement and to figure out which key values are being duplicated ...

However, there's an alternative approach that works really well when you are dealing with a complex process. Once you've identified the table the error is occurring on, try dropping the unique index on that table (usually called PS_<TABLENAME>) and then letting the process insert the duplicate rows.

You can then use SQL to group by the key fields to find the duplicated rows causing the unique constraint error. This can often be a lot faster than trying to track down bind variables being passed to the insert statement at the relevant point in a large log file!

Database Signon Error

While attempting to start the application server on an Oracle database, the following error message kept appearing in the application sever logs:

File: /vob/peopletools/src/pssys/stmget.cppSQL error. Stmt #: 2736  Error Position: 23  
Return: 942 - ORA-00942: table or view does not exist
Failed SQL stmt:SELECT OWNERID FROM PS.PSDBOWNER WHERE DBNAME=:1
PSAPPSRV.9554 (0) [05/26/11 09:48:10](1) GenMessageBox(200, 0, M): 
/vob/peopletools/src/pssys/stmget.cpp: A SQL error occurred. 
Please consult your system log for details.
PSAPPSRV.9554 (0) [05/26/11 09:48:10](1) GenMessageBox(0, 0, M): 
Database Signon: Could not sign on to database <DBNAME> with user <USERID>
PSAPPSRV.9554 (0) [05/26/11 09:48:10](0) Server failed to start

One of the things that can cause this is if the public synonym for PS.PSDBOWNER has not been created or granted public access. To fix, run the following in the database:

CREATE PUBLIC SYNONYM PSDBOWNER FOR PS.PSDBOWNER;
 
GRANT SELECT ON PS.PSDBOWNER TO PUBLIC;
NOTE: also confirm that the database owner and name correctly matches the databases you are in, particularly if working with a refreshed environment.

Only One Primary Email Permitted

After upgrading to PeopleTools 8.51, the sign on page started displaying the following message:

only-one-primary-email-address-is-permitted.png

Only one Primary Email Address is permitted. (48,9)

This message may also appear when you are trying to update a user profile. It is caused by users having more than one primary email address flagged in the PSUSEREMAIL table:

select OPRID, count(*)
from PSUSEREMAIL
where PRIMARY_EMAIL = 'Y'
group by OPRID
having count(PRIMARY_EMAIL) > 1;

The fix is to change the PRIMARY_EMAIL flag from Y for all but one email address for each User (OPRID).

One way is to set the primary email flag to N where an email address has not been specified (this may not always be an option).

update PSUSEREMAIL
set PRIMARY_EMAIL = 'N'
where PRIMARY_EMAIL = 'Y'
and EMAILID = ' '
and OPRID in (
    select  OPRID
    from    PSUSEREMAIL
    where   PRIMARY_EMAIL = 'Y'
    group by OPRID
    having count(PRIMARY_EMAIL) > 1
);

Other fixes might including picking a specific email type (e.g. BUS) as the primary email and changing all other types so that the primary email flag is N.

Writing Data Conversion Scripts

Here are some general guidelines around how to write a data conversion script:

Select Random Users

When testing, a common requirement is to select from a list of random Users (OPRID) or IDs (EMPLID). The following SQL provides a way to do this using:

PSOPRDEFN for a list of users (OPRIDs) PS_PERSON for a list of people (EMPLIDs)

NOTE: that this SQL is database platform specific.

Oracle

Select 100 random users (OPRIDs):

select  OPRID
from    
(
    select      OPRID
    from        PSOPRDEFN
    order by    dbms_random.value
) 
where rownum <= 100;

Select 100 random people (EMPLIDs):

select  EMPLID
from    
(
    select      EMPLID
    from        PS_PERSON
    order by    dbms_random.value
) 
where rownum <= 100;

Change the rownum <= 100 line to adjust the number of rows returned.

SQL Server

Select 100 random users (OPRIDs):

select top 100 OPRID
from PSOPRDEFN
order by NEWID();

Select 100 random people (EMPLIDs):

select top 100 EMPLID
from PS_PERSON
order by NEWID();

Oracle Client

The Oracle client is required on your PC if you want to connect to the PeopleSoft database in two tier with applications such as application designer, data mover, and configuration manager.

This is where you can download the latest version of the Oracle client. Note that the Oracle client is backwards compatible so you can use a version that is newer than the version of the Oracle database you are connecting to. So for example, you can use the 11g client and connect to a 10g database. There a number of versions of the Oracle client, the one I generally tend to install is the runtime client, although all you really need is the Instant Client provided you install it correctly.

Remember that you PeopleSoft applications are 32 bit applications, so while you can install a 64 bit client on your PC, PeopleSoft will not work. If you do this, you'll get an error like this:

app-designer-64-bit-client-error.jpg

Missing or invalid version of SQL ibrary PSORA (200, 0)

This is what happened when I tried to run application designer with an Oracle 64 bit client installed by mistake.

Data Archive Candidates

You can use the following SQL in Oracle to identify the top 100 tables that may be good candidates for archiving. Replace <OWNER> with the owner of the PeopleSoft schema (e.g. SYSADM). The field NUM_ROWS tells you the number of rows in the table (from when it was last analysed). The query also provides the approximate size of the table in MB.

Note this query can take quite a while to run - give it at least 1-2 minutes. For a quicker version, remove the sub-query that returns the approximate size of each table.
select
    OWNER,
    TABLE_NAME,
    NUM_ROWS,
    TABLESPACE_NAME,
    STATUS,
    LOGGING,
    LAST_ANALYZED,
    (
        select  ltrim(to_char((sum(BYTES)/(1024*1024)), '9G999D99'), ' ') || ' MB'
        from    USER_EXTENTS
        where   SEGMENT_TYPE = 'TABLE'
        and     SEGMENT_NAME = ARCHIVE_CANDIDATES.TABLE_NAME
    ) APPROX_SIZE
from (
    select
        OWNER,
        TABLE_NAME,
        NUM_ROWS,
        TABLESPACE_NAME,
        STATUS,
        LOGGING,    
        LAST_ANALYZED
    from 
        ALL_TABLES A
    where 
        OWNER = '<OWNER>' 
        and TABLE_NAME like 'PS%'
        and NUM_ROWS is not null      
    order by NUM_ROWS desc
) ARCHIVE_CANDIDATES 
where
    ROWNUM <= 100
;

Returning SQL Result as Boolean

If you return either a true or false string as the result of executing SQL through PeopleCode, it can be stored directly into a PeopleCode boolean variable.

To explain what I mean, here's a simple example. Say you to want check if a user has a role (for example, the PeopleSoft Administrator role). You could create the following SQL definition called HAS_ROLE to give you a true or false result:

SELECT CASE WHEN EXISTS ( 
 SELECT 1 
  FROM PSROLEUSER 
 WHERE ROLEUSER = :1
   AND ROLENAME = :2) THEN 'true' ELSE 'false' END AS HAS_ROLE
  FROM DUAL
NOTE: this is an Oracle example using case when exists syntax but you can do something similar in other database platforms.

You can execute this in a SQLExec statement and store the result in a boolean variable:

Local boolean &bHasRole;

SQLExec(SQL.HAS_ROLE, "<OPRID>", "<ROLE>", &bHasRole);

Another example is to show or hide a field. You can directly pass the result of the HAS_ROLE SQL to the visible property of a field like this:

SQLExec(SQL.HAS_ROLE, "<OPRID>", "<ROLE>", RECORD.FIELD.Visible);

If the result is true, the field is visible, if it is false, the field is invisible. All in one nifty piece of code. You could do something similar with other boolean (true/false) properties such as Enabled or DisplayOnly.

Oracle Data Dictionary

There's a view of all the data dictionary views in oracle called DICTIONARY. So you can query it like so:

select 
    TABLE_NAME,
    COMMENTS 
from 
    DICTIONARY 
order by 
    TABLE_NAME;

This includes comments about most of the views which is really handly. Almost all the views use plurals (ALL_TABLES not ALL_TABLE)

To find all the tables associated with a schema, query ALL_TABLES and specify the appropriate OWNER e.g. SYSADM.

Oracle Dynamic Performance Views

Oracle dynamic performance views (v$) are built on the virtual tables ($x). The views (v$) are much more useful than the virtual tables ($x). To get a listing of all them use the following query:

select
    NAME
from 
    V$FIXED_TABLE
where 
    TYPE = 'VIEW';

Some of the most useful views include:

Global dynamic performance views have the prefix gv$. They are viewing across all instances when using a real application cluster (RAC) configuration.

SQL*Plus

If you've ever worked on Oracle databases then you've probably heard of, and stay well away from SQL*Plus - unless you happen to be a DBA!

I totally understand your position. As a general querying tool, SQL*Plus is less than ideal For such work you should be using something like SQLTools++ or TOAD.

However for running SQL updates, inserts, and PL-SQL scripts, SQL*\Plus should be your tool of choice.

Here's a few reasons why:

There is one exception to this rule - PeopleSoft Data Mover. Data Mover scripts are a bit inflexible and lack logging capabilities. However the one thing that datamover does extremely well is exporting data to flat files (DAT files) and importing it back in. If you ever need to write a script where you first want to take a backup of the data in one or more tables or you want to import data from a flat file from somewhere else - then Data mover should be your tool of choice. Data mover is also the best choice if you need to support more than one database platform.

When you run any script, you should generate a log file to indicate what is happening. This is where things like spooling output to a file come into play.

To generate a basic log file, all you need to do is:

Here's a simple example of a sqlplus script that spools output to a file and indicates what is happening.

SPOOL Example.log
PROMPT Script Starting
PROMPT Creating Example.log in the current directory
 
PROMPT Truncating PS_EXAMPLE_TBL
TRUNCATE TABLE PS_EXAMPLE_TBL;
 
PROMPT Inserting data into PS_EXAMPLE_TBL
INSERT INTO PS_EXAMPLE_TBL VALUES("TEST", 1);
 
COMMIT;
 
PROMPT Script Complete
SPOOL OFF

Looping in SQL

The following SQL is an example of looping through user data and presenting it as a comma separated list of roles for each user.

This SQL will only work on Oracle databases.
select
   ROLEUSER as USER_ID,
   substr(SYS_CONNECT_BY_PATH(ROLENAME, ','),2) as ROLES
from
   (
   select
     ROLEUSER,
     ROLENAME,
     count(*) OVER ( partition by ROLEUSER ) as ROLECOUNT,
     ROW_NUMBER () OVER ( partition by ROLEUSER order by ROLENAME) ROLESEQUENCE
   from
     PSROLEUSER
   where
     ROLEUSER is not null)
where
   ROLECOUNT=ROLESEQUENCE
start with
   ROLESEQUENCE = 1
connect by prior
   ROLESEQUENCE+1=ROLESEQUENCE
and prior
   ROLEUSER=ROLEUSER
;

How does it work? Well here's a break down.

The query inside the from clause essentially groups the data for looping.

select
   ROLEUSER,
   ROLENAME,
   count(*) OVER ( partition by ROLEUSER ) as ROLECOUNT,
   ROW_NUMBER () OVER ( partition by ROLEUSER order by ROLENAME) ROLESEQUENCE
from
   PSROLEUSER
where
   ROLEUSER is not null

It returns the user (ROLEUSER), the name of the role (ROLENAME), a count of the number of roles for that user (ROLECOUNT) and a sequence number for each role row (ROLESEQUENCE).

So for example if the user PS has the roles:

The result would look like this:

ROLEUSER ROLENAME ROLECOUNT ROLESEQUENCE
PS PeopleSoft Administrator 5 1
PS PeopleTools 5 2
PS Portal Administrator 5 3
PS ProcessSchedulerAdmin 5 4
PS Security Administrator 5 5

This essentially sets up the loop - PS has 5 roles and each of them has been placed in sequence using the Oracle system ROW_NUMBER partitioned over the role user and ordered by the role name.

The outside part of the query then uses the SYS_CONNECT_BY_PAT starting with the ROLECOUNT equal to the ROLESEQUENCE which starts at 1 and is incremented by 1 each time. To ensure that we are looping for the same user each time, the SYS_CONNECT_BY_PATH uses the line prior ROLEUSER=ROLEUSER. So when we move to the next user, the loop starts again with a ROLESEQUENCE of 1.

You don't have to fully understand the syntax, but what this SQL demonstrates is how to create a simple loop in Oracle using plain old SQL.

No Matching Buffer Errors

A no matching buffer error occurs when there is a mismatch in the data within the component buffer scroll hierarchy (scroll 0, scroll 1, scroll 2, scroll 3).

It occurs due to one or more orphaned data rows in the lower scroll levels that do not link back to the higher scroll levels through keys. The component buffer is where PeopleSoft enforces its own referential integrity. This means that while there may not be any relationships between tables defined at the database level, when records are loaded into a component, they need to maintain a key based relationship according to the scroll level structure in that buffer.

The classic message is something like this:

No matching buffer found for level. (15,26)

Typically no matching buffer errors are caused by processes that delete or update rows from parent records without updating or removing rows from the appropriate child record within the component scroll level structure. If the component buffer can load a row from a lower (child/grand-child) scroll that doesn't match on keys to the scroll levels above it, the error will occur.

There are two fundamental ways to fix a no matching buffer error:

NOTE: no matching buffer errors are particularly common on effective dated and sequenced records.

A SQL trace is a good place to start with investigating buffer errors. The point at which a rollback happens in the trace is typically where the no matching buffer error has occurred. This is usually at the point when the orphaned row in the child record was loaded.

Please see this page about querying the structure of a component which provides a great way to disect a component for troubleshooting no matching bfufer errors.

After you've identified the culprit child record, the next step is to examine the component scroll structure and find candidate records in the scroll levels above to find the cause of the issue. So if the child record is at scroll level 2, look at records at scroll level 1, and 0. Remember this error is key-based so you only need to be concerned with records that have one or more of keys in the child record.

The error is not necessarily with data from the record you selected the — the error may be on any other child record being loaded in the component that shares the same keys. So don't make any assumptions about the culprit record. You may need to systematically check tables loaded by the component.

Sometimes, opening the component with include history (all effective dates) will prevent the no matching buffer error. This is a sign that the error is a result of an effective date or effective sequence mismatch that is causing the problem.

Structure of a Component

The following queries give you the structure of a component.

This query gives you the pages, occurs level, record and field name for a given component:

select distinct
    C.PNLGRPNAME,
    P.PNLNAME,
    P.OCCURSLEVEL,
    R.RECTYPE,
    R.RECNAME,
    P.FIELDNAME
from
    PSPNLGROUP C inner join PSPNLFIELD P
    on C.PNLNAME = P.PNLNAME
    inner join PSRECDEFN R
    on  R.RECNAME = P.RECNAME
where
    C.PNLGRPNAME = 'YOUR_COMPONENT_NAME'
order by P.OCCURSLEVEL, R.RECNAME asc;

This query gives a summary of the records, what occurs level they are on, and what type of record they are (record type):

select distinct
    P.OCCURSLEVEL,
    R.RECTYPE,
    R.RECNAME, 
    R.RECDESCR
from
    PSPNLGROUP C inner join PSPNLFIELD P
    on C.PNLNAME = P.PNLNAME
    inner join PSRECDEFN R
    on  R.RECNAME = P.RECNAME    
where
    C.PNLGRPNAME = 'YOUR_COMPONENT_NAME'
order by P.OCCURSLEVEL, R.RECNAME asc;
NOTE: A RECTYPE value of 0 indicates a database table.

Getting the Current Environment

For security and consistency you should wrap code that is environment specific around logic that determines the environment you are in.

You should apply this to anything you wouldn't want to run in a development environment the same way as a production environment. Some common examples include interfaces to 3rd party systems, sending out notifications (email) and dangerous database operations.

There are a number of ways to work out which environment you are in, and these vary in success depending on your environment and configuration. This article covers 4 options:

  1. Use the Environment Settings in PSOPTIONS.
  2. Use the distribution node in PS_SERVERDEFN
  3. Look at the database name field in the process request table - PSPRCSRQST.DBNAME
  4. Use the %DbName PeopleCode system variable
  5. Query the database meta-tables.

Option 1: Use the Environment Settings in PSOPTIONS.

This is configured under:

PeopleTools > Utilities > Administration > PeopleTools Options

You can set:

As long as this information is maintained and kept to date between refreshes then this is certainly the best way to store information about your database. However, the issue with this approach is it relies on consistency and setup to work. It can fail for example if there is a refresh and PSOPTIONS doesn't get updated, so you think you are in production when in fact you are in test or development.

Option 2: Use the distribution node in PS_SERVERDEFN

Another option is to use the process scheduler server distribution settings. However this assumes that your report node definition name matches the database name, which may not always be the case. So this is not a very reliable way of getting the database name and one of the other options is a far better choice.

Note that report nodes are stored in the system table PS_CDM_DIST_NODE.

Option 3: Look at the database name field in the process request table - PSPRCSRQST.DBNAME

This is the database name passed through the process run parameters. If you have a look under:

PeopleTools > Process Scheduler > Process Types

For various process types that you can run through process scheduler (e.g. Application Engine, COBOL and SQR), this is usually the process scheduler variable %%DBNAME%%.

Option 4: Use the %DbName PeopleCode system variable

Very simple - here's a message box example:

MessageBox(0, "", 0, 0, "Database Name = " | %DbName);

This has the advantage of just "working" in PeopleCode without query the database.

Option 5: Query the database meta-tables.

Oracle: select name from v$database; Oracle: select ora_database_name from dual for the fully qualified database name Microsoft SQL Server: select db_name()

Of course this one is database platform specific.

Which option is best?

As usual, it depends here are the caveats:

Search Message Catalog Explain Text

The following PL/SQL is my attempt to search the message catalog for a particular piece of text stated as a regular expression. It is designed for use in Oracle 10g+ databases as it makes use of Oracle's regular expressions functionality.

There are some limitations with it - in particular, the conversion from LONG to VARCHAR2 is limited to a size of 32,000 characters, but you can have more than that many characters in a message catalog entry, and if you hit such an entry, the PL/SQL will fail.

To use the PL/SQL you will need to do two things:

NOTE: you can modify the cursor SEARCH_MESSAGE_CATALOG SQL to suite your needs — whether you want to search for a specific message set, a range of message sets or the entire message catalog.
/*
    Search Message Catalog for a Text String 
    http://www.peoplesoftwiki.com/books/database/search-message-catalog-explain-text
    Please run this in either sql*plus or SQLTools++.
*/

SET SERVEROUTPUT ON
SET PAGESIZE 500;
SET LINESIZE 500;
 
DECLARE
 
  vMSG_SET        PSMSGCATDEFN.MESSAGE_SET_NBR%Type;
  vMSG_NBR        PSMSGCATDEFN.MESSAGE_NBR%Type;
  vMSG_TEXT       PSMSGCATDEFN.MESSAGE_TEXT%Type;
  vMSG_SEVERITY   PSMSGCATDEFN.MSG_SEVERITY%Type;
  vMSG_EXPLAIN    varchar2(32000);
  vMSG_CONTEXT    varchar2(32000);
  nPOSITION       number(10);
  vSEARCH_REGEX   varchar2(100);
 
  CURSOR SEARCH_MESSAGE_CATALOG IS
    SELECT
        MESSAGE_SET_NBR,
        MESSAGE_NBR,
        MESSAGE_TEXT,
        MSG_SEVERITY,
        DESCRLONG
    FROM PSMSGCATDEFN
    WHERE MESSAGE_SET_NBR = 99999 -- Specify message set
    ORDER BY MESSAGE_SET_NBR, MESSAGE_NBR;
 
BEGIN
 
  vSEARCH_REGEX := '\sTest\s'; -- Specify your regular expression
 
  OPEN SEARCH_MESSAGE_CATALOG;
 
  LOOP
 
    FETCH SEARCH_MESSAGE_CATALOG
    INTO vMSG_SET, vMSG_NBR, vMSG_TEXT, vMSG_SEVERITY, vMSG_EXPLAIN;
 
    EXIT WHEN SEARCH_MESSAGE_CATALOG%NOTFOUND;
 
    nPOSITION := REGEXP_INSTR(vMSG_EXPLAIN, vSEARCH_REGEX);
 
    IF nPOSITION > 0 THEN
 
    /* Show the "text" around where the search string was found */
    vMSG_CONTEXT := SUBSTR(vMSG_EXPLAIN, (nPOSITION - 25), 50);
    vMSG_CONTEXT := REPLACE(vMSG_CONTEXT, chr(10), ' ');
    vMSG_CONTEXT := REPLACE(vMSG_CONTEXT, chr(13), ' ');
 
    DBMS_OUTPUT.ENABLE(1000000);
    DBMS_OUTPUT.PUT_LINE(chr(13));
    DBMS_OUTPUT.PUT_LINE('Message Set      : ' || vMSG_SET);
    DBMS_OUTPUT.PUT_LINE('Message Nbr      : ' || vMSG_NBR);
    DBMS_OUTPUT.PUT_LINE('Message Text     : ' || vMSG_TEXT);
    DBMS_OUTPUT.PUT_LINE('Message Severity : ' || vMSG_SEVERITY);
    DBMS_OUTPUT.PUT_LINE('Match Position   : ' || nPOSITION);
    DBMS_OUTPUT.PUT_LINE('Message Context  : ... ' || vMSG_CONTEXT || ' ...');
    DBMS_OUTPUT.PUT_LINE(chr(13));
 
    END IF;
 
  END LOOP;
 
  DBMS_OUTPUT.PUT_LINE(chr(13));
  DBMS_OUTPUT.PUT_LINE('Search Complete.');
 
  CLOSE SEARCH_MESSAGE_CATALOG;
 
END;
/

Structure of a Rowset-Based Message

The SQL below will return the structure of your message if it is rowset based (uses PeopleSoft record definitions to define the structure).

select RF.FIELDNAME
from PSRECFIELD RF
where
    RF.RECNAME = 'MESSAGE_RECNAME'
    and not exists (
        select  1
        from    PSMSGFLDOVR MFO
        where   MFO.MSGNAME = 'MSGNAME'
        and     MFO.APMSGVER = 'VERSION_N'
        and     MFO.RECNAME = RF.RECNAME
        and     MFO.FIELDNAME = RF.FIELDNAME
        and     MFO.DONOTPUBLISH = 1
    )
order by RF.FIELDNUM;

Formatting SQL in Notepad++

Suppose you have the following text in your editor:

select FIELD1, FIELD2, FIELD3 from TABLE1;

And you want this formatted to look like this:

select 
    FIELD1,
    FIELD2,
    FIELD3
from TABLE1;

How would you do it?

Well here's an example using search-replace and regular expressions in Notepad++ though you can easily do this in any other good text editor.

There are three steps involved:

Navigate to:

Search > Replace (or CTRL+H)

Set the Search Mode to Regular expression (see screenshots below)

Replace the select and white space with select, new line and tab:

replace_step1.png

This gives you:

select 
    FIELD1, FIELD2, FIELD3 from TABLE1;

Replace the commas and white space with a comma, new line and tab:

replace_step2.png

This gives you:

select 
    FIELD1,
    FIELD2,
    FIELD3 from TABLE1;

Replace the white space and from with a new line and from:

replace_step3.png

This gives you the desired result:

select 
    FIELD1,
    FIELD2,
    FIELD3
from TABLE1;

Also, don't forget to check out the article on auto generating a select statement.

PeopleTools Tables

PeopleTools tables are the tables in your PeopleSoft environment that store PeopleTools meta-data - data about the underlying development platform for your environment. So this includes things like fields, records, pages, components, menus, and portal structures.

Access to these tables is available in every PeopleTools installation through application designer. But how do you find out which table is used for what?

This comes down to a bit of reverse engineering ..

Naming convention for PeopleTools tables

The majority of PeopleTools tables start with PS and do not have an underscore between PS and the table name (e.g. PSRECNAME). You also don't need to prefix the table name with PS in this case as the record definition and table names match.

To see what I mean, open up PSRECDEFN in application designer and have a look at the Non-Standard SQL Table Name field on the Record Type tab:

non-standard-sql-table-name.png

Ordinary application tables such as the INSTALLATION table do not have a non-standard SQL name set which is why you need to prefix PS_ to them when you query them out of the database. There are some strange exceptions to this, notably some of the process scheduler tables such as the process definition table, PRCSDEFN which has the a non-standard SQL name of PS_PRCSDEFN.

Incidentally, if you want to query which table record definitions have non-standard SQL table names, use the SQLTABLENAME field in PSRECDEFN like so:

select RECNAME, RECDESCR, SQLTABLENAME
from PSRECDEFN
where SQLTABLENAME <> ' '
and RECTYPE = '0'
order by RECNAME;
NOTE: not every PeopleTools record has a description just to keep you guessing ;)

Identifying PeopleTools tables through Data mover Scripts

On your PeopleTools file server, if you have access to %PS_HOME%\scripts you should be able to find the data mover (.dms) scripts delivered with the PeopleSoft installation.

The DMS script mvprdexp.dms contains a full list of PeopleTools tables and pt_release_export.dms lists the core PeopleTools tables. Both scripts group PeopleTools tables by type. For example, PeopleTools tables for records & indexes, fields and translates, field formats, pages, menus, components etc. Very handy information. If you can get a copy of this file, keep it close at as a reference.

Identifying PeopleTools tables through PeopleSoft Query

If you run the PeopleSoft Query application (not through the PIA) either using Go > Query in application designer or starting the executable, psqed.exe, you can get it to show you access groups.

To do this, choose:

View > Preferences > Component View tab and select the Show Access Groups tab

peoplesoft-query-show-access-groups.png

Your database window where you used to select record definitions should now be grouped. Scroll down and find the PeopleTools access group PT_ACCESS_GROUP and expand the folder.

This should now show you the PeopleTools tables by grouping (e.g. XMLPUBLISHER for XML Publisher, RECORD_DEFINITION for Record Definition etc).

NOTE: it does not appear that you can do this through Query Manager in the PIA.

Resources on the Internet

PTRef by GoFaster which also has schema information is the best resource out there.

You can also get the utlity from Github to extract this information yourself. Thanks David Kurtz !

Reusing Views

PeopleSoft has thousands of views! If you want to know just how many there are, try this query to give you the distinct views (RECTYPE = 1) from the PeopleTools record definition table, PSRECDEFN.

select count(distinct RECNAME)
from PSRECDEFN
where RECTYPE = '1';

If you want to know who created these views, use the following query. Note that a LASTUPDOPRID of PPLSOFT means that the view was delivered by PeopleSoft.

select LASTUPDOPRID, count(distinct RECNAME)
from PSRECDEFN
where RECTYPE = '1'
group by LASTUPDOPRID
order by count(distinct RECNAME) desc;

With that many views out there, there's a (good) chance you'll stumble on one that serves your needs. This saves you creating a new customisation, and reduces the development and testing effort as well.

Generally, in order to reuse a view, you need to find views that have the fields you are after. The following SQL does this by matching on up to three fields.This doesn't mean that the views returned are ideal - you'll still need to check the view SQL yourself, it just helps to locate them faster.

Replace the % with the fields you want to match on. If you have less than three fields, leave the other matches as %.

select
    RD.RECNAME,
    RD.RECDESCR,
    RD.FIELDCOUNT,
    RD.LASTUPDDTTM,
    RD.LASTUPDOPRID,
    STD.SQLTEXT
from
    PSRECDEFN RD inner join PSSQLTEXTDEFN STD
    on  RD.RECNAME = STD.SQLID
where
    RD.RECTYPE = '1'
    and STD.SQLTYPE = '2'
    and exists (
        select  1
        from    PSRECFIELD RF1
        where   RF1.RECNAME = RD.RECNAME
        and     RF1.FIELDNAME like '%'
    )
    and exists (
        select  1
        from    PSRECFIELD RF2
        where   RF2.RECNAME = RD.RECNAME
        and     RF2.FIELDNAME like '%'
    )
    and exists (
        select  1
        from    PSRECFIELD RF3
        where   RF3.RECNAME = RD.RECNAME
        and     RF3.FIELDNAME like '%'
    )
;
NOTE: you can expand the SQL to match on more fields if required just keeping adding exist conditions in the format shown.