A SEMINAR REPORT ON

ORACLE
(IN PARTIAL FULFILLMENT FOR THE AWARD OF THE DEGREE) OF BACHLOR OF TECHNOLOGY IN COMPUTER ENGINEERING SESSION 2009-2013 GUIDED BY: Ms. RENU JONWAL Asst. professor (CS/IT) SUBMITTED BY: ANUJ SEN B.Tech. IV yr. CS (09EJGCS011)

JAGANNATH GUPTA INSTITUTE OF ENGINEERING & TECHNOLOGY

JAGANNATH INSTITUTE OF ENGINEERING & TECHNOLOGY, JAIPUR

BONAFIDE CERTIFICATE

This is to certify that this Technical report ORACLE. Is the bonafide work of ANUJ SEN Enrol.No= 09EJGCS011 carried out in partial fulfillment for the award of degree of B.TECH of RAJESTHAN TECHNICAL UNIVERCITY, KOTA under my supervision.

Mr. MAHESH VERMA (Associate professor) HOD (CS/IT) SUPERVISOR

Ms. RENU JONWAL (Assistant Professor)

 Preface. Acknowledgement. Introduction to Oracle. Oracle SQL. Introduction. Operators in SQL. Oracle SQL Functions. Database Objects. SQL*Plus. ORACLE PL/SQL Introduction PL/SQL Block Structure Select statement in PL/SQL Controlling PL/SQL Flow of Execution Oracle database Instance Initialization of parameter files Pfile SPFile Starting up Shutdown Command
Bibliography

Preface
Dear readers, In this report we are discussing about oracle database management system. In the first heading we know what is oracle? After that we will discuss about the features and its details. We all are know that oracle is very leading database management system in now days so we are presenting a short report about oracle which give u a small description about oracle.
Oracle is an Object-relational database. A relational database is an extremely simple way of thinking and managing the data used in business. It is nothing more than a collection of tables of data. We all encounter table every day: weather reports, stocks charts, sports scores. These all tables, with column headings and rows of information simply presented. Even so the relationalapproach can be sophisticated and powerful enough for even the most complex of business.

I think this is helpful for readers.

Acknowledgement
We place on record and warmly acknowledge the continuous encouragement, invaluable supervision, timely suggestions and inspired guidance offered by our guide Prof.RENU JONWAL, Assistant Professor, Department of Computer Science and Information Technology, Jagannath Gupta Institute of Engineering & Technology, Jaipur, in bringing this report to a successful completion. We are grateful to Prof. MAHESH VERMA, Head of the Department of Computer science and Information Technology, for permitting us to make use of the facilities available in the department to carry out the project successfully. Last but not the least we express our sincere thanks to all of our friends who have patiently extended all sorts of help for accomplishing this undertaking. Finally we extend our gratefulness to one and all who are directly or indirectly involved in the successful completion of this project report.

ANUJ SEN (COMPUTER SCIENCE)

Introduction to Oracle
Oracle is most widely database used in the world. It runs virtually every kind of computer. It functions virtually identically on all the machines, so when we learn it on one, we can use it on any other. This fact makes knowledgeable oracle user and developers very much in demand, and makes oracle knowledge and skills very portable. Oracle can easily be used for simple operations such as entering multiple data and running standard reports. But an approach would ignore its great power; it would be like be buying a high performance racing car, and then pulling it around with a horse. Oracle is an Object-relational database. A relational database is an extremely simple way of thinking and managing the data used in business. It is nothing more than a collection of tables of data. We all encounter table every day: weather reports, stocks charts, sports scores. These all tables, with column headings and rows of information simply presented. Even so the relational approach can be sophisticated and powerful enough for even the most complex of business. An object relational database supports all of the features of a relational database while also supporting object-oriented concepts and features. Oracle has grown from its humble beginnings as one of a number of databases available in the 1970s to the market leader of today. In its early days, Oracle Corporation was known more as an aggressive sales and promotion organization than a technology supplier. Over the years, the Oracle database has grown in depth and quality, and its technical capabilities now are generally recognized as the most advanced. With each release,

Oracle has added more power and features to its already solid base while improving the manageability.

Features of Oracle
To give some structure to the broad spectrum of the Oracle database, weve organized the features into the following sections:

Database application development features:

Database Programming: All flavors of the Oracle database include different languages and interfaces that allow programmers to access and manipulate the data in the database. Database programming features usually interest two groups: developers building Oracle-based applications that will be sold commercially and IT organizations within companies that customdevelop applications unique to their businesses. Database Extensibility: The Internet and corporate intranets have created a growing demand for storage and manipulation of nontraditional data types within the database. There is a need for extensions to the standard functionality of a database for storing and manipulating image, audio, video, spatial, and time series information. These capabilities are enabled through extensions to standard SQL.

 Database connection features:

Database Networking: Database users connect to the database by establishing a network connection. You can also link database servers via network connections. Oracle provides a number of features to establish connections between users and the database and/or between Database servers. Oracle Application Server: The popularity of Internet and intranet applications has led to a change in deployment from client/server (with fat clients running a significant piece of the application) to three-tier architecture (with a browser supplying everything needed on a thin client). The Oracle Application Server provides a means of implementing the middle tier of a three-tier solution for web-based applications, componentbased applications, and enterprise application integration.

