Oracle, Java, Php, Javascript, Document Management System, Mybatis

Handling null values in Oracle Select Statements

2012-02-01T21:56:00.000-08:00

Oracle Sql Select statement do not evaluate null values. Null values should be handled in the where condition of sql statements for all nullable columns. Oracle nvl function provides best way to handle null values.

create table emp(
emp_id number,
emp_name varchar2(20),
emp_age number);

alter table emp add constraint emp_pk primary key (emp_id);

insert into emp values(1,'mahesh',null);

insert into emp values(2,null,20);

insert into emp values(3,null,23);

insert into emp values(4,null,null);

An employee table contain column named emp_name that can have null values. When emp_name column is compared with "like" operator the rows with null values for emp_name columns are not evaluated by the select statement.

select * from emp where emp_name like '%';

EMP_ID EMP_NAME EMP_AGE
------ -------------------- ----------
1 mahesh

To evaluate column that can have null values, nvl function can be handled for emp_name column for null values as shown below.

select * from emp
where nvl(emp_name,'!') like '%';

EMP_ID EMP_NAME EMP_AGE
------ -------------------- ----------
1 mahesh
2 20
3 23
4

How To Make Dynamic Sql Query Using Mybatis

2012-01-15T03:06:00.000-08:00

Sql queries can be dynamic using Mybatis, may help reusing sql queries for more than one occasion. As per the context, Sql "where" conditions can be appended and executed for resultsaccordingly. Here are few samples that demonstrate the use of test conditons in Sql queries.

Sample 1 demonstrate the simple test of condition when S_EMP_NAME parameter is not null

#Sample 1
SELECT A.EMP_ID,A.EMP_NAME,B.DEPT_ID B
FROM EMP A,DEPT B
WHERE A.DEPT_ID=B.DEPT_ID
AND A.EMP_ID=#{EMP_ID}
<if test="S_EMP_NAME !=null ">
AND A.EMP_NAME LIKE #{S_EMP_NAME} || '%'
</if>

Sample 2 demonstrate the build of total "where" condition. The previxOverrides variable is "AND | OR' is generated dynamically to make the statement complete.

#Sample 2
SELECT A.EMP_ID,A.EMP_NAME
FROM EMP A
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="S_EMP_ID !=null ">
A.EMP_ID=#{S_EMP_ID}
</if>
<if test="S_EMP_NAME !=null ">
A.EMP_NAME LIKE #{S_EMP_NAME}||'%'
</if>
</trim>

Sample 3 demonstrate build of like search for EMP_NAME only when S_EMP_ID parameter is null

#Sample 3
SELECT A.EMP_ID,A.EMP_NAME
FROM EMP A
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="S_EMP_ID !=null ">
A.EMP_ID=#{S_EMP_ID}
</if>
<if test="S_EMP_ID ==null ">
<if test="S_EMP_NAME !=null ">
A.EMP_NAME LIKE #{S_EMP_NAME}||'%'
</if>
</if>
</trim>

How To Return Oracle Cursor Using Mybatis in Oracle stored procedure call

2012-01-12T03:34:00.001-08:00

In some cases it is necessary to get the set of data in the same procedure call. Sample example is demonstrated below.

Employee class to hold the single employee.

public class EMPLOYEE {
private Integer EMP_ID;
private String EMP_NAME;

public void setEMP_ID(Integer eMP_ID) {
EMP_ID = eMP_ID;
}
public Integer getEMP_ID() {
return EMP_ID;
}
public void setEMP_NAME(Integer eMP_NAME) {
EMP_NAME = eMP_NAME;
}
public String getEMP_NAME() {
return EMP_NAME;
}
}

Code To Access Cursor in java

Map map = new HashMap();
map.put("EMP_ID",11111);
myConnection.insert("cursor_exec", map);
List emp = (List) map.get("EMP");
for (int i = 0; i < emp.size(); i++) {
System.out.println(emp.get(i).getEMP_ID() || emp.get(i).getEMP_NAME() );
}

Mybatis Mapping for returning Oracle Cursor during procedure call

<resultMap id="getCursor" type="office.main.EMPLOYEE">

