UNIT – II

ORACLE 9i

Introducing Oracle9i

Oracle9i takes business where it needs to be: meeting and exceeding stringent
demands for high-quality service in a service-driven marketplace. Oracle9i is designed to
optimize traditional, internet and intranet applications, and to stimulate the emerging hosted
application market on the internet.

Oracle9i builds on historic strengths to offer the first complete and simple software
infrastructure for the internet's next generation of intelligent, collaborative applications. The
Oracle9i new features expedite delivery of critical performance, scalability, and availability
essential to providing hosted service software for anyone, anywhere, anytime.

Oracle9i architecture is depicted in Figure 1-1.

Figure 1-1 Oracle9i Architecture

1
PERSONAL DATA BASE:

Oracle9i Database

The Oracle9i Database introduces the following advanced and automated design features that
refine Oracle9i Application Server and Oracle9i Developer Suite to optimize performance for
traditional applications and the emerging hosted application market.

Oracle9i Real Application Clusters

Replacing Oracle Parallel Server, Oracle9i Real Application Clusters provides out-of-the-
box, linear scaling transparency, compatibility with all applications without redesign, and the
ability to rapidly add nodes and disks.

Systems Management

Integrated system management products create a complete view of all critical components
that drive e-business processes. From the client and application server to the database and
host, Oracle9i quickly and completely assesses the overall health of an e-business
infrastructure.

High Availability

Setting a new standard for high availability, Oracle9i introduces powerful new functionality
in areas of disaster recovery, system fault recovery, and planned downtime.

High Security

Oracle9i offers the most secure internet platform for protecting company information through
multiple layers of security for data, users, and companies. Included are features for building
internet-scale applications, for providing security for users, and for keeping data from
different hosted user communities separate.

Oracle9i Application Server

Recognized as the leading application server for database-driven Web sites,

Oracle9i Application Server offers the industry's most innovative and comprehensive set of
middle-tier services.

2
 Client/Server Database

Oracle 9i is a client/server database management system based on the relational data model.
It handles failures well through transaction logging and allows administrators to manage
users and databases through administrative tools.

CLIENT/SERVER IN RESPECT OF DATABASES

A Database Management System (DBMS) lies at the center of most Client/Server systems in
use today.

– Provide transparent data access to multiple and heterogeneous clients.

– Process client data requests at the local server. – Send only the SQL result to the clients
over the network.

Centralized and Client-Server Architecture

Centralized and client-server architectures are two fundamental approaches in the
design of database management systems (DBMS) and other network-based systems. Each has
its own set of characteristics, advantages, and disadvantages.
Centralized Architecture

In a centralized architecture, all data and processing are handled by a single central
server. This server is responsible for storing, retrieving, and updating data, as well as
executing application programs and user interfaces. Users access the system through
terminals or workstations that connect to the central server. This architecture is also known as
a monolithic architecture1.
Advantages
 Simplicity: With only one server to manage, the system is easier to maintain and
administer.
 Consistency: All clients access the same data, ensuring data consistency and integrity.
Disadvantages
 Scalability Issues: As the number of clients or the amount of data increases, the central
server can become a bottleneck.
 Single Point of Failure: If the central server goes down, all clients lose access to the data

3
What is SQL*Plus?

SQL*Plus provides an interactive and batch processing environment that

dispatches commands to the SQL and PL/SQL engines. You can work either in the
interactive SQL*Plus command-line interface (CLI) or in Oracle SQL Developer through a
Java-based GUI.

SQL*Plus is an interactive and batch query tool that is installed with every Oracle
Server or Client installation. It has a command-line user interface, and more recently, a web-
based user interface called iSQL*Plus.

On Windows platforms, context menu options accessed through the right mouse
button enable local files to be sent to the iSQL*Plus user interface.

SQL*Plus has its own commands and environment, and it provides access to the Oracle
RDBMS. It allows you to enter and execute SQL, PL/SQL, SQL*Plus and operating system
commands to perform the following:

 enter SQL*Plus commands to configure the SQL*Plus environment

 enter, edit, store, retrieve, and run SQL commands and PL/SQL blocks

 format, perform calculations on, store, and print from query results

 interact with an end user

 startup and shutdown a database

 connect to a database

 define variables

 capture errors

 list column definitions for any table

 perform database administration

