Use of Oracle Packages, Procedures and Functions in PeopleSoft

The Oracle database platform (and other platforms supported by PeopleSoft) allow the creation of database stored procedures and functions. This allows all database based tools to use the same code to perform particular common functions. This means that the code only needs to be designed and written once, and therefore maintained once and there is an inherent assurance that there are no esoteric differences in the way the code behaves just because it is called from SQR and PeopleCode.

So there are enormous advantages to using stored packages and procedures. However, these stored packages and procedures are outside of the PeopleSoft realm. They are not an Application Upgrader object and so cannot be moved between PeopleSoft databases easily and automatically as PeopleSoft records and panels can. This means that these items can become "forgotten". New versions can be left in development when testing starts and a beta release version remains on the training database.

Here is a simple idea to put those stored procedures and functions into the PeopleSoft realm and make them Application Upgrader objects: Add the stored procedure code to the end of a record view definition as shown below.

The following procedure is annotated from its SQL equivalent and is added to a "dummy" view:


CREATE VIEW PS_SX_SQL_CREATE (COMPANY) AS SELECT COMPANY FROM
PS_INSTALLATION;
/**************************ENSURESNEWLINE****************************/
create or replace package pshr as
function LastName(p_emplid in ps_personal_data.emplid%type)
return varchar2;
pragma restrict_references(LastName, WNDS, WNPS, RNPS);
end pshr;
/**************************EnsuresNewLine****************************/
/
/**************************EnsuresNewLine****************************/
create or replace package body pshr as
function LastName(p_emplid in ps_personal_data.emplid%type)
return varchar2 is
v_result varchar2(100);
begin
select substr(name,1,instr(name,',')-1)
into v_result
from ps_personal_data
where emplid = p_emplid;
return v_result;
end LastName;
end pshr;

Notes:

  • The view needs to be created to ensure the PeopleSoft build process works OK.
  • The PeopleSoft build process sets all non quoted strings to upper case and removes newlines, word wrapping at about 70 characters. Therefore the "EnsuresNewLine" comments allow the essential slashes to be on new lines.
  • The PeopleSoft build process adds a slash to the end of the script so you do not need to add one there.

After the script that is built looks like the one below:


--
-- WARNING:
--
-- This script should not be run in Data Mover. It may contain platform
-- specific syntax that Data Mover is unable to comprehend. Please use the
-- SQL query tool included with your database engine to process this script.
--
CREATE VIEW PS_SX_SQL_CREATE (COMPANY) AS CREATE VIEW PS_SX_SQL_CREATE
(COMPANY) AS SELECT COMPANY FROM PS_INSTALLATION;
/**************************ENSURESNEWLINE****************************/
CREATE OR REPLACE PACKAGE PSHR AS FUNCTION LASTNAME(P_EMPLID IN
PS_PERSONAL_DATA.EMPLID%TYPE) RETURN VARCHAR2; PRAGMA
RESTRICT_REFERENCES(LASTNAME, WNDS, WNPS, RNPS); END PSHR;
/**************************ENSURESNEWLINE****************************/
/
/**************************ENSURESNEWLINE****************************/
CREATE OR REPLACE PACKAGE BODY PSHR AS FUNCTION
LASTNAME(P_EMPLID IN PS_PERSONAL_DATA.EMPLID%TYPE) RETURN
VARCHAR2 IS V_RESULT VARCHAR2(100); BEGIN SELECT
SUBSTR(NAME,1,INSTR(NAME,',')-1) INTO V_RESULT FROM
PS_PERSONAL_DATA WHERE EMPLID = P_EMPLID; RETURN
V_RESULT; END LASTNAME; END PSHR;
/
COMMIT
/

So this enables you to create PL/SQL functions and procedures, user definitions etc. and keep them within the PeopleSoft build mechanism.

Computing Articles