- SQR SQL Trace
- Stringing Text in SQR
- SQR CreateProcess Failed
- SQR Trace
- Page Breaking In SQR
- Dates in SQR
- Output to the Report Repository
- Error Code 2904
- Error Code 3719
- Error Code 4713
- Error Code 5400
- Error Code 5528
SQR SQL Trace
To trace the status of cursors being executed, append the
-S flag to your parameter list. You can do this through the command line (if you're brave) or through the process definition in the override options:
While you can normally extract the appropriate SQL from the SQR, there are cases where this proves quite useful. In particular it is really helpful for investigating performance issues with SQRs as it tells you the SQL text, number of compiles, executes and rows related to the cursor.
Unfortunately this doesn't resolve dynamic SQL or tell you the value of bind parameters that were passed to the SQL so it certainly has limitations but as far as I know its the closest you can get to a SQL trace in SQR.
Stringing Text in SQR
You can use the commands
into in SQR to string together text by a particular character and put it into a file reference variable. Probably the most useful application for this is in creating a delimited file such as a CSV file.
Here's an example of the syntax
string $seq $emplid $lastname $firstname 'Y' by ',' into $testfile
The above code strings together the variables
$firstname as well as the constant value
Y by a comma and stores the result into the
$testfile file reference variable.
SQR CreateProcess Failed
When trying to run an SQR on an NT process scheduler, I received the following error:
Error! SQR Failed To Process *** *** System Error Code 2 ****** PS_StartProc: CreateProcess failed: cmd = C:\PT8.51\bin\sqr\ORA\binw\sqrw cwd = C:\oracle\instantclient11r2 *** ******************* Error ***********************
Somewhat cryptic! What's happened was that the
%PS_HOME% directory had been changed, so the path it was using to run sqrw had changed. Going back to the process scheduler configuration and changing the
SQRBIN parameter to the correct location, and restarting process scheduler resolved the issue.
One of the first things you used to learn as a PeopleSoft developer is to add debug flags in an SQR program and then to run the program with debug flags turned on to get additional information in your
.out in the process monitor) file.
The general syntax is to append:
-debug[trace flag letters]
Some of the common trace flags include
-debugd generally for data and
-debugf for flow (where you are in the program). You can determine the flags you want by scanning through the SQR (related SQCs) and picking out the appropriate trace flag letters by looking for
#debug[letter] statements in the code.
Or, you can use a catch-all like this (note it also catches just a
-debug statement without a letter at the end)
You do this in the process definition by appending to the parameter list in the override options like so (but without the whole alphabet!)
Page Breaking In SQR
The page breaking functionality in SQR is really one of the most useful features in the language. The power of the page breaking functionality (
on-break) is that you can break as required while processing rows being returned in a
So this means you can simply order the data selected out of the database and page-break as required rather than having to check for complex page break conditions manually using variables to store the last value(s) processed.
The key to the
on-break code is the
level parameter which is essentially acts a priority (level 1 is the first priority, then level 2, then level 3 and so on).
For example, here's a snippet of code used to break based on three selected columns:
begin-select CRSE_ID () on-break level=1 print=never CAMPUS () on-break level=2 print=never ACAD_GROUP () on-break level=3 print=never before=Print-New-Page() . . . end-select
In this example, it will break in order of level priority, that is first on on a change to
CRSE_ID, then on a change to
CAMPUS and third on a change to
Note the procedure call to
Print-New-Page() is made using the before keyword which means that the break happens before the next row change in ascending order of break levels. That is, the
Print-New-Page procedure will happen before the change in data for a new
Dates in SQR
There are two main ways to store and use dates in SQR:
- Store them as a character string in a string variable OR
- Store them as date variables
Both types of variables (strings and dates) use the $ dollar sign prefix but dates are declared differently to strings.
So why use a date variable instead of a string? Well the main reason is if you want to perform any date based calculations. If just need to store a date in a variable then using a string is fine. However if you wanted to perform a calculation like adding
7 days to a date then a date variable is the way to go. Date variables are also required when using the delivered SQR date functions.
Date variables are declared in the setup section like this:
begin-setup declare-variable date $dtVariable end-declare end-setup
Now that you have a date variable, there a number of delivered date functions you can use in SQR including:
strtodate()to convert a string to date
datetostr()to convert a date to a string
dateadd()to add to a date
datediff()to subtract dates
datenow()to get teh current date
Return the number of days between the column variable,
&EFFDT and the variable
let #nbrOfDays = datediff(&EFFDT, $BaseDate, 'DAYS')
Add four weeks to the $dtFinish variable:
let $dtFinish = dateadd($dtStart, 'WEEK', 4)
The options available for the period include
Output to the Report Repository
If you are developing a new SQR that creates files that need to be posted from the process scheduler to the report repository, (report manager), here are the steps for doing this. Note this uses delivered SQCs.
getlogou.sqc can be used to post files to the report repository. There are two functions in this SQC:
get-prcsoutputdirwhich is used to get the original output destination from the
get-logoutputdir-valuewhich is used to get the process output directory from the
Both functions require a global variable,
#prcs_process_instance to be declared somewhere in your SQR. This is normally taken care of if you call
The orignal output destination is what has been entered into the process definition for the output location. This is normally
%%Log/Output Directory%%. The process output directory is the expanded application server path to the log output directory.
The log output directory contains folders that are posted to the report repository. The naming convention for these folders is:
CCLTRGEN is an SQR process. If the process instance is
1234567 then folder for this process in the log output directory would be:
This folder typically contains log files (
.out), any of the common files output by the process (e.g.
This is a typical example of how you would use the get-logoutputdir-value procedure to determine the output location for a CSV file generated by your SQR:
do Get-logoutputdir-value ! getlogou.sqc if (rtrim($prcsoutputdir,' ') <> '') move $prcsoutputdir to $CSVPath end-if
You can check the process output location using the following SQL if you know the process instance:
select PRCSOUTPUTDIR from PSPRCSPARMS where PRCSINSTANCE = '123456789'
Error Code 2904
(SQR 2904) File number already opened.
This indicates that the file number you used for reference (e.g. 1) is already in use.
Two common reasons are:
- The procedure you use to do the open is being called more than once
- There is a call to some other code (perhaps in an SQC?) that also opens a file with the same file reference.
The easiest solution is to increment your file reference number and see if it fixes it. So go from 1 to 2. Make sure you update your write statements too!
If you are still having issues, then it might be that your code to open the file is being called multiple times, you may need to move where it is.
Error Code 3719
(SQR 3719) Columns names and expressions must be unique or be given unique pseudonyms (&name)
This is a common error for people new to SQR. It happens when you use SQR statements in a
end-select block and do not indent them. If any SQR statement is flush left within these blocks (e.g. a let or move statement) then it will cause the error.
Note that the line shown in the error log file is actually the line directly after the error and not the line causing the error which can be rather confusing.
This error can also happen if you use the same table alias more than once. For instance, say you used the alias A for both
PSOPRDEFN A and
PSROLEUSER A. The error will appear telling you that the alias
A must be unique. Simply use different aliases for each table, e.g.
PSOPRDEFN A and
Error Code 4713
(SQR 4713) Cannot open the #INCLUDE file: 'setenv.sqc' (2): No such file or directory SQR for PeopleSoft: Program Aborting.
This generally occurs when you are trying to run SQRs manually (outside of the PeopleSoft process scheduler) and you haven't supplied the correct report arguments. Here's an example of the arguments you would use to run an SQR (using
sqrw.exe). Note the trailing slash on the input directory is very important. Substitute
PS_HOME with the appropriate path.
- Report Name:
- Username :
- Password :
- Database :
-ZIFPS_HOME\sqr\pssqr.ini -iPS_HOME\sqr\ -oPS_HOME\log\setspace.log -keep
Error Code 5400
(SQR 5400) Second page write attempted while writing current page. Check BEFORE-PAGE, AFTER-PAGE procedures.
This error can happen if you exceed the number of lines reserved in your
begin-footingstatements. For example, if you reserve 3 line with
begin-footing 3 then try to print on the 4th line, the error will occur. Try increasing the reserved lines for your header/footer, or reducing the number of lines being printed.
Error Code 5528
(SQR 5528) ORACLE OCIStmtExecute error 933 in cursor 11: ORA-00933: SQL command not properly ended
I caused this error by placing a single commit; statement within the same
end-sql block used by a delete statement. The solution was to move the commit; to its own
end-sql block like this:
#ifdef ORACLE begin-sql commit; end-sql #endif
There are actually a number of reasons why this error may occur and they are generally due to Oracle not being able to interpret the SQL passed by SQR. The error tells you which cursor is in error. If you can't find what part of your code is causing the issue, start with an SQR SQL trace then use the output file to find the cursor number that is causing you grief.