oracle 11g r2 installation error in windows

I downloaded the files from OTN with following names:
win64_11gR2_database_1of2.zip
and
win64_11gR2_database_2of2.zip

Extracted both into the SAME directory with these names
C:\Oracle\Disk1 and
C:\Oracle\Disk2
Started the installation from C:\Oracle\Disk1\database\setup.exe
And the Installation failed due to file not found of {C:\app\11g\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\WFMLRSVCApp.ear and some other files too}
I went through the http://download.oracle.com/docs/cd/E11882_01/install.112/e10843/toc.htm

but there was nothing which mentioned where or how to rename the directories.All my downloaded files were in the same directory but still I was facing the problem. As both zip files contained database folder so I couldn't override the files. I solved the file not found issue by coping all the folders under "C:\Oracle\Disk2\database\stage\Components" to
"C:\Oracle\Disk1\database\stage\Components"
After restarting the installation it went like a charm and without any issues.

courtsey:-https://forums.oracle.com/forums/thread.jspa?threadID=1054514

Foreign key constraint with example

A foreign key constraint (also called referential integrity constraint) on a column ensures that the value in that column is found in the primary key of another table.
If a table has a foreign key that references a table, that referenced table can be dropped with a drop table .. cascade constraints.
It is not possible to establish a foreign key on a global temporary table. If tried, Oracle issues a ORA-14455: attempt to create referential integrity constraint on temporary table.

Referential integrity constraint is that which depend upon the parent and child relationship. In this one of the column have the primary key constraint and one of the column of another table have the foreign key constraint. When you create a foreign key constraint, Oracle default to "on delete restrict" to ensure that a parent rows cannot be deleted while a child row still exists. If you tried to do this it will show you error related to referential integrity constraint.
But if you still want to delete value from child table but never want to delete parent table column value then that purpose you can use 'on delete cascade' option.

The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.
A foreign key with a cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table with automatically be deleted. This is called a cascade delete.

A foreign key with a cascade delete can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Below given an example for foreign key constraint

SQL> create table salary(
2 rollno number(5), name varchar(15), design varchar(15),
3 bpay number(7,2), da number(6,2), total number(8,2),
4 constraint salary_pk primary key(rollno));

Table created.

SQL> insert into salary values(&rollno , '&name','&design',&bpay,&da,&total)
2 ;
Enter value for rollno: 10001
Enter value for name: S.Krishnan
Enter value for design: HOD
Enter value for bpay: 25000
Enter value for da: 1500
Enter value for total: 26500
old 1: insert into salary values(&rollno , '&name','&design',&bpay,&da,&total)
new 1: insert into salary values(10001 , 'S.Krishnan','HOD',25000,1500,26500)

1 row created.

SQL>

Let insert some more records into salary table

SQL> select * from salary;

ROLLNO NAME DESIGN BPAY DA TOTAL
---------- --------------- --------------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 26500
10002 K.K.Omana Asst.Manager 20000 1200 21200
10003 Anishkumar. K Asst.Manager 20000 1200 21200
10004 Girishkumar .K Asst.Manager 20000 1200 21200
10005 Arunkumar. K Programmer 15000 1000 17000

Now create another table named address with foreign key

SQL> create table address( rollno number(5) , address varchar(30),
2 pincode number(6),
3 constraint address_fk foreign key(rollno) references salary(rollno));

Table created.

SQL> insert into address values (&rollno,'&address',&pincode);
Enter value for rollno: 10001
Enter value for address: thaliyil veettil
Enter value for pincode: 670548
old 1: insert into address values (&rollno,'&address',&pincode)
new 1: insert into address values (10001,'thaliyil veettil',670548)

1 row created.

SQL> /
Enter value for rollno: 10002
Enter value for address: panakkada house
Enter value for pincode: 670581
old 1: insert into address values (&rollno,'&address',&pincode)
new 1: insert into address values (10002,'panakkada house',670581)

1 row created.