</resultMap>
<select id="cursor_exec" statementType="CALLABLE" parameterType="java.util.Map">
{call
proc_name(#{EMP_ID,jdbcType=NUMERIC},#{EMP,jdbcType=CURSOR,mode=OUT,javaType=java.sql.ResultSet,resultMap=getCursor})}
</select>

How To Pass or Parameterize Name of Table in Sql Select Statements in Mybatis

2012-01-12T02:20:00.000-08:00

Table name can be passed to Sql queries in Mybatis. Instead of the #{PARAM_NAME} used in general WHERE condition "${TABLE_NAME}" is used to parameterize table name .

Below is the sample example shown below.

<select id="getName" resultType="java.lang.String" parameterType="java.util.Map">
SELECT COL_NAME1 FROM ${TABLE_NAME}
WHERE PARAM_1=#{PARAM_1}
AND PARAM_2=#{PARAM_2}
AND PARAM_3=#{PARAM_3}
</select>

Function and Procedure call Mybatis

2012-01-12T02:12:00.000-08:00

Oracle functions and procedures can be executed through Mybatis called using "<select></select>" XML descriptors. Depending on the behaviour of the procedure "<update></update>" and "<delete></delete>" descriptors can also be used. Sample of function and procedure call is presented below.

<select id="getEmpName" parameterType="java.util.Map" resultType="java.lang.String">
select getEmpName(#EMP_ID) from dual
</select>

<update id="proc_emp_update" statementType="CALLABLE" parameterType="java.util.Map" >
{call
emp_update(#{EMP_ID},#{EMP_NAME},#{DEPT_ID,jdbcType=NUMERIC},#

{DEPT_NAME,jdbcType=VARCHAR,mode=OUT})
}
</update>

The getEmpName function returns employee name. The statementType="CALLABLE" knows that is the procedure invocation. The jdbcType=NUMERIC must be provided for nullable fields during the procedure invocation. The jdbcType can be supported data type provided by Mybatis such as jdbcType=DATE for Date , jdbcType=VARCHAR for String, jdbcType=CURSOR for Cursor. mode=OUT defines that it is out parameter returned by the procedure.

Using Sql Queries in Mybatis

2012-01-12T01:10:00.000-08:00

Mybatis provides XML descriptors such as "<select></select>", "<insert></insert>", "<update></update>","<delete></delete>" for SQL DML statements. The DML statements can be accessed through "id" property in the XML descriptors. The "parameters" descriptor defines the user parameters passed to the DML statements and "resultType" descriptor defines the type of result the SQL statement returns. The parameters are used in the SQL statments with #{PARAMETER_NAME}. Here is the sample of basic DML operations in Mybatis.

<select id="getEmployee" parameterType="java.util.Map" resultType="java.util.Map">
select * from employee
where emp_id=#{EMP_ID}
and emp_name like #{EMP_NAME}||'%'
</select>

<insert id="insertEmployee" parameterType="java.util.Map">
insert into employee(emp_id,emp_name) values (#{EMP_ID},#{EMP_NAME})
</insert>

<update id="updateEmployee" parameterType="java.util.Map">
update employee set emp_name=#{EMP_NAME}
where emp_id=#{EMP_ID}
</update>

<delete id="deleteEmployee" parameterType="java.util.Map">
delete from employee
where emp_id=#{EMP_ID}
</delete>

Unlike SQL statements in oracle semi-colon must be avoided in the DML statements at the end.

Using CASE WHEN Oracle SQL

2012-01-04T00:02:00.000-08:00

An example demonstrate using CASE WHEN in Oracle Sql.

CREATE TABLE DEPT(
DEPT_ID NUMBER,
DEPT_NAME VARCHAR2(100));

ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY (DEPT_ID);

CREATE TABLE EMP(
EMP_ID NUMBER,
EMP_NAME VARCHAR2(100),
DEPT_ID NUMBER);

ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID);

ALTER TABLE EMP ADD CONSTRAINT EMP_FK FOREIGN KEY (DEPT_ID)
REFERENCES DEPT(DEPT_ID);

INSERT INTO DEPT VALUES(1,'Human Resource');

INSERT INTO EMP VALUES(100,'Employee One',1);

INSERT INTO EMP VALUES(101,'Employee Two',null);

COMMIT;

SELECT A.EMP_ID,A.EMP_NAME,A.DEPT_ID,
CASE
WHEN A.DEPT_ID IS NOT NULL THEN
(SELECT DEPT_NAME FROM DEPT WHERE DEPT_ID=A.DEPT_ID)
ELSE
''
END DEPT_NAME
FROM EMP A

Object Programming in Oracle

2012-01-02T20:40:00.000-08:00

This article demonstrate how to code object oriented program in oracle. An employee object type is created with two properties EMP_ID and EMP_NAME. Another type TBL_EMP is created used to hold employees. Next employee table named emp is created that has same properties as object OBJ_EMP.

The procedure INSERT_EMP takes TBL_EMP type as its only IN parameter. If the count is greater than zero the program inserts into emp table.

CREATE OR REPLACE TYPE OBJ_EMP AS OBJECT (EMP_ID NUMBER , EMP_NAME VARCHAR2(100));

CREATE OR REPLACE TYPE TBL_EMP AS TABLE OF OBJ_EMP;

create table emp (
emp_id number,
emp_name varchar2(100)
);

alter table emp add constraint emp_pk primary key (emp_id);

CREATE OR REPLACE PROCEDURE INSERT_EMP( P_EMP TBL_EMP)
IS
v_obj_emp OBJ_EMP;
BEGIN

--insert into emp select * from table(p_emp);

IF P_EMP.COUNT > 0 THEN
FOR i IN 1 .. P_EMP.COUNT LOOP
v_obj_emp := P_EMP( i );

INSERT INTO emp ( emp_id,emp_name )
VALUES ( v_obj_emp.emp_id,v_obj_emp.emp_name);
END LOOP;
END IF;

END;
/

The INSERT_EMP procedure is invoked from anonymous block of plsql below. Here an employee object is intialized and table of employee type created. Employee type object t_emp is extend to create a row in the table object. The index of table object start with one which we assign to p_obj_emp object initially created. The t_emp table object is extended again and assigned to newly intialized p_obj_emp. Finally, INSERT_EMP procedure is invoked with t_emp object as parameter.

declare
p_obj_emp obj_emp := new obj_emp(1,'mahesh prajap');
t_emp tbl_emp := new tbl_emp();

begin
t_emp.extend();
t_emp(1):=p_obj_emp;

t_emp.extend();
p_obj_emp.emp_id:=2;
p_obj_emp.emp_name:='aarav prajap';
t_emp(2):=p_obj_emp;
INSERT_EMP(t_emp);
end;
/

The object programming concept in oracle can be evenly used with object oriented programming languages like java. List of objects can be passed directly to oracle procedure so that we can manipulate those passed objects in oracle as tables. There will be need and requirement in business logic that whole object be passed as tables and oracle plsql procedure takes control over the business procedures. The single object procedure call makes multiple call to procedure that makes more connection to oracle database and much of the time may be spent on such database connections.

Document Management System Guide Part 2

2011-11-15T22:27:00.001-08:00

Registered user can login to the application through their user name and password. The URL can be obtained from the system administrator of Document Management System.

Customer Registration

Customers involved in the business process can be registered. This helps in generating report based on the customer identity.

Document Transaction Steps and flow diagram

1. Document Entry.
2. Document Forward.
3. Document Review.
4. Document Authorize.
5. Document Reject

Document Transaction Generation

This is the document transaction generation module. In the screen shot Document Type, Customer list and forwarder lists is auto populated from the initial setups. User can select appropriate values from the list to generate document transaction. A document may contain multiple files as shown below in the screen shot.

Some Useful Notes:

1. Document Type is user defined and parameterized document. It helps to differentiate the type of document generated. As per the configuration, a document number may contain document short string, document date and document serial number.eg AO060720100000000019.

2. “Forward To” user field is the default user the document to be forwarded to. It must not be empty. The block “User Forwarder List” contain list of users in the priority list to be forwarded one by one from left side simultaneously. If a default user, that is user in “Forward To” field do not respond within DOCUMENT TIME OUT (DOCUMENT_TIMEOUT) parameter default set to 30 minutes, then the document is revoked from “Forward To” user (admin) and auto forwarded to first user in User “Forwarded List” (surendras). If “surendars” do not respond within DOCUMENT TIME OUT, then the document is revoked from the user and auto forwarded to user “dhrubab”. During the document auto forward process, whenever any one of the user (admin, surendras, dhrubab or malikad) respond, the users in the forwarder chain are cancelled from the priority list. Then the document is processed as per the action (FORWARD, REVOKE, AUTHORIZE, REJECT) taken by the forwarder user.

3. Document No: It is system generated number to uniquely identify the document being generated. Document number generated depends upon the configuration of document type definition. A Document Number can contain multiples related files with the document. Screen shot show a Bank Guarantee document that contain citizen ship certificate and guarantee fill up form in portable document format.

4. + sign button indicates the addition of the file in the document to be generated. Clik on the + sign button to add new row to the document.

5. – sign button on each row indicates the deletion of the file from the document. Click on the –sign button to delete the current row from the document.

6. Remarks on the each document can be included in the remarks field that should be informative to the user receiving the document. Remarks are also show on document flow report.

7. The application supports files names in portable document format (PDF) for preview during document processing. Files in other formats are not available for preview but they can be downloaded to the local computer and opened as per user demand. It is suggested to process files in portable document format as the time to generate preview for portable document format is much lesser than file downloads. Avoid processing document files other than portable document format (PDF) to minimize bandwidth usage.

8. Extensions of filenames are mandatory in “File Name” field. The system returns error and file could not be transmitted if extension are avoided. For instance, if only name of file “citizen” is placed in “File Name” field, application would return error during file upload process. So, it is required to enter extension of the file with their file name that is “citizen.pdf” in “File Name” field.

9. Files must be stored in Application Home Directory default to “C:\docman”. The default Application Home Directory can be changed upon request to the administrator.

10. “Save” Button saves the files and generates a unique Document Number for transaction.

11. Email alert is sent to the default user (admin) upon Document Transaction Generation.

12. Refer to the screen shot shown below.

Screen shot of Document Transaction Entry:

Document Forward/Revoke/Authorize/Reject

The Document Authorize/Forward/Reject/Review grid shows the list of document forwarded to login user for approval. Click on the record to display the details of the document. Document file in portable document format (PDF) can be previewed while other file formats need to be downloaded to the local computer.

As per the requirement, a document can be processed for:
1. REVIEW document.
2. FORWARD for next level approval.
3. AUTHORIZE document.
4. REJECT document.

User Actions:

1. REVIEW Document
Whenever modification is required in the document, it is sent for review. User performing review can be same user that forward the document or any other user in the user list.

2. FORWARD Document
Whenever approval is sought from next authority level, then the document is forwarded to the respective user.

3. AUTHORIZE Document
Permit the document to carry on the business process.

4. REJECT Documents
Disallow to process the business activity.

A remark is mandatory while processing the document. The document recipient can track the state of the document and business process from the remarks as well. So, remarks must be precise and clear that can be taken as reference to authenticate the document. Remarks are also shown on document flow report.

Screen shot of File preview

Invalid Documents:

During document transaction processing, if file name specified in file name field does not exist in Application Home Directory, the resulting document number is marked as Invalid Document. Invalid Documents can be processed from Invalid Document menu from “Invalid Document” Grid as show in the screen shot below. Respective files can be placed in Application Home Directory and process “Upload File” button to validate the resulting document.

Email Notification and Alerts

Email Notification in the form of alerts is sent to the user that have been forwarded a document or sent for review. During AUTHORIZE and REJECT of document, email is sent to all the users involved for the document processing. This will help user to know the state of the document as well.

Background process for Auto Email Notification can be started and stopped from Email Notify menu.

User Reports

Reports are embedded in the application and rendered in Portable Document Format (pdf).

Screen shot of List of Documents

Screen shot of Document Flow Report

Document Management System Guide Part 1

2011-11-13T02:05:00.000-08:00

Registered user can login to the application through their user name and password.

Context Menu is loaded at the left hand side panel and log in information is shown at the right side panel. Middle panel is the working area where forms are loaded.

Usage of Refresh Button:

Clear And Reload Form:

Refresh button has been placed at the top of each form. It helps to clear and reload form without visiting context menu at right hand side panel.

Retrieval of Online Data:

The data grid (Manage User) is not auto loaded after modification in the existing record and after creation of new record (user) as well. For example, if new user is registered, need to press refresh button to load the new user registered (changes) in the “Manage User” grid.

Data is loaded on the grid for further processing. When user clicks on the specific record, the details are loaded on the processing block (User Register), so that it can be modified as required. The user form controls such as “Add”, “Update”, and “Delete” buttons are loaded as per role granted to the user and their access on the each working forms.

Search in Drop down List:

The drop down list can be used for searching your required data as shown in the screen below. The list searches your data on each “Enter Key”. Type your text, to search in the list and enter the “Enter Key”. The list then returns filtered or matched data to the user that can be selected.

Role Based Access Control Mechanism

Role can be created from Manage Role menu. Required forms can be selected from the list and privilege on specific controls can be permitted and revoked. Mark required controls (“Insert”, ”Update”, ”Delete”, ”Cancel”, ”Verify”) to provide access on forms and unmark those controls that are not permitted to the role. There is no need to grant access permission on each form to the users. The user belonging to the role directly has access control to the role forms.

Steps for Role Creation:
Add
1. Enter Role Name, Description and select Authority level from list.
2. Select Add Group Authority and check mark the record to give access in the forms. Specific operation can be allowed and disallowed by marking and unmarking the operation.
3. The press “Add” button to save the Role information.

Screen shot of Role creation (First Tab View)

Screen shot of Role creation (Second Tab View)

Modify
1. Select record from the “Role Setup” data grid.
2. Make necessary changes.
3. Press “Update” button.

Delete
1. Select record from the “Role Setup” data grid.
2. Press “Delete” button.

User Creation and password management

User can be created with ease with necessary information on “User Register” block. The user belonging to the role can be selected from the Role list. The user receives access permission from the role assigned. Date can be selected from the date box. Valid email address is required. This email address is used during document Forward process.

Steps for User Creation:

Add
1. Enter username and password.
2. Select Role, Authority from list.
3. Enter valid email address, date and employee id for the user.
4. The press “Add” button to save the user information.

Modify
1. Select record from the “Manage User” data grid.
2. Make necessary changes.
3. Press “Update” button.

Delete
1. Select record from the “Manage User” data grid.
2. Press “Delete” button.

Screen shot user creation

Define your own Document Type

DMS allows define your own document involved in the business process. New document type can be added as a part of the business process. Document number is generated based on the document type definition. The document number is parameterized and can include two digit message string, document message date and ten digit message serial numbers. Document number AO060720100000000019 represents Account Open document on 6th July 2010 with serial number 19. This document category has starting serial number set to zero.

System Parameters

System parameters define the behavior of the application.

1. EMAIL_NOTIFICATION :
Enable or disable email notification service in the application. The value of “Y” enables the email notification service else not.

2. DOCUMENT_TIMEOUT:
Timeout in minutes for processing forwarded documents. If recipient do not respond to the document for DOCUMENT_TIMEOUT period, then document is marked as timeout and forwarded to next user in the priority list.

3. AUTO_FORWARD_TIME:
Time specified in minutes, application waits to process document in priority list.

4. DOC_REPOSITORY:
It is the central repository for storing the processed documents.

5. WORKING_DIRECTORY:
It is used temporarily during file download process.

6. MAX_FILE_TRF_SIZE:
Maximum File Transfer Size in Bytes application can handle. The application will not entertain file size greater than MAX_FILE_TRF_SIZE parameter.

Screen shot of the system parameter is shown below:

Code to Pass ArrayList From java to Oracle Database

2011-11-09T01:45:00.000-08:00

package com.test;

import java.sql.*;
import java.sql.Connection;

public class Conn {
static String url = "jdbc:oracle:thin:@localhost:1521:oradb";

static String user = "username";

static String pass = "password";

public static Connection getConnection() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(Conn.url, Conn.user,
Conn.pass);
return conn;

} catch (Exception ms) {
ms.printStackTrace();
return null;
}
}

public static void main(String ss[]) {
Connection conn = Conn.getConnection();
}

}

public class TableTest {
int id;
String name;
String attribute1;
String attribute2;

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public String getAttribute1() {
return attribute1;
}

public void setAttribute1(String attribute1) {
this.attribute1 = attribute1;
}

public String getAttribute2() {
return attribute2;
}

public void setAttribute2(String attribute2) {
this.attribute2 = attribute2;
}

}

import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.OraclePreparedStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

public class OracleStoredProcedure {

private final String ORACLE_STRUCT = "T_TYPE";
private final String ORACLE_ARRAY = "TB_T_TYPE";

public void insertAll(List records) {
Connection conn = null;
StructDescriptor structDescriptor = null;
ArrayDescriptor arrayDescriptor = null;
int iSize = records.size();
Object[] arrObj = null;
Object[][] recObj = null;
try {

conn = Conn.getConnection();

structDescriptor = StructDescriptor.createDescriptor(ORACLE_STRUCT,
conn);
arrayDescriptor = ArrayDescriptor.createDescriptor(ORACLE_ARRAY,
conn);

arrObj = new Object[3];
recObj = new Object[iSize][3];
// Structuring obj and arrays
for (int j = 0; j < iSize; j++) {
TableTest ob = (TableTest) records.get(j);
recObj[j][0] = ob.name;
recObj[j][1] = ob.attribute1;
recObj[j][2] = ob.attribute2;

// arrObj[j] = new STRUCT(structDescriptor, conn, recObj[j]);
}
ARRAY arr = new ARRAY(arrayDescriptor, conn, recObj);
oracle.jdbc.OraclePreparedStatement preparedStatement = (OraclePreparedStatement) conn
.prepareStatement("{call POPULATE_TABLE_TEST (?)}");
preparedStatement.setArray(1, arr);
preparedStatement.execute();

} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();

}
} catch (SQLException e2) {
e2.printStackTrace();
}
}
}