Log into Oracle Database with SQL*Plus:

The easiest way to start looking at data in your Oracle database is to use the built-in tool
called SQL*Plus. SQL*Plus gives you a convenient interface with a database in which you
can:

4
 1. Create database objects such as tables, indexes, and constraints
2. Create users and roles
3. Grant or revoke privileges of users and roles
4. Edit and execute queries
5. Adjust output settings to produce simple reports
6. Edit and execute PL/SQL blocks
7. Insert, update, and delete data in tables
8. Spool output to a file
9. Read and write files containing SQL or PL/SQL scripts

SQL*Plus Commands

It is a command-line tool for Oracle Database that allows users to interact with the database
using SQL and PL/SQL commands. Here are some essential SQL*Plus commands:

Connecting to a Database

To connect to a database, use the CONNECT command.

CONNECT username/password@hostname:port/service_name;

Selecting Data

To retrieve data from a table, use the SELECT command.

SELECT * FROM tablename;

Describing a Table

To display the structure of a table, use the DESCRIBE command.

DESCRIBE tablename;

Setting Line Size

To set the maximum line width for query output, use the SET LINESIZE command.
SET LINESIZE n;

5
Setting Page Size

To set the number of lines per page for query output, use the SET PAGESIZE command.

SET PAGESIZE n;

Spooling Output to a File

To redirect query output to a file, use the SPOOL command.

SPOOL filename;

Executing a Script File

To execute a script file, use the START command.

START [Link];

Editing Commands

To open the default text editor to edit the current command buffer, use the EDIT command.
EDIT;

Executing Host Commands

To execute an operating system command, use the HOST command.

HOST command;

Exiting SQL*Plus

To disconnect from the database and exit SQL*Plus, use the QUIT or EXIT command.
QUIT;
-- or
EXIT;

ALTERNATE TEXT EDITORS IN SQL * PLUS

An alternative way to edit the SQL*Plus buffer is to use the text editor within your
operating system. The exact editor used depends on which operating system you are using.
For example, if you are using Windows, the editor invoked is Microsoft Notepad or
download Notepad++.

6
 In Oracle SQLPlus, the default text editor is typically set to a basic editor like
Notepad on Windows or vi on Unix/Linux systems. However, you can configure alternate
text editors to suit your preferences. Here's how you can define alternate text editors in
SQLPlus:

1. Using the DEFINE_EDITOR Command

 You can set your preferred editor by using the DEFINE_EDITOR command in SQL*Plus. For
example:

 Copy the codeDEFINE _EDITOR = 'notepad'

Replace 'notepad' with the name of your preferred editor, such as 'vim', 'nano', or 'gedit'.

2. Setting the Editor in the SQL*Plus Environment

 On Windows:
o Open the sqlplus command prompt.

o Use the SET command to define the editor:

SQL*Plus Worksheet in Oracle SQL Developer

The SQLPlus Worksheet in Oracle SQL Developer is a powerful tool that allows users to
execute SQL and SQLPlus commands for querying, managing, and interacting with Oracle
databases. It supports a wide range of commands and functionalities, making it a versatile
environment for database operations.

Supported SQL*Plus Commands

The SQL Worksheet supports most SQL*Plus commands, except those related to formatting.
Commonly used commands include:
-- Execute a SQL script

@[Link]

-- Describe the structure of a table

DESC employees;

-- Define a variable

DEFINE var_name = 'value';

7
-- Execute a PL/SQL block

EXEC dbms_output.put_line('Hello World');

-- Save the current SQL script

SAVE [Link] CREATE;

-- Show system variables or settings

SHOW ALL;

-- Disconnect from the database

DISCONNECT;

-- Exit the SQL Worksheet

EXIT;

These commands allow users to perform tasks such as defining variables, executing scripts, and managing
database connections.

Oracle DDL Examples

