Education and Research Department
Project Specification for Oracle
Dr. Ravindra M.P.
Signature / Date
Infosys Technologies Limited
Question Bank - Oracle for Beginners
Author Venkata Archana Alishetty
Date Written May 30, 2007.
I hereby declare that this document is based on my personal experiences. To the
best of my knowledge, this document does not contain any material
Subqueries can be used to answer queries such as "who has a salary more than Tom's". For such
query, two queries have to be executed: the first query finds Tom's
Oracle Procedure Tuning Tips
Documented by Mihir Sanghvi
Disclaimer: This document has been made purely on project experiences.
This document covers tips that can be used to optimize the performance of
stored procedures. 3 tips that have
Performance Tuning - Now You are the V8 Expert
Richard J. Niemiec
Version8 of the Oracle database has brought on a whole new level of issues for the DBA. While the queries for tuning the
database and individual queries has not changed much,
Each data block has an associated data source. Action by the application or by the
end user can cause Form Builder to issue a query to the data source. The following
paragraphs describe the way Form Builder constructs and processes quer
What are the components of physical database structure of Oracle database?
Oracle database is comprised of three types of files. One or more datafiles, two are more redo log
files, and one or more control files.
What are the components of logical database
SOME USEFUL SQL QUERIES AND PL/SQL BLOCKS
Date written (mm/dd/yy): 15/07/05
I hereby declare that this document is based on my personal experiences and experiences
of our project members. To the best of my knowledge, this
Introduction to PL/SQL
PL/SQL is a programming language by Oracle Corporation. It is a procedural language extension of
SQL. This language aims at bridging the gap between database technology and high-level procedural
PL/SQL is a
Raise_application_error is a procedure of package DBMS_STANDARD which
allows to issue an user_defined error messages from stored sub-program or database
The RAISE_APPLICATION_ERROR is actually a procedure defined by Oracle
I. (The following query retrieves "2" highest paid employees FROM each Department :
SELECT deptno, empno, sal
FROM emp e
2 > ( SELECT COUNT(e1.sal)
FROM emp e1
WHERE e.deptno = e1.deptno AND e.sal < e1.sal )
ORDER BY 1,3 DESC;
II. Query that will di
The SQL Optimizers
Whenever you execute a SQL statement, a component of the database known as the optimizer
must decide how best to access the data operated on by that statement. Oracle supports two
optimizers: the rule-base optimizer (which was
Q: Emp table had an employee with salary 2000. I issued an update statement to set the salary to
3000. Then I issued a create table statement. However the create table command errored out. I want
to rollback the earlier update statement. Can I do that?
ROWNUMBERING WITH AN ORDER BY CLAUSE
One of the most often uses of the pseudo column rownum is to provide serial numbers to the records in a
query. This feature is widely used in reports to represent systematic display of information.
Getting Started with Oracle Reports
Author: Prashant P Earath
Date written: June 16, 2006
I hereby declare that this document is based on my experience. To the best of my
knowledge, this document does not contain any material that infringes th
1. To see current user name
Sql> show user;
Change SQL prompt name
SQL> set sqlprompt Manimara >
Switch to DOS prompt
How do I eliminate the duplicate rows ?
SQL> delete from table_name where rowid not in (select
Oracle divides database command into six groups. The six command groups are:
1. Data definition language (DDL) commands.
a. These commands create tables, create declarative constraints, create stored
procedures, and create triggers.
b. Most types of datab
ORACLE DATABASE ADMINISTRATOR
Oracle Database 10g new features: 12 new 10g features in a nutshell
Mike Ault, Madhu Tumma and Daniel Liu
Rating: -4.21- (out of 5)
If you are planning to work with Oracle10g but don't quite know what to expect fro
I have a table in sql server with two fields named 'start_time' and
'stop_time'. both fields have datatype set as 'datetime'. now I want to
subtract the 'start_time' from 'stop_time'? I have tried datediff()
fuction,but no use. I want the correct outpput
NOTE:- TO LIST OUT ALL THE STORED PROCEDURES THE COMMAND IS
SQL> SELECT * FROM USER_SOURCE;
-1. LOCAL PROCEDURE
PROCEDURE P2 IS
SELECT SAL,JOB INTO SALARY,DESG FROM EMP WHERE EMPNO=&N;
-This document lists the order in which triggers fire in Oracle Forms 4.5:
o The first column, Action, describes the function to be performed
(e.g. deleting a record).
o The second column, Trigger Firing Order, describes the order
in which triggers fire a
What is SQL*Plus and where does it come from?
SQL*Plus is a command line SQL and PL/SQL language interface and reporting tool
that ship with the Oracle Database Server. It can be used interactively or driven from
SQL*Plus's predecessor was called
PREDEFINED EXCEPTIONS EXAMPLES
TOO MANY ROWS
NO DATA FOUND
Best Practices for Oracle Forms
Best Practices for Oracle Forms Developer 2000
AUTHOR: Mandar Inamdar
DATE: 05th Nov 2003
I hereby declare that this document is based on my personal experiences and / or experie
SQL programming tips
1.Things to keep in mind while writing a sql
2. To Disable Indexes
3. To Enable Indexes
4.To Insert/delete/select when an index is in the unusable state follow these
5. Use Of Cursors To Insert Data As A Bulk In To A Tab
SQL Trace File
Reply from Nishant KAVI on 2/6/2003 10:32:00 AM
Before you can use SQL Trace, you need to take care of some prerequisites.
Three initialization parameters affect how SQL Trace operates. One of them
points to the location in which Oracle wri
RE: explain the differences between cost-based optimiz.
A long time ago, the only optimizer in the Oracle database was the Rule-Based Optimizer (RBO).
Basically, the RBO used a set of rules to determine how to execute a query. If an index was available