public static void main(String a[]) {
List list = new ArrayList();
TableTest ab = new TableTest();
ab.setId(1);
ab.setName("user1");
ab.setAttribute1("m1");
ab.setAttribute1("m2");
list.add(ab);

TableTest abc = new TableTest();
abc.setId(2);
abc.setName("user2");
abc.setAttribute1("a1");
abc.setAttribute1("a2");
list.add(abc);
new OracleStoredProcedure().insertAll(list);

}
}

Javascript : Apply method

2011-11-09T00:49:00.000-08:00

Javascript functions are objects with spcified property named apply. The apply method is used as the constructor of the object. The object is passed as the first parameter to the apply method. The second parameter is always an array used to initialize the object being passed with the array object.
A function named Person has three parameters and initialize the object property with the passed arguments. Here "this" refer to the object that invoke the apply method ie Aperson object. Aperson function applies Person attributes with itself as first argument and array as its parameters.

function Person(firstname,middlename,lastname)
{
this.firstname = firstname;
this.middlename= middlename;
this.lastname = lastname;
}

function Aperson(age, firstname, middlename, lastname)
{
this.age = age;
Person.apply(this, new Array(firstname, middlename, lastname))
}

aperson = new Aperson(31,"Bill","Jill","King")
console.log(aperson.firstname + " " + aperson.middlename + " " + aperson.lastname + " is " + aperson.age +" years old.") ;

Output
Bill Jill King is 31 years old.

Javascript: Array functions Push, Unshift, Pop, Shift

2011-11-08T23:52:00.000-08:00

Push : The push method adds an element to the end of the array. If the method takes more than one elements, they are appended from left to right order.

var arraydata = ['a','b'] ;
arraydata.push('c');
arraydata.push('d','e');
console.log(arraydata);

Output
['a','b','c','d','e']

Unshift :The unshift method adds an element to the start of the array. If the method takes more than one elements, they are appended from right to left order.

var arraydata = ['b','a'] ;
arraydata.unshift('c');
arraydata.unshift('e','d');
console.log(arraydata);

Output
["e", "d", "c", "b", "a"]

Pop: The pop method removes an element from the end of the array and returns it.

var arraydata= ['a','b','c','d','e'];
console.log(arraydata.pop());
console.log(arraydata);

Output:
e
["a", "b", "c", "d"]

Shift : The shift method removes an element from the start of the array and returns it.

var arraydata= ['a','b','c','d','e'];
console.log(arraydata.shift());
console.log(arraydata);

Output:
a
["b", "c", "d", "e"]

arguments property of Javascript function

2011-11-08T23:25:00.000-08:00

Javascript arguments property inside any function is array of function parameters passed. An arguments property is available only inside the function. Any function parameter can be accessed from the arguments property as usual array indexed by number starting from 0. Lets take an example.

argumentExample=function()
alert(arguments.length);
for loop counter between 0 and arguments.length
alert(arguments[counter]);

argumentExample("firstItem","secondItem",232,"fourthItem");

Callback in javascript

2011-11-08T21:43:00.001-08:00

We create function normally to get arguments and return specific type of value in most of the programming languages. But functions in javascript behaves different. Function in javascript are objects that has predefined properties.

In javascript we can pass function as an argument and execute that passed function from the called function. We can execute passed function from the called function using the predefiend function property "call". The first parameter to the call back function acts as scope of the function and other parameters can be accessed normal way.

Lets take an example. Here function named "func" is the main function that takes an argument. Inside the function object "p" is created that has two property named "name" and "lastname". Similarly "company" object is created with property named "name" and "address". Also variable named "fullname" is created.

Another function test is created with two parameters "param1" and "param2". Here alert is given for the passed parameters and scope provided to the test from the called function.

Now we call the function "func" with the function "test" as parameter.
The line
a.call(p,fullname,company);
calls back the passed function "test". The first parameter "p" here acts as scope of the function during callback and can be accessed using keyword "this" as done on lline
alert(this.name+' ' +this.lastname);

func=function(a){
p={};
p.name='myname';
p.lastname='mylastname';
var fullname= 'abc xyz ';
company = new Object();
company.name='My Tech P. Ltd';
company.address='Nepal';
a.call(p,fullname,company);
}

test=function(param1,param2){
alert(param1);
alert(param2.name+param2.address);
alert(this.name+' ' +this.lastname);
}

func(test);

Program to send Email in Php with Authentication

2011-11-08T03:59:00.001-08:00

PEAR Installation

PEAR is an extension for PHP. These days PEAR is included with PHP, but you need to install it yourself. I use PEAR on all my PHP developments.

Installing PEAR:

Open a command line window (i.e. Start->Run->cmd)
Go to the PHP directory, in my case C:\php. Type go-pear.bat. Follow the instructions.
The PEAR extension get installed in C:\php\PEAR directory.
Once PEAR is installed, go to the php.ini file in your Apache2 directory. Find the

Code:
;include_path = ".;c:\php\includes"

Remove the semi-colon (to un-comment it), and then add C:\php\PEAR to it

Code:
include_path = ".;c:\php\includes;C:\php\PEAR"
;include_path = ".;C:\wamp\php\includes;C:\wamp\php\PEAR"

---------------------------------------------------------------------------------------------
** The 'pear' command is not currently in your PATH, so you need to
** use 'c:\wamp\php\pear.bat' until you have added
** 'C:\wamp\php' to your PATH environment variable.

* WINDOWS ENVIRONMENT VARIABLES *
For convenience, a REG file is available under C:\wamp\php\PEAR_ENV.reg .
This file creates ENV variables for the current user.

Double-click this file to add it to the current user registry.

-----------------------------------------------------------------------------------------------
#########Pear mail package Installation################

go to http://pear.php.net/package/Mail/download/ for supported Mail packages