Distributed database features:

Distributed Queries and Transactions: Data within an organization is often spread among multiple databases for reasons of both capacity and organizational responsibility. Users may want to query this distributed data or update it as if it existed within a single database.

 Heterogeneous Services: Heterogeneous Services allow nonOracle data and services to be accessed from an Oracle database through generic connectivity via ODBC and OLE-DB included with the database. Data movement features: Moving data from one Oracle database to another is often a requirement when using distributed databases, or when a user wants to implement multiple copies of the same database in multiple locations to reduce network traffic or increase data availability. You can export data and data dictionaries (metadata) from one database and import them into another.

Performance features:
Database Parallelization: Databases tasks implemented in parallel speed up querying, tuning, and maintenance of the database. By breaking up a single task into smaller tasks and assigning each subtask to an independent process, you can dramatically improve the performance of certain types of database operation. Examples of query features implemented in parallel include: Table scans Nested loops Sort merge joins GROUP BYs NOT IN sub queries (anti-joins) User-defined functions Index scans

 Select distinct UNION and UNION ALL Hash joins ORDER BY and aggregation Bitmap star joins Partition-wise joins Stored procedures (PL/SQL, Java, external routines) Data Warehousing and Business Intelligence: Oracle has also added some performance enhancements that specifically apply to data warehousing applications.

Database management features:

Oracle Enterprise Manager: As part of every Database Server, Oracle provides the Oracle Enterprise Manager(EM), a database

management tool framework with a graphical interface used to manage database users, instances, and features (such as replication) that can provide additional information about the Oracle environment. EM can also manage Oracles Application Server, Collaboration Suite, and E-Business Suite. Backup and Recovery: As every database administrator knows, backing up a database is a rather mundane but necessary task. An improper backup makes recovery difficult, if not impossible. Unfortunately, people often realize the extreme importance of this everyday task only when it is too lateusually after losing business-critical data due to a failure of a related system.

 Database Availability: Database availability depends upon the reliability and management of the database, the underlying operating system, and the specific hardware components of the system. Oracle has improved availability by reducing backup and recovery times. It has done this through: Providing online and parallel backup and recovery. Improving the management of online data through range partitioning.

Oracle SQL

The

history of SQL begins in an IBM laboratory in San Jose, California,

where SQL was developed in the late 1970s.

SQL stands for Structured Query Language, and the language itself is often
referred to as "sequel."

It is a standard and programming language.

SQL lets you access and manipulate databases. It was originally developed for IBM'sDB2 product. American National Standard Institute is primary organization for fostering of
technology standard. Long established computer standard includes the American code for information interchange (ANSI).

SQL

is a nonprocedural language, in contrast to the procedural or third

generation languages (3GLs) such as COBOL and C that had been created up to that time.

Nonprocedural
operation.

means what rather than how. For example, SQL describes

what data to retrieve, delete, or insert, rather than how to perform the

 Some

common RDBMS that used SQL are ORACLE, CYBASE, and

Microsoft SQL SERVER.

What Can SQL do?

SQL can execute queries against a database. SQL can retrieve data from a database. SQL can insert records in a database. SQL can update records in a database. SQL can delete records from a database. SQL can create new databases. SQL can create new tables in a database. SQL can create stored procedures in a database. SQL can create views in a database. SQL can set permissions on tables, procedures, and views.

SQL Statement
SELECT INSERT UPDATE DELETE MERGE CREATE ALTER DROP RENAME TRUNCATE COMMIT ROLLBACK SAVEPOINT GRANT REVOKE Data retrieval Data manipulating language(DML)

Data definition language(DDL)

Transaction control

Data control language(DCL)

Statement SELECT INSERT UPDATE DELETE MERGE CREATE ALTER DROP RENAME TRUNCATE COMMIT ROLLBACK SAVEPOINT GRANT REVOKE

Description Retrieves data from database Enter new rows, changes existing rows and removes unwanted rows from tables in the database, respectively. Set up changes and removes data structures from the tables.

Manages the changes made by DML statements. Changes to the data can be grouped together into logical transactions. Gives or remove access rights to both the Oracle database and the structure with in it.

SELECT Statement
Syntax: SELECT [ALL|DISTINCT] columns/expr [AS newname] FROM table [WHERE conditions] [GROUP BY fields] [HAVING conditions] [ORDER BY column/integer [ASC|DESC]] SELECT DISTINCT FROM WHERE GROUP BY HAVING ORDER BY Specifies the columns or fields to be included in the result set. Identifies the tables or views from which the data will be retrieved. The predicate(s) that determine which rows will be retrieved. Use to group output by the field with duplicate values and apply group functions to the grouped data. Use to place a condition on results of groupfunction calculations. Determines the sequence of the rows (Default order is ascending).

