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.

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'

Get Your Mix Account and Vote

Do you have a Mix account yet? If not, then get one. After you create an account, add the PeopleSoft and PeopleTools groups to your profile. And then, be sure to vote for ideas submitted by your peers. For example, if you want Code Completion in Application Designer or Search by process name when adding process definitions to projects, then vote for it.

There are several good communities for PeopleSoft developers. Why join Mix? Mix has one thing the other communities don't. Mix has PeopleSoft developers and consultants. Yes, the other communities do have some Oracle staff, but not like Mix. Are you looking for a "Meet the Experts" experience all year, not just at OOW? Then try Mix.

Query for Component and/or CREF Navigation

I regulary have to figure out the menu/portal navigation to a component given nothing more than a component or a CREF name (AKA Content Reference or Portal Object Name). For example, looking at a Pagelet definition in the Pagelet Wizard, you may see the cryptic content reference name for a component, but nothing telling you how to navigate to that component and no hint to help you find it in the portal registry. Likewise, a user might give you the menu and component name of a troubled component, but not the navigation to that component. It is situations like this that we, as developers, can realize value from the PeopleTools meta-data. Since the navigation, CREF name, and menu/component relationship is stored in the PeopleTools database, we can query a tools table to determine this navigation. The following Oracle specific SQL displays the navigation to a given CREF name. If you use a different database, and are familiar enough with your database's SQL, please translate this statement and post it as a comment for the benefit of the rest of the community.

WITH PORTAL_REGISTRY AS (
SELECT RTRIM(REVERSE(SYS_CONNECT_BY_PATH(REVERSE(PORTAL_LABEL), ' >> ')), ' >> ') PATH
     , LEVEL LVL
  FROM PSPRSMDEFN
 WHERE PORTAL_NAME = 'EMPLOYEE'
 START WITH PORTAL_OBJNAME = :1
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME )
SELECT PATH
  FROM PORTAL_REGISTRY
 WHERE LVL = (
SELECT MAX(LVL)
  FROM PORTAL_REGISTRY )

NOTE: Even though I've written my fair share of SQL, I'm no expert. Please don't grade me on the elegance and efficiency of the previous statement. If you are an SQL expert, and have a better way of writing this recursive SQL statement, then, please, for the benefit of the community, paste that statement into a comment. That said, I'll continue with the topic at hand...

If we pass EP_STANDARD_CF_TMPLT_GBL as bind :1, then the result will be Root >> Set Up Financials/Supply Chain >> Common Definitions >> Design ChartFields >> Configure >> Standard Configuration.

Modifying the above SQL statement a little, we can determine the navigation for a given menu, component, and market (the Ctrl-J data our users usually give us):

WITH PORTAL_REGISTRY AS (
SELECT RTRIM(REVERSE(SYS_CONNECT_BY_PATH(REVERSE(PORTAL_LABEL), ' >> ')), ' >> ') PATH
     , LEVEL LVL
  FROM PSPRSMDEFN
 WHERE PORTAL_NAME = 'EMPLOYEE'
 START WITH PORTAL_REFTYPE = 'C'
   AND PORTAL_URI_SEG1 = :1
   AND PORTAL_URI_SEG2 = :2
   AND PORTAL_URI_SEG3 = :3
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME )
SELECT PATH
  FROM PORTAL_REGISTRY
 WHERE LVL = (
SELECT MAX(LVL)
  FROM PORTAL_REGISTRY )

If we pass DESIGN_CHARTFIELDS for bind :1 (menu), STANDARD_CF_TMPLT for bind :2 (component), and GBL for bind :3 (market), then we will get the same result as the previous query, but using the Ctrl-J data rather than the CREF name.