Randomly Generated Data - Oracle SQL

select
  trunc(dbms_random.value(1, 11)) as seconds
from dual
connect by level < 11

(You could join the results to a table using rownum I guess)

SQL to Group or Chunk Date Time Fields by n Minutes

Say you want to chunk some statistics data into 10 minute intervals. as of Oracle 11g you can use the extract() function that will pull out hours, minutes and the like from a DateTime column.

select
  to_date(trunc(dttm_stamp) || ' ' || extract(hour from dttm_stamp) || ':' || lpad(ceil(extract(minute from dttm_stamp)/10 + 0.1) * 10 - 10, 2, '0'), 'dd-mon-yy hh24:mi') as dttm_stamp
, count(some_field) "count"
from 
  ps_statistics_table
group by
  trunc(dttm_stamp) || ' ' || extract(hour from dttm_stamp) || ':' || lpad(ceil(extract(minute from dttm_stamp)/10 + 0.1) * 10 - 10, 2, '0')
;

Note the +0.1 bit that handles the range of results, up to that point spanning from 0 to 6. We can't have nn:60 as a vaild DateTime. This is also why we have the -10 part.

Also note that depending on the platform that is actually executing the SQL you may have to fiddle around with the DateTime formats. 'dd-mon-yy hh24:mi' might not cut it for you. PeopleSoft will require 'yyyy-mm-dd hh24:mi' for example.

A breakdown of the entire line:

select
  dttm_stamp as a
, extract(minute from dttm_stamp) as b
, ceil(extract(minute from dttm_stamp)/10 + 0.1)  as c
, ceil(extract(minute from dttm_stamp)/10 + 0.1) * 10 - 10 as d
, lpad(ceil(extract(minute from dttm_stamp)/10 + 0.1) * 10 - 10, 2, '0') as e
, trunc(dttm_stamp) || ' ' || extract(hour from dttm_stamp) || ':' || lpad(ceil(extract(minute from dttm_stamp)/10 + 0.1) * 10 - 10, 2, '0') as dttm_stamp
from ps_statistics_table
;

The final results from the first query:

17/11/2013 10:00:00 AM 99
17/11/2013 10:10:00 AM 120
17/11/2013 10:20:00 AM 50
17/11/2013 10:30:00 AM 10
17/11/2013 10:40:00 AM 65
17/11/2013 10:50:00 AM 77
17/11/2013 11:00:00 AM 189
17/11/2013 11:10:00 AM 201
17/11/2013 11:20:00 AM 235
17/11/2013 11:30:00 AM 188
17/11/2013 11:40:00 AM 105
17/11/2013 11:50:00 AM 77

Merging PDFs Produces Blank Pages

So you're creating a number of individual PDFs, doing something with them, and then you want to merge them all into one, nice, long PDF at the end.

Something like this:

/* Merge! */
Local boolean &booMergeSuccess = &objMergeTool.mergePDFs(&arrFiles, &sNSMergedFileDestination | &sDirSep | "merged.pdf", &sError);

You can see this in Communication Generation for example.

But when you open the PDF only the first page has data, the rest are blank. What's going on?

I finally reduced this down to being something "wrong" with the individual PDFs that were being created, before they were merged. It wasn't the templates. It was something specific to the PDF files themselves.

Comparing them to an environment where (luckily) the merge did work showed that the PDFs that did merge had no security, while the ones that didn't merge did have security. File > Properties > Security.

xdo.cfg was the same in both environments. So where was this coming from?

I noticed that, in 8.53 at least, there are some new parameters for BI Publisher Report Definitions, namely PDF Security. The default for pdf-security was True.

I changed this to False and the merge worked. Finally.

This is overriding the global value at the report level. The global value is set at:
Reporting Tools > BI Publisher > Setup > Global Properties

tl;dr solution:

update PSXPGLBPROP
set propvalue = 'False'
where proplname = 'pdf-security';

Order SQL Using A Given, Distinct List

Say you have a requirement of updating the preferred phone number using a given, ad hoc, ordered list, where #1 in the list was the preferred number, if it exists, followed by #2 in the list and so on. Basically order by X, Y, Z.

This SQL will fit the bill: It's much easier to read the SELECT version first, as we can use the WITH clause:

with x_order as
      (         select 1 as ord, 'HOME' as phone_type from dual
      union all select 2 as ord, 'MOB'  as phone_type from dual
      union all select 3 as ord, 'WORK' as phone_type from dual
      union all select 4 as ord, 'SWK'  as phone_type from dual
      union all select 5 as ord, 'OTR'  as phone_type from dual
      union all select 6 as ord, 'FAX'  as phone_type from dual)

select * from ps_personal_phone ph
where emplid = '12345678'
and ph.phone_type =
      (select a.phone_type
      from
        ps_personal_phone a
      , x_order b
      where a.emplid = ph.emplid
      and b.phone_type = a.phone_type
      and b.ord =
            (select min(b2.ord)
            from
              ps_personal_phone a2
            , x_order b2
            where a2.emplid = a.emplid
            and b2.phone_type = a2.phone_type))
;

And the UPDATE version:

update ps_personal_phone ph
set ph.pref_phone_flag = 'Y'
where emplid = '12345678'
and ph.phone_type =
      (select a.phone_type
      from
        ps_personal_phone a
      ,
            (         select 1 as ord, 'HOME' as phone_type from dual
            union all select 2 as ord, 'MOB' as phone_type from dual
            union all select 3 as ord, 'WORK' as phone_type from dual
            union all select 4 as ord, 'SWK' as phone_type from dual
            union all select 5 as ord, 'OTR' as phone_type from dual
            union all select 6 as ord, 'FAX' as phone_type from dual) b
      where a.emplid = ph.emplid
      and b.phone_type = a.phone_type
      and b.ord =
            (select min(b2.ord)
            from
              ps_personal_phone a2
            ,
                   (         select 1 as ord, 'HOME' as phone_type from dual
                   union all select 2 as ord, 'MOB' as phone_type from dual
                   union all select 3 as ord, 'WORK' as phone_type from dual
                   union all select 4 as ord, 'SWK' as phone_type from dual
                   union all select 5 as ord, 'OTR' as phone_type from dual
                   union all select 6 as ord, 'FAX' as phone_type from dual) b2
            where a2.emplid = a.emplid
            and b2.phone_type = a2.phone_type))
;

Table Lock

Some useful tables when something somewhere has a table locked:

SELECT * FROM V$LOCK;

SELECT * FROM dba_blockers;

SELECT * FROM dba_locks;

SELECT * FROM dba_waiters;

SELECT * FROM V$SQLTEXT WHERE ADDRESS IN (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID IN (SELECT HOLDING_SESSION FROM dba_blockers));

SELECT * FROM V$SESSION WHERE SID IN (SELECT HOLDING_SESSION FROM dba_blockers);

SELECT * FROM V$SQLTEXT WHERE SQL_ID = '21hy5qgd56a6g';

SELECT * FROM V$SESSION WHERE SID = 152;

Move a File Using PeopleCode

There's no generic function to move files in the PeopleCode universe, only to open them. Here's an elegant solution using the Java io package.

Local JavaObject &fromFile = CreateJavaObject("java.io.File", &strFromPath);
Local JavaObject &toFile = CreateJavaObject("java.io.File", &strToPath);
&fromFile.renameTo(&toFile);

And this is where our ED workflow redesign team went insane.