Data Definition Language (DDL) in Oracle is used to define and manage database structures
like tables, views, and indexes.
Common DDL commands include CREATE, ALTER, DROP, TRUNCATE, and RENAME.

1. CREATE Command

The CREATE command is used to create database objects such as tables.

CREATE TABLE employees (
employee_id NUMBER(5) PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
hire_date DATE,
salary NUMBER(10, 2)
);
This creates a table named employees with columns for employee details.

8
 2. ALTER Command

The ALTER command modifies the structure of an existing table.

ALTER TABLE employees ADD department VARCHAR2(30);
This adds a new column department to the employees table.

3. DROP Command

The DROP command removes a database object permanently.

DROP TABLE employees;
This deletes the employees table along with its data and structure.

4. TRUNCATE Command

The TRUNCATE command removes all rows from a table but retains its structure.
TRUNCATE TABLE employees;
This clears all data from the employees table without generating rollback data.

5. RENAME Command

The RENAME command changes the name of a database object.

RENAME employees TO staff;
This renames the employees table to staff.

Key Considerations

 Implicit Commit: DDL commands automatically commit transactions.

 Irreversible: Commands like DROP and TRUNCATE cannot be rolled back.
 Constraints: Ensure no dependent objects exist before dropping or altering tables.

NAMING RULES AND CONVENTIONS

Underscores separate the distinct terms that define the Table or Column as per the
standard conventions.

Table names must start with the application name. For example, FSI_, REV_, and so on. All
table names must be in plural form. · Table names must have indicators of the functional
area.

9
Here are some key recommendations based on "Oracle PL/SQL Coding Standards" and Best
Practices: Use meaningful, descriptive names that clearly indicate the purpose of the
variable. Avoid reserved words and special characters. Use underscores (_) to separate words
for readability.

DATA TYPES:

In SQL*Plus, which is an Oracle command-line tool, data types are used to define the kind of
data that can be stored in a table's columns. Here’s a concise explanation of the commonly
used SQL data types:

1. Character Data Types

 CHAR(size): Fixed-length character data. The size specifies the number of characters
(default is 1, max is 2000).

 VARCHAR2(size): Variable-length character data. The size specifies the maximum number
of characters (up to 4000).

 CLOB: Used for large text data (up to 4GB).

2. Numeric Data Types

 NUMBER(p, s): Stores numeric data.

o p (precision): Total number of digits (max 38).

o s (scale): Number of digits to the right of the decimal point.

 FLOAT: A subtype of NUMBER for floating-point numbers.

3. Date and Time Data Types

 DATE: Stores date and time values (e.g., DD-MON-YYYY HH:MI:SS).

 TIMESTAMP: Includes fractional seconds in addition to the DATE type.

 INTERVAL YEAR TO MONTH: Stores a time interval in years and months.

 INTERVAL DAY TO SECOND: Stores a time interval in days, hours, minutes, seconds,
and fractional seconds.

4. Binary Data Types

 BLOB: Binary Large Object, used for storing binary data (e.g., images, videos, etc.).

 BFILE: Stores a pointer to a binary file stored outside the database.

10
5. Other Data Types

 RAW(size): Stores binary data in hexadecimal format (max size: 2000 bytes).

 LONG: Stores variable-length character data (up to 2GB, but deprecated in favor of CLOB).

 ROWID: Stores the unique address of a row in a table.

CONSTRAINTS:

SQL*Plus, constraints are rules enforced on data in a table to ensure its integrity and
validity. They help maintain accuracy and consistency in the database. Below are the types of
constraints and their definitions:

1. NOT NULL

 Ensures that a column cannot have a NULL value.

 Example:

 Copy the codeCREATE TABLE employees (

 emp_id NUMBER PRIMARY KEY,
 emp_name VARCHAR2(50) NOT NULL
 );

2. UNIQUE

 Ensures that all values in a column or a combination of columns are unique.

 Example:

 Copy the codeCREATE TABLE employees (

 emp_id NUMBER PRIMARY KEY,
 email VARCHAR2(100) UNIQUE
 );

