Introduction
Application Overview
Software Requirements
Terminology
Configuring the Application
Deploying and Running the Application
Sample Application Files
Additional References

Introduction

• Prerequisite
  
  
• Technical Overview

Prerequisite

• PL/SQL
  
  

Technical Overview

Regular expressions represent a powerful tool for describing and manipulating text data. These are supported by a wide variety of programming and scripting languages, text editors, and now by Oracle Database 10g SQL and PL/SQL.
It is a method for simple and complex patterns for searching and manipulating a text. The regular expression APIs facilitate to search, extract, format, and manipulate a text in the database.

The newest Oracle Database release, Oracle Database 10g, implements regular expression support in the form of the following four new functions, which work equally well in SQL and PL/SQL:

• REGEXP_LIKE - This is similar to the LIKE operator. Returns true if the first argument matches an expression
• REGEXP_REPLACE - This replaces a string with the given matching substring pattern
• REGEXP_INSTR - Returns the starting position of the first substring within a string that matches a regular expression
• REGEXP_SUBSTR - Returns the substring that matches a regular expression

Regular expressions are extremely useful, because they allow programmers to work with text in terms of patterns. They are considered the most sophisticated means of performing operations such as string searching, manipulation, validation, and formatting in all applications that deal with text data. Also they are used in bioinformatics to assist with identifying DNA and protein sequences. Linguists use regular expressions to aid research of natural languages. The introduction of native regular expression support to SQL and PL/SQL in the Oracle Database revolutionizes the ability to search for and manipulate text within the database by providing expressive power in queries, data definitions and string manipulations.

Application Overview

In this sample application, we implement the usage of REGEXP_LIKE regular expression function with the scenario of user registration. When the application runs, it displays a form where the user can enter his details. Two stored procedures CHECK_EMAIL and CHECK_PHONE created in the database, use the REGEXP_LIKE function to evaluate the correctness of e-mail and phone number entered by the user. When the user submits the form, the DBManager class connects to the database and executes the two user define procedures by passing the required data. If the given data is not valid then the stored procedure throws a SQL error. This error is displayed to the user in the message field. If both email and phone are valid then the user data is inserted into the database.

Software Requirements

List the softwares required for configuring and running this sample application.

• Oracle JDeveloper 10g or J2ESDK1.4.x
• Oracle Database 10g
• Oracle Database 10g JDBC Driver
  

Terminology

Term	Definition
<SAMPLE_HOME>	The directory where the sample is extracted
<JAVA_HOME>	Directory where JAVA is installed
<ORACLE_HOME>	The directory where Oracle Database is installed

Configuring the Application

• Unzip the downloaded RegExpValidate.zip. Extract the file contents into <SAMPLE_HOME> directory.
  This creates REGEXPValidate folder with all the source files.
  
• Open SQL prompt. Connect as scott/tiger and run the RegExp.sql script from <SAMPLE_HOME>/REGEXPValidate/config folder. This will create the necessary table and procedures used by this application.
  
• Edit REGEXPvalidate/config/Connection.properties file in your favorite editor. Change the HostName, Port, SID, UserName and Password to connect to your own database Example,
  
  

  HostName	=	localhost (or name of the server where database is installed)
  SID	=	ORCL
  Port	=	1521
  UserName	=	Scott
  Password	=	tiger

Deploying and Running the Application

The application can be deployed and run in either of the following ways:

• Run the Application using Oracle JDeveloper 10g
  
  
• Run the Application using JDK

Run the Application using JDeveloper 10g

This section describes the steps required in running this application using Oracle JDeveloper 10g.

• Open Oracle JDeveloper 10g and use File/Open option to select the RegExpValidate.jws from the <SAMPLE_HOME>/RegExpValidate directory
  
  
• Next, select Project/Make RegExpValidate.jpr from main menu
  
  
• Now, select Run/Run RegExpValidate.jpr from main menu. This opens up the Registration Form frame.

Run the Application using JDK

This section describes the steps involved in running this sample application.

• Open the command prompt and change the working directory to <SAMPLE_HOME>/RegExpValidate/src
• Set the classpath to include JDBC driver library ( i;e classes12.jar or ojdbc14.jar ), Connection.properties, and the present working directory. For example,
  set CLASSPATH=d:\<ORACLE_HOME>\jdbc\lib\classes12.jar;<SAMPLE_HOME>/REGEXPValidate/config;. ;
  
• Compile the classes using the following command
  javac oracle/otnsamples/plsql/*.java
  
• Run the sample by issuing the following command
  Java oracle.otnsamples.plsql.Registration
  
• This brings up the Registration Form frame. Enter the required values and "Submit" the form.
  

Sample Application Files 

This section will provide a tabular listing of the sample application files, along with their respective directory locations and a description of what they do in the overall scheme of the application.

Directory	File	Description
REGEXPvalidate\doc	readme.html	This file
REGEXPvalidate\src\oracle\otnsamples\plsql	*.java	Java source files used in this sample application
REGEXPvalidate\config	Connection.properties	The properties file that holds the database details
REGEXPvalidate\config	RegExp.sql	SQL file, used to create necessary tables and procedures to run this sample application.

Additional References 

• First Expressions
• Writing Better SQL Using Regular Expressions
  

Please enter your comments about this sample application here.