How to find a perticular cell value from an entire database?

Today we ran into a strange issue.

We have got an email from an application . The strange fact was the mail sender was an x employee of our organization.

We had to take a decision to change it :) but all we had is database credentials and name of an x employee.


Searching a particular name in entire database seemed a mammoth task if done manually.So we looked here and there.
Finally we got a pl-sql script to accomplish the same.

Please find the below post where i got a solution from experts.
https://forums.oracle.com/forums/thread.jspa?threadID=2374991&messageID=10272629#10272629

Oracle XE on Windows.

Installing Oracle XE on your Window machine is very Easy.

Here are the details you should keep it with yourself after installation.

SYS/SYSADMIN password.
Database Port (Default 1521)
Apex URL
example:http://127.0.0.1:8080/apex/

I would recommend you to create a normal user after the installation.
You may refer following article for creating new user.

First Lecture Oracle(Sql)

Step 1)
Installation and Configuration of Oracle 10g XE.
Step 2)
After Installation create user account using Database Administrative account.
(This can be easily done using Oracle Database XE home page.Please Refer Document given at the end of the article for creating new user account using XE homepage)

Step 3)
Login with User account and Create a simple table

create table student_table (name varchar2(10),roll number(3));

Insert some values into it.
Insert some duplicate values,null values.
Explain how to avoid it by using primary and not null key.

Step 2:Create employee and department table.

create table employee_table
(
employee_id number(5) constraints empidpk primary key,
employee_name varchar2(10)constraints empnamenn not null,
hire_date date,
salary number(5),
dept_no number(2) references department_table(dept_no)
);

Explain why the table creation is failed?

create table department_table
(
dept_no number(2) constraints dept_idpk primary key,
dept_name varchar2(10) constraints dept_namenn not null,
dept_location varchar2(15) constraints dept_locnn not null
);

Insert values into Department table:
insert into department_table values (10,'account','mumbai');
insert into department_table values(30,'admin','pune');
insert into department_table values(20,'hr','delhi');

Ask them to modify student table created in step 1 accordingly.
More on this coming soon.

Refer below tutorial for you to quickly up and running using Oracle Database XE by creating a simple application.
http://download.oracle.com/docs/cd/B25329_01/doc/admin.102/b25610/toc.htm

Oracle Server

ORACLE SERVER:
Oracle Server is composed of two major parts
1) Oracle Instance.
2) Oracle Database.

Oracle Server = (Oracle Instance + Oracle Database)

An Oracle instance is a is composed of two major parts
1) Memory structure.
2) Background processes.

Oracle instance = (Memory structure + background processes.)

The typical background processes are

PMON, SMON , MMON ,DBWR ,LGWR ,CKPT ,ARCH etc ..

Click on image to zoom...

(Note:
1>The Instance must be started to access the data in the database.

2>There are several files, processes, and memory structures in an oracle server; however, not all of them are used when processing a SQL statement. Some of them are used to improve the performance of the database, ensure that the database can be recovered in event of a software or hardware error, or perform other tasks necessary to maintain the database.

3>Every time an instance is started, a System Global Area (SGA) is allocated and Oracle background processes are started.

4>Always opens one and only one database

)

View

The Most Important part of View is its Syntax. And understanding its component.

Syntax:

CREATE OR REPLACE VIEW view [FORCE|NOFORCE*][(alices[,alice],…)]

AS sub query

[WITH CHECK OPTION[CONSTRAINT constraint name]]

[WITH READ ONLY [CONSTRAINT constraint name]]




[] : OPTIONAL
*:Default Parameter


The sub query that defines the view can’t contain order by clause However you can use order by to display view contents.

Use of views

• Data Independence.
• Complex queries easy.
• Restrict data access.
• · To present different view of same data.

Delete

· You cant delete a primary key row who has an reference with another table value .

For example.

Delete from department where department_id=10 (Provided there are employees working in department 10)

Will return Integrity constraint violated error.

However

Following statement will easily get executed

Delete from department where department_id=10 (Provided there are no employees working in department 10)

· There is no Delete * from

Merge

Merge Statement:


Description:

• Useful in data warehousing application.
• Update the row if they already exists and Insert the rows if they are not present.
• Avoids Multiple update statements.

syntax:


MERGE INTO table_name table_alias

USING (table|view|sub_query) alias

ON (join condition)

WHEN MATCHED THEN

UPDATE SET

col1 = col_val1,

col2 = col2_val

WHEN NOT MATCHED THEN

INSERT (column_list)

VALUES (column_values);



Example:


MERGE INTO copy_emp c

USING employees e

ON (c.employee_id = e.employee_id)

WHEN MATCHED THEN

UPDATE SET

c.first_name = e.first_name,

c.last_name = e.last_name,

...

c.department_id = e.department_id

WHEN NOT MATCHED THEN

INSERT VALUES(e.employee_id, e.first_name, e.last_name,

e.email, e.phone_number, e.hire_date, e.job_id,

e.salary, e.commission_pct, e.manager_id,

e.department_id);

More coming soon.