How to print a URL in PDF from SQR

An ITToolbox post led me to explore this topic. More importantly I had an eureka moment long time ago when I had accidentally came across a hyperlink in a PDF generated from a SQR program but had a malformed URL. At the time, I didn't bother to find out why it was malformed nor fix it. I have done so now and you will see how you can have an clickable URL in the PDF generated from a SQR that the user(s) can click and navigate to directly.

The key to making the PDF understand the hyperlink lies in the text "Website:" that you will have to add before the URL. That alone does not help you to generate a URL that you can click from PDF. You have to have 2 more print statements to print a blank after the URL and another print statement (yes you have to print something) after the URL for it to be recognized as a URL.

Here is the SQR code that I used for testing this out -

1:  #define widthofpage 126  
2:  #define widthofpage2 127  
3:    
4:    
5:  #define debugY  
6:    
7:    
8:  begin-setup  
9:   page-size 75 130  
10:    
11:    declare printer  
12:     type     = HPLASERJET  
13:     orientation  = portrait  
14:     top-margin  = 0.30  
15:     left-margin  = 0.1  
16:     font     = 5  
17:     point-size  = 7  
18:     line-size   = 11  
19:  end-setup  
20:    
21:  begin-report  
22:    
23:   do Init-Report  
24:  end-Report  
25:    
26:  !***********************************************************************  
27:  ! Procedure: Init-Report                       *  
28:  !       The main driving procedure                *  
29:  !***********************************************************************  
30:  begin-Procedure Init-Report  
31:    
32:    
33:   do Process-Main  
34:    
35:  end-procedure  
36:    
37:  !***********************************************************************  
38:  ! Procedure: Process-Main                       *  
39:  !      Control the process either by choosing paygroup or by   *  
40:  !      Employee ID's entered on the run control panel      *  
41:  !***********************************************************************  
42:  begin-procedure Process-Main  
43:    
44:     !Do Print-Paygroup-Select  
45:     do print-hyperlink  
46:    
47:  end-procedure  
48:    
49:  begin-procedure print-hyperlink  
50:   graphic () font 9 6       
51:   print 'Website:http://www.google.com' (3,30)  
52:   print ' ' ()   
53:   graphic () font 12 8  
54:   Print 'How to print a URL in PDF from SQR ' (-2,0) center  
55:    
56:  end-procedure  

The end result is that you can now see the URL with the hand icon (not captured in the screenshot but you can see the tooltip text as a result of the URL being recognized)

Clicking the link will prompt this security message from Adobe acrobat software. Click on "Allow" -

And voila, a new browser window opens the website link from the PDF document generated by the SQR.

The obvious drawback are that you cannot avoid the "Website:" text being printed in the PDF and you cannot have a shortcut text for the URL like you can have in the HTML anchor tag. So hopefully this will help you get the workaround you need while having to print a link in the PDF generated by SQR. Post a comment below if you have questions or have found a better way to achieve the same.

Adverse SQR performance as a result of the Oracle setting _unnest_subquery=FALSE

As I mentioned in an earlier post that the performance tuning recommendation from Oracle for PeopleSoft customers requires setting the init.ora parameter _unnest_subquery to FALSE.

While this is probably a panacea to many a performance issues seen in the system, it is not the case in some occasions. Just like an adverse reaction of a drug that is meant to cure one disease ends up you having another disease; in the case of my customer's PeopleSoft Campus Solution system it caused the custom attendance calculation process to see a sudden and huge spike in the time take to complete (from ~40 odd minutes to 7.5 hours).

Diagnosis gone wrong:

The issue was dealt by many a consultants and system admins over the course of a year without any tangible difference in performance and then I happened to join the team and thus all and sundry were eager to have their hands off this issue. And so I got to know that all cliched solutions were tried out by this time.

As I expected, the issue was misdiagnosed or rather no proper diagnosis was done. The very mention of performance issue and the guys went about lining up the usual suspects (cliched performance fixes) - 

• Gathering stats on the tables
• Flushing shared pool
• altering session parameters
• rebuilding indexes
• putting hints and doing trial and error

When nothing worked, perhaps they just clung on to hope that the problem would disappear just as it miraculously appeared out of nowhere.

What I did differently:

The first thing I did was to do a real-time monitoring of the SQL's as they were being executed by the SQR while the process took long time to run. I used the below SQL to do that - 

 SELECT S.username,   
     s.sid,   
     s.inst_id,   
     s.serial#,   
     s.osuser,   
     t.sql_id,   
     sql_text   
 FROM  v$sqltext_with_newlines t,   
     gv$session s   
 WHERE t.address = s.sql_address   
     AND t.hash_value = s.sql_hash_value   
     AND s.status = 'ACTIVE'   
     AND s.username <> 'SYSTEM'   
     AND osuser <> :1   
 ORDER BY s.sid,   
      t.piece   

