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.

Tracing Application Engines

Tracing particular Process Definitions
This SQL script can make things a little clearer.

select '-TRACE ' || (
1    + -- Initiate the Application Engine Step trace
2    + -- Initiate the Application Engine SQL trace
4    + -- Trace dedicated Temp Table Allocation to AET file
128  + -- Initiate the Statement Timings trace to file, which is similar to the COBOL timings trace to file
256  + -- Initiate the PeopleCode Detail to the file for the Timings trace
1024 + -- Initiate the Statement Timings trace, but, instead of writing to the trace file, this trace stores the results in the following tables: PS_BAT_TIMINGS_LOG and PS_BAT_TIMINGS_DTL
2048 + -- Adding this value requests a database optimizer trace file
4096 + -- Request a database optimizer to be inserted in the Explain Plan Table of the current database
8192   -- This value sets a trace for Integration Broker transform programs
) as TRACE from dual;

select '-TOOLSTRACESQL ' || (
1    + -- Trace SQL statements
2    + -- Trace SQL statement variables
4    + -- Trace SQL connect, disconnect, commit and rollback
8    + -- Show fetched rows (indicates that it occurred, not data)
16   + -- Show all other API calls except ssb
32   + -- Set Select Buffers (identifies the attributes of columns to be selected).
64   + -- Show database API specific calls
128  + -- Show COBOL statement timings
256  + -- Show Sybase bind information
512  + -- Show Sybase fetch information
4096 + -- Show manager information
8192   -- Show Mapcore information
) as TOOLSTRACESQL from dual;

select '-TOOLSTRACEPC ' || (
1    + -- Trace instructions
2    + -- List the program
4    + -- Show assignments to variables
8    + -- Show fetched values
16   + -- Show stack
64   + -- Trace start of programs
128  + -- Trace external function calls
256  + -- Trace internal function calls
512  + -- Show parameter values
1024 + -- Show function return values
2048   -- Trace each statement in program
) as TOOLSTRACEPC from dual;