SQL> /
Enter value for rollno: 10004
Enter value for address: embron house
Enter value for pincode: 670481
old 1: insert into address values (&rollno,'&address',&pincode)
new 1: insert into address values (10004,'embron house',670481)

1 row created.

SQL> /
Enter value for rollno: 10009
Enter value for address: niya manzil
Enter value for pincode: 670426
old 1: insert into address values (&rollno,'&address',&pincode)
new 1: insert into address values (10009,'niya manzil',670426)
insert into address values (10009,'niya manzil',670426)
*
ERROR at line 1:
ORA-02291: integrity constraint (MAHI.ADDRESS_FK) violated - parent key not
found


SQL> select * from address;

ROLLNO ADDRESS PINCODE
---------- ------------------------------ ----------
10001 thaliyil veettil 670548
10002 panakkada house 670581
10004 embron house 670481

SQL>

Also you can't delete a parent table column value if corresponding column in the child table still exist.

For example,

SQL> delete from salary where rollno=10002;
delete from salary where rollno=10002
*
ERROR at line 1:
ORA-02292: integrity constraint (MAHI.ADDRESS_FK) violated - child record found
SQL>

By using the 'on delete cascade' you can avoid this problem , for that we have to alter the table with 'on delete cascade' option. so rearrange the table as follow,

SQL>alter table address drop constraint address_fk;

SQL>alter table address add constraint address_fk foreign key(rollno)
references salary(rollno) on delete cascade;

Now try to delete the record

SQL> delete from salary where rollno=10002;

1 row deleted.

SQL>

Check Constraint in Oracle with example

A check constraint requires a value in the database to comply with a specified condition.
below given one example for this,

SQL> Create table check_example2 (
2 name varchar(15),design varchar(15) ,
3 bpay number(7,2),da number(6,2),
4 constraint check2 check (da <=2500));

Table created.

SQL> insert into check_example2 values
2 ('&name','&design',&bpay,&da);
Enter value for name: Mahesh P
Enter value for design: CEO
Enter value for bpay: 20000
Enter value for da: 2000
old 2: ('&name','&design',&bpay,&da)
new 2: ('Mahesh P','CEO',20000,2000)

1 row created.

SQL> /
Enter value for name: Manoj
Enter value for design: MD
Enter value for bpay: 18000
Enter value for da: 1800
old 2: ('&name','&design',&bpay,&da)
new 2: ('Manoj','MD',18000,1800)

1 row created.

SQL> /
Enter value for name: Suraj
Enter value for design: clerk
Enter value for bpay: 10000
Enter value for da: 1200
old 2: ('&name','&design',&bpay,&da)
new 2: ('Suraj','clerk',10000,1200)

1 row created.

SQL> /
Enter value for name: Aneesh Babu
Enter value for design: CEO
Enter value for bpay: 22000
Enter value for da: 2600
old 2: ('&name','&design',&bpay,&da)
new 2: ('Aneesh Babu','CEO',22000,2600)
insert into check_example2 values
*
ERROR at line 1:
ORA-02290: check constraint (MAJI.CHECK2) violated

Constraint in oracle with example

Oracle constraints are means in the process of defining some conditions about the database that must remain true while inputting/modifying/deleting data in the database.

These Oracle constraints can be attribute-based (column), tuple-based (table), key based and referential integrity based.

As Oracle views are always dynamically generated from their base tables, so the view can not contain constraints.

If there is a violation of the constraints caused by some actions performed on the database, then the Oracle constraints aborts the action accordingly. The Oracle implementation of constraints differs from the SQL implementation of these constraints.

Different type of constraints

1.UNIQUE CONSTRAINT /CANDIDATE KEY
A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns,

SQL> create table unique_sample(name varchar(15) unique,design varchar(15));

Table created.

SQL> insert into unique_sample values ('&name','&design');
Enter value for name: Mahesh P
Enter value for design: GM
old 1: insert into unique_sample values('&name','&design')
new 1: insert into unique_sample values('Mahesh P','GM')