pear install Mail-1.1.14
pear install --alldeps Mail-1.1.14

########Email with authentication#############

";
$to = "XYZ ";
$subject = "Hi!";
$body = "Hi,\n\nHow are you? This is test php mail.";

$host = "mail.domain.com";
$username = "user@myserver.com";
$password = "sdfdsf";

$headers = array ('From' => $from,
'To' => $to,
'Subject' => $subject);
$smtp = Mail::factory('smtp',
array ('host' => $host,
'auth' => true,
'username' => $username,
'password' => $password));

$mail = $smtp->send($to, $headers, $body);

if (PEAR::isError($mail)) {
echo("

" . $mail->getMessage() . "

");
} else {
echo("

Message successfully sent!

");
}
?>

Document Management System

2011-11-06T02:31:00.001-08:00

Content

About Document Management System.
How to use Document Management System.
Customer Registration.
Document Transaction steps and flow diagram.
Document Transaction Generation.
Document Forward/Revoke/Authorize/Reject.
Invalid Document.
Email Notification and Alerts.
User Reports.

Document Management System

A document management system (DMS) is a computer system (or set of computer programs) used to track and store electronic documents and/or images of paper documents. Document management systems are becoming more important as it becomes increasingly obvious that the paperless office is an ideal that may never be achieved. Instead, document management systems strive to create systems that can handle paper and electronic documents together.

Document management system handles documents in such a way that information can be created, shared, organized and stored efficiently and appropriately. The focus of document management system is on the organization and storage of documents. Documents are stored in an organized and secure way and allow documents to be found easily. There is a move within large organizations to use document management software to help them do this. This system is designed to make handling electronic files more efficient and effective way.

Document Management system enables the automatic routing of documents to the user responsible for working on them. Enterprises can rely on documents to be processed efficiently through a step-by-step process and there is no risk that they can be lost or overlooked. Workflow can be defined for the document processing and ensures that documents are forwarded to the appropriate user automatically at specified times, alerting the user of the necessity to process them through emails. This system ensures the smooth flow of documents through the enterprise.

In any enterprise it is vital that the security of electronic documents is appropriately managed. Document Management system ensure that access to the system is restricted only to those with the correct access permissions, both to ensure the integrity of data and to reduce the number of documents presented to the user only to those documents that are relevant to his or her role.

Document Transaction Steps and flow diagram

Document Entry.
Document Forward.
Document Review.
Document Authorize.
Document Reject

Technical Details

Full Open Source Implementation
ZK
JDK 1.6
Spring Web Application Development Framework
Apache Tomcat v6/ Jetty
MySql
Rsync
Jasper Reports
Linux/Fedora
Enterprise Eclipse Platform for Software Development

Core Features

Web Based Application
Role Based Menu Access Control
User Access Control
User File Management
Auto Email Notification
Reports on Portable Document Format(PDF)
Version Control
Security/Access Control
Support Multiple File Types
Reports/Metrics
Backup/Recovery

Switch the default system temporary tablespace

2009-11-12T22:07:00.000-08:00

create temporary tablespace temp_new tempfile
'd:\oracle\oradata\test\temp1.dbf' size 500m autoextend on next 10m maxsize unlimited
extent management local;

Switch to new temporary tablespace. You may need to switch the temporary tablespace to new one to reduce the size of the current tempfile and remove it.

alter database default temporary tablespace temp_new;

You may not switch until the tablespace is in use.

Drop the old one .

drop tablespace temp_od including contents and datafiles;

Switch undo to new undo tablespace Oracle 9i onwards

2009-11-12T21:58:00.000-08:00

Create New Undo Tablespace

create undo tablespace undo_new datafile
'd:\oracle\oradata\test\undodata1.dbf' size 1024m autoextend off maxsize unlimited online;

Issue these command one or more times to force the commited data write in datafiles.

alter system checkpoint;

alter system switch logfile;

alter database archive log current;

Modify the SIDinit.ora file parameter

undo_tablespace=undo_new

Shutdown the database

shutdown immediate;

Check the parameter value

show parameter undo_tablespace

Drop the old undo tablespace and physical datafiles

drop tablespace undo_old including contents and datafiles;

Find IP Address and Host Name of Oracle Server from Client Environment

2009-10-25T22:28:00.000-07:00

Invoke the following section to get the Oracle Database Server IP Address from Client Environment.

set serveroutput on
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME); -- get local host name
DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_ADDRESS); -- get local IP addr
END;
/

Get the IP Address of the client

select SYS_CONTEXT('USERENV', 'IP_ADDRESS', 15) ipaddr from dual;

Move or Rename Oracle Datafiles

2009-09-30T01:14:00.000-07:00

Move or rename the datafiles from current location to another one. Then startup the database in mount mode as:

startup mount

Then rename the datafile as below:

alter database rename file '/abc/mydatafile.dbf' to '/xyz/mydatafile.dbf';

This will update the location of datafile in control file. Then open the database as

alter database open;

You can now view the location of datafile from the v$datafile view.

Identify Oracle Patches Applied in AIX Server

2009-09-30T00:32:00.001-07:00

List of installed patches

$ORACLE_HOME/OPatch/opatch lsinventory

Detail List of installed patches

$ORACLE_HOME/OPatch/opatch lsinventory -details

Enviornment variable $ORACLE_HOME must be set.

AJAX Frameworks with Server-Side Java Support All Open Source

2009-08-09T00:48:00.000-07:00

DWR - DWR (Direct Web Remoting) is a way of calling Java code on the server directly from JavaScript in the browser. DWR consists of two main parts: JavaScript running in the user's browser to communicate with the server and dynamically update the web page, and a Java Servlet running on the server that processes requests and sends responses back to the browser. DWR takes a novel approach to AJAX by dynamically generating JavaScript code based Java classes. Thus the web developer can use Java code from JavaScript as if it were local to the web-browser; whereas in reality the Java code runs in the web-server and has full access to web-server resources.

JSON-RPC-Java - JSON-RPC-Java is a dynamic JSON-RPC implementation in Java. It allows you to transparently call server-side Java code from JavaScript with an included lightweight JSON-RPC JavaScript client. It is designed to run in a Servlet container such as Tomcat and can be used with JBoss and other J2EE Application servers to allow calling of plain Java or EJB methods from within a JavaScript DHTML web application.

AjaxTags - The AJAX Tag Library is a set of JSP tags that simplify the use of Asynchronous JavaScript and XML (AJAX) technology in JavaServer Pages. This tag library fills that need by not forcing J2EE developers to write the necessary JavaScript to implement an AJAX-capable web form. The tag library provides support for live form updates for the following use cases: autocomplete based on character input to an input field, select box population based on selections made from another field, callout or balloon popups for highlighting content, refreshing form fields, and toggling images and form field states on/off.

Echo 2 - Echo2 is the next-generation of the Echo Web Framework, a platform for developing web-based applications that approach the capabilities of rich clients. The 2.0 version holds true to the core concepts of Echo while providing dramatic performance, capability, and user-experience enhancements made possible by its new Ajax-based rendering engine.

AjaxAnywhere - AjaxAnywhere is designed to turn any set of existing JSP components into AJAX-aware components without complex JavaScript coding.

ActiveMQ Ajax Support - Ajax support in ActiveMQ builds on top of the REST connector for ActiveMQ which allows any web capable device to send or receive messages over JMS. All the Ajax examples are currently using OpenRico.

Tacos - The Tacos library project provides components and ajax behaviour for the Tapestry java web application framework. Most of the library relies almost exclusively on Dojo.

WebWork 2.2 Awesome AJAX support built on top of DWR and Dojo. Form validation, tabbed panels, remotable forms, and remote divs. More AJAX components will be coming in subsequent releases.

Simple Web Framework - The Simple Web Framework (SWF) is an event based framework targeting Struts developers who want to build rich Web applications but do not want to migrate to JSF. The SWF is built upon the same Jakarta commons basis as Struts, but uses a different request processor (front controller.) The SWF event model supports XmlHttpRequest (as well as form/submit) based event posting, similar to VB.NET or JSF, with In place Page Updating (IPU) rather than page reloading.

Wicket 1.1 rc2 - Dojo, Scriptaculous and Wicket integration. The release consists of an Ajax handler, allowing you to create your own custom Wicket Ajax components based on the Dojo toolkit.

Taconite - The heart of Taconite is a parser that converts normal (X)HTML code into a series of JavaScript commands that dynamically create the content on the browser. This parser allows you, the developer, to write content in way that is natural -- as (X)HTML. You no longer have to crowd your pages with a slew of document.createElement and document.appendChild commands to dynamically create new content. The Taconite custom parser is implemented as a set of JSP custom tags, which can be used in any Java servlet container, or as a client-side JavaScript library, meaning it can be used in conjunction with virtually any server-side technology.

SWATO - Server side Java library can be deployed in Servlet 2.3+ compatible containers. Client side JavaScript library is base on Prototype. JSON based marshalling. Spring integration. Includes several reusable components like auto-suggest Textbox, Javascript templates and logging.

Zimbra - Zimbra is an open source server and client technology for AJAX based messaging and collaboration. The collaboration server is built using Java based technologies. The server features file based message storate, a SQL metadata storage, Lucene based search, clustering, replication, archiving and LDAP support.

Orbeon Forms - An XForms engine much improved over OPS 2.8's, significant improvements in the Page Flow Controller, and more. The XForms engine is based on Ajax technologies. This makes the XForms engine much more responsive to user interaction than with OPS 2.8.