Example: SELECT ename, age, add as Address, sal Salary FROM emp WHERE sal>10000 ORDER BY sal DESC;

Operators in SQL

Arithmetic operators:
Used for arithmetic operations. -, +, *, /.

Concatenation operator(||):
It combines columns or character string to other columns.

Comparison operators:
<,<= ,>, >=, (<>, !=, ^=). Other comparison operators: LIKE: we use LIKE operator to perform wildcard search using % and _. % represents any sequence of zero or more characters. _ represents any single character. IN: Determine if a value is a member of a specific search set. IS NULL: Determine if a value is null. BETWEENAND..: Use to display rows based on a range of values.

Logical Operators: AND, OR, NOT. We can use NOT with various operators such as NOT IN, NOT
BETWEEN, NOT LIKE etc.

Set Operators: Set operators are used to combine results of two or more queries. UNION, UNION ALL, INTERSECT, MINUS.

Oracle SQL Functions

Number Functions: Number functions accept numeric input and return numeric values.

Example:
ABS (n), ROUND (n [, m]), TRUNC (n [, m]) Character Functions: Character functions accept character input and return either character or numeric values. Example: CONCAT (char1, char2), INITCAP (char), LOWER (char), LTRIM (char [, set]), LPAD (char, n [, char2])

Group Functions: Default is to include all the candidate rows, but using DISTINCT causes the group functions to use only the distinct values of the argument expression. Example: AVG ([DISTINCT | ALL] n), COUNT (* | [DISTINCT | ALL] expr), MAX ([DISTINCT | ALL] expr), MIN ([DISTINCT | ALL] expr), STDDEV ([DISTINCT | ALL] n), SUM ([DISTINCT | ALL] n), VARIANCE ([DISTINCT | ALL] n)

Date Functions and formats:

Date functions operate on values of the DATE data type. All date functions return a value of DATE data type, except the MONTHS_BETWEEN function, which returns a number. Example:

TRUNC [,fmt]),SYSDATE,ROUND(d[,fmt]),NEXT_DAY(d,char), NEW_TIME (d, a, b),MONTHS_BETWEEN (d, e)