1 row created.

SQL> /
Enter value for name: Manoj
Enter value for design: clerk
old 1: insert into unique_sample values('&name','&design')
new 1: insert into unique_sample values('Manoj','clerk')

1 row created.

SQL> /
Enter value for name: Mahesh P
Enter value for design: peon
old 1: insert into unique_sample values('&name','&design')
new 1: insert into unique_sample values('Mahesh P','peon')
insert into unique_sample values('Mahesh P','peon')
*
ERROR at line 1:
ORA-00001: unique constraint (MAHI.SYS_C005359) violated
Here note down the system generated constraint name SYS_C005359

The main data dictionary views for constraints are

USER_CONSTRAINTS - Constraint definitions on user's own tables
ALL_CONSTRAINTS - Constraint definitions on accessible tables
DBA_CONSTRAINTS - Constraint definitions on all tables

If you note given any name to constraint oracle will automatically assign a name and
the naming convention is SYS_Cnnnnnn to derive the name of the constraint

SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from DBA_CONSTRAINTS
where CONSTRAINT_TYPE='U'and TABLE_NAME='UNIQUE_SAMPLE';

CONSTRAINT_NAME C
------------------------------ -
SYS_C005359 U

The status of CONSTRAINT_TYPE fields are

C (check constraint on a table)
P (primary key)
U (unique key)
R (referential integrity)
V (with check option, on a view)
O (with read only, on a view)

Note:-

*You can name the constraint at the time of table creation by

sql>create table unique_sample(name varchar(15) ,design varchar(15),constraint const_uni1 unique(name));

*You can disable the constrait by

SQL> alter table UNIQUE_SAMPLE drop constraint const_uni1 ;

*if you want to recover the changes

SQL> alter table unique_sample add constraint const_unit1 unique(name);

we can add constraint to a table by the above method

if any duplicate values are found , it gives error ,you have to delete the duplicate entry
then execute the above query.

*You can temporary disable the constraint by

SQL>alter table unique_sample disable constraint const_uni1;

Now you can insert as many duplicate entry , but whenever you want to enable constraint

you have to delete all duplicate entries in the table.

2.NOT NULL CONSTRAINT

A NOT NULL constraint prohibits a database value from being null. below is the examle

SQL> create table notnull_sample (name varchar(15) not null , design varchar(15));

Table created.

SQL> insert into notnull_sample values ('&name','&design');
Enter value for name: mahi
Enter value for design: gm
old 1: insert into notnull_sample values ('&name','&design')
new 1: insert into notnull_sample values ('mahi','gm')

1 row created.

SQL> insert into notnull_sample values ('&name','&design');
Enter value for name: manu
Enter value for design: ceo
old 1: insert into notnull_sample values ('&name','&design')
new 1: insert into notnull_sample values ('manu','ceo')

1 row created.

SQL> insert into notnull_sample values ('&name','&design');
Enter value for name:
Enter value for design: peon
old 1: insert into notnull_sample values ('&name','&design')
new 1: insert into notnull_sample values ('','peon')
insert into notnull_sample values ('','peon')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("MAHI"."NOTNULL_SAMPLE"."NAME")

3.PRIMARY KEY (UNIQUE key + NOT NULL) CONSTRAINT

A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null. below is the example

SQL>create table primary_sample ( name varchar(15),design varchar(15),constraint primary1 primary key(name));

SQL> insert into primary_sample values ('&name','&design');
Enter value for name: Mahesh P
Enter value for design: Programmer
old 1: insert into primary_sample values ('&name','&design')
new 1: insert into primary_sample values ('Mahesh P','Programmer')

1 row created.

SQL> insert into primary_sample values ('&name','&design');
Enter value for name: Manoj P
Enter value for design: L D Clerk
old 1: insert into primary_sample values ('&name','&design')
new 1: insert into primary_sample values ('Manoj P','L D Clerk')