JsOrb - JsOrb includes code generators that build on demand JavaScript classes for your POJOs and as proxies to your business logic interfaces. The JavaScript classes have the same methods as your POJOs and business logic interfaces, so your JavaScript code ends up looking surprisingly similar to Java.

ZK - ZK is an event-driven, XUL-based, AJAX-embedded, all Java framework to enable rich user interfaces for Web applications. With ZK, you represent and manipulate RIA in XUL/HTML components all at the server similar to how you developed desktop apps. ZK includes an AJAX-based event-driven engine to automate interactivity, and a rich set of XUL-based components.

Rialto - Rialto is a cross browser javascript widgets library. It supports pure javascript development and JSP/taglib development. A JSF integration is planned.

JSP Controls Tag Library - JSP Controls Tag Library (the "Library") provides the lifecycle for portlet-like JSP components. The Library does not require a portal engine or other central controller. The components built with the Library can be used in any JSP-based application. The Library supports two request processing modes: Non-Ajax and Ajax. Pages composed with JSP Controls Tag Library look and behave the same way whether they run in Ajax mode or not.

OpenLaszlo - OpenLaszlo programs are written in XML and JavaScript and transparently compiled to Flash and DHTML. The OpenLaszlo APIs provide animation, layout, data binding, server communication, and declarative UI.

XAP - The basic intent of XAP is to leverage existing Ajax projects such as Apache Kabuki and Dojo, as well as other community efforts such as Eclipse openAjax. It aims to be pluggable with various Ajax toolkits, reduce the need of scripting and solve the development challenge as well as application maintenance challenges associated with Ajax programming.

AJAX4JSF - Ajax4jsf fully leverages the benefits of the JavaServer Faces framework including lifecycle, validation, and conversion facilities, along with management of static and dynamic resources. Ajax4jsf enables page-wide AJAX support instead of the traditional component-wide support. This means you can define the event in the page that invokes an AJAX request and then the areas of the page that should be synchronized with the JSF Component Tree after the AJAX request changes the data on the server.

ThinkCAP JX - ThinkCAP's core is an advanced MVC framework that manages the runtime layout and presentation of GUI components, state management, data binding, validation, and data persistence. The framework is driven by Java classes and XML metadata generated by the Workbench. The current 6.0 version is very Struts-like.

jMaki - jMaki is all about enabling Java developers to use JavaScript in their Java based applications as either a JSP tag library or a JSF component.jMaki uses the best parts of Java and the best parts of JavaScript to deliver a rich AJAX style widgets. jMaki currently provides bootstrap widgets for many components from Dojo, Scriptaculus, and Yahoo UI Widgets. Also included in this project are a set of AJAX widgets with a focus on Web 2.0. Included are a RSS widget, a del.icio.us Bookmark widget, a Chat widget, and many more to come.

GWT - Google Web Toolkit (GWT) is a Java software development framework that makes writing AJAX applications like Google Maps and Gmail easy for developers who aren't experience with javascript. GWT lets you avoid tedious and error-prone javascript programming while offering your users the same dynamic, standards-compliant experience. You write your front end in the Java programming language, and the GWT compiler converts your Java classes to browser-compliant JavaScript and HTML.

Helmi - Helmi's Open Source RIA Platform permits client- and server-side engineers to work independently while communicating effectively through an object-oriented environment. This solution is composed of a 100% browser-based Client Framework, a Virtual Browser that enables cross-browser application operations, an Integrated Development Environment (IDE), and a J2EE-based AJAX Server Connect that simplifies transmitting data from server to client.

Open jACOB - Open-jACOB is a Java rapid application development tool (RAD) based on Eclipse that enables the development of Rich Internet Applications (RIA) that are based entirely on Java. The primary goal of the jACOB Framework is to handle web applications with CRUD (create, retrieve, update, delete) with little or no Java programming. Instead of telling the application how to retrieve and how to display the data. The developer creates the Web-UI with a point&click eclipse UI-Designer.

ThinWire - ThinWire is an open source development framework that allows you to easily build RIA applications. Notable features include a familiar event-driven GUI programming model, state maintenace via variables and not via session, exclusively server-side language and supports all major browsers.

ICEfaces - ICEfaces is a RIA framework that delivers unique Ajax Push capabilities. Discover the power of Ajax Push and create collaborative and dynamic enterprise applications. ICEfaces leverages the entire standards-based Java EE ecosystem of tools and execution environments. Rich enterprise application features are developed in pure Java, and in a pure thin-client model. ICEfaces is based on JavaServer Faces (JSF).

Managing Online Transaction Processing Oracle Database (OLTP)

2009-05-21T04:38:00.000-07:00

In an OLTP system, several factors may effect the performance of the oracle database. The administrator should configure database parameters taking into account the numbers of synchronous users logging into the system at time and the type of processes they carry out. The administrator should also have knowledge about the way in which the application manages and create session during logging into the system so that the desired level of parameters can be set. The most important parameters to be taken into account in OLTP system are as below:

processes : maximum operating system user processes that can connect to database.
sessions : This parameter is derived as (1.1 *processes)+5. If the process logged into the database create session out
of it then the derived session using above formula may not be enough for OLTP system. If this is the case, one should increase this parameter as needed.
transactions: This parameter is derived as (1.1 *sessions). It is the maximum number of current transaction that can occur. The greater is this parameter more SGA is allocated and more undo data segment needed.
open_cursors :specifies the maximum number of DML statements a session can have.
parallel_max_servers : specifies the maximum no of parallel execution processes and parallel recovery processes for an instance.

Monitor v$resource_limit view and compare the current utilization, max utilization, initial allocation and limit value for the parameters and modify the parameters as needed.

Using Sqlloader load data from excel to oracle database

2009-05-20T04:34:00.000-07:00

Sql Loader is the oracle utility to load data into oracle database from external source datafile specified in specific format. The different types of files comes into being during the data load.

Parameter file : specifies the parameters neccesary to invoke sql loader utility. This may contain the oracle user id and password needs to connect to database, placement of the control file need to parse data and placement of the log files
during the load. Different options may be specified in this file during load such as rows of data after which to commit, disable the archiving option , parallel load option, conventional and direct path load etc.

Control file : specifies the format of the data to be loaded and conditions necessary to load data during the load. The data can be replaced,appended, inserted, truncated in the table. Sql loader does not create table and load into the table. INFILE specifies the source data file whereas DISCARDFILE specifies logs of the rejected rows during the load.The method of field segregation is specified by keyword FIELD TERMINATED BY. Source data may be included in the control file itself using begindata option.

Data file : name of the source data file that contains the data to load as format specified in the control file.

Log file : name of log file to log the result of log during the load.

Discard file : name of file to log the rejected rows during the load.Data can be loaded into the database in two ways:

Direct : This option in parameter file load the data from memory directly into the oracle blocks without generation of the redologs. Direct path loads cannot be used in clustered tables.

Conventional : uses sql insert statement to load data. Generation of redo log, archive files, execution of triggers are the characteristic of conventional path load.

Below is the sample of sql load from the excel file saved in csv format:

Parameter file contents:

userid=username/password
control=c:\myData.ctl
errors=10
log=c:\myData.log
load=1000

Control file contents:

LOAD DATA
INFILE 'c:\myData.csv'
DISCARDFILE 'c:\myData.dsc'
append --REPLACE , APPEND, TRUNCATE
INTO TABLE myData
-- PARTITION (Partition_table_name)
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(v_name, v_acct)

Sql loader can be invoked from command prompt as:

sqlldr parfile=c:\myData.par

Check the logfiles during the load

Database Verify Utilily (dbv) in Oracle Database

2009-04-29T05:00:00.000-07:00

The Database verify utility is mechanism to check the block corruption of the oracle datafiles at Operating system level. It validate the blocks specified by the dbv utility parameters and generates output accordingly. dbv utility is generally useful
for verifying and validating online as well as offline datafiles. Generally this procedure is not used for online data files as errors may be induced in the online files during the execution of validating the files and successive checks may be needed. The Database utiliy can be executed as below

dbv file=datafile_name blocksize=datafile_blocksize

It is the duty of Database administrator to check the file corruption at frequent basis. Below is the script that generates the script for the checks required for the datafiles in unix enviornment and executes the same at the end of the generation

#!/bin/ksh
# dbv script in unix enviornement
#
SQLPLUS=${ORACLE_HOME}/bin/sqlplus
wlogfile=dbv.${ORACLE_SID}
echo "connecting to oracle database and generating commands"
$SQLPLUS system/manager >>$wlogfile << EOF
set echo off feedback off verify off pages 0 termout off
set linesize 150
spool dbv.cmd
select 'dbv file=' || name || ' blocksize=' || block_size ||' feedback=' || round(blocks*.10,0) from v\$datafile;
spool off
set feedback on verify on pages24 echo on termout on
EOF
echo "command generated succesfully"
echo "verifying datafiles
ksh dbv.cmd
#End

The verification would end up as below

DBVERIFY - Verification complete

Total Pages Examined : 61440
Total Pages Processed (Data) : 995
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1
Total Pages Empty : 60444
Total Pages Marked Corrupt : 0
Total Pages Influx : 0

Summary of the verification