(d

Conversion Functions: Conversion functions convert a value from one data type to another. Generally, the form of the function names follows the convention data type TO data type, the first data type is the input data type; the last data type is the output data type. Example: The SYSDATE function returns the current date and time from Oracle. If you want to only manipulate the CURRENT DATE, and you are not selecting other columns from a table, you may issue a SELECT statement using the Oracle one row table: SELECT SYSDATE FROM DUAL;

Database Transaction
Transaction consists of statement that make up of a consistent change to the data. A database traction consists of one of the following: DML statements which constitute one consistent change to the data. One DDL statement. One DCL statement. A transaction begin when the 1st DML statement is encountered and ends when one of the following occur: A COMMIT or ROLLBACK is issued. A DDL statement such as create is issued. A DCL statement is issued.

 The user exits iSQL*plus. A machine fails or the system crashes. After transaction ends, the next executable statement automatically starts the next transaction.

DDL statement or a DCL statement is automatically committed and

therefore implicitly ends the transaction.

Database Objects

There are several type of database objects: Table: It is a basic unit of storage composed of rows & columns. View: It logically represents subset of data from one or more tables. Sequence: It is used to generate primary key values. Index: Improves the performance of some queries. Synonym: It provides an alternative name for an object.

Table
It is a basic unit of storage composed of rows & columns. We dont need to specify size of the table at the time of creation. Table names & column names must begin with an alphabet. If a table does not belong to a user, the owner name must be prefix to the table.

Creating a Table
Syntax: CREATE TABLE [Schema].table name ( Column name data type [DEFAULT expr][column constraint] . [table constraint] ); Example: CREATE TABLE emp (roll no number(5) CONSTRAINT emp_rollno_pk PRIMARY KEY, name varchar2(20) CONSTRAINT emp_name_nn NOT NULL, age number(2), CONSTRAINT emp_age_chkCHECK (age>20)); To create a table, a user must have CREATE TABLE privilege. DEFAULT option prevents NULL values from entering the column.

Constraint
Unique constraint:

Ensures that no two rows of a table can have duplicate values in a

specific column or set of columns. Primary key constraint: Only one primary key can be created for a table. The primary key is column or set of columns that uniquely identifies each row in the table. The primary key constraint ensures that the columns cannot contain null values. Foreign key constraint: The FOREIGN key REFRENCE integrity constraint gesignates a column or a combination of columns as a foreign key and establish a relationship between primary key and unique key in the same table or different table. A foreign key value must match an existing value in the parent table or be null. ON DELETE CASCADE: It deletes the dependent rows in the child table when arpw in the parent table is deleted. ON DELETE SET NULL: It converts foreign key values to null when a row in parent table is removed. Without these two options, the row in the parent table cant be deleted if it referenced in the child table. Check constraint: It defines a condition that each row must satisfy.

 The condition can use the same construct as query conditions with
the following exception: References to CURRVAL, NEXTVAL and ROWNUM pseudo columns. Calls to SYSDATE, UID, USER, functions.

Joining Tables
Having a common column in two tables implies a relationship between two tables, the nature of relationship is determined by which table uses the column as the primary key. Sometimes we have to select data from two or more tables to make our result complete, we have to perform a join. The common column appearing in the child table is referred to as foreign key.

Types of joins
Oracle property joins: Equi join.

Non-equijoin. Outer join. Self join. Cartesian product.

SQL:1999 Compliant joins: Natural join.

Using clause. Full or left or right outer join.

 Arbitrary join condition using ON clause. Cross join.

View

A view is a logical table based on a table or another view. A view contains no data of its own but is like a window through which data
from table can be viewed or changed.

The view is stored as a select statement in the data dictionary.

Advantages of Views

View restricts access to a data because a view can display selected columns
and rows from the table.

View

can be used to make a simple query to retrieve the result of

complicated query.

One view can be used to retrieve data from several tables.

Creating a View
Syntax: CREATE [OR REPLACE][FORCE/NOFORCE] VIEW view name [(alias (, alias).)] AS sub query [WITH CHECK OPTION [CONSTRAINT constraint name]] [WITH READ ONLY [CONSTRAINT constraint name]]; OR REPLACE FORCE Recreates the view if it exist. Creates the view regardless of whether or not the

NOFORCE Alias WITH CHECK OPTION WITH READ ONLY Example:

base table exists. Creates the view only if the base table exists. Specify name for expression selected by the view query. Specifies that only rows accessible to the view can be inserted or updated. Ensures that no DML operation can be performed on this view.

CREATE VIEW v1 SELECT ename, job, deptno FROM emp Where deptno=20;

Removing a View
Syntax: DROP VIEW view_name; Example: DROP VIEWv1;

Sequence
A sequence is a user created database object that can be shared by multiple user to generate a sequential series of unique integers. Normally a sequence is used to generate primary key values that must unique for each row. The sequence is generated & incremented by an internal Oracle Server routine.

 Two sequence methods NextVal: provides the next value in a sequence. CurrVal: provides the current value in a sequence.

Creating a Sequence
Syntax: CREATE SEQUENCE sequence name [INCREMENT BYn] [START WITHn] [{MAXVALUE n/NOMAXVALUE}] [{MINVALUE n/NOMINVALUE}] [{CYCLE/NOCYCLE}] [{CACHE n/NOCACHE}] INCREMENT BY n Interval by which the sequence is incremented or decremented (default value is 1). START WITHn First no. to be generated (default is 1). MAXVALUE Max. value to be generated. NOMAXVALUE Specifies a max. value of 10^27 for an increasing sequence and -1 for a decreasing sequence. MINVALUE Min. value to be generated. NOMINVALUE Specifies a min. value of 1 for an increasing sequence and (10^26) for a decreasing sequence. CYCLE/NOCYCLE Specifies whether the sequence continues to generate values after reaching its max. or min. values(default is NOCYCLE). CACHE/NOCACHE Specifies how many values the Oracle server preallocates and keep in memory(default is 20). Example: CREATE SEQUENCE seq INCREMENT BY10 START WITH100

MAXVALUE 1000 CACHE 50;

Removing a Sequence
Syntax: DROP SEQUENCE sequence_name; Example: DROP SEQUENCE seq;

Index
An Oracle server index is a schema object that can speed up of retrieval of rows by using a pointer. An index provides direct & fast access to rows in a table. Once index is created, no direct activity required by the user. If we dont have index of a column , then a full table scan occurs. When we drop a table, corresponding index is automatically drop.

Creating an Index
Syntax: CREATE INDEX index_name ON table_name(column,column..); Example: CREATE INDEX CustNameIdx

ON CUSTOMER(Name);

Removing an Index
Syntax: DROP INDEXindex_name; Example: DROP INDEXCustNameIdx;

Synonym
To refer to a table owned by another user we need to prefix the table name with the name of the user who create it followed by a period. Creating synonym eliminates the need to qualify the object name with the schema and provide us with an alternative name for a table, view, sequence, procedure, etc.

Creating an Synonym
Syntax: CREATE [PUBLIC] SYNONYM synonym_nameFOR object; Example: CREATE PUBLIC SYNONYM loc FOR smith.location;

Removing an Synonym
Syntax: DROP SYNONYM synonym_name; Example:

DROP SYNONYMloc;

Error handling
Prevents database locking. Ensures that errors are presented to the user in a sensible format. Makes code robust. Essential when using PL/SQL as formal programming language or interfacing with Oracle applications.

Grant & Revoke statement

Use the GRANT statement to grant: System privileges to users and roles. Both privileges and roles are either local, global, or external. Grant roles to users and roles. To grant the CREATE SESSION system privilege to the sample user hr, allowing hr to log on to Oracle Database, issue the followingstatement: GRANT CREATE SESSION TO hr; To grant the dw_manager role with the ADMIN OPTION to the sample user sh, issue the following statement: GRANT dw_manager TO sh WITH ADMIN OPTION; Use the REVOKE statement to: Revoke system privileges from users and roles. Revoke roles from users and roles.

Revoke object privileges for a particular object from users and roles. REVOKE UPDATE ON hr.employees FROM emp;

Enterprise Manager Console

The Oracle Enterprise Manager Console provides graphical facilities for managing an Oracle database. The utility can be used to manage: Database structures such as tables and views. User accounts, passwords, roles, and privileges. The Manager Console includes a SQL scratchpad for executing SQL statements.

Application Logic
Oracle database application can be processed using : Programming language to invoke Oracle DBMS commands. Stored procedures. Start command to invoke database commands stored in .sql files.

Triggers.

SQL*Plus
Oracle SQL*Plus or the Oracle Enterprise Manager Console may be used to manage an Oracle database. SQL*Plus is a text editor available in all Oracle. Except inside quotation marks of strings, Oracle commands are caseinsensitive. The semicolon (;) terminates a SQL statement. The right-leaning slash (/) executes SQL statement stored in Oracle buffer.

 SQL*Plus can be used to: Enter SQL statements. Submit SQL files created by text editors, e.g., notepad, to Oracle.

SQL*Plus Prompt

Oracle PL/SQL
Procedural Language/SQL (PL/SQL) is Oracle Corporations procedural language extension to SQL, thestandard data access language for relational databases. PL/SQL offers modern software engineeringfeatures such as data encapsulation, exception handling, information hiding, object orientation, andbrings state-of-the-art programming to the Oracle Server and toolset. PL/SQL incorporates many of the advanced features of programming languages that were designedduring the 1970s and 1980s. It allows the data manipulation and query statements of SQL to be included In block-structured and procedural units of code, making PL/SQL a powerful transaction processinglanguage.

With PL/SQL, you can use SQL statements to finesse Oracle data, and PL/SQL controlstatements to process the data.

PL/SQL Environment
PL/SQL is not an Oracle product in its own right,it is a technology used by Oracle server and by certainOracle tools. Blocks of PL/SQL are passed to and processed by a PL/SQL engine, which may reside withinthe tool or within the Oracle server. The engine that is used depends on where the PL/SQL block is beinginvoked from. When you submit PL/SQL blocks from a Pro*C or a Pro*Cobol program, user-exit, iSQL*Plus, or ServerManager, the PL/SQL engine in the Oracle Server processes them. It separates the SQL statements and sendsthem individually to the SQL statements executor.

 A single transfer is required to send the block from the application to the Oracle Server, thus improvingperformance, especially in a client-server network.

PL/SQL code can also be stored in the Oracle Server assubprograms that can be referenced by any number of applications connected to the database.

Benefits of PL/SQL

Integration:

PL/SQL plays a central role in both the Oracle server (through stored procedures, stored functions,database triggers, and packages) and Oracle development tools (through Oracle Developer

componenttriggers).

 Oracle Forms Developer, Oracle Reports Developer, and Oracle Graphics Developer applications makeuse of shared libraries that hold code (procedures and functions) and can be accessed locally or remotely. SQL data types can also be used in PL/SQL. Combined with the direct access that SQL provides, theseshared data types integrate PL/SQL with the Oracle server data dictionary.

PL/SQL bridges the gapbetween convenient access to database technology and the need for procedural programming capabilities.

PL/SQL in Oracle Tools: Many Oracle tools, including Oracle Developer, have their own PL/SQL engine, which is independent ofthe engine present in the Oracle Server. The engine filters out SQL statements and sends them individually to the SQL statement executor in theOracle server. It processes the remaining procedural statements in the procedural statement executor,which is in the PL/SQL engine. The procedural statement executor processes data that is local to the application (that is, data alreadyinside the client environment, rather than in the database). This reduces the work that is sent to the Oracleserver and the number of memory cursors that are required. Improved Performance: PL/SQL can improve the performance of an application. The benefits differ depending on the executionenvironment.

 PL/SQL can be used to group SQL statements together within a single block and to send theentire block to the server in a single call, thereby reducing networking traffic. Without PL/SQL, theSQL statements are sent to the Oracle server one at a time. Each SQL statement results in anothercall to the Oracle server and higher performance overhead. In a networked environment, theoverhead can become significant. PL/SQL can also operate with Oracle Server application development tools such as Oracle Formsand Oracle Reports. By adding procedural processing power to these tools, PL/SQL enhancesperformance. You can take advantage of the procedural capabilities of PL/SQL, which are not available in SQL.

PL/SQL Block Structure: Every unit of PL/SQL comprises one or more blocks. These blocks can be entirely separate or nested onewithin another. The basic units (procedures, functions, and anonymous blocks) that make up a PL/SQLprogram are logical blocks, which can contain any number of nested sub blocks. Therefore, one block canrepresent a small part of another block, which in turn can be part of the whole unit of code. Modularized Program Development: Group logically related statements within blocks. Nest subblocks inside larger blocks to build powerful programs.

 Break down a complex problem into a set of manageable, welldefined, logical modules andimplement the modules with blocks. Place reusable PL/SQL code in libraries to be shared between Oracle Forms and Oracle Reportsapplications or store it in an Oracle server to make it accessible to any application that can interactwith an Oracle database. Portability: Because PL/SQL is native to the Oracle server, you can move programs to any host environment(operating system or platform) that supports the Oracle server and PL/SQL. In other words,PL/SQL programs can run anywhere the Oracle server can run,you do not need to tailor them toeach new environment. You can also move code between the Oracle server and your application. You can write portableprogram packages and create libraries that can be reused in different environments. Identifiers: In PL/SQL you can use identifiers to do the following: Declare variables, cursors, constants, and exceptions and then use them in SQL and proceduralstatements. Declare variables belonging to scalar, reference, composite, and large object (LOB) data types. Declare variables dynamically based on the data structure of tables and columns in the database. Procedural Language Control Structures:

 Procedural Language Control Structures allow you to do the following: Execute a sequence of statements conditionally. Execute a sequence of statements iteratively in a loop. Process individually the rows returned by a multiple-row query with an explicit cursor. Errors: The Error handling functionality in PL/SQL allows you to do the following: Process Oracle server errors with exception-handling routines. Declare user-defined error conditions and process them with exception-handling routines.

PL/SQL Block Structure

 The Declerative section contains all variables, constants, cursors, and userdefined exceptions that are referenced in the executable and declarative sections. Executable section contains SQL statements to manipulate data in the database and PL/SQL statements to manipulate datain the block. Exception handling section Specifies the actions to perform when errors andabnormal conditions arise in the executable section.

SELECT Statements in PL/SQL

The INTO clause is required. Queries must return one and only one row. Syntax: SELECT select_list INTO {variable_name[, variable_name]... | record_name} FROM table [WHERE condition];

Operators in PL/SQL

Operator
** +, *, / +, -, || =, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN NOT AND OR

Operation
Exponentiation Identity, negation Multiplication, division Addition, subtraction, concatenation Comparison

Logical negation Conjunction Inclusion

Controlling PL/SQL Flow of Execution

You can change the logical execution of statements using conditional IF statements andloop control structures. Conditional IF statements: Syntax:

IF condition THEN Statements; [ELSIF condition THEN Statements;] ELSE Statements;] END IF; There are 3 forms of IF statement: IF-THEN-END IF IF-THEN-ELSE-END IF IF-THEN-ELSIF-END IF CASE Expressions: Syntax: CASE selector WHEN expression1 THEN result1 WHEN expression2 THEN result2 ... WHEN expressionN THEN resultN [ELSE resultN+1;] END; Basic loop: Syntax: LOOP statement1; ... EXIT [WHEN condition]; END LOOP;

 condition is a Boolean variable orexpression (TRUE, FALSE, or NULL); WHILE Loops: Syntax: WHILE condition LOOP statement1; statement2; ... END LOOP; Condition is evaluated at the beginning of each iteration. FOR Loops: Syntax: FOR counter IN [REVERSE] lower_bound..upper_bound LOOP statement1; statement2; ... END LOOP; Do not declare the counter; it is declared implicitly.

