HOWTO: Generate GUID from PeopleCode

A few months ago I demonstrated a handful of ways to Base64 encode strings — none of them were delivered. That post generated some outstanding feedback. Customers, consultants, and Oracle employees pointed me at several other alternatives, including the delivered Pluggable Encryption technique documented in PeopleBooks. I hope this post generates the same amount of discussion.

First, why would a PeopleSoft developer generate a GUID? My motivation is a database cache. I have a transaction that inserts information into a cache Record and an IScript that reads values from that cache. Rather than pass transaction keys to the IScript, I just pass a GUID that identifies the transaction's row in the cache. Besides decoupling the IScript from a transaction, it provides a bit of security through obfuscation (see OWASP Top 10 2007-Insecure Direct Object Reference)

Now, how to generate a GUID from PeopleCode... PeopleBooks does not list any GUID generation functions, so the next place to look for this functionality is in related technologies accessible to PeopleCode. For example, the Oracle database provides the SYS_GUID function for generating GUID's in the RAW. Here is the SYS_GUID function in action:

Local string &guid;
SQLExec("SELECT RAWTOHEX(SYS_GUID()) FROM PS_INSTALLATION", &guid);
MessageBox(0, "", 0, 0, "GUID from DB: " | &guid);

If you are just looking for a random string, then read no further. If you want a formatted GUID, then you will need to add the dashes yourself. Microsoft SQL Server has a similar function that actually returns a fully formatted plain text GUID.

The problem with these SQL alternatives is that they are database specific. I'm not going to complain about a user taking full advantage of the database's features. But, if there is an alternative that may reduce future maintenance costs (like the cost of swtiching from one database to another), then I'll consider the low cost alternative.

Since all PeopleSoft application servers run Java, we can use the JRE's GUID methods to generate a GUID. If you are on PeopleTools 8.49 with Java 1.5, then this short PeopleCode snippet will give you a fully formatted GUID:

GetJavaClass("java.util.UUID").randomUUID().toString();

On my laptop, the code above generated d65ca460-fc93-4420-a889-8b36311ee4a0.

What if you are using an older version of PeopleTools (prior to 8.49)? The Apache commons id project has a UUID Java class that is similar to the delivered Java 1.5 UUID class. For more information about Java GUID generation, see this java.util.UUID mini-FAQ.

Who knows, if you dig through your application's PeopleCode, you might find an undocumented method for generating GUID's ;)

Using JDBC to Execute Stored Procedures with Output Parameters

PeopleSoft allows developers to execute database stored procedures using PeopleCode that resembles SQLExec("EXEC PACKAGE.PROC_NAME(:1, :2)", &bind1, &bind2);. Even though stored procedures can have input and output parameters, SQLExec discards output parameters. Several years ago I found an interesting post that described how to use DBMS_PIPE with Oracle database to return output parameters, but, it appears the author removed that post. What made the DBMS_PIPE solution so compelling was that it shared the PeopleSoft database connection. The alternative presented below, unfortunately, requires you to maintain a user name and password, preferably encrypted and stored in a secure location. Because of the class loading issues mentioned in my post Using Oracle JDBC from PeopleCode, this post uses the Oracle specific data access classes. If you use a different database, the classes will differ, but the concept is the same. Furthermore, if you use a different database and JDBC driver, you may be able to use the standard, generic JDBC classes as described in the PSST0101 post Writing to Access Databases

Before demonstrating how to call a stored procedure from PeopleCode, we need a stored procedure to call. The following PL/SQL describes a stored procedure that has two parameters: one in and one in/out. The implementation of the procedure hard codes the output value for simplicity.

CREATE OR REPLACE PACKAGE JJM_IN_OUT AS

  PROCEDURE P(
    IN1    IN     VARCHAR2,
    INOUT1 IN OUT VARCHAR2);
END JJM_IN_OUT;
/

CREATE OR REPLACE PACKAGE BODY JJM_IN_OUT AS
  PROCEDURE P(
    IN1    IN     VARCHAR2,
    INOUT1 IN OUT VARCHAR2) IS
  BEGIN
    INOUT1 := 'Hello World';
  END P;
END JJM_IN_OUT;
/
show errors

To build this package, copy the PL/SQL above into a text editor and then run it from SQLPlus. The procedure test follows:

SQL> var out1 varchar2(100)
SQL> exec JJM_IN_OUT.P('x', :out1);

PL/SQL procedure successfully completed.

SQL> print out1

OUT1
-------------------------------------------------------
Hello World

SQL>

The following IScript demonstrates calling a procedure with in/out parameters from PeopleCode:

Function IScript_OutParms()
   Local JavaObject &driver = CreateJavaObject("oracle.jdbc.OracleDriver");;
   Local JavaObject &info = CreateJavaObject("java.util.Properties");
   
   &info.put("user", "dbuser");
   &info.put("password", "secret");
   
   Local JavaObject &conn = &driver.connect("jdbc:oracle:thin:@server:1521:SID", &info);
   Local JavaObject &stmt = &conn.prepareCall("{call JJM_IN_OUT.P (?,?)}");
   Local JavaObject &types = GetJavaClass("java.sql.Types");
   
   REM ** set input parameter values;
   &stmt.setString(1, "aa");
   &stmt.setString(2, "bb");
   
   REM ** register the second parameter as a output parameter;
   &stmt.registerOutParameter(2, &types.VARCHAR);
   
   REM ** execute the SQL statement;
   &stmt.execute();
   
   %Response.SetContentType("text/plain");
   
   REM ** Display the value of the second parameter;
   %Response.WriteLine("JJM_IN_OUT.P output parameter value: " | &stmt.getString(2));
   
   &conn.close();
