Running the University App

Running University Application

Download Source code from here PS5 version

Download Updated PS5 Source Code from here version 2

PS4 Original Version 1

Create Database User and Schema

login as sys (as sysadmin) and create university user

CREATE USER university IDENTIFIED BY welcome1;

GRANT resource, connect to university;

GRANT CREATE session, CREATE table, CREATE view,CREATE procedure, CREATE synonym, CREATE Trigger, Create Sequence to university ;

commit;

login as university user and create the required tables, sequence, triggers etc

–Step 1—————————–

CREATE SEQUENCE “UNIVERSITY”.”STUDENTSEQ” MINVALUE 1

MAXVALUE 100000 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ;

–Step 2—————————-

CREATE TABLE “UNIVERSITY”.”APPLICATIONS”

(

“ID” NUMBER NOT NULL ENABLE,

“FISTNAME” VARCHAR2(20 BYTE),

“LASTNAME” VARCHAR2(20 BYTE),

“AGE” NUMBER,

“MARKSPERCENT” NUMBER,

“RESERVATION” VARCHAR2(20 BYTE),

“ANNUALINCOME” VARCHAR2(20 BYTE),

“ADMISSIONSTATUS” VARCHAR2(20 BYTE),

CONSTRAINT “APPLICATIONS_PK” PRIMARY KEY (“ID”) USING INDEX

PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING

TABLESPACE “USERS” ENABLE

);

commit;

–Step 3———————————

CREATE OR REPLACE TRIGGER “ASSIGN_APPL_ID” BEFORE INSERT ON applications

FOR EACH ROW

BEGIN

IF :NEW.ID IS NULL OR :NEW.ID < 0 THEN

SELECT STUDENTSEQ.NEXTVAL

INTO :NEW.ID

FROM DUAL;

END IF;

END;

ALTER TRIGGER “UNIVERSITY”.”ASSIGN_APPL_ID” ENABLE;

commit;

–Step 3—————————-

CREATE TABLE “UNIVERSITY”.”CCARDS”