ORACLE DATABASE INSTANCE

Initialization Parameter Files
Entries are specific to the instance being accessed There are two kinds of parameters: Explicit: Having an entry in the file Implicit: No entry within the file, but assuming the Oracle default values Multiple files can be used for a single database to optimize performance in different situations. Changes to entries in the file take effect based on the type of initialization parameter file used; Static parameter file, PFILE -- Persistent parameter file, SPFILE

Initialization Parameter Files

To start an instance, the Oracle server reads the initialization parameter file. Two types of initialization parameter files exist: Static parameter file, PFILE, commonly referred to as initSID.ora Persistent parameter file, SPFILE, commonly referred to as spfileSID.ora

Parameter File Contents

A list of instance parameters The name of the database the instance is associated with Allocations for memory structures of the System Global Area (SGA) What to do with filled online redo log files The names and locations of control files Information on undo segments

PFILE
initSID.ora The PFILE is a text file that can be modified with an operating system editor. Modifications to the file are made manually. Changes to the file take effect on the next startup. Its default location is $ORACLE_HOME/dbs.
The PFILE is a text file that can be maintained using a standard operating system editor. The parameter file is read only during instance startup. If the file is modified, the instance must be shut down and restarted in order to make the new parameter values effective. Some parameters are dynamic, which means that they can be modified while the instance is running. Changes to dynamic parameters are not reflected in the PFILE. By default, the PFILE is located in the $ORACLE_HOME/dbs directory on a Unix machine and named initSID.ora. A sample init.ora file is created by the Universal Installer during installation. This sample init.ora file can be used to create an instance-specific initSID.ora. EXAMPLE:-