Total Pages Examined : number of pages examined
Total Pages Processed (Data) : number of pages inspected that contained table data
Total Pages Failing (Data) : number of table blocks that have corruption
Total Pages Processed (Index): number of blocks inspected by dbv that contained index data
Total Pages Failing (Index): number of index blocks that are corrupted
Total Pages Processed (Other): number of blocks processed other than table and indexes that are corrupted
Total Pages Empty : number of unused blocks in the file
Total Pages Marked Corrupt : show the number of corrupt blocks during scan if it is non zero there is error in the datafile
Total Pages Influx : number of blocks rechecked due to the blocks in use

Control Resource Utilization in Oracle

2009-04-09T22:06:00.000-07:00

Resource utilization can be controlled either through profiles or Oracle Database Resource Manager. Profiles are generally used to control the password protection, cpu usage assigned to users under profile. While Oracle Database Manager makes use
of total resource available in the hardware by distribution of the resources among the groups when certain resource plans are inactive. Managing resources through Database resource manager is the best solution to manage the utilization of cpu
usage, active session pool, degree of parallelism limit, automatic group switching mechanism, execution time limit, undo pool and idle time limit whereas profiles can be only temporary solution to manage resource utilization.

Here is an example to demonstrate manage cpu usage through profile.

Change the resource_limit parameter either in pfile or through the command line by executing

alter system set resource_limit=true;

CREATE PROFILE PROF_QUERY LIMIT
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 1000;

CPU_PER_SESSION UNLIMITED provides cpu usage to the session for UNLIMITED TIME
CPU_PER_CALL 1000 provides cpu usage to the SQL PARSE,EXECUTE,FETCH for 10 secs for every SQL Statement.

Thus the users under the profile PROF_QUERY can use resource for unlimited time in a session and for 10 secs for each Sql Statement.

Check the default profile of the user

select * from dba_users WHERE USERNAME='TEST'

Change the profile of the user

ALTER USER TEST PROFILE PROF_QUERY;

Check the parameters of profile

select * from dba_profiles where profile='PROF_QUERY'

To implement and modify Oracle Database Manager feature for resource utilization, change compatible parameter to 8.1.0.0.0 or above, from pfile

compatible='8.1.0.0.0'

Jsp Page Example

2009-02-22T01:25:00.000-08:00

Shows the demonstration of page directive to import java classes,get the hostname through predifined HttpServletRequest variable,access count since last reboot of machine, use of jsp:useBean Directive, class named Man and MysqlConn must exist in package pack and loads the list from the mysql database.

<%@ page import = "java.util.*,pack.MysqlConn,java.sql.*" %>
Current Date <%= new java.util.Date() %>

Remote Host <%=request.getRemoteHost() %>
<%! private int remoteAccess=0; %>
Access Count :<%= ++remoteAccess %>

angle bracket jsp:useBean id="test" class="pack.Man" scope="page"/ angle bracket
angle bracketjsp:setProperty name="test" property="empName" value="Maheswor Prajapati" /angle bracket
angle bracketjsp:setProperty name="test" property="address" value="bkt"/angle bracket
angle bracketsp:setProperty name="test" property="gender" value="male"/angle bracket
Name: angle bracket jsp:getProperty name="test" property="empName" /angle bracket
Address :angle bracketjsp:getProperty name="test" property="address" /angle bracket>
Gender: angle bracketjsp:getProperty name="test" property="gender" /angle bracket

<%! ResultSet set=null; %>
<%
MysqlConn con=new MysqlConn();
set=con.getEmp();
%>

The output is:
begin select tag
<%

while (set.next()){
%>
begin option tag value='<%=set.getString(1) %>' ><%=set.getString(2)%> end option tag
<%
}

%>

end select tag

Program to Connect Mysql Database in Java

2009-02-22T01:15:00.000-08:00

The program connects mysql database in java. It assumes that the database named test exist in the local computer and mysql.jar is loaded in the JRE system library. It retrives and displays the name of the employee from the database.

package pack;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class MysqlConn {
static String user = "mahesh";
static String password = "prajap";
static String url = "jdbc:mysql://localhost/test";

public static Connection dbConnect() {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
java.sql.Connection conn = DriverManager.getConnection(url, user,
password);
return conn;
} catch (Exception ms) {
ms.printStackTrace();
return null;
}
}

public static void main(String args[]) {
MysqlConn conn = new MysqlConn();
conn.getEmp();
}

public void getEmp() {

Connection conn = MysqlConn.dbConnect();
String queryStr = "select emp_id,emp_name from emp";

try {
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(queryStr);
while(rs.next()){
System.out.println("The employee is: "+rs.getString(2));
}

} catch (Exception ms) {
ms.printStackTrace();

}
}

}

To retrive source code of database objects in oracle

2009-02-13T02:57:00.000-08:00

The user_source view provides the source code of the named objects in the user schema. One can get the source code for the object by issuing

SET LINES 1200
SET PAGES 1200
SELECT TEXT FROM USER_SOURCE WHERE NAME='object_name';

Multiplexing Control Files and Redo Log Files in Oracle Database

2009-02-13T02:08:00.000-08:00

Placing the oracle server processing files such as redo log and control files in multiple destination is multiplexing. It is good practise to run database in muliplexing mode such that one can recover or run the database from the multiplexed files in case of failure.

Multiplexing Control Files

Create pfile from spfile;
Shutdown the database;
shutdown immediate;
Change
control_files='filepath1\CONTROL01.CTL',
filepath1\CONTROL02.CTL',
filepath1\CONTROL03.CTL'
To
control_files='filepath1\CONTROL01.CTL',filepath2\CONTROL01.CTL',
filepath1\CONTROL02.CTL',filepath2\CONTROL02.CTL',
filepath1\CONTROL03.CTL',filepath2\CONTROL03.CTL' in pfile.

Copy control files from the first destination to second destination
Start the database
statup;
confirm the muliplexing.
show parameter control_files

Multiplexing Redo Log files

List the status of the Redo Log files by firing
SELECT * FROM V$LOG;
Drop all the redolog that are in inactive and unused state by issuning
ALTER DATABASE DROP LOGFILE 'filepath\logfilename.log';

Then create the redolog files group in muliple destination by issuning
ALTER DATABASE ADD LOGFILE GROUP group_number ('filepath1\logname.log','filepath2\logname.log') SIZE 5M;
Repeat the process to add the group each time

To drop active and current log files Switch the logfile to next by issuing
ALTER SYSTEM SWITCH LOGFILE;
Then drop the redo log and create the new group as before.

Then issue
SELECT * FROM V$LOG;

Remember that for best performance redo log files must be of same size.
This completes the muliplexing of control files and redo log files.

Moving User Indexes and User Tables to new tablespace

2009-02-12T03:31:00.000-08:00

It is the need for good performance to maintain separate tablespace for user indexes. One can assign the tablespace name to the user index tablespace at the time index creation or can move the index to a new tablespace afterwards.

At the time of table creation
CREATE INDEX index_name ON table_name (col1, col2) TABLESPACE tablespace_name;
The index will be created in users default tablespace if the tablespace option is ommitted.

To move index to other index tablespace
ALTER INDEX index_name REBUILD TABLESPACE indx_tablespace_name;
This move the index to name tablespace and rebuild the index.

To move table to new tablespace
ALTER TABLE table_name MOVE TABLESPACE tablespace_name;
This will move table to new tablespace.

Program in Java to convert physical files in jpg format to Binary files in Oracle Database

2009-02-09T23:29:00.000-08:00

Connection to Oracle database

import java.sql.*;
import java.sql.Connection;

public class Conn {
static String url = "jdbc:oracle:thin:@ipaddress:1521:dbname";

static String user = "username";

static String pass = "password";

public static Connection getConnection() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(url, user, pass);
return conn;

} catch (Exception ms) {
ms.printStackTrace();
return null;
}
}

public static void main(String ss[]) {
Connection conn = Conn.getConnection();
}

}

Create binary files

import java.io.File;
import java.io.FileFilter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

class MySignFilter implements FileFilter {
public boolean accept(File pathname) {
return (pathname.getName().toLowerCase().endsWith("jpg"));
}
}

public class Transfer {

Connection conn = Conn.getConnection();

public void ReadFile() {

try {
File chkerror = new File("C:/pic/errfile.txt");
chkerror.setWritable(true);
FileWriter writer = new FileWriter(chkerror);
PrintWriter printwriter = new PrintWriter(writer);
File file = new File("C:/pic");
MySignFilter mySign = new MySignFilter();
File list[] = file.listFiles(mySign);

for (int i = 0; i < list.length; i++) {

FileInputStream ins = new FileInputStream(list[i]);
byte b[] = new byte[ins.available()];
System.out.println(i+ ":"+ list[i].getName().trim().toLowerCase().replace(".jpg", ""));
if (b.length < 65536) {

PreparedStatement ps = conn
.prepareStatement("insert into binfiles "
+ " (fname,fvalue) "
+ " values (?,?)");
ps.setString(1, list[i].getName().trim().toLowerCase().replace(".jpg", ""));
ps.setBinaryStream(2, ins, (int) list[i].length());
ps.executeUpdate();
ps.close();
ins.close();
conn.commit();

} else {
printwriter.append(list[i].getName().trim() + "\n");
}

}
printwriter.flush();
printwriter.close();
writer.close();

} catch (Exception ms) {
ms.printStackTrace();
}
}

public static void main(String args[]) {
Transfer trn = new Transfer();
trn.ReadFile();
}

}