From this I noticed a couple of SQL's that were always seen running and running long. Both SQL's had an "exists" clause and when I extracted the explain plan I found that the plan for both the SQL's were significantly costlier as shown below.
SQL1 (Before):

 SELECT Count(*)   
 FROM  ps_attend_tmplt C   
 WHERE C.institution = :1   
     AND C.strm = :2   
     AND C.class_attend_dt <= :3   
     AND EXISTS (SELECT 'x'   
           FROM  ps_stdnt_enrl_l_vw C1   
           WHERE C1.emplid = :4   
              AND C1.crse_id = :5   
              AND C1.strm = :2   
              AND C1.class_nbr = C.class_nbr   
              AND C1.stdnt_enrl_status = 'E')   

SQL2 (Before):

 SELECT Count(*)   
 FROM  ps_attend_tmplt A   
 WHERE A.institution = :1   
     AND A.strm = :2   
     AND EXISTS (SELECT 'x'   
           FROM  ps_stdnt_enrl_l_vw A1   
           WHERE A.institution = A1.institution   
              AND a.strm = a1.strm   
              AND a.class_nbr = a1.class_nbr   
              AND A1.emplid = :3   
              AND A1.crse_id = :4   
              AND A1.strm = :2   
              AND A1.class_nbr = A.class_nbr   
              AND A1.stdnt_enrl_status = 'E')   

Note: I did get a much better plan when the session parameter _unnest_subquery is TRUE. However there was no going back on that one. It had to remain FALSE. So the only option was to rewrite the SQL's to perform better without altering the program logic.

Now that it was clear that the plan was too costly I rewrote the SQL's to do a equi-join to get the row count rather than using exists clause as shown below. I was probably lucky that the SQL with an equi-join would suffice in this situation. A more straightforward and easier solution was to use nested begin-select statement within the SQR program, which I thought of using if equi-join is not an option.

With the revised SQL, the plan prepared by Oracle was way better than before.

SQL1 (after):

 SELECT Count(1)   
 FROM  ps_attend_tmplt C,   
     ps_stdnt_enrl_l_vw C1   
 WHERE C.institution = :1   
     AND C.strm = :2   
     AND C.class_attend_dt <= :3   
     AND C.institution = C1.institution   
     AND C.strm = C1.strm   
     AND C.class_nbr = C1.class_nbr   
     AND C1.emplid = :4   
     AND C1.crse_id = :5   
     AND C1.strm = :2   
     AND C1.class_nbr = C.class_nbr   
     AND C1.stdnt_enrl_status = 'E'   

SQL2 (After):

 SELECT Count(*)   
 FROM  ps_attend_tmplt A,   
     ps_stdnt_enrl_l_vw A1   
 WHERE A.institution = :1   
     AND A.strm = :2   
     AND A.institution = A1.institution   
     AND A.strm = A1.strm   
     AND A.class_nbr = A1.class_nbr   
     AND A1.emplid = :3   
     AND A1.crse_id = :4   
     AND A1.strm = :2   
     AND A1.class_nbr = A.class_nbr   
     AND A1.stdnt_enrl_status = 'E'   

Thus I was able to bring back the run time performance from 7.5 hours+ to the usual 30 to 40 minutes. What did you do in a similar situation or is there a better way out? Leave a comment below. Thanks!!

Mystery of _unnest_subquery=FALSE resolved

Continuing from where I left off in my previous post, we promptly referred this issue with the COBOL process SFPGLINT to Oracle as we had not done any customization. In the meanwhile we switched back the parameter "_unnest_subquery" to TRUE as an interim workaround. Oracle on their part started the investigation of the case with the usual suspicion around whether we are on a "supported version" of Oracle DB, PeopleTools, Weblogic etc...

Going down this path uncovered the horror that the production database (on version 11.2.0.2) was not up to date on the mandatory oracle patches for PeopleTools 8.52.We were missing patch numbers - 12704078, 13504791, 16065596 

What were these mandatory Oracle patches?
You can get them here ->[Required Interim Patches for the Oracle Database with PeopleSoft(1100831.1)]
Based on your database version and PeopleTools version you need to be up to date and not have missed any of them.

How did Oracle find out what we missed? 
Oracle requested for the init.ora file and the output of this command (Need to login as SYS/SYSADM) - 

 $ORACLE_HOME/OPatch/opatch lsinventory > /tmp/opls.txt   