# Initialization Parameter File: initdb01.ora db_name = db01 instance_name = db01 control_files = ( /u03/oradata/db01/control01db01.ctl, /u03/oradata/db01/control02db01.ctl) db_block_size = 4096 db_block_buffers = 500 shared_pool_size = 31457280 # 30M Shared Pool db_files = 1024 max_dump_file_size = 10240 background_dump_dest = /u05/oracle9i/admin/db01/bdump user_dump_dest = /u05/oracle9i/admin/db01/udump core_dump_dest = /u05/oracle9i/admin/db01/cdump undo_management = auto undo_tablespace = undtbs ..

Rules for Specifying Parameters

Specify the values in the following format: keyword=value. All parameters are optional. The server has a default value for each parameter. This value may be operating system dependent, depending on the parameter. Parameters can be specified in any order. Comment lines begin with the # symbol. Enclose parameters in double quotation marks to include character literals. Additional files can be included with the keyword IFILE. If case is significant for the operating system, then it is also significant in filenames. Multiple values are enclosed in parentheses and separated by commas. Note: Develop a standard for listing parameters; either list them alphabetically or group them by functionality. The PFILE varies from instance to instance and not does necessarily look like the preceding example.

SPFILE spfileSID.ora
Binary file with the ability to make changes persistent across shutdown and startup. Maintained by the Oracle server. Records parameter value changes made with the ALTER SYSTEM command. Can specify whether the change being made is temporary or persistent. Values can be deleted or reset to allow an instance to revert to the default value ALTER SYSTEM SET undo_tablespace = 'UNDO2';
SPFILE, new to Oracle9i, is a binary file. The file is not meant to be modified manually and must always reside on the server side. By default, the file is located in $ORACLE_HOME/dbs and has a default name in the format of spfileSID.ora. Once the file is created it is maintained by the Oracle server. The SPFILE provides the ability to make changes to the database persistent across shutdown and startup.

