Oracle9i XML DB Card Payment Gateway Application

This document is organized into following sub-sections :

• Overview of the sample application
• Application Usecase Diagram
• XML DB Features
• Dependencies
• Installation and Configuration
• Description of Sample files

This document describes the Card Payment Gateway sample application that demonstrates the new features of the Oracle9i XML DB (Oracle9i Release 2 onwards). This application is a concise demonstration of new XML features incorporated in Oracle9i XML DB. The application manages information of Credit Card Holders and Merchants as XML documents in XMLType tables. Every customer XML has a unique customer id and has the general information about the customer like the Name, address, e-mail id, so on. The customer can be either a Card Holder or a Merchant. The card and merchant accounts are stored in the XML document with unique Account No and other information like Account type, balance, limit, and so on. The application supports for creating accounts for Visa, Master and Amex customers.

This application provides some of the functionalities that are provided by a credit card company on the Internet. It allows the customers to log in to the credit card account and check the account details. Customers can view card balances and account details. They can also modify their profile stored with the credit card company.

Following are the important functions provided by this application:

a) Login to the CPG application --> Users can login to this application as either a Administrator or a Customer.

Application administrators can log on to the card payment gateway application and add customer records or deactivate the account.
Customers enter their login ID and password to this application. If the password is valid, the customer is allowed to log on to the application.

b) Administrators may perform the following actions:

1) Create new Customers and Accounts -->
The Admin can only create the new customers and their accounts. A customer can be of two types, Card Holder or Merchant. Card Holder is the customer who buys the Credit Card for personal use. Merchant is the commercial entity where card holders can use their Credit Cards for purchasing.

Once a Customer is added, the Accounts for the Customer can also be added. Creating a customer includes the general details of the customer like the name, address, e-mail id, type of customer, etc. Account information includes the balance, account type(Master, Visa or Amex), credit limit in case of card holders, etc.

2) Edit Customers and Accounts -->
Admin can also update details of existing Customers and Accounts. For instance, admin can update the Customer details to the current values or update the Account details to increase the credit limit.of a Card Holder or deactivate the account.

c) Customers perform the following actions :

1) Account balance and transaction reference-->
Once the customer is assigned an account, the customers can browse their account details. The Customer can see the account balance and see the previous transaction history. Customers can also view transactions between a particular period (Eg. All transactions between 15-Apr-2002 and 01-May-2002).

2) Profile Update-->
Customers can update their personal profile information through this section. Customer information is stored in XML documents in XMLType objects. This information is updated by using XML piecewise updates.

Sample Web Services client

For demonstration purposes this application provides a simple client that acts as a merchant or a service provider. This client creates Web Service client requests to simulate actual card purchases. These requests are handled by the Payment Gateway RPC style Web Service. This service either accepts or denies the transaction depending on the available credit limit and generates transaction logs if the transaction is successful.

This Web Service is used to validate the card purchases made by credit card holders at the merchant outlets. When the card purchase happens, the Merchant, using the Web Service client submits the purchase details like the Credit Card No, Amount along with the Merchant Account No. to the Web Services client. The cliet invokes the Web Service with these details. The Web service then validates the purchase against the credit card holder's Account and returns the result.

The Web Service is a Java stateless RPC style Web Service that is implemented on OC4J. The client Java class receives the inputs provided by the user through a JSP, and invokes the Web Service method with the details. The Java class method implementation will then call the PL/SQL package procedure to validate the request and will respond to the client with the result received from the PL/SQL procedure. The application will store all the valid transactions in the transaction table CPG_TRANSACTIONS.

The following picture shows how the Web Service is consumed:

• DBMS_XMLSCHEMA Package
  
  Oracle9i XML DB's XML schema functionality is available through the PL/SQL supplied package, DBMS_XMLSCHEMA. This is the server side component that handles the registration of XML schema definitions for use by Oracle9i XML DB applications.
  
  This application defines the XML schemas for the entities involved in the sample i.e for customers, card accounts and transactions. These schemas are registered inside the database using the DBMS_XMLSCHEMA package provided by Oracle9i XML DB.
  
  Use of xdb:SQLType="....." :
  Oracle9i XML DB gives certain control over the names of the OBJECT TYPES that are created during registration of an XML schema. This application uses those tags, attributes and stores customer details in XML_CUSTOMER object. Similarly, Account details are stored in XML_ACCOUNT object and transaction details are stored in XML_TRANSACTION object.
  

