My Oracle Support Banner

EPY: All About Stored Statements (Primary Solution) (Doc ID 607665.1)

Last updated on FEBRUARY 02, 2022

Applies to:

PeopleSoft Enterprise HCM Payroll for North America - Version 8.8 SP1 to 9.2 [Release 8.8 to 9]
Information in this document applies to any platform.


Introduction to Stored Statements and Static SQL

The term "static SQL" refers to predefined SQL statements (e.g. Select, Insert, Update, etc.) which are predefined and thus able to be stored externally from the COBOL programs that use them.  This is in contrast to "Dynamic SQL" which is constructed by the COBOL process as part of the logical flow of the program.

The text of the "static SQL" statement is stored in the database, in the PS_SQLSTMT_TBL.  Most of the SQL executed by PeopleSoft HCM COBOL programs is static SQL.   These SQL statements are delivered in DMS scripts and stored independently from the COBOL programs that execute them. They are referred to as "stored statements" or "stored SQL statements".

The static SQL statements are loaded by DataMover into a PeopleTools table called PS_SQLSTMT_TBL. When you run a COBOL program, the system will retrieve the stored SQL statements from PS_SQLSTMT_TBL as needed by the program.

Files with ".DMS" file type:

There are three types of files with ".DMS" names:

  1. DMS scripts to load a project into the AUDB; the project contains database objects like records, panels, menus, etc. This type of .DMS script file has an accompanying .DAT or .AU file containing the project and objects to be loaded. The objects in the project will be used to update Tools tables.
  2. DMS scripts to load application data (e.g. tax rates or garnishment rules). This type of .DMS script file has an accompanying .DAT file containing the actual data to be loaded. The data will be used to update application tables.
  3. DMS scripts to load SQL stored statements. This type of .DMS script file does not have an accompanying .DAT or .AU file. Stored statement .DMS script files are named for the COBOL program which will use the SQL statements.


Note:  This document only addresses the third type of DMS file.



To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
 Introduction to Stored Statements and Static SQL
 Creating the Static SQL Statement .DMS Files
 Examples of Stored Statement Names
 Where to Find the Stored Statement Script Files
 Individual DMS Scripts VS. STORE*.DMS Scripts
 HCM Product Suite STORE*.DMS Scripts
 How to Run (Load) Stored Statements
 Selective Loads VS. All Stored Statements for a Product

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.