Program to create jpg files from the binary data stored in SqlServer

2009-02-09T23:17:00.000-08:00

It is sometimes necessary to create the physical files from the binary data stored in the database. Given below is the code in Visual Basice that would create the files in jpg format accessing the binary data stored in SqlServer database. It is required to make ODBC Connection named "sqlserver" before the program can be executed.

Dim cAcc As New ADODB.Connection
Dim rAcc As New ADODB.Recordset

Private Sub Form_Load()
cAcc.Open "Provider=MSDASQL.1;Password=password;Persist Security Info=True;User ID=username;Data Source=sqlserver"
End Sub

Private Sub Command1_Click()
rAcc.Open "select mname,mblob from mFiles", cAcc '

Dim strStream As New ADODB.Stream
strStream.Type = adTypeBinary
strStream.Open
Dim acctno As String
Do While Not rAcc.EOF
strStream.Write rAcc.Fields("mblob").Value
picname = rAcc.Fields("mname").Value
strStream.SaveToFile "C:\pic\" + picname + ".jpg", adSaveCreateOverWrite
rAcc.MoveNext
Loop

End Sub

Performance Tuning in Oracle Database

2009-02-05T02:18:00.000-08:00

Tuning performance is a difficult task to perform. One should know the area on which the tuning is required before performing the real tuning. This column is added to know the database parameters to tune to meet the usage of the hardware requirement during the installation of oracle database.

Parameters affecting the real time transaction processing system in oracle database are as below:

For instance the server is of 1GB memory. The size of the parameters is specified in bytes.

sga_max_size=536870912
reset this parameter upto 50% of the total memrory (in bytes) -512m
shared_pool_size=107374182
reset this parameter upto 10% of the total memrory (in bytes) -102m
large_pool_size=53687091
reset this parameter upto 5% of the total memrory (in bytes) -51m
db_cache_size=107374182
reset this parameter upto 10% of the total memrory (in bytes) -102m
pga_aggregate_target=214748364
reset this parameter upto 20% of the total memrory (in bytes) -204m
sort_area_size=53687091
reset this parameter upto 5% of the total memrory (in bytes) -51m

Create the pfile from the current spfile and change or add the parameters neccessary parameters in the parameter file. Rename
the spfile to take the effect of the pfile in the next startup of the database or create spfile from the changed pfile.

These parameters helps in processing the DML statements during Online Transaction Processing System.

Turn Off Archive log mode

2008-12-19T00:58:00.001-08:00

Once the Oracle database in archivelog mode can be turned off with the following steps:

Shutdown the database
shutdown immediate;

Change the parameter log_archive_start=false in pfile. This will stop the automatic archival of the database and the database administrator have to manually archive the
redo file for backup issuing alter system archive log all in archivelog mode;

Start the database in mount mode
startup mount;

Alter the database in noarchivelog mode. This will change the database mode to noarchive log mode such that redo log files can be overwritten before they are archived.
alter database noarchivelog;

Open the database
alter database open;

Verify the database status
archive log list;

Command to Remove Window Service including Oracle Starter Database Service

2008-11-25T21:58:00.000-08:00

Oracle installation create window starter service for Oracle database. It may sometimes be necessary to drop the unnecessary services. It can be removed in two ways in case of oracle starter service:

Using oracle utililty named oradim -
used to create, delete, edit the System Identification Number for the database instance.

Add new SID
oradim -new -sid sidname -intpwd password -maxusers no_of _users -startmode a
Edit SID
oradim -edit -sid sidname -intpwd password -maxusers no_of _users -startmode a
Delete SID
oradim -delete -sid sidname

Using windows OS Commnad -
can delete any other Services including service for Oracle Database

Delete Service
sc delete ServiceName

Performance and Database Connections

2008-11-19T00:43:00.000-08:00

Various performances related issue is typically caused by large number of database connection and we have to consider following major areas that can be checked and adjusted for the number of oracle database connections in oracle 9i.

If we are using the different server names for the same oracle database (TNS names) that will create additional, unnecessary connections. Therefore server name and TNS names both must match and case sensitive.

Oracle database doesn’t do the sufficient locking to prevent conflicts between connections on the same server. The conflicts can show up under very high database usage and timing, and only from a few application plus major factor about the timer initiated disconnection and dead connection detention feature that allows SQL NET to identify connection that have been left hanging by the abnormal termination of a client and unfortunately this feature is available only after the release of 2.1 and later. And only if the SQL NET can identify dead connections due to the client process or machine being unreachable, the connection will be closed when an error is generated by the send operation and server process will exit and a small probe packet will be sent from the server to client at user defined interval and that will automatically forces a database rollback of uncommitted transactions and locks held by the user of the broken connection and allow further regular continuous transactions. Thus, this feature minimizes the waste of resources by connections that are no longer valid plus we can specify the expire time in SQLNET.ORA of database server side (file usually in $ORACLE_HOME/network/admin) which will direct SQLNET to send a probe to the client through the network and if the client doesn’t respond, it will be killed.

MAX CONNECTION parameter: We have to consider the total number of db connections for dbusers per data source. Additional database connection requests beyond this value will be queued for the next available connection. If this value is exceeded, the user request will fail generating errors in the log file.

minConnection=5
maxConnection=50
initialConnection=5
poolGrowth=5

Run a script on the database server, each node with incoming connections grouped by
a. db user
b. program
c. machine name

Script:

select username, count(*) from v$session
group by username;
select machine count(*)
from v$session
group by machine;

select program, count(*)
from v$session
group by program;

Note: Outcome of this can be send to the developers and they will have to take corrective steps if necessary.

Locks in Oracle Database

2008-11-10T00:06:00.000-08:00

Locks are held by Oracle Database to maintain the integrity during the concurrent updates. Locks are held until the transaction in a session is committed or rollbacked. Oracle maintains row level lock in the tables that are recently being updated.

In the distributed transaction, the network connection also plays vital role during locking. Once locked transaction in this mode never released if the failure in completion of processing on the remote side. This may also result in the object lock so that complete system may be affected.

So, it is the database administrator job to look after the database as well as to monitor the network passage side by side. Usually Oracle Database administrator use
v$locked_object to monitor the lock objects in the oracle database. One can get the session information and object held in the lock by linking v$locked_object to
v$session and dba_objects. Use folowing query to check the lock held in Oracle Database.

select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine
from v$locked_object a , v$session b, dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id;

You may kill the session to release the lock by

alter system kill session 'SID,SERIAL#';

Oracle releases locks during the restart of the database.

How to decrease the size of the datafiles

2008-11-02T01:18:00.000-07:00

The dba_data_files view gives information about the physical datafiles in the database whereas dba_free_space view gives information about the free space in the datafiles with their related block id. The query below gives the datafiles free space and total space allocated for the datafiles.

select a.TABLESPACE_NAME, a.FILE_NAME, free_space/1024/1024 free_space_in_mb, a.BYTES/1024/1024 total_space_allocated_in_mb
from dba_data_files a, (select TABLESPACE_NAME,FILE_ID,sum(BYTES) FREE_SPACE from dba_free_space group by TABLESPACE_NAME,FILE_ID) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and
a.FILE_ID=b.FILE_ID
order by a.TABLESPACE_NAME,a.FILE_NAME;

With this information one can decrease the size of the datafile by the free space with the command given below:

alter database datafile 'filepath\filename' resize filesize;

Free Space in Percentage Tablespace Wise

select z.TABLESPACE_NAME,sum(z.free_space_in_mb) free_space_in_mb,
sum(z.total_space_allocated_in_mb) total_space_allocated_in_mb,
round(sum(z.free_space_in_mb)/sum(z.total_space_allocated_in_mb)*100,2) free_pct
from (
select a.TABLESPACE_NAME, a.FILE_NAME, free_space/1024/1024 free_space_in_mb, a.BYTES/1024/1024 total_space_allocated_in_mb
from dba_data_files a, (select TABLESPACE_NAME,FILE_ID,sum(BYTES) FREE_SPACE from dba_free_space group by TABLESPACE_NAME,FILE_ID) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and
a.FILE_ID=b.FILE_ID
) z
group by z.tablespace_name
order by z.TABLESPACE_NAME;

Free Space in Percentage At Data File Level

select a.TABLESPACE_NAME, a.FILE_NAME, free_space/1024/1024 free_space_in_mb, a.BYTES/1024/1024 total_space_allocated_in_mb, round((free_space/a.BYTES),2)*100 free_pct
from dba_data_files a, (select TABLESPACE_NAME,FILE_ID,sum(BYTES) FREE_SPACE from dba_free_space group by TABLESPACE_NAME,FILE_ID) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and
a.FILE_ID=b.FILE_ID
order by a.TABLESPACE_NAME,a.FILE_NAME;

Export Oracle Dump file from command prompt or from Window Run Mode

2008-10-23T22:25:00.000-07:00

The oracle Export Utility is used to export entire database or users or list of tables to the path given by the FILE parameter. The DIRECT path method is much faster than conventional method. The direct path method reads data directly from the disk and places in the buffer and then sent to the export client , writes it to the disk. Whereas conventional path follow SQL SELECT statement to fetch the data thus have to evaluate expression in the memory before transporting the file to the client to write to the disk. The COMPRESS parameter specifies the extent management during import of the table data. If it is set to Y then the table data is consolidated into single extent during import otherwise it uses current storage parameter of the tables during import. It is good practise to verify the log file after the export of the file have been completed to verify the successful export.