Tracing on the Server:
./psft/pt/8.52/appserv/*/psappsrv.cfg is where you can set tracing for Batch server instances.

[Trace]
;=========================================================================
; Trace settings
;=========================================================================
; SQL Tracing Bitfield
;
; Bit       Type of tracing
; ---       ---------------
; 1         - SQL statements
; 2         - SQL statement variables
; 4         - SQL connect, disconnect, commit and rollback
; 8         - Row Fetch (indicates that it occurred, not data)
; 16        - All other API calls except ssb
; 32        - Set Select Buffers (identifies the attributes of columns
;             to be selected).
; 64        - Database API specific calls
; 128       - COBOL statement timings
; 256       - Sybase Bind information
; 512       - Sybase Fetch information
; 1024      - SQL Informational Trace
; Dynamic change allowed for TraceSql and TraceSqlMask
TraceSQL=0

;-------------------------------------------------------------------------
; PeopleCode Tracing Bitfield
;
; Bit       Type of tracing
; ---       ---------------
; 1         - Trace Evaluator instructions  (not recommended)
; 2         - List Evaluator program        (not recommended)
; 4         - Show assignments to variables
; 8         - Show fetched values
; 16        - Show stack
; 64        - Trace start of programs
; 128       - Trace external function calls
; 256       - Trace internal function calls
; 512       - Show parameter values
; 1024      - Show function return value
; 2048      - Trace each statement in program  (recommended)
; Dynamic change allowed for TracePC
TracePC=0

;-------------------------------------------------------------------------
; AE Tracing Bitfield
;
; Bit       Type of tracing
; ---       ---------------
; 1         - Trace STEP execution sequence to AET file
; 2         - Trace Application SQL statements to AET file
; 4         - Trace Dedicated Temp Table Allocation to AET file
; 8         - not yet allocated
; 16        - not yet allocated
; 32        - not yet allocated
; 64        - not yet allocated
; 128       - Timings Report to AET file
; 256       - Method/BuiltIn detail instead of summary in AET Timings Report
; 512       - not yet allocated
; 1024      - Timings Report to tables
; 2048      - DB optimizer trace to file
; 4096      - DB optimizer trace to tables
TraceAE=0

;------------------------------------------------------------------------
; Performance Monitor Tracing Bitfield
;
; Bit       Type of tracing
; ---       ---------------
; 1         - Trace All performance monitor agent activity
TracePPM=0

Portal Navigation Path - Oracle PL/SQL Function

Thanks to Jake Gowens a.k.a. Zapp Brannigan

FUNCTION navString (prtlName IN CHAR, prtlObjName IN CHAR) RETURN VARCHAR2 IS

  prtlLabel         VARCHAR2(30);
  prtlParent        VARCHAR2(30);
  result            VARCHAR2(255);

BEGIN

  prtlLabel := '';

  SELECT MAX(PORTAL_LABEL), MAX(PORTAL_PRNTOBJNAME)
    INTO prtlLabel, prtlParent
    FROM PSPRSMDEFN
   WHERE PORTAL_NAME = prtlName
     AND PORTAL_OBJNAME = prtlObjName
     AND PORTAL_REFTYPE = 'C';

  if prtlLabel IS NOT NULL then
    if prtlParent = 'PORTAL_ROOT_OBJECT' then
      result := prtlLabel;
    else
      result := navStringF(prtlName, prtlParent) || ' > ' || prtlLabel;
    end if;
  end if;

  return result;

END navString;

Example:
Select navString(a.portal_name, a.portal_objname)
From psprsmdefn a
Where portal_uri_seg2 = 'JOB_DATA';

Returns:
Workforce Administration > Job Information > Job Data

Automatically Generate Upgrade Scripts

Snippet of PeopleCode that takes a Project and creates an upgrade script for all the Records in that Project. Does 90%+ of the work for you.

Requires a Prompt to select the Project, a button to put this code under, and a HTML Area to output the results to.

If None(NS_MN_TEST.PROJECTNAME.Value) Then
   MessageBox(0, "", 0, 0, "Enter a Project Name");
Else
   
   Local string &title, &result, &fields;
   Local integer &f;
   
   Local Record &recPSPROJECTDEFN = CreateRecord(Record.PSPROJECTDEFN);
   &recPSPROJECTDEFN.PROJECTNAME.Value = NS_MN_TEST.PROJECTNAME.Value;
   &recPSPROJECTDEFN.SelectByKey();
   
   Local Record &recPSOPRDEFN = CreateRecord(Record.PSOPRDEFN);
   &recPSOPRDEFN.OPRID.Value = &recPSPROJECTDEFN.LASTUPDOPRID.Value;
   &recPSOPRDEFN.SelectByKey();
   
   &title = &title | "/*" | Char(13);
   &title = &title | "     WORK UNIT:     " | &recPSPROJECTDEFN.PROJECTNAME.Value | Char(13);
   &title = &title | "     DEVELOPER:     " | &recPSOPRDEFN.OPRDEFNDESC.Value | Char(13);
   &title = &title | "     DATE:          " | String(%Date) | Char(13);
   &title = &title | "     DESCRIPTION:   " | &recPSPROJECTDEFN.PROJECTDESCR.Value | Char(13);
   &title = &title | "     INSTRUCTIONS:  EXECUTE IN THE TARGET" | Char(13);
   &title = &title | "     DEPENDENCIES:  " | Char(13);
   &title = &title | "*/" | Char(13) | Char(13);
   
   &title = &title | "-- List of Records that have data converted by this script:" | Char(13);
   
   
   Local SQL &sql = CreateSQL("SELECT OBJECTVALUE1 FROM PSPROJECTITEM A, PSRECDEFN B WHERE A.PROJECTNAME = :1 AND A.OBJECTTYPE = 0 AND A.OBJECTID2 = 0 AND B.RECNAME = A.OBJECTVALUE1 AND B.RECTYPE = 0", NS_MN_TEST.PROJECTNAME.Value);
   
   Local string &recName;
   While &sql.Fetch(&recName)
      
      If Left(&recName, 3) = "NS_" Then
         
         &title = &title | "--    " | &recName | Char(13);
         
         &result = &result | Char(13) | "-- " | &recName | Char(13);
         &result = &result | "Delete From ps_" | &recName | ";" | Char(13);
         Local Record &rec = CreateRecord(@("record." | &recName));
         &result = &result | "Insert Into ps_" | &recName | "(" | Char(13);
         For &f = 1 To &rec.FieldCount
            If &f = 1 Then
               &fields = "  " | &rec.GetField(&f).Name | Char(13);
            Else
               &fields = &fields | ", " | &rec.GetField(&f).Name | Char(13);
            End-If;
         End-For;
         &result = &result | &fields | ")" | Char(13);
         &result = &result | "Select " | Char(13) | &fields | "From ps_" | &recName | "@nsconv.unsw.edu.au;" | Char(13);
         
      End-If;
      
   End-While;
   
   &title = &title | Char(13) | "SPOOL ON;" | Char(13);
   &title = &title | "SET ECHO ON;" | Char(13);
   &title = &title | "SPOOL 'c:\temp\" | Substring(&recPSPROJECTDEFN.PROJECTNAME.Value, 4, 7) | "_data_conversion.log' REPLACE;" | Char(13);
   
   
   NS_MN_TEST.HTMLAREA1.Value = &title | Char(13) | Char(13) | &result | Char(13) | Char(13) | "commit;" | Char(13) | Char(13) | "SPOOL OFF;";
   
End-If;
Paste your text here.