Date Validation - Oracle SQL

In Peoplesoft where we read data from the files, especially for inbound interface , conversion and one time data load. Date is usually read as character field in different format and converted to date. We need to validate the date for further processing. It will be good if we can validate date field in SQL (set processing) than the validating using people code (row by row). Oracle doesn't have very useful function IsDate() not sure why.

Lets see how we can do set processing for the date validation.

1) Regular expression came to my mind to check the pattern and validate the date easily.

Select 'X' from dual
Where REGEXP_LIKE ('1999-10-10','^(1920)\d\d[- /.](0[1-9]1[012])[- /.](0[1-9][12][0-9]3[01])$')
 This is simple regular expression which matches only the date format. It doesn't check for leap years and 30,31 days for all the months.

2) Select 'X' from dual
Where NOT REGEXP_LIKE ('2009-2-29','^((((19|20)(([02468][048])([13579][26]))-02-29))((20[0-9][0-9])(19[0-9][0-9]))-((((0[1-9])(1[0-2]))-((0[1-9])(1\d)(2[0-8])))((((0[13578])(1[02]))-31)(((0[1,3-9])(1[0-2]))-(2930)))))$')

This regular expression is quite big difficult right every time correctly. Need to copy paste where ever it is requried.

3) if the regular expression is getting difficult, then can create a PL/SQL function and use that function in the sql to validate.
we can use the above sql itself in the function. or use the following.

function IsDate (str long) return boolean is the_Date date;
the_date := to_date(str,'dd-mon-yyyy'); -- note the correction to date mask from what you had below
RETURN true; -- conversion success
when others then RETURN false; -- conversion failed
In the Peoplesoft Implementation project we can create such function once and can be used in many SQL/Peoplecode/AE's. It is difficult to maintain this procedure in different instances like Dev,FIT,SIT,UAT,PPROD,PROD, but is it worth the effort.


Special SQL operators make the SQL simple to understand & write.

ALL, SOME  and ANY :- These operators are not widely used in the SQL, these keywords exists in both Oracle and MS SQL. These keywords can be very useful in developing many peoplesoft realated query. It makes the query simple to write and understand.

Eg:- If you want the list of employees who are terminated in all the assignments (employee records).

 1) Select EMPLID from PS_PERSON A
Where 'I' = ALL (Select HR_STATUS from PS_CURRENT_JOB J

2) Select EMPLID from PS_PERSON A
Where 'A' <> ANY (Select HR_STATUS from PS_CURRENT_JOB J

ANY or SOME: Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Evaluates to FALSE if the query returns no rows.
ALL: Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, < , <=, >=. Evaluates to TRUE if the query returns no rows.

It is doesn't do anything new, it is just user friendly wrap keywords. The optimizer expands a condition that uses the ALL comparison operator followed by a parenthesized list of values into an equivalent condition that uses equality comparison operators and AND logical operators.The optimizer transforms a condition that uses the ANY or SOME operator followed by a sub query into a condition containing the EXISTS operator and a correlated sub query.

Due to this behaviour of the optimizer in Oracle SQL, we need to analyze sql for the performance before we take decision to use it in the production. These constructs have been useful for me in doing the data analysis and creating the quick query to get list of the employee based on certain condition.


Forgot password link setup in Peopleosoft.

Many customers want forgot password option and email the password option. Based on the hint questions user can be authorized and get the password delivered to their email.

Below Embedded Scribd document contains the steps to create the forgot password setup in peoplesoft.


Type Ahead feature in the Peoplesoft 9.1/Ptools 8.50

In PeopleSoft 9.1/ PeopleTools 8.50, they have introduced a new feature, as you type through a field it tries to provide possible values for the field.

Yes you can certainly disable the type ahead feature either globally (at User level) or at the Field level (all Users).

My Personalization > Navigation Personalization > Type ahead = NO , default is YES


SQL Loader - Basics

Many times we need to load the data in to Peoplesoft Loading tables or other tables for conversion  and interface and bulk load for minor data migrations.

SQL Loader is very fast and easy way for loading the data. It is not used much in real-time with productions instance, but with BPEL you can automate the loading of data in to tables in real-time. SQL Loader fast and very efficient.
There is some of the Basics of SQL Loader.

SQL Loader:

               SQL*Loader is the primary method for quickly populating Oracle tables with data from external files. It has a powerful data-parsing engine that puts little limitation on the format of the data in the datafile. SQL*Loader is invoked when you specify the sqlldr command.

SQL*Loader can be used to do the following:
  • Load data across a network. This means that a SQL*Loader client can be run on a different system from the one that is running the SQL*Loader server.
  • Load data from multiple datafiles during the same load session
  • Load data into multiple tables during the same load session
  • Specify the character set of the data
  • Selectively load data
  • Load data from disk, tape, or named pipe
  • Specify the character set of the data
  • Generate sophisticated error reports, which greatly aid troubleshooting
  • Load arbitrarily complex object-relational data
  • Use either conventional or direct path loading.
SQL*Loader Control File:            The control file is a text file written in a language that SQL*Loader understands. The control file tells SQL*Loader where to find the data, how to parse and interpret the data, and where to insert the data.


Magic of Oracle Regular Expression Functions

Oracle SQL has Regular Expression support from 2003. Many people doesn't know about it and has not been used by many of the Peoplesoft developers and legacy SQR and Peoplecode.

These functions can simplify a lot of pattern matching work in the SQL and AE / SQR program.

REGEXP_LIKE Determine whether pattern matches
REGEXP_SUBSTR Determine what string matches the pattern
REGEXP_INSTR Determine where the match occurred in the string
REGEXP_REPLACE Search and replace a pattern

contains the details of using the regular expression iPublish Postn Oracle SQL.



Can be used to select fields containing only alpha numeric character.

Pattern matching for Phone number , Postal code , Email id , National Identifier , Employee ID , Name , Addresss , Bank account number , credit card number , different identifier.


Usefull Peoplesoft Viewlets

Very usefull viewlets and must watch for Peoplesoft Techies.
Don't miss the Security , Webservices & Intergration broker , Reporting