The ALTER SYSTEM command is used to change the value of instance parameters. The SCOPE setting determines the scope of the change. MEMORY: Changes the parameter value only in the currently running instance SPFILE: Changes the parameter value in the SPFILE only BOTH: Changes the parameter value in the currently running instance and the SPFILE ALTER SYSTEM SET parameter = value [SCOPE = MEMORY|SPFILE|BOTH] The SPFILE can be modified with the ALTER SYSTEM command when the instance starts using the SPFILE or using the PFILE with the parameter SPFILE.

Creating an SPFILE
SPFILE can be created from an initSID.ora file using the CREATE SPFILE command, which can be executed before or after instance startup: CREATE SPFILE FROM PFILE;
An SPFILE is created from an initSID.ora file using the CREATE SPFILE command. This can be executed before or after the database is open. CREATE SPFILE [='SPFILE-NAME'] FROM PFILE[='PFILE-NAME'] Where: SPFILE-NAME: Name of the SPFILE to be created: if not specified, the default SPFILE name is assumed. PFILE-NAME: Name of a PFILE, must be available on the server side. The CREATE SPFILE command requires the SYSDBA role to execute. The syntax can be reversed to create a PFILE

How to Modify the SPFILE Configuration

1. Launch the Console: from an SPFILE. % oemapp console 2. Choose Launch Standalone 3. Expand your working database from the databases folder 4. Expand Instance folder and click Configuration 5. In the General tab, click All Initialization Parameters 6. Modify a parameter in the value column.

5. Click OK.

SPFILE Example
*.background_dump_dest='$ORACLE_HOME/admin/db01/bdump' *.compatible='9.0.0' *.control_files='/u03/oradata/db01/ctrl01db01.ctl','/u03/orad ata/db01/ctrl02db01.ctl' *.core_dump_dest='$ORACLE_HOME/admin/db01/cdump' *.db_block_buffers=500 *.db_block_size=4096 *.db_files=40 *.db_name='db01' *.instance_name='db01' *.remote_login_passwordfile='exclusive' *.shared_pool_size=31457280 # 30M Shared Pool *.undo_management='AUTO' db01.undo_tablespace='UNDOTBS01' db02.undo_tablespace='UNDOTBS02' Rules Applying to SPFILE The comments specified on the same lines as a parameter setting in the PFILE are maintained in the SPFILE. All other comments are ignored. Although the text of an SPFILE is easily viewed in Unix the SPFILE is binary.

STARTING UP

Starting Up a Database
Opening the Database Normal database operation means that an instance is started and the database is mounted and open; with normal database operation, any valid user can connect to the database and perform typical data access operations. Opening the database includes the following tasks: Opening the online data files Opening the online redo log files If any of the data files or online redo log files are not present when you attempt to open the database, the Oracle server returns an error. During this final stage, the Oracle server verifies that all the data files and online redo log files can be opened and checks the consistency of the database. If necessary, the System Monitor background process (SMON) initiates instance recovery.

Start up the instance and open the database:

STARTUP PFILE=$ORACLE_HOME/dbs/initdb01.ora

Starting Up:To start up an instance, use the following command: STARTUP [FORCE] [RESTRICT] [PFILE=filename] [OPEN [RECOVER][database] |MOUNT |NOMOUNT] Note: This is not the complete syntax. where: OPEN enables users to access the database MOUNT mounts the database for certain DBA activities but does not provide user access to the database NOMOUNT creates the SGA and starts up the background processes but does not provide access to the database PFILE=parfile enables a nondefault parameter file to be used to configure the Instance. FORCE aborts the running instance before performing a normal startup RESTRICT enables only users with RESTRICTED SESSION privilege to access the database