EXP username/password FILE=file_path\filename.dmp DIRECT=Y COMPRESS=Y ROWS= Y OWNER=user_to_export LOG=log_path_name\logfilename.txt

You can export the selected tables from the file with the TABLES option

EXP username/password FILE=file_path\filename.dmp DIRECT=Y COMPRESS=Y ROWS= Y OWNER=user_to_export TABLES=TABLE_NAME LOG=log_path_name\logfilename.txt

Import Oracle Dump file from command prompt or execute from window Run mode

2008-10-23T00:31:00.000-07:00

The oracle Import Utility is oracle database tool used to import users, database, tablesapces from database dump file. The utility can be invoked from the command prompt and also could be executed from windows Run mode. You could simply change the parameters and paste the command given below from windows Run mode. The log parameter maintains the log of the import.

IMP username/password FILE=file_path\filename.dmp FROMUSER=user_previously_dumped TOUSER=current_user ROWS=Y INDEXES=Y CONSTRAINTS=Y LOG=log_path_name\logfilename.txt

You can import the selected tables from the file with TABLES option

IMP username/password FILE=file_path\filename.dmp FROMUSER=user_previously_dumped TOUSER=current_user TABLES=BPT_SIGNATURES ROWS=Y INDEXES=Y CONSTRAINTS=Y LOG=log_path_name\logfilename.txt

Managing Oracle tablespace and datafiles

2008-10-21T23:55:00.000-07:00

It is the best practise to separate user data from system data in an oracle database. Doing so enhances the performance of the database.
Oracle manages its datafiles in logical space known as Tablespace. A tablespace can have multiple datafiles but a single datafile can belong to one and only one tablespace. Datafiles are the physical files that stores data which are divided again into logical segment and extents. It should be noted that autoextend clause extend
the datafile automatically as and when needed but the auto extension of the size of datafile depend on the maximum file size limit of the operating system and it also degrade performance of the database. Locally managed extents maintain its data state information in its own header(bitmapped). This avoids the throughput in the data dictionary, which increases performance as well.

It is the best approach to separate user datafile and user data index file in separate tablespace. Temporary tablesapce are used for sorting operation required during query executions.

--user data holder tablespace and datafiles
CREATE TABLESPACE TABLESPACE_NAME
DATAFILE '%ORACLE_BASE%\ORADATA\SID\DATAFILE01.DBF' SIZE 100M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

--add more datafile to a tablespace

ALTER TABLESPACE TABLESPACE_NAME ADD DATAFILE '%ORACLE_BASE%\ORADATA\SID\DATAFILE02.DBF' SIZE 100M AUTOEXTEND ON;

--user index tablespace and datafiles
CREATE TABLESPACE TABLESPACE_NAME_INDX
DATAFILE '%ORACLE_BASE%\ORADATA\SID\DATA_INDX01.DBF'
SIZE 100M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

--user temporary tablespace and datafiles
CREATE TEMPORARY TABLESPACE TABLESPACE_TEMP
TEMPFILE '%ORACLE_BASE%\ORADATA\SID\TEMP01.DBF'
SIZE 20M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Simple AJAX

2008-10-21T21:49:00.000-07:00

Make your web page optimized by implementing AJAX technology. AJAX stands for Asynchronous JavaScript and XML made popular by Google.

XMLHttpRequest object is the key behind the communication between clients and server. Different browsers use different methods to create the XMLHttpRequest object. Internet Explorer uses an ActiveXObject, while other browsers uses the built-in JavaScript object called XMLHttpRequest.

The onreadystatechange event of the XMLHttpRequest will process the response from a server. By invoking the function on this event we retrieve the information sent by the server.

The readyState property holds the status of the server's response. Each time the readyState changes, the onreadystatechange function will be executed.

Here are the possible values for the readyState property:
State Description
0 Request is not initialized
1 Request has been set up
2 Request has been sent
3 Request is in process
4 Request is complete

Here is the sample code.

start of html tag
start of body tag

start of javascript function

function simpleAjax(){
var xmlHttp;
try {
// Firefox, Opera 8.0+, Safari
xmlHttp=new XMLHttpRequest();
}
catch (e) {
// Internet Explorer
try {
xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
}
catch (e) {
try {
xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
}
catch (e) {
alert("Your browser is very old, does not support AJAX!");
return false;
}
}
}
xmlHttp.onreadystatechange=function() {
if(xmlHttp.readyState==4) {
//get data from the server
document.simpleAjax.fromServer.value=xmlHttp.responseText;
}
}
xmlHttp.open("GET","simpleAjax.php",true);
xmlHttp.send(null);
}

end of javascript function

start of form tag name="simpleAjax"

Name: between angle bracket(input type="text" onkeyup="simpleAjax()" name="user")
Data: between angle bracket(input type="text" name="fromServer")

end of form tag
end of body tag
end of html tag

php file

///output the data to the client
////echo "Data from Server";
//?>

save the form as "simpleAjax.php" in the same directory as html file.

How to run Oracle 9i database in Archive Mode

2008-10-20T02:04:00.000-07:00

Steps:
1) Create pfile if the database is started from spfile.
create pfile from spfile;
This will create pfile in the %ORACLE_HOME%/database/pfileSID.ora
or
create pfile='pathname' from spfile;

2) Shutdown the database
shutdown immediate

3) add parameters
log_archive_start=true
log_archive_format='SID%D%T%S.ARC'
log_archive_dest='%ORACLE_BASE%\oradata\archive'

4) Start up the database in mount stage
startup mount;

5) Activate the database to archiving mode
alter database archivelog;

6) Verify the activation
archive log list;

7) Open the database for use
alter database open;

8) Generate the archive log manually
alter system archive log current;

This turns the database in archive log mode.

Query to view assigned tablespace and its associated datafiles to the database users

2007-11-24T22:36:00.000-08:00

########RETURN NULL FOR THE TABLESPACE NOT ASSIGNED TO USERS#######
Here
ts# represents tablespace number
file# represents datafile number

select * from
(select c.username username,'default tablespace' type,a.ts# ts#,a.name tablespace,b.file# file#,b.name name from v$tablespace a, v$datafile b,dba_users c
where a.ts#=b.ts# and
a.name=c.default_tablespace(+)
union all
select c.username,'temporary tablespace' type, a.ts# ts#,a.name tablespace,b.file# file#,b.name name from v$tablespace a, v$tempfile b,dba_users c
where a.ts#=b.ts# and
a.name=c.temporary_tablespace)z
order by z.username,z.type;

Creating Database Manually

2007-11-08T00:49:00.000-08:00

Here is the sample to create the oracle 9i database manually.

Steps:
1) run oradim utilily to create oracle agent service from command prompt.
eg oradim - new -sid sidname -intpwd password -maxusers no_of _users -startmode a
2) run orapwd utility to create password file in the default %ORACLE_HOME%\DATABASE folder
3) add folder named oradata and SID in %ORACLE_BASE% directory.
4) add folder named bdump, udump,cdump,pfile,create folder inside %ORACLE_HOME%\admin\SID directory
5) prepare initialisation file initSID.ora OR SPFILESID.ora IN %ORACLE_HOME%\DATABASE direcotry
6) Using Oracle Managed Files set
db_create_file_dest='%ORACLE_BASE%\oradata\SID'
db_create_online_log_dest_n='%ORACLE_BASE%\oradata\SID'
replace n with integer starting 1 upto 5 destinations
7) startup database in nomount stage from prompt
startup nomount
8) run the script below:

#########DATABASE CREATION USING DIRECTORY PATH NAMES
CREATE DATABASE database_name
logfile
group 1 ('%ORACLE_BASE%\oradata\SID\redo01.dbf') size 10m,
group 2 ('%ORACLE_BASE%\oradata\SID\redo02.dbf') size 10m,
group 3 ('%ORACLE_BASE%\oradata\SID\redo03.dbf') size 10m
maxlogfiles 10
maxlogmembers 3
maxloghistory 1
maxdatafiles 100
maxinstances 1
datafile '%ORACLE_BASE%\oradata\SID\system01.dbf' size 500m
undo tablespace UNDOTBS1
datafile '%ORACLE_BASE%\oradata\SID\undotbs.dbf' size 200m
autoextend on next 5120k maxsize unlimited
default temporary tablespace temp
tempfile %ORACLE_BASE%\oradata\SID\temp01.dbf' size 200m
autoextend on next 5120k maxsize 500m
character set US7ASCII
national character set AL16UTF16
set TIME_ZONE='America/New_York';

#########OMF MANAGED DATABASE CREATION

CREATE DATABASE database_name
logfile
group 1 size 10m,
group 2 size 10m,
group 3 size 10m
maxlogfiles 10
maxlogmembers 3
maxloghistory 1
maxdatafiles 100
maxinstances 1
datafile size 500m
undo tablespace UNDOTBS1
datafile size 200m
autoextend on next 5120k maxsize unlimited
default temporary tablespace temp
tempfile size 200m
autoextend on next 5120k maxsize 500m
character set US7ASCII
national character set AL16UTF16
set TIME_ZONE='America/New_York';

9) run script catalog.sql and catproc.sql as a sys user from %ORACLE_HOME\rdms\admin directory
10) run script pupbld.sql as a system user from %ORACLE_HOME\sqlplus\admin directory

This completes the database manual database creation.