3. PRIMARY KEY

 Combines NOT NULL and UNIQUE. It uniquely identifies each record in a table.

 Example:

 Copy the codeCREATE TABLE employees (

 emp_id NUMBER PRIMARY KEY,
 emp_name VARCHAR2(50)
 );

11
4. FOREIGN KEY

 Establishes a relationship between two tables by linking a column in one table to the primary
key in another.

 Example:

 Copy the codeCREATE TABLE orders (

 order_id NUMBER PRIMARY KEY,
 emp_id NUMBER,
 FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
 );

5. CHECK

 Ensures that all values in a column satisfy a specific condition.

 Example:

 Copy the codeCREATE TABLE employees (

 emp_id NUMBER PRIMARY KEY,
 salary NUMBER CHECK (salary > 0)
 );

6. DEFAULT

 Assigns a default value to a column if no value is provided during insertion.

 Example:

 Copy the codeCREATE TABLE employees (

 emp_id NUMBER PRIMARY KEY,
 status VARCHAR2(10) DEFAULT 'Active'
 );

CREATE AN ORACLE TABLE

In SQL*Plus, you use the CREATE TABLE statement. Below is an example of how to
define a table:

CREATE TABLE employees (

employee_id NUMBER(6) PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50) NOT NULL,
email VARCHAR2(100) UNIQUE,
hire_date DATE NOT NULL,
job_id VARCHAR2(10) NOT NULL,

salary NUMBER(8, 2),

12
 department_id NUMBER(4),
CONSTRAINT fk_department FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);
Explanation:

1. employee_id NUMBER(6) PRIMARY KEY: Defines a column employee_id as a 6-

digit number and sets it as the primary key.

2. first_name VARCHAR2(50): Defines a column first_name with a maximum length

of 50 characters.

3. last_name VARCHAR2(50) NOT NULL: Ensures last_name cannot be null.

4. email VARCHAR2(100) UNIQUE: Ensures email values are unique.

5. hire_date DATE NOT NULL: Stores date values and ensures it cannot be null.

6. job_id VARCHAR2(10) NOT NULL: Stores job IDs with a maximum length of 10
characters.

7. salary NUMBER(8, 2): Stores salary values with up to 8 digits, including 2 decimal
places.

8. department_id NUMBER(4): Stores department IDs as a 4-digit number.

9. CONSTRAINT fk_department FOREIGN KEY: Adds a foreign key constraint

linking department_id to the departments table.

ALTER TABLE
The ALTER TABLE statement in SQL is used to add, delete, or modify columns in an
existing table. It can also be used to add and drop various constraints on an existing table.

Adding a Column

To add a column to a table, use the following syntax:

ALTER TABLE table_name

ADD column_name datatype;

13
Example:
ALTER TABLE Customers

ADD Email varchar(255);

Modifying a Column

To change the data type of a column in a table, use the following syntax:
ALTER TABLE table_name

ALTER COLUMN column_name datatype;

Example:
ALTER TABLE Persons

ALTER COLUMN DateOfBirth year;

Dropping a Column

To delete a column from a table, use the following syntax:

ALTER TABLE table_name

DROP COLUMN column_name;

Dropping, Renaming & Truncating Table:

1. Dropping a Table

 Definition: Permanently deletes a table and all its data from the database.

 Syntax:

 Copy the code DROP TABLE table_name;

 Key Points:

o Cannot be undone.

o Removes the table structure and its data.

2. Renaming a Table

 Definition: Changes the name of an existing table.

Syntax:
 RENAME old_table_name TO new_table_name;

14
  Key Points:

o Only the table name changes; data and structure remain intact.

o Useful for reorganizing or clarifying table names.

3. Truncating a Table

 Definition: Removes all rows from a table but retains the table structure for future
use.

 Syntax:

 Copy the code TRUNCATE TABLE table_name;

 Key Points:

o Faster than DELETE as it doesn’t log individual row deletions.

o Cannot be rolled back.

o Resets any auto-increment counters.

TABLE TYPES

In SQL*Plus, which is an Oracle database command-line tool, tables are fundamental

database objects used to store data. Here are the types of tables commonly used in SQL:

1. Permanent Tables

 These are the standard tables that store persistent data.

 Data in these tables remains stored until explicitly deleted.

 Example: CREATE TABLE employees (id NUMBER, name VARCHAR2(50));

2. Temporary Tables

 Used to store data temporarily during a session or transaction.

 Data is automatically deleted when the session ends or the transaction is committed/rolled
back.

 Two types:

o Global Temporary Tables (GTT): Data is session-specific or transaction-specific.

o CREATE GLOBAL TEMPORARY TABLE temp_table (id NUMBER) ON COMMIT

DELETE ROWS;

15
3. External Tables

 Allow access to data stored outside the database (e.g., in flat files).

 Data is not stored in the database but can be queried as if it were in a table.

 Example:

CREATE TABLE external_table

ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_dir
ACCESS PARAMETERS (FIELDS TERMINATED BY ',')
LOCATION ('[Link]'));

4. Index-Organized Tables (IOT)

 Store data in a B-tree index structure rather than a heap.

 Useful for faster retrieval of data based on primary keys.

 Example:

 Copy the codeCREATE TABLE iot_table (id NUMBER PRIMARY KEY, name VARCHAR2(50))
ORGANIZATION INDEX;

5. Partitioned Tables

 Large tables divided into smaller, manageable pieces called partitions.

 Improves performance and manageability for large datasets.

 Example:

CREATE TABLE partitioned_table (id NUMBER, name VARCHAR2(50))

PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (200));

6. Clustered Tables

 Tables that share the same data blocks because they are frequently joined.

 Improves performance for specific queries.

 Example:

CREATE CLUSTER emp_dept_cluster (dept_id NUMBER);

16
SPOOLING

In SQL*Plus, spooling refers to the process of directing the output of SQL queries or
commands to a file, rather than displaying it on the screen. This is particularly useful for
saving query results, logs, or reports for later use or sharing.

Key Points About Spooling:

1. Command: The SPOOL command is used to start and stop spooling.

2. Syntax:
o To start spooling:

o SPOOL file_name

This creates a file (e.g., file_name.lst or file_name.txt) and writes the output to it.

o To stop spooling:

o SPOOL OFF

3. Example:
4. SPOOL [Link];
5. SELECT * FROM employees;
6. SPOOL OFF;
o This saves the query result of SELECT * FROM employees into a file named [Link].

7. File Location: By default, the file is created in the current working directory unless a
full path is specified.
8. Use Cases:
o Generating reports.

o Logging SQL execution results.

o Debugging or auditing SQL scripts.

ERROR CODES

In SQLPlus, error codes are typically associated with Oracle Database errors. These error
codes are numeric identifiers that help diagnose and resolve issues encountered during SQL
execution. Below is an overview of how error codes are structured and used in SQLPlus:

17
1. Structure of Oracle Error Codes

 Oracle error codes are prefixed with ORA-, followed by a five-digit number.

 Example: ORA-00942: table or view does not exist.

2. Common SQL*Plus Error Codes

Here are some frequently encountered error codes in SQL*Plus:

 ORA-00900: Invalid SQL statement

Occurs when the SQL syntax is incorrect or unsupported in SQLPlus.*

 ORA-00942: Table or view does not exist

Happens when you reference a table or view that is not present in the database.
 ORA-01722: Invalid number
Triggered when a non-numeric value is used where a numeric value is expected.
 ORA-00001: Unique constraint violated
Occurs when attempting to insert a duplicate value into a column with a unique
constraint.
 ORA-12154: TNS: could not resolve the connect identifier specified
Happens when the database connection string is incorrect or missing.

3. How to Handle Error Codes

 Check the Error Message: SQL*Plus provides a detailed error message along with the code.
Use this to understand the issue.

 Consult Oracle Documentation: Oracle provides comprehensive documentation for each

error code, explaining causes and solutions.

 Use SHOW ERRORS Command: If you're working with PL/SQL blocks, use SHOW
ERRORS to view compilation errors.

4. Example of an Error in SQL*Plus

SQL> SELECT * FROM non_existent_table;
ORA-00942: table or view does not exist

This error indicates that the table non_existent_table is not found in the database.

18