End-Function;

After making the connection, the code prepares an SQL statement: {call JJM_IN_OUT.P (?,?)}. JDBC procedure calls use the syntax {call proc_name (?,?)} (the question marks represent the procedure's parameters).

The code above is for demonstration purposes only. Be sure to store the database user name and password in a secure manner. When executing SQL using a second connection, be sure to consider deadlock, race conditions, etc.

JSON In (and out) of Oracle - JSON Data Type

Lewis Cunningham just posted this link in a comment to one of my JSON posts: JSON In (and out) of Oracle - JSON Data Type. Nice work Lewis!

Export PeopleSoft Attachments using PL/SQL

Oracle provides PeopleTools developers with the ability to store files with transactions using the File Attachment API. Likewise, the File Attachment API includes PeopleCode functions for extracting and displaying attachments. What if you need to export attachments directly from a database or from SQR where you don't have access to the File Attachment PeopleCode functions? Here is some PL/SQL demonstrating how to export attachments. You just need to set the file name and the attachment record name. I labeled the values you need to change with TODO:. Code for other databases should be relatively similar.

CREATE OR REPLACE DIRECTORY TEMP_DIR AS 'c:\temp'
/

DECLARE
  -- Max PS attachment chunk size is 28000
  CV_BUFFER_MAX    NUMBER        := 28000;

  -- Name of file to export. This is the name that was used to store the file
  -- and is the name that will be used to create a new file.
  -- TODO: change the name to match the name of your exported file
  lv_file_name     VARCHAR2(128) := 'theattachedfile.xls';
  lv_buffer        RAW(28000);

  lv_file_ref      utl_file.file_type;
  
BEGIN
  lv_file_ref := utl_file.fopen('TEMP_DIR', lv_file_name, 'WB');

  FOR r_chunks IN
      (SELECT FILE_DATA
            , FILE_SIZE
     -- TODO: Change record to the name of your attachment record
         FROM PSFILE_ATTDET
        WHERE ATTACHSYSFILENAME = lv_file_name) LOOP

    dbms_lob.read(r_chunks.FILE_DATA, r_chunks.FILE_SIZE, 1, lv_buffer);
    utl_file.put_raw(lv_file_ref, lv_buffer, true);
  END LOOP;

  utl_file.fclose(lv_file_ref);

  EXCEPTION
    WHEN OTHERS THEN
    -- Close the file if something goes wrong.
      IF UTL_FILE.is_open(lv_file_ref) THEN
        UTL_FILE.fclose(lv_file_ref);
      END IF;
    RAISE;
END;
/

You will notice that this code exports an attachment to the database server's file system.

How do you find file attachment records? The easiest way I can think of is to query the PSRECFIELD table for all record definitions that contain the FILE_ATTDET_SBR sub record. Here is the SQL:

SELECT *
  FROM PSRECFIELD
 WHERE FIELDNAME = 'FILE_ATTDET_SBR'

PeopleSoft on Oracle WHOAMI

Determine the PeopleSoft OPRID from Oracle

Occasionally, while writing components or processes in PeopleSoft, I have wished I could obtain the OPRID directly from the database without using META-SQL, META-Variables, or any other PeopleSoft magic. While those mechanisms work very well within the PeopleSoft framework, they are not accessible from PL/SQL procedures, triggers, or views. I was discussing this with my DBA a few months ago and he kindly pointed me to the CLIENT_INFO field of the v$session view. As Chris Heller explained in his post Associating database connections with PeopleSoft users dated Sunday, August 13, 2006, EnableDBMonitoring needs to be turned on before the CLIENT_INFO field will contain the OPRID. Chris Heller's post also explains where to find CLIENT_INFO on non-Oracle database platforms.

Following the good advice of my DBA, I wrote the following SQL fragment to parse the OPRID from the CLIENT_INFO field.

SUBSTR(sys_context('USERENV', 'CLIENT_INFO'), 1, INSTR(sys_context('USERENV', 'CLIENT_INFO'), ',', 1, 1) - 1)

Notice that I use the sys_context function rather than querying the v$session view directly. The v$session view is a great view for system administrators, but it lists all sessions, not just the logged in user's session.

Because this fragment is rather verbose, I wrote a PL/SQL function to encapsulate the logic of the fragment. However, when used in a WHERE clause, this fragment executes a lot faster than the function.

Here is an example SQL statement that will return the PeopleSoft logged in user. I would like to tell you that you can run this statement from any SQL tool, but it will only work when run from PeopleSoft. This is because each database client is responsible for setting the CLIENT_INFO and each client tool sets this value differently (if the client tool even sets CLIENT_INFO).

SELECT SUBSTR(sys_context('USERENV', 'CLIENT_INFO'), 1, INSTR(sys_context('USERENV', 'CLIENT_INFO'), ',', 1, 1) - 1)
FROM DUAL

Here is a PL/SQL block that sets the variable lv_oprid to the PeopleSoft OPRID.

DECLARE
  lv_oprid VARCHAR2(30);
  -- ... more variables declared here
BEGIN
  lv_oprid := SUBSTR(sys_context('USERENV', 'CLIENT_INFO'), 1, INSTR(sys_context('USERENV', 'CLIENT_INFO'), ',', 1, 1) - 1);
  -- ... do something with OPRID
END;

In a future post I will show how to use this technique to log information about users for debugging and auditing. I also hope to show how to use this technique to improve the user experience with custom pagelets.

If you are trying to restrict the visible rows in a search record, then I suggest you take a look at Larry Grey's post Little known Row Level Security hook dated Thursday, May 18, 2006.