• XMLType Tables
  
  One can use the XMLDB features to create XMLType tables and columns. The createXML() static function of the XMLType API can be used to create XMLType instances for insertion. By storing your XML documents as XMLType, XML content can be readily searched using standard SQL queries. By associating schema with an XMLType column XML DB ensures conformance of all XMLType instances to the associated schema.
  
  XMLType tables are created to store data used by this application. These tables are constrained by specific XML schema definitions. By creating the XMLType tables based on the XML schema definitions, this application makes use of the structured storage of the XML documents inside the database. This enables the application to validate the XML documents against the XML schema automatically and store valid data inside the database. Also, the application benefits from the advantages of structured storage like performance improvements due the re-writing of queries using XPath, leveraging the object-relational capabilities, piecewise updates, having database constraints, efficient DML operations, etc.,
  

• XML piecewise updates
  
  When XMLType is stored object relationally, using XML schema mapping, updates are optimized to directly update pieces of the document. This is much faster and efficient. UpdateXML function is provided to achieve this.
  
  In this application, credit card account holders, merchants and administrators are allowed to edit and update certain data present in the tables holding the account and customer details. Whenever existing data is updated, instead of replacing entire XML content this application uses XML piecewise update facility. The piecewise update happens automatically when XML is stored in the structured object relational way through XMLType tables. So, when updateXML is used on these XMLType tables, the XML DB updates only the values that are involved in the function instead of updating the whole document.
  

• XMLType transformation
  
  In Oracle9i XML DB, XMLType instances or XML data stored in XMLType tables, columns, or views in Oracle9i database, can be (formatted) transformed into HTML, XML, and other mark-up languages, using XSL stylesheets and XMLType’s function, transform().
  
  In this application, the user interface that involves the XML data from the database will be transformed in the database to HTML using the XMLType.transform function and returned to the client program. Thus, the application benefits from the usage of the native transform capability provided by the Oracle9i XML DB.
  

• XPath Searches
  
  XPath is a W3C recommendation for navigating XML documents. XPath models the XML document as a tree of nodes. It provides a rich set of operations to “walk” the tree and to apply predicates and node test functions.
  
  In order to search the content stored in XMLType tables XPATH expressions are used in this application. The primary use of XPath in Oracle9i XML DB is in conjunction with the extract(), extractValue(), and existsNode() functions. This functionality is used in retrieving account information, updating existing XML documents, retrieving list of all transactions between a certain period of time, searching for a customer and in searching for an account. Apart from this XPATH searches are used to generate transaction reference reports.
  

• XMLType indexing
  
  Oracle9i XML DB allows users to index, search, and analyze XML content stored in the Oracle database. This makes data access and retrieval faster. Oracle9i XML DB provides unique features that enable querying for content and structure at the same time.
  
  The application involves different operations where searching of the XML data is required like selecting a customer or account to edit, getting transaction details for a period and selecting account details. For making the searching more efficient, we will create indexes on the customer id, account no, transaction id, account type, transaction dates and customer names. These will help in speeding up the searching of the customer, account and transaction information in the database.
  
  Note: The database explain plan and the performance improvements when using indexes can be viewed from application Main page and the search pages.
  
  
  
• Use of DBMS_XMLGEN
  
  Oracle9i XML DB provides different PL/SQL packages and SQL functions for accessing, retrieving and maniuplating XML content stored in the Oracle database. Thus XML DB makes it easy for the users to work with the XML content stored in the database by readily making these utility functions and packages available in the database.
  
  DBMS_XMLGEN creates XML documents from any SQL query by mapping the database query results into XML. This package is used for executing the query built when a Admin submits a search criteria for getting Customer or an Account.
  
  Go to Readme2.html