This text file will contain the oracle patch numbers installed in the database. You can then compare if you have all those mandatory patches (relevant to you) from the PeopleTools patch spreadsheet listed in this text file. If you can't find it, then it is highly likely you are missing it.

Eventually after these missing patches were applied in production we then switched back the database parameter flag _unnest_subquery to FALSE and ran the accounting entry generation process SFPGLINT and it worked fine. Thus the issue was resolved without having to do any further investigation.

That a SQL query would stop fetching rows with a database parameter set to False when the database is missing mandatory patches was beyond me. Murphy's law in full force. This issue highlighted the importance of not missing on mandatory patches, a lesson I won't forget soon.

Note: While applying the missing patches you we realized each patch has certain pre-requisite patch and then there is possibility of the mandatory patch mentioned being overridden by another recent patch which was not applicable to our DB version. If you end up in a similar situation you need to log a case with oracle requesting for a merge-patch for your DB version.

The curious case of _unnest_subquery=FALSE in Oracle PeopleSoft

At the customer site where I'm currently working I came across a very odd issue recently. The Student Financials "Generate Accounting Entries" COBOL process - SFPGLINT stopped working and failed to generate any rows and this continued for several days. The main SQL of the COBOL process failed to return any rows, despite data being present for the criteria specified in the SQL.

That it was happening only in production and not any of the non-production instances only added to the mystery surrounding this issue. As a last resort, I imported the production data into the non-production instance and the exact same SQL was able to produce rows. This led me to believe that there is some difference at the database settings that is causing this strange behavior.

Using this sql (Note: You can also get this from the init.ora file) - 

 select * from v$parameter;  

I compared and narrowed down to the differences between the two databases (PROD and non-PROD). One of the difference happened to be this mysterious parameter - _unnest_subquery . While it was TRUE in all non-production databases it was set to FALSE in Production.

At this point I had very little knowledge of what this parameter was and why it was set to FALSE. So I went ahead and altered my SQLTools session where I was replicating the issue, using the command -

 alter session set "_unnest_subquery" = FALSE ;  

After altering the session this command run, I re-ran the same SQL and to my surprise the SQL failed to return any rows. 

I knew how the problem can be fixed now (by modifying the initialization parameter _unnest_subquery to TRUE), but it was deeply disturbing to know that Oracle database would produce different results for the same SQL because of an initialization parameter.

A google search of this parameter revealed that this parameter was all too familiar for PeopleSoft customers elsewhere. This blog post gave me insights into what this parameter does. At my customer place, it turned out that a decision was taken to change this parameter "_unnest_subquery" to FALSE in Production (a few weeks prior to this COBOL process issue being noticed) in order to resolve other performance issues seen in online pages and batch programs, as explained in the blog post.

Continuing to explore more on the google search results revealed that this recommendation to set this parameter to FALSE for PeopleSoft came from none other than Oracle who ships the Oracle databases with the parameter set to TRUE as default. The recommendation is found in the Oracle support site [Required Interim Patches for the Oracle Database with PeopleSoft(1100831.1)]. The recommendation is found for all database versions from 9i all the way to the more recent 12g (See below). 

Although this parameter should ideally have a performance impact, it still didn't explain as to why there is difference in the result rather than performance. So at this point we went ahead and logged a case with Oracle. I will continue on this topic in a later post as to where this leads to.

Trimming spaces and adding spaces on a BI Publisher report

Quite often in BI Publisher, there is a need to trim unnecessary spaces and add spaces on occasions. 

Think of printing an address. PS_ADDRESSES table has so many fields but when printing the address, some of the fields may not necessarily have values, leaving the address formatting  messed up with a lot of white spaces. This is what I'm talking about - 

The RTF template shown above generates the output as shown here - 

Removing unwanted space
In order to solve this issue, I created one BI Publisher field "mail address" with the code written as shown below.

As you can see now, all the extra white space for fields that didn't have a value is now gone (I'll ignore the border issue for now). Though this fixed the extra space issue, it introduced a new problem. I had given a deliberate space between CITY and POSTAL field in the formula but was ignored by BI Publisher in the output. 

Adding Space
The way you can add a space is quite simple, enclose the space within quotes and embed it between the BI publisher tag as shown below -

 <?if:C.ADDRESS1 != ' '?><?C.ADDRESS1?><?end if?>  
 <?if:C.ADDRESS2 != ' '?><?C.ADDRESS2?><?end if?>  
 <?if:C.ADDRESS3 != ' '?><?C.ADDRESS3?><?end if?>  
 <?if:C.ADDRESS4 != ' '?><?C.ADDRESS4?><?end if?>   
 <?C.CITY?><?' '>?<?C.POSTAL?>  

This gives the proper output I was looking for -