1 row created.

SQL> insert into primary_sample values ('&name','&design');
Enter value for name: Mahesh P
Enter value for design: UD Clerk
old 1: insert into primary_sample values ('&name','&design')
new 1: insert into primary_sample values ('Mahesh P','UD Clerk')
insert into primary_sample values ('Mahesh P','UD Clerk')
*
ERROR at line 1:
ORA-00001: unique constraint (MAHI.PRIMARY1) violated


SQL> insert into primary_sample values ('&name','&design');
Enter value for name:
Enter value for design: GM
old 1: insert into primary_sample values ('&name','&design')
new 1: insert into primary_sample values ('','GM')
insert into primary_sample values ('','GM')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("MAHI"."PRIMARY_SAMPLE"."NAME")

3. COMBINED / COMPOSITE PRIMARY KEY

Uniqueness in a composite primary key is determined by combining the values in all
columns of the compositer primary key .If the combination is unique , then the key is
unique .

consider the following example

I want to create a table named results for storing the result of a students in an engineering
college . There may be a lot of students studying in a particular semester , so the field for semester(here semno) should not be a primary key. Also we have to store the results of a particular student for all semester ,so the filed for student(here rollno) also not to be a primary key. Here we have to met only one condition , both the filed rollno and semno should not repeat simultaneously

SQL> create table results (
2 semno number(1),
3 rollno number(5),
4 english number(3),
5 science number(3),
6 maths number(3),
7 hindi number(3),
8 constraint result_pk primary key(semno,rollno));

Table created.

SQL> insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi');
Enter value for semno: 1
Enter value for rollno: 1001
Enter value for english: 78
Enter value for science: 85
Enter value for maths: 85
Enter value for hindi: 86
old 1: insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi')
new 1: insert into results values ('1' , '1001' ,'78' , '85' , '85' , '86')

1 row created.

SQL>
SQL> insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi');
Enter value for semno: 2
Enter value for rollno: 1001
Enter value for english: 80
Enter value for science: 85
Enter value for maths: 92
Enter value for hindi: 89
old 1: insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi')
new 1: insert into results values ('2' , '1001' ,'80' , '85' , '92' , '89')

1 row created.

SQL> insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi');
Enter value for semno: 1
Enter value for rollno: 1002
Enter value for english: 69
Enter value for science: 89
Enter value for maths: 91
Enter value for hindi: 89
old 1: insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi')
new 1: insert into results values ('1' , '1002' ,'69' , '89' , '91' , '89')

1 row created.

SQL> insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi');
Enter value for semno: 3
Enter value for rollno: 1001
Enter value for english: 85
Enter value for science: 86
Enter value for maths: 87
Enter value for hindi: 89
old 1: insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi')
new 1: insert into results values ('3' , '1001' ,'85' , '86' , '87' , '89')

1 row created.

SQL> select * from results;

SEMNO ROLLNO ENGLISH SCIENCE MATHS HINDI
---------- ---------- ---------- ---------- ---------- ----------
1 1001 78 85 85 86
2 1001 80 85 92 89
1 1002 69 89 91 89
3 1001 85 86 87 89

SQL> insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi');
Enter value for semno: 2
Enter value for rollno: 1001
Enter value for english: 89
Enter value for science: 98
Enter value for maths: 87
Enter value for hindi: 87
old 1: insert into results values ('&semno' , '&rollno' ,'&english' , '&science' , '&maths' , '&hindi')
new 1: insert into results values ('2' , '1001' ,'89' , '98' , '87' , '87')
insert into results values ('2' , '1001' ,'89' , '98' , '87' , '87')
*
ERROR at line 1:
ORA-00001: unique constraint (MAHI.RESULT_PK) violated

Note : Oracle enforces uniqueness on the combination of values in semno and rollno fields , and hence our 5th insert statement causes an error because it is an exact duplicate of the first.