RECOVER begins media recovery when the database starts

Initialization Parameter File

By default, when the database is started, the Oracle server looks in $ORACLE_HOME/dbs for an initialization parameter file. The Oracle server first attempts to read file spfilesid.ora and if not found then file initsid.ora. If neither file is located, an error message is received.

The ALTER DATABASE Command Change the state of the database from NOMOUNT to
MOUNT:
ALTER DATABASE db01 MOUNT; Open the database as a read-only database: ALTER DATABASE db01 OPEN READ ONLY;

Changing the Status of the Database To open the database from STARTUP NOMOUNT to a MOUNT stage or from MOUNT to an OPEN stage, use the ALTER DATABASE command: ALTER DATABASE { MOUNT | OPEN } To prevent data from being modified by user transactions, the database can be opened in readonly mode. To start up an instance, use the following command: ALTER DATABASE OPEN [READ WRITE| READ ONLY] where: READ WRITE opens the database in read-write mode, so that users can generate redo logs READ ONLY restricts users to read-only transactions, preventing them from generating redo log information.

Opening a Database in Restricted Mode Use the STARTUP command to restrict access to a
database: Use the ALTER SYSTEM command to place an instance in restricted mode:
STARTUP RESTRICT ALTER SYSTEM ENABLE RESTRICTED SESSION; Opening a Database in Restricted Mode A restricted session is useful, for example, when you perform structure maintenance or a database export and import. The database can be started in restricted mode so that it is available only to users with the RESTRICTED SESSION privilege. The database can also be put in restricted mode by using the ALTER SYSTEM SQL command: ALTER SYSTEM [ {ENABLE|DISABLE} RESTRICTED SESSION ] where: ENABLE RESTRICTED SESSION: enables future logins only for users who have the RESTRICTED SESSION privilege DISABLE RESTRICTED SESSION: disables RESTRICTED SESSION so that users who do not have the privilege can log on.

Terminate Sessions
After placing an instance in restricted mode, you may want to kill all current user sessions before performing administrative tasks. This can be done by the following: ALTER SYSTEM KILL SESSION 'integer1,integer2' where: integer1: is the value of the SID column in the V$SESSION view integer2: is the value of the SERIAL# column in the V$SESSION view

Opening a Database in Restricted Mode (continued)

Note: The session ID and serial number are used to uniquely identify a session. This guarantees that the ALTER SYSTEM KILL SESSION command is applied to

the correct session even if the user logs off and a new session uses the same session ID. Effects of Terminating a Session The ALTER SYSTEM KILL SESSION command causes the background process PMON to perform the following steps upon execution: Roll back the users current transaction Release all currently held table or row locks. Free all resources currently reserved by the user.

Opening a Database in Read-Only Mode

A databases can be opened as a read-only database. A read-only database can be used to: Execute queries Execute disk sorts using locally managed tablespaces Take data files offline and online, not tablespaces Perform recovery of offline data files and tablespaces.

Shutting Down the Database

Shut down the database to make operating system offline backups of all physical structures and to have modified static initialization parameters take effect. To shut down an instance you must connect as SYSOPER or SYSDBA and use the following command: SHUTDOWN [NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT ]

Shutdown Normal
Normal is the default shutdown mode. Normal database shutdown proceeds with the following conditions: No new connections can be made.

 The Oracle server waits for all users to disconnect before completing the shutdown. Database and redo buffers are written to disk. Background processes are terminated, and the SGA is removed from memory. Oracle closes and dismounts the database before shutting down the instance. The next startup does not require an instance recovery. Shutdown Transactional A transactional shutdown prevents clients from losing work. A transactional database shutdown proceeds with the following conditions: No client can start a new transaction on this particular instance. A client is disconnected when the client ends the transaction that is in progress. When all transactions have finished, a shutdown immediately occurs. The next startup does not require an instance recovery.

Shutdown Immediate
Immediate database shutdown proceeds with the following conditions: Current SQL statements being processed by Oracle are not completed. The Oracle server does not wait for users currently connected to the database to disconnect. Oracle rolls back active transactions and disconnects all connected users. Oracle closes and dismounts the database before shutting down the instance. The next startup does not require an instance recovery.

Shutdown Abort
If the normal and immediate shutdown options do not work, you can abort the current database instance. Aborting an instance proceeds with the following conditions: Current SQL statements being processed by the Oracle server are immediately terminated. Oracle does not wait for users currently connected to the database to disconnect. Database and redo buffers are not written to disk. Uncommitted transactions are not rolled back. The instance is terminated without closing the files. The database is not closed or dismounted. The next startup requires instance recovery, which occurs automatically.

BIBLIOGRAPHY
I took assistance and consulted from the underneath named sources of information:

Introduction_To_Oracle9i_SQL_Student_Guide. Introduction_To_Oracle9i_PL-SQL_Student_Guide. Official website of IBM. ww.oracle.com Encyclopedia website. www.wikipedia.com