( “CUSTID” NUMBER,

“BANKNAME” VARCHAR2(20 BYTE),

“CARDNUMBER” NUMBER,

“PIN” NUMBER,

“BALANCE” NUMBER

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE “SYSTEM” ;

REM INSERTING into UNIVERSITY.CCARDS

Insert into UNIVERSITY.CCARDS (CUSTID,BANKNAME,CARDNUMBER,PIN,BALANCE)

values (1,’HDFC’,12345678,1234,402200);

Insert into UNIVERSITY.CCARDS (CUSTID,BANKNAME,CARDNUMBER,PIN,BALANCE)

values (2,’ICICI’,87654321,1234,4297300);

commit;

——————————————————–

— DDL for Index CCARDS_PK

——————————————————–

CREATE UNIQUE INDEX “UNIVERSITY”.”CCARDS_PK” ON “UNIVERSITY”.”CCARDS” (“CUSTID”)

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE “SYSTEM” ;

——————————————————–

— Constraints for Table CCARDS

——————————————————–

ALTER TABLE “UNIVERSITY”.”CCARDS” ADD CONSTRAINT “CCARDS_PK” PRIMARY KEY (“CUSTID”)

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE “SYSTEM” ENABLE;

ALTER TABLE “UNIVERSITY”.”CCARDS” MODIFY (“CUSTID” NOT NULL ENABLE);

–Step 4—————————-

CREATE TABLE “UNIVERSITY”.”STUDENTS”

( “STUDENTIND” VARCHAR2(20 BYTE),

“STUDENTNAME” VARCHAR2(40 BYTE),

“RESCATEGORY” VARCHAR2(20 BYTE),

“PERCENTMARKS” NUMBER,

“FEESPAID” VARCHAR2(20 BYTE),

“STATUS” VARCHAR2(40 BYTE)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE “SYSTEM” ;

REM INSERTING into UNIVERSITY.STUDENTS

Insert into UNIVERSITY.STUDENTS (STUDENTIND,STUDENTNAME,RESCATEGORY,PERCENTMARKS,FEESPAID,STATUS)

values (’10’,’Don’,’GEN’,80,’1000.0′,null);

Insert into UNIVERSITY.STUDENTS (STUDENTIND,STUDENTNAME,RESCATEGORY,PERCENTMARKS,FEESPAID,STATUS)

values (‘HDFC’,’James’,’GEN’,80,’1500.0′,null);

Insert into UNIVERSITY.STUDENTS (STUDENTIND,STUDENTNAME,RESCATEGORY,PERCENTMARKS,FEESPAID,STATUS)

values (‘HDFC’,’Mike Muller’,’OBC’,null,’1500.0′,null);

Insert into UNIVERSITY.STUDENTS (STUDENTIND,STUDENTNAME,RESCATEGORY,PERCENTMARKS,FEESPAID,STATUS)

values (‘HDFC’,’James’,’GEN’,80,’1000.0′,null);

Insert into UNIVERSITY.STUDENTS (STUDENTIND,STUDENTNAME,RESCATEGORY,PERCENTMARKS,FEESPAID,STATUS)

values (‘HDFC’,’James’,’GEN’,80,’1000.0′,null);

Insert into UNIVERSITY.STUDENTS (STUDENTIND,STUDENTNAME,RESCATEGORY,PERCENTMARKS,FEESPAID,STATUS)

values (’22’,’James Smith’,’OBC’,80,’1000.0′,null);

commit;

Creating Database Adapter Connection in EM

Setting Datasource

I assume that with the schema available in the source you have already created University Database, lets make it talk with Weblogic Server,  login to WLS Console http://localhost:7001/console , go to fmw_domain-> Services-> Data sources -> New -> Generic Datasource, create Generic Datasource by Name UniversityDS JNDI Name jdbc/UniversityDS

Select Oracle Thin Driver, Click Next ,

Database Name: orcl

Host Name: localhost

port: 1521

Database username: university

password: welcome1

Next

Driver Class Name:oracle.jdbc.xa.client.OracleXADataSource

URL:jdbc:oracle:thin:@localhost:1521:university

Properties: user=university

Test DB Connection

Next

Select Admin Server and Finish

Under Configuration -> Connection Pool tab, give required parameters and Test for DB Connection

Setting DB Adapter

go to fmw_domain-> Deployments -> DBAdapter -> Configuration -> Outbound Connection pools,

Create a NEW Outbound connection Pool ,  select javax.resource.cci.ConnectionFactory click Next set JNDI Name as eis/DB/universityDB click Finish and OK.

set xADataSourceName to jdbc/UniversityDS and hit ENTER key

Go to Deployments -> DB Adapter -> Update plan

this will redeploy

/home/james/Oracle/Middleware/Oracle_SOA1/soa/connectors/DbAdapter.rar

BAM Adapter Configuration

BAM  Adapter Configurations: Go to Oracle BAM Adapter, ensure that eis/bam/soap outbound connection is defined

check the ports, if changed update BAM Adapter deployment

JMS Adapter Configuration

Login to weblogic console . Navigate to Services -> Messaging -> JMS Module

Click on New Button, Give system module name as AdmissionJMSModule

Click Next, Select Admin and SOA Server for deployment

Click Finish and Create New JMS System module resource as Queue

Give the Queue Name as AdmissionQueue and

JNDI name as eis/jms/AdmissionQueue

Click Next, Create a New Sub Deployment AdmissionQueue-Sub

Click OK  and select this sub deployment

Select the SOAJMS Server, Click Finish

Go to deployment and select JMS Adapter and Update the Deployment

NOTE Admin Server needs restart for all these changes to take effect

Testing Credit card validation service

Changing DB Connection in Source Code

This version of source had XE as database , the current one is using orcl as DB connection string , so it might be just good to check the DB Adapter connection in the source code

Go to -> University Application -> CCValidationSyncService -> composite.xml -> hasfundsinAcc -> Edit

change XE to orcl , or anything depending upon ur database Service Name or SID

Deploy this Application

Running the Application from EM

Input ( for details read the corresponding usecase at @ Compreshensive Solutions page)

Output, Writes into Database

Student Identity card process

Deploy the application

input ( for details read the corresponding usecase at @ Compreshensive Solutions page)

Output

Setting up Oracle Service Bus – Partnet Network

Go to OSB Console at http://localhost:7001/sbconsole

Click on Create -> Project Explorer -> New project name as IDCardMakers

now click on IDCardMakers , Create Resource of type WSDL

go to Student ID card process Web service URL http://localhost:7001/soa-infra/services/default/StudentIDCardMakers/identitycardproc_client_ep?WSDL download the WSDL file into a local folder

Browse and select this WSDL , this will automatically give the name as identitycardproc_client_ep

Edit Conflicts , this will look for IdentityCardProc.xsd, pick that from JDeveloper Project file ,

Create a new Resource of type XML Schema , Copy paste from IdentityCardProc.xsd taken from JDeveloper Project, this should resolve the issue with the XSD Dependency

then create a Business Service

by name IDcardBusnServ , select WSDL from Web Service

IDCardMakers/identitycardproc_client_ep

Create Proxy Service on Top of Existing Business Service

IDCardMakerProxyService

Active Changes,

Go to Proxy Service under Actions Execute Launch Test Console

Give input and check for output , things should work fine and the Process should be executed from a OSB Proxy Service

NOTE the protocol selected is sb

Also you can avoid SOA layer and directly call OSB Service through BPM using Direct Binding

The Proxy Service URL will be

http://127.0.0.1:7001/sbresource?Proxy/IDCardMakers%2FIDCardMakerProxyService

Deploy StudentCardService

Test StudentCardService from the Console

Deploy University Admission Process

This is the final process to be that has to be deployed

Now we are all set to test the Process, for usecase, and actual end to end flow refer this Article link

Comprehensive Oracle Solutions

Download Updated PS5 Source Code from here

Art of Webcenter Templating

You can bring a corporate look and feel for your portal , please refer this article

full

About the Author

About the Author