# Oracle Database/Print version

The current, editable version of this book is available in Wikibooks, the open-content textbooks collection, at
https://en.wikibooks.org/wiki/Oracle_Database

# Introduction

Oracle RDBMS is one of the most used relational database management system. It has been created in 1979 and its request language is called the PL/SQL.

## Installing Oracle

As with most software products, it must be installed; Windows, Linux and Unix versions are available for use, and there are different editions available.
* Express Edition (XE) is free but uses a slightly older version of the Oracle database engine, and has upper RAM and storage limits of 4 GB in mono-processor. It is not available for Unix and needs to register online.
* Standard Edition One removes the basic limitations for storage, and will support multi-cpu systems.
* Standard Edition (SE) provides additional features pertaining to cluster management (Oracle Real Application Clusters, alias Oracle RAC), and may be run on systems containing additional CPUs.
* Enterprise Edition (EE) has no limitations, and may also include optional features that are suitable to large corporations.
* Personal Edition (PE) is not provided for the 12th version. It was a kind of single user Enterprise Edition.
Once downloaded, the .zip file(s) must be extracted, if they are two their folders must be merged (they are named "database"). Then launch to install:
* In Linux, runInstaller.sh.
* In Windows, setup.exe.

### Starting script

Oracle Database launches automatically at each boot, which is useful for a server but slows the system significantly for a PC which isn't supposed to use it in full time. To avoid this we can create a simple script to launch and stop Oracle manually:
* In Linux : remove Oracle from the daemons (eg: /etc/init.d).
* In Windows : execute services.msc, and toggle the services OracleServiceXE and OracleXETNSListener in manual start. When you need to use Oracle, launch as an administrator, the following script Oracle.cmd:
** For the XE version: net start OracleServiceXE net start OracleXETNSListener pause net stop OracleXETNSListener net stop OracleServiceXE
** For the SE version: net start OracleServiceORCL net start OracleDB12Home1TNSListener pause net stop OracleDB12Home1TNSListener net stop OracleServiceORCL
If the message "Access denied" occurs, relaunch the script with a right click, as an administrator.

### Identifying system requirements

The database server needs at least:
* 1 GB free space on a hard drive for XE, 3.5 for SE.
* 1 GB RAM.
* Windows, Linux, Oracle Solaris, or IBM AIX.
Since the version 12c, a 64 bits processor is mandatory.
The environment variables settings are automatic.
The system objects naming follows the Optimal Flexible Architecture (OFA).

## References

* ↑ http://db-engines.com/en/ranking
* ↑ https://docs.oracle.com/html/B13614_01/preinsta.htm#i1067829
* ↑ https://docs.oracle.com/cd/E11882_01/install.112/e47689/appendix_ofa.htm#LADBI1381

# Database Interfaces
## SQL*Plus

SQL*Plus is a command interface provided with the DBMS. On Windows it can be launched either from:
* The start menu, Oracle folder, Run SQL Command Line shortcut.
* The path C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe.
* But the best is to use the environment variable and to connect to the DBMS at the same time, with a shell console. By default it can be done with:
Otherwise if you already have an account, the syntax is:
The first step is to create a user (eg: root):
The second allow him to connect and to confer him the necessary privileges:

## Web interface

A second interface is provided with the DBMS: the web interface. It can be accessed from:
* For SE 12c :
** https://localhost:5500/em/shell
* For XE 11g :
** The start menu, Oracle folder, Get Started shortcut.
** The URL http://localhost:8080/apex/f?p=4950.
* For XE 10g :
** The start menu, Oracle folder, Database control.
** http://localhost:1158/em/console/logon/logon.
Then we must connect to the DBMS:
* User name: sys (sometimes sysman for system manager)
* Password: the one chosen during the installation.
* Connect as: SYSDBA.
The console appears after, allowing to modify the database configuration (service restart, architecture, performances, backups...).
By clicking on Application Express, we can optionally create a new connection user account, which will be used to connect to Oracle. Once logged, it can access to some the database manipulation tools, for example SQL Workshop\SQL Commands to enter some SQL code.
Attention: if you create a first account with the GUI, they will need the default SQL*Plus account for GRANT.

## Oracle SQL Developer

Oracle SQL Developer is an IDE developed in Java. It's provided with SE, but for XE the rich client must be downloaded on http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html.
Once installed and launched, we have to set a connection to let it be authenticated on the Oracle databases. Just fill the account created on the web interface, to access to the data manipulation options.
For example by clicking on the top left on New (the plus icon or CTRL + N), we can open an SQL file to begin to execute some code.

## DBCA

Database Configuration Assistant (DBCA) is a graphical interface available on Windows or the *nixes.

## Hello world

Once one SQL console of those seen previously launched, it becomes possible to execute some PL/SQL (Procedural Language/Structured Query Language): the procedural language created by Oracle, and specific to its relational database.
The slash tells the program to stop the multiline instruction.

## References

* ↑ http://www.snapdba.com/2013/05/creating-an-oracle-11g-database-using-dbca-non-asm/
* ↑ http://docs.oracle.com/cd/B16351_01/doc/server.102/b14196/install003.htm

# Administering Users

Within Oracle, users may be managed through the webpage under the administration section, and within the Database Users subsection.

## Creating and managing database user accounts
### Adding users

New users can be created by an administrator or a user who has a "CREATE USER" privilege using Oracle Enterprise Manager GUI by clicking on the "Create" link in the "Users" section . Enter the username and password for the new user. You can also set the password to expire (where the user must change the password on the next login), and whether an account is locked (to prevent a user from connecting to the system).
In most cases, users should belong to the Connect role. If the user needs to create tables or have more advanced capability, the user should be placed within the Resource role. Database admins should appear in the DBA role. If desired, users may be given individual permissions within Directly Grant Privileges section.
Alternatively, users can also be created using the "CREATE USER" SQL command.
Eg:
After creating the user, the "CONNECT" role needs to be granted for the user to connect to the database.

### Removing users

When a user no longer needs to be present within the database, you click the drop button within the web interface.
To script it:
To remove the attached objects to the user:

## Creating and managing roles

The roles list is available with:
Personalized roles can be created:
Then it's possible to use it with the users:

## Revoking privileges

We can revoke a privilege from a user or a role:

## Modifying privileges

We can change a password with:

## Deleting a role

We can remove a role:

## References

* ↑ https://docs.oracle.com/cd/B14117_01/server.101/b10759/statements_9008.htm
* ↑ https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6012.htm

# Tables
## Architecture

The Oracle architecture considers one database per server, in which we can find several tablespaces, equivalent to the MySQL and MS-SQL databases objects, containing tables and stored procedures.
In the Windows Express version, these data are stored into C:\oraclexe\app\oracle\oradata\XE.
These variables and keywords are not sensible to casing.

## Create tablespaces

Once connected, it's possible to begin to create some tables directly, in the default tablespace. However before, we can add some tablespaces in some defined files:

## Create schemas

A schema is a permission accorded to a set of elements, like tables and stored procedures. The keyword AUTHORIZATION specifies the user name:

## Create tables

Example:
In SQL Developer, with a right click on the tables, New table..., we can generate and execute this creation in an array, which is translated into PL/SQL in the DDL tab:
We can also set the table tablespace, by selecting it into the GUI, or with the keyword TABLESPACE in the creation clause.

### Available data types

The possible column types are:
* Characters:
** CHAR: 2 kB.
** VARCHAR: 4 kB.
** VARCHAR2: 4 kB, synonymous of VARCHAR.
** NCHAR: 2 kB.
** NVARCHAR2: 4 kB.
* Numeric:
** NUMBER.
** BINARY_INTEGER.
** BINARY_FLOAT.
** BINARY_DOUBLE.
* Date:
** DATE.
** TIMESTAMP.
* RAW.
* LONG RAW.
* BLOB.
* CLOB.
* NCLOB.
* ROWID.
* UROWID.
* BFILE.
* XMLType.
* UriType.

## List tables

The following system view can display the system and the users tables:

## Manage tables content
### Insert rows

Multiple rows:
The DUAL table is a special one-row, one-column table present by default in Oracle, used because the SELECT clause needs a FROM clause, but some queries don't require any tables.

### Delete rows
## Read tables
### Structure

To get its structure, we can use either the function desc (description) or the system view ALL_TAB_COLUMNS.

#### desc

If the table doesn't exist, the error which occurs is: ORA-00923: FROM keyword not found where expected.

#### ALL_TAB_COLUMNS
### Content

To get its content:
The number of dashes represents the field size.

## Constraints

In essence, constraints safeguard and validate the data.
Primary Key (PK) and Unique constraints both ensure the data is not duplicated. PK also ensure the data is not null. Oracle will automatically generate index for PK and Unique constraints. A table can only have one PK, but it can have multiple unique constraints.
Foreign Key (FK) ensure the data exists in the column of the parent table it refer to. Each parent record can have multiple child records, but each child can relate to ONLY one parent record. A column with FK may not necessary to have an index.
FK can only refer to column with PK or Unique constraint. Example:
A table can ONLY have one Primary Key, but it can have multiple UNIQUE key. if the child table(s) require to referencing column other than primary key, the column on the parent table must have UNIQUE constraint.
Cannot create PK or Unique on a column contains duplicate data
Insert data into a column with FK, the value must already exist in the column that the FK reference to.
As long as a foreign key exist, the parent table can truncate/delete the data or disable the PK or Unique constraint
Find out the constraint information in Oracle
Disable constraint that have foreign key refer to is not allowed, in order to do this, you have to disable the foreign key first.
If the data in parent table is deleted, re-enable the foreign key that contain data reference to the missing data is not allowed.
Generate a SQL statements to disable all the Foreign Key on a specified table

## Modify tables structure

Example of renaming:
First field values constraint addition:
Primary key addition:
Primary key removal:
Foreign key addition:

## Partitioning

The Oracle partitioning is a process to split a huge table into several smaller ones in order to increase its performance.

### Range

Example:

### Hash

Example:

### List

Example:

### Interval

Example:

### Create and use external tables
## References

* ↑ https://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_6013.htm
* ↑ https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm

# SELECT Statement
## Listing the capabilities of SQL SELECT statements

A SELECT statement retrieves data from database. With a SELECT statement, you can use the following capabilities:
* Projection: select the columns in a table that are returned by a query.
* Selection: select the rows in a table that are returned by a query using certain criteria to restrict the result.
* Joining: bring together data that is stored in different tables by specifying the link between them.

## Executing a basic SELECT statement

* SQL statements are not case-sensitive.
* SQL statements can be entered on one or more lines.
* Keywords like SELECT, FROM cannot be abbreviated or split across lines.
* In SQL Developer, SQL statements can optionally be terminated by a semicolon (;). Semicolons are required when you execute multiple SQL statements.
* In SQL*Plus, you are required to end each SQL statement with a semicolon (;).
* Select All Columns
* Select Specific Columns
* Exclude duplicate rows
* Use Arithmetic Operators
** The operator precedence is the same as normal mathematics, (ie. / * + -)
** Arithmetic expressions containing a null value evaluate to null
* Use Column Heading Defaults
** SQL Developer:
*** Default heading display: Uppercase
*** Default heading alignment: Left-aligned
** SQL*Plus:
*** Default heading display: Uppercase
*** Character and Date column headings: Left-aligned
*** Number column headings: Right-aligned
* Use Column Alias
** Renames a column heading
** AS keyword between the column name and alias is optional
** Requires double quotation marks if it contains spaces, special characters, or case-sensitive
* Literal Character Strings
** Date and character literal values must be enclosed within single quotation marks
** Each character string is output once for each row returned
* Escape the single quote character use two single quotes
* Escape the single quote character use alternative quote (q) operator
* Learn the DESCRIBE command to display the table structure

## Describing various types of conversion functions that are available in SQL

Implicit data type conversion
Implicit conversion occurs when Oracle attempts to convert the values, that do not match the defined parameters of functions, into the required data types.
Explicit data type conversion Explicit conversion occurs when a function like TO_CHAR is invoked to change the data type of a value.

## Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions

* Nest multiple functions
* Apply the NVL, NULLIF, and COALESCE functions to data

## Applying conditional expressions in a SELECT statement

* Use conditional IF THEN ELSE logic in a SELECT statement

## Describing various types of functions available in SQL

* Describe the differences between single row and multiple row functions
Single row functions return one result per row.
Single row functions:
Character functions

## Using character, number, and date functions in SELECT statements

* Manipulate strings with character function in the SELECT and WHERE clauses
* Manipulate numbers with the ROUND, TRUNC and MOD functions
* Perform arithmetic with date data
* Manipulate dates with the date functions

## Generating a Cartesian product of all rows from two or more tables
# Restricting and Sorting Data
## Limiting the rows that are retrieved by a query

* Write queries that contain a WHERE clause to limit the output retrieved
** Character strings and date values are enclosed with single quote
** Character values are case-sensitive and date values are format-sensitive
** The default date display format is DD-MON-YY
** An alias cannot be used in the WHERE clause
* List the comparison operators and logical operators that are used in a WHERE clause
| Operator | Meaning |
| = | Equal to |
| > | Greater than |
| >= | Greater than or equal to |
| < | Less than |
| <= | Less than or equal to |
| <> | Not equal to (can also use != or ^=) |
| BETWEEN ... AND ... | Between two values (inclusive) |
| IN (set) | Match any value in a list |
| LIKE | Match a character pattern '%' - zero or many; '_' - one character |
| IS NULL | is a null value |
| AND | returns TRUE if both conditions are true |
| OR | returns TRUE if either condition is true |
| NOT | returns TRUE if the condition is false |
* Describe the rules of precedence for comparison and logical operators
| Precedence | Operators | Description |
| 1 | parenthesis | Expression within parenthesis always evaluate first |
| 2 | /, * | Division and Multiplication |
| 3 | +, - | Addition and subtraction |
| 4 | || | Concatenation |
| 5 | =, <, >, <=, >= | Equality and inequality comparison |
| 6 | [NOT] LIKE, IS [NOT] NULL, [NOT] IN | Pattern, null, and set comparison |
| 7 | [NOT] BETWEEN | Range comparison |
| 8 | <>, !=, ^= | Not equal to |
| 9 | NOT | NOT logical condition |
| 10 | AND | AND logical condition |
| 11 | OR | OR logical condition |

## Sorting the rows that are retrieved by a query

* Write queries that contain an ORDER BY clause sort the output of a SELECT statement
* Sort output in descending and ascending order

## Using ampersand substitution to restrict and sort output at runtime

Use substitution variables to:
* Temporarily store values with single-ampersand (&) and double-ampersand (&&) substitution
Use substitution variables to supplement the following:
* WHERE conditions
* ORDER BY clauses
* Column expressions
* Table names
* Entire SELECT statements

# Managing Data
## Manipulating data through SQL using INSERT, UPDATE, and DELETE
## Using Data Pump to import data

SQL Dump should either Exports in CSV or Plain SQL Format.

## Creating directory objects
# Views
## Dictionary views
### Explaining the data dictionary
### Adding comments
# SQL
## Retrieving Data Using the SQL SELECT Statement
### List the capabilities of SQL SELECT statements

Selection, projection, join

### Execute a basic SELECT statement

* Select All Columns:
* Select Specific Columns:
* Use Column Heading Defaults
* Use Arithmetic Operators:
* Understand Operator Precedence
* Learn the DESCRIBE command to display the table structure

## Restricting and Sorting Data
### Limit the rows that are retrieved by a query

* Write queries that contain a WHERE clause to limit the output retrieved
* List the comparison operators and logical operators that are used in a WHERE clause
* Describe the rules of precedence for comparison and logical operators
* Use character string literals in the WHERE clause

### Sort the rows that are retrieved by a query

* Write queries that contain an ORDER BY clause sort the output of a SELECT statement
* Sort output in descending and ascending order

### Use ampersand substitution to restrict and sort output at runtime

the ampersand operator is used to take the input at runtime( ex:-&employeename) and if ampersand is used twice i.e && then it will take the input of single ampersand operator and is used to provide data to the query at runtime.

## Using Single-Row Functions to Customize Output
### Describe various types of functions available in SQL

* Describe the differences between single row and multiple row functions

### Use character, number, and date functions in SELECT statements

* Manipulate strings with character function in the SELECT and WHERE clauses
* Manipulate numbers with the ROUND, TRUNC and MOD functions
* Perform arithmetic with date data
* Manipulate dates with the date functions

## Using Conversion Functions and Conditional Expressions
### Describe various types of conversion functions that are available in SQL

Implicit data type conversion
Implicit conversion occurs when Oracle attempts to convert the values, that do not match the defined parameters of functions, into the required data types.
Explicit data type conversion Explicit conversion occurs when a function like TO_CHAR is invoked to change the data type of a value.

### Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions

* Nest multiple functions
* Apply the NVL, NULLIF, and COALESCE functions to data

### Apply conditional expressions in a SELECT statement

* Use conditional IF THEN ELSE logic in a SELECT statement

## Displaying Data from Multiple Tables
### Write SELECT statements to access data from more than one table using equijoins and nonequijoins
### View data that generally does not meet a join condition by using outer joins

* Join a table by using a self join

### Generate a Cartesian product of all rows from two or more tables
## Manipulating Data
### Describe each data manipulation language (DML) statement
### Insert rows into a table

Inserting data in database is done through "insert" command in oracle.
Syntax:
Example:
By the above query the employee table gets populated by empid:-1 , empname:-'Rahul' and empdesignation:-'Manager'.

### Update rows in a table

To update rows in a table, write:
It will update all the rows present in the table by the given value in the selected field.
We can also add queries to this command to make a real use for example,
You can add your query after the where clause according to your need.
Example:

## Controlling transactions

* Save and discard changes with the COMMIT and ROLLBACK statements
* Explain read consistency

## Using DDL Statements to Create and Manage Tables
### Create a simple table

"Create table" command is used to create table in database.
Syntax:
The above Query will create a table named employee with which contain columns empid, empname, empdesignation followed by their datatypes.

### Describe how schema objects work
## Retrieving Data Using Sub-queries
### Write a multiple-column sub-query
### Use scalar sub-queries in SQL

SELECT * FROM TAB

### Use the WITH clause
## Write a multiple-column sub-query
## Hierarchical Query

Hierarchical Query allows you the transverse through a self-reference table and display the Hierarchical structure. eg. the employee table contain the manager id the employee.
list out the whole hierarchical structure of the employees
list out all the employees under manager 'Kochhar'
list out all the manager that 'Lorentz' report to
* pseudocolumn LEVEL -> root = 1, next level=2,3,4,5...etc
* SYS_CONNECT_BY_PATH(col, '/') shows the full path, 2nd parameter is seperator (9i)
* CONNECT_BY_ROOT(col) return the value of the root node in the current hierarchy (10g)
* pseudocolumn CONNECT_BY_ISLEAF return 1 if the return value is at the last node on the Hierarchy (ie. leaf) (10g)
* order SIBLINGS by re-order the sequence of the output and preserve the hierarchical relationship (10g)
* connect by NOCYCLE prior child = parent
** NOCYCLE means stop tranverse the hierarchy at the level when the child reference back to the root. (10g)
** pseudocolumn CONNECT_BY_ISCYCLE evaluate to "1" if the current row references a parent. (10g)

## Regular Expression Support
### Use regular expressions to search for, match, and replace strings

Regular Expression :
| Class | Expression | Description |
| Anchoring Character | ^ | Start of a line |
| Anchoring Character | -$ | End of a line |
| Quantifier Character | * | Match 0 or more times |
| Quantifier Character | + | Match 1 or more times |
| Quantifier Character | ? | Match 0 or 1 time |
| Quantifier Character | {m} | Match exactly m times |
| Quantifier Character | {m,} | Match at least m times |
| Quantifier Character | {m, n} | Match at least m times but no more than n times |
| Quantifier Character | \n | Cause the previous expression to be repeated n times |
| Alternative and Grouping | | Separates alternates, often used with grouping operator |
| Alternative and Grouping | ( ) | Groups subexpression into a unit for alternations, for quantifiers, or for backreferencing (see "Backreferences" section) |
| Alternative and Grouping | [char] | Indicates a character list; most metacharacters inside a character list are understood as literals, with the exception of character classes, and the ^ and - metacharacters |
| Posix Character | [:alnum:] | Alphanumeric characters |
| Posix Character | [:alpha:] | Alphabetic characters |
| Posix Character | [:blank:] | Blank Space Characters |
| Posix Character | [:cntrl:] | Control characters (nonprinting) |
| Posix Character | [:digit:] | Numeric digits |
| Posix Character | [:graph:] | Any [:punct:], [:upper:], [:lower:], and [:digit:] chars |
| Posix Character | [:lower:] | Lowercase alphabetic characters |
| Posix Character | [:print:] | Printable characters |
| Posix Character | [:punct:] | Punctuation characters |
| Posix Character | [:space:] | Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed |
| Posix Character | [:upper:] | Uppercase alphabetic characters |
| Posix Character | [:xdigit:] | Hexidecimal characters |
| Equivalence class | = = | An equivalence classes embedded in brackets that matches a base letter and all of its accented versions. eg, equivalence class '[=a=]' matches ä and â. |
| Match Option | c | Case sensitive matching |
| Match Option | i | Case insensitive matching |
| Match Option | m | Treat source string as multi-line activating Anchor chars |
| Match Option | n | Allow the period (.) to match any newline character |

# PL/SQL
## PL/SQL
### Introduction

PL/SQL stands for Procedural Language extension of SQL. It is a combination of SQL along with the procedural features of programming languages and it enhances the capabilities of SQL by injecting the procedural functionality, like conditional or looping statements, into the set-oriented SQL structure.

### Advantages of PL/SQL

* Procedural Language Capability: PL/SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).
* Block Structures: PL/SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in the database and reused.
* Better Performance: PL/SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.
* Exception Handling: PL/SQL handles exceptions (or errors) effectively during the execution of a PL/SQL program. Once an exception is caught, specific actions can be taken depending upon the type of the exception or it can be displayed to the user with a message.

### Limitation

PL/SQL can only use SELECT, DML(INSERT, UPDATE, DELETE) and TC(COMMIT, ROLLBACK, SAVEPOINT) statements. DDL (CREATE, ALTER, DROP) and DCL(GRANT, REVOKE) cannot be used directly. Any DDL/DCL however, can be executed from PL/SQL when embedded in an EXECUTE IMMEDIATE statement.

### Basic Structure

Each PL/SQL program consists of SQL and PL/SQL statements which form a PL/SQL block. A PL/SQL Block consists of three sections:
Declaration Section: This section is optional and it starts with the reserved keyword DECLARE. This section is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section.
Execution Section: This section is mandatory and it starts with the reserved keyword BEGIN and ends with END. This section is where the program logic is written to perform any task. The programmatic constructs like loops, conditional statement and SQL statements form the part of execution section.
Exception Section: The section is optional and it starts with the reserved keyword EXCEPTION. Any exception in the program can be handled in this section, so that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled, the block terminates abruptly with errors.
Every statement in the above three sections must end with a ; (semicolon). PL/SQL blocks can be nested within other PL/SQL blocks. Comments can be used to document code.
This is how PL/SQL looks.

### PL/SQL Placeholders

Placeholders are temporary storage area. Placeholders can be any of Variables, Constants and Records. Oracle defines placeholders to store data temporarily, which are used to manipulate data during the execution of a PL SQL block.
Depending on the kind of data you want to store, you can define placeholders with a name and a datatype. Few of the datatypes used to define placeholders are as given below.
The placeholders, that store the values, can change through the PL/SQL Block.

#### PL/SQL Variables

The General Syntax to declare a variable is:
* variable_name is the name of the variable.
* datatype is a valid PL/SQL datatype.
* NOT NULL is an optional specification on the variable. If NOT NULL is specified, you must provide the initial value.
* value or DEFAULT value is also an optional specification, where you can initialize a variable.
* Each variable declaration is a separate statement and must be terminated by a semicolon.
The below example declares two variables, one of which is a not null.
The value of a variable can change in the execution or exception section of the PL/SQL Block. We can assign values to variables in the two ways given below.
1) Directly assign value to variable.
2) Assign values to variables directly from the database columns.
The example below will get the salary of an employee with id '12345' and display it on the screen.
NOTE: The slash '/' indicates to execute the above PL/SQL Block.

#### PL/SQL Records

Records are composite datatypes, which contains a combination of different scalar datatypes like char, varchar, number etc. Each scalar data types in the record holds a value. A record can store values of a row in a table.
A datatype can be declared in the same way as you create a table, like col_name_1. If a field is based on a column from database table, you can define the datatype as col_name_2. You can also use %type method to declare datatype of variable and constant. Similar to %type, if all the fields of a record are based on the columns of a table, it can be declared by using %rowtype method.
For Example:
Declaring the record as a ROWTYPE Advantages: 1) Do not need to explicitly declare variables for all the columns in a table. 2) If the column specification in the database table is altered, the code does not need to update.
Disadvantage: 1) When a record is created as a ROWTYPE, fields will be created for all the columns in the table and memory will be used to create the datatype for all the fields.
Assign values to record Similar to variable, you can assign value to record either by direct assign or through the SELECT statements
If the records is declared as ROWTYPE, SELECT * can be used to assign values.
The column value of the record can be retrieved as below syntax

#### Scope of Variables and Records

PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution section of an outer block can contain inner blocks. Variables which are accessible to an outer Block are also accessible to all nested inner blocks; however, the variables declared in the inner blocks are not accessible to the outer blocks.
Based on their declaration we can classify variables into two types.
* Local variables - These are declared in a inner block and cannot be referenced by outer blocks.
* Global variables - These are declared in an outer block and can be referenced by its itself and by its inner blocks.
In the below example, two variables are created in the outer block and assigning their product to the third variable created in the inner block. The variables 'var_num1' and 'var_num2' can be accessed anywhere in the block; however, the variable 'var_result' is declared in the inner block, so it cannot be accessed in the outer block.

#### PL/SQL Constants

As the name implies a constant is a value used in a PL/SQL Block that remains unchanged throughout the program. A constant is a user-defined literal value. You can declare a constant and use it instead of actual value.
For example:
You must assign a value to the constant while declaring it. If you assign a value to the constant later, Oracle will prompt exception.

### PL/SQL Conditional Statements

PL/SQL supports programming language features like conditional statements, iterative statements.
The syntax of conditional statements:
Note: be aware of the keyword ELSIF, there is no 'E' before 'IF'.

### PL/SQL Iterative Statements

An iterative statements are used when you want to repeat the execution of one or more statements for specified number of times. There are three types of loops in PL/SQL:
1. Simple Loop A Simple Loop is used when a set of statements is to be executed at least once before the loop terminates. An EXIT condition must be specified in the loop, otherwise the loop will get into an infinite number of iterations. When the EXIT condition is satisfied the process exits from the loop.
Note: a) Initialize a variable before the loop body. b) Increment the variable in the loop. c) Use a EXIT WHEN statement to exit from the Loop. If you use a EXIT statement without WHEN condition, the statements in the loop is executed only once.
2. While Loop A WHILE LOOP is used when a set of statements has to be executed as long as a condition is true. The condition is evaluated at the beginning of each iteration. The iteration continues until the condition becomes false.
Note: a) Initialize a variable before the loop body. b) Increment the variable in the loop. c) EXIT WHEN statement and EXIT statements can be used in while loops it is seldom used.
3. FOR Loop A FOR LOOP is used to execute a set of statements for a pre-determined number of times. Iteration occurs between the start and end integer values given. The counter is always incremented by 1. The loop exits when the counter reaches the value of the end integer.
Note: a) The counter variable is implicitly declared in the declaration section, so it's not necessary to declare it explicitly. b) The counter variable is incremented by 1 and does not need to be incremented explicitly. c) EXIT WHEN statement and EXIT statements can be used in FOR loops but it is seldom used.

### PL/SQL Cursors

A cursor contains information on a select statement and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.
There are two types of cursors in PL/SQL:

#### Implicit cursor:

When you execute DML statements like DELETE, INSERT, UPDATE and SELECT..INTO statements, implicit statements are created to process these statements.
Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN.
For example, When you execute INSERT, UPDATE, or DELETE statements the cursor attributes tell us whether any rows are affected and how many have been affected. When a SELECT... INTO statement is executed in a PL/SQL Block, implicit cursor attributes can be used to find out whether any row has been returned by the SELECT statement. PL/SQL returns an error when no data is selected.
The status of the cursor for each of these attributes are defined in the below table.
| Attribute | Return Value |
| SQL%FOUND | The return value is TRUE, if the DML statements like INSERT, DELETE and UPDATE affect at least one row and if SELECT ….INTO statement return at least one row. The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE do not affect row and if SELECT….INTO statement do not return a row. |
| SQL%NOTFOUND | The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE at least one row and if SELECT ….INTO statement return at least one row. The return value is TRUE, if a DML statement like INSERT, DELETE and UPDATE do not affect even one row and if SELECT ….INTO statement does not return a row. |
| SQL%ROWCOUNT | Return the number of rows affected by the DML operations INSERT, DELETE, UPDATE, SELECT |
| SQL%ISOPEN | Always return FALSE |
uses implicit cursor attributes:
In the above PL/SQL Block, the salaries of all the employees in the 'employee' table are updated. If none of the employee's salary are updated we get a message 'None of the salaries where updated'. Else we get a message like for example, 'Salaries for 100 employees are updated' if there are 100 rows in 'employee' table.

#### Explicit cursor:

An explicit cursor is defined in the declaration section of the PL/SQL Block. It must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.
There are four steps in using an Explicit Cursor.
* DECLARE the cursor in the declaration section.
* OPEN the cursor in the Execution Section.
* FETCH the data from cursor into PL/SQL variables or records in the Execution Section.
* CLOSE the cursor in the Execution Section before you end the PL/SQL Block.
Declaration
For example:
Using Cursor When a cursor is opened, the first row becomes the current row. When the data is fetched it is copied to the record or variables and the logical pointer moves to the next row and it becomes the current row. On every fetch statement, the pointer moves to the next row. If you want to fetch after the last row, the program will throw an error. When there is more than one row in a cursor we can use loops along with explicit cursor attributes to fetch all the records.
Note:
* We can fetch the rows in a cursor to a PL/SQL Record or a list of variables created in the PL/SQL Block.
* If you are fetching a cursor to a PL/SQL Record, the record should have the same structure as the cursor.
* If you are fetching a cursor to a list of variables, the variables should be listed in the same order in the fetch statement as the columns are present in the cursor.
* When we try to open a cursor which is not closed in the previous operation, it throws exception.
* When we try to fetch a cursor after the last operation, it throws exception.
For Example:
Oracle provides some attributes known as Explicit Cursor Attributes to control the data processing while using cursors. We use these attributes to avoid errors while accessing cursors through OPEN, FETCH and CLOSE Statements.
| Attributes | Return Values |
| Cursor_name%FOUND | TRUE, if fetch statement returns at least one row. FALSE, if fetch statement doesn’t return a row. |
| Cursor_name%NOTFOUND | TRUE, , if fetch statement doesn’t return a row. FALSE, if fetch statement returns at least one row. |
| Cursor_name%ROWCOUNT | The number of rows fetched by the fetch statement. If no row is returned, the PL/SQL statement returns an error. |
| Cursor_name%ISOPEN | TRUE, if the cursor is already open in the program. FALSE, if the cursor is not opened in the program. |
Cursor with a Simple Loop:
The cursor attribute %ISOPEN is used to check if the cursor is open, if the condition is true the program does not open the cursor again. The cursor attribute %NOTFOUND is used to check whether the fetch returned any row. If there is no row found, the program would exit. Typically, when the cursor reach the last row, no more row can be fetched.
Cursor with a While Loop:
Using %FOUND to evaluate if the first fetch statement returned a row, if TRUE, the program moves into the while loop. Inside the loop, use fetch statement again to process the next row. If the fetch statement is not executed once before the while loop, the while condition will return false in the first instance and the while loop is skipped.
Cursor with a FOR Loop: When using FOR LOOP, you do not need to declare a record or variables to store the cursor values, do not need to open, fetch and close the cursor. These functions are accomplished by the FOR LOOP automatically.
When the FOR loop is processed a record 'rec_emp' of structure 'cur_emp' gets created, the cursor is opened, the rows are fetched to the record 'rec_emp' and the cursor is closed after the last row is processed. By using FOR Loop, you can reduce the number of lines in the program.

### PL/SQL Procedures

When calling store procedure from the PL/SQL Block, you simply use the store procedure name to call. If you prefix the 'EXECUTE' keyword in front of the store procedure name, you will receive an error.

### PL/SQL Triggers
# XML Management

Oracle possesses a variety of powerful XML features. A tremendous amount of documentation exists regarding Oracle's XML features. This resource is intended to be a cheat sheet for those of us who don't have time to wade through the hundreds of pages of documentation, but instead wish to quickly understand how to create simple XML output and input XML into a database.

## DBMS_XMLGEN
### Functions
#### getXML

Gets the XML document by fetching the maximum number of rows specified. It appends the XML document to the CLOB passed in. Use this version of GETXML Functions to avoid any extra CLOB copies and to reuse the same CLOB for subsequent calls. Because of the CLOB reuse, this GETXML Functions call is potentially more efficient.
Syntax:
Generates the XML document and returns it as a temporary CLOB. The temporary CLOB obtained from this function must be freed using the DBMS_LOB.FREETEMPORARY call:
Converts the results from the SQL query string to XML format, and returns the XML as a temporary CLOB, which must be subsequently freed using the DBMS_LOB.FREETEMPORARY call:
Example:
The following procedure parses the fields in the employee table into XML and saves the XML as CLOB rows in a table.

#### setRowSetTag

Sets the name of the root element of the document. The default name is ROWSET. Setting the rowSetTag to NULL will stop this element from being output. An error is produced if both the row and the rowset are NULL and there is more than one column or row in the output. The error is produced because the generated XML would not have a top-level enclosing tag.
Syntax:
Example:
Sample output:
This encloses the entire XML result set in the tag specified by the second parameter.

#### setRowTag

This function sets the name of the element each row. The default name is ROW. Setting this to NULL suppresses the ROW element itself. This produces an error if both the row and the rowset are NULL and there is more than one column or row in the output. The error is returned because the generated XML must have a top-level enclosing tag.
Syntax:
Example:
This tells the XML generator to enclose the columns of each row in an AUTHOR tag.
Sample output:
Every row output is now enclosed inside the AUTHOR tag.

### Examples
#### Dumping a Query Result as XML

Sample procedure for dumping the results of an SQL query as XML.
The returned XML results will look similar to the following:

# Multimedia Databases
## Description

Oracle Multimedia is a services suite provided with Oracle Database (excluding the Express version where it can't be added) since the version 8 (in 1997), to manage the multimedia databases.
It's composed by the package ORDSYS ("ORD" for object-relational data) allowing the multimedia objects management into the database. This package includes several classes:
* ORDMultimedia: abstract superclass storage the common attributes and methods to the classes ORDAudio, ORDImage, and ORDVideo.
* ORDAudio: sound properties storage.
* ORDDoc: heterogeneous properties storage.
* ORDImage: images properties storage.
* ORDVideo: videos properties storage.
* ORDSource: multimedia BLOB or BFILE (accessible in HTTP) properties storage.
* DICOM (Digital Imaging and Communications in Medicine).
Attributes :
| ORDAudio | ORDDoc | ORDImage | ORDVideo |
| description | source | source | description |
| source | format | height | source |
| format | mimeType | width | format |
| mimeType | contentLength | contentLength | mimeType |
| comments | comments | fileFormat | comments |
| encoding | | contentFormat | width |
| numberOfChannels | | compressionFormat | height |
| sampleSize | | mimeType | frameResolution |
| compressionType | | | frameRate |
| audioDuration | | | videoDuration |
| | | | numberOfFrames |
| | | | compressionType |
| | | | numberOfColors |
| | | | bitRate |

## Oracle HTTP Server

Oracle HTTP Server (OHS) allows to execute PL/SQL requests from a navigator. It's a Web Tier component of the Oracle Fusion Middleware, based on Apache 2.

## References

* ↑ "Managing Oracle Multimedia Installations".
* ↑ "Gestion avancée d'image sous Oracle avec Java" (in French).
* ↑ "Common Methods and Notes for Oracle Multimedia Object Types". {{cite web}}: Unknown parameter |site= ignored (help)
* ↑ "Common Methods and Notes for Oracle Multimedia Object Types". {{cite web}}: Unknown parameter |site= ignored (help)
* ↑ Lynne Dunckley, Larry Guros (8 avril 2011). Digital Press (ed.). Oracle 10g Developing Media Rich Applications. {{cite book}}: Check date values in: |date= (help)CS1 maint: uses authors parameter (link)
* ↑ "Medical Imaging and Communication". {{cite web}}: Unknown parameter |site= ignored (help)
* ↑ "ORDAudio". {{cite web}}: Unknown parameter |site= ignored (help)
* ↑ "ORDDoc". {{cite web}}: Unknown parameter |site= ignored (help)
* ↑ "ORDImage". {{cite web}}: Unknown parameter |site= ignored (help)
* ↑ "ORDVideo". {{cite web}}: Unknown parameter |site= ignored (help)
* "Multimedia User's Guide". {{cite web}}: Unknown parameter |site= ignored (help)

# Spatiotemporal Databases
## Spatial data

When typing the fields, some represent graphical objects, and so are considered as "Spatial" (cf. spatial database). Consequently, they are manipulated with different requests than for the text.
With Oracle, its implemented since the version 7, in an extension of the Enterprise Edition, provided objects with the prefix SDO for Spatial Data Option.

## Objects

To store the spatial objects, we use the field type SDO_GEOMETRY, and the seven methods to manipulate it:
* Get_Dims
* Get_GType
* Get_LRS_Dim
* Get_WKB
* Get_WKT
* ST_CoordDim
* ST_IsValid
Then the request operators:
* SDO_FILTER: list the objects which interact with the target.
* SDO_JOIN: spatial join.
* SDO_NN: target nearest neighbor.
* SDO_NN_DISTANCE: distance with the nearest neighbor.
* SDO_RELATE: list the objects which interact in a certain manner.
* SDO_WITHIN_DISTANCE: returns true if two objects are within a certain distance from one to another.

## Spatiotemporal data

We use a predicate to foresee the stored objects movement. However, the spatiotemporal databases need frequent updates.

## Indexation

Les modes d'indexation choisis par Oracle pour les données spatiales sont l'arbre R, l'arbre Q, et le Z-order.

## Link with the GIS

To represent the data on maps, we use a geographic information system (GIS). For example:
* GRASS GIS
* QGIS
If the software need an ODBC data source to access to the Oracle databases:
* Launch %windir%\system32\odbcad32.exe.
* Add a system source. The Oracle driver can be chosen in the list if the DBMS is installed.
* Fill the TNS service name with the name which can be found into C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tnsnames.ora.
* Then write the password of the connection created with SQL*Plus.

## Examples

* http://download.oracle.com/otndocs/products/spatial/pdf/au_melbourne06_start.pdf
* http://download.oracle.com/otndocs/products/spatial/pdf/GeocodingInOracleUsing_HERE_MapContent.pdf

## References

* ↑ "Spatial Developer's Guide". {{cite web}}: Unknown parameter |site= ignored (help)
* ↑ "SDO_GEOMETRY Object Type". {{cite web}}: Unknown parameter |site= ignored (help)
* ↑ "Spatial Operators". {{cite web}}: Unknown parameter |site= ignored (help)
* ↑ "Authorizing Access to Dynamic Spatial-Temporal Data". {{cite web}}: Unknown parameter |site= ignored (help)
* ↑ "Spatial Concepts". {{cite web}}: Unknown parameter |site= ignored (help)
* ↑ "ZOrder Method". {{cite web}}: Unknown parameter |site= ignored (help)
* ↑ "Geographic Resources Analysis Support System (GRASS): More Than a Mapping Tool". {{cite web}}: Unknown parameter |site= ignored (help)

# 10g Advanced SQL

This documentation details the usage of the latest query methodology on the Oracle 10g DBMS.

# Joins

Join queries combine rows from two or more tables, views, or materialized views. If multiple tables are listed in the query's FROM clause the Oracle Database performs a join. Columns from any of the tables may be listed in the select list. Columns that exist in both tables, however, must be qualified, in order to avoid ambiguity.
The following query returns the mortgage information for all payments received from customers during the year 2007.
The other way of writing the same query can be

## NATURAL JOIN

The NATURAL JOIN joins two tables which contain a column or multiple columns with the same name and data-type.
The following query joins the customer table to the invoice table with a natural join, the natural join utilizes the customer_id that is present on both the customer table and the invoice table. It returns the customer and invoice data for invoices that have not had any payments made on them.

## INNER JOIN

Most of the commonly used joins are actually INNER JOINs. The INNER JOIN joins two or more tables, returning only the rows that satisfy the JOIN condition. Here are some examples of INNER JOINs.
This joins the customer and order table, connecting the customers to their orders. The result contains a combined list of customers and their orders, if a customer does not have an order, they are omitted from the result.
The Other way of writing query is

## OUTER JOIN

The OUTER JOIN joins two or more tables, returning all values whether or not the join condition is met. When a value exists in one table but not the other, nulls are used in the place of the columns that are joined to a record without a JOIN companion.
There are three specific types of outer joins: FULL OUTER JOIN, LEFT OUTER JOIN and RIGHT OUTER JOIN.

### FULL OUTER JOIN

With the FULL OUTER JOIN the query will return rows from either of the tables joined, whether or not there is any matching data on the table joined. If no matching data exists, nulls are placed into the fields where data would have otherwise existed.
In the following example, the data in a table is synced with the data that is regularly imported into a data import table via SQL Loader. A stored procedure is then used to see if anything was added, updated or removed and the rows are merged accordingly.

### LEFT OUTER JOIN

With the LEFT OUTER JOIN the query will return rows only if the row exists in the table specified on the left side of the join. When no matching data is found from the table on the right side of the join, nulls are placed into the fields where the data would have otherwise existed.
The following example will return all of the customers and their associated cases if they have one. If the customer has no case then it will only return the data for the customer.

### RIGHT OUTER JOIN

With the RIGHT OUTER JOIN the query will return rows only if the row exists in the table specified on the right side of the join. When no matching data is found from the table on the left side of the join, nulls are placed into the fields where the data would have otherwise existed.
The following example will return a list of trucks and their cargo. If a truck has no cargo then a null will be put in place of the field specifying the cargo's load_id.

# Subqueries
## Operators
### UNION [ALL]

The UNION operator outputs the items that exist in both result sets. The UNION ALL operator outputs all of the items in the two sets, whether or not both sets contain the item.
The following query returns all customers from San Francisco whose balance is 100000 and 500000.

### MINUS

The query after the MINUS operator is removed from the result set of the queries before the operator.
In the following example, the first part of the query gets all of the customers. In the second part of the inactive customers are taken out. Finally, in the third part of the query, customers with zip codes between 80000 and 90000 removed from the set.

### INTERSECT

The INTERSECT operator only returns the results that are present in both of the queries.
The following example returns all of the customers who have a balance due in Los Angeles.

# Case Statements

The following queries are equivalent, they return all of the customers from Switzerland. The CASE statement translates the single character status flags "A" and "I" to "ACTIVE" and "INACTIVE" If a value is NULL then it returns the string "NULL"

## Basic Usage

The simplest form of a CASE statement specifies the variable and then the possible values to check for.

## Searched Case

The searched CASE expression is the more advanced form of case. Instead of specifying the value to be checked at the beginning, each WHEN statement has a comparison that is checked.

# Regular Expression Support
## Use regular expressions to search for, match, and replace strings

Regular Expression :
| Class | Expression | Description |
| Anchoring Character | ^ | Start of a line |
| Anchoring Character | $ | End of a line |
| Quantifier Character | * | Match 0 or more times |
| Quantifier Character | + | Match 1 or more times |
| Quantifier Character | ? | Match 0 or 1 time |
| Quantifier Character | {m} | Match exactly m times |
| Quantifier Character | {m,} | Match at least m times |
| Quantifier Character | {m, n} | Match at least m times but no more than n times |
| Quantifier Character | \n | Cause the previous expression to be repeated n times |
| Alternative and Grouping | | | Separates alternates, often used with grouping operator |
| Alternative and Grouping | ( ) | Groups subexpression into a unit for alternations, for quantifiers, or for backreferencing (see "Backreferences" section) |
| Alternative and Grouping | [char] | Indicates a character list; most metacharacters inside a character list are understood as literals, with the exception of character classes, and the ^ and - metacharacters |
| Posix Character | [:alnum:] | Alphanumeric characters |
| Posix Character | [:alpha:] | Alphabetic characters |
| Posix Character | [:blank:] | Blank Space Characters |
| Posix Character | [:cntrl:] | Control characters (nonprinting) |
| Posix Character | [:digit:] | Numeric digits |
| Posix Character | [:graph:] | Any [:punct:], [:upper:], [:lower:], and [:digit:] chars |
| Posix Character | [:lower:] | Lowercase alphabetic characters |
| Posix Character | [:print:] | Printable characters |
| Posix Character | [:punct:] | Punctuation characters |
| Posix Character | [:space:] | Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed |
| Posix Character | [:upper:] | Uppercase alphabetic characters |
| Posix Character | [:xdigit:] | Hexidecimal characters |
| Equivalence class | = = | An equivalence classes embedded in brackets that matches a base letter and all of its accented versions. eg, equivalence class '[=a=]' matches ä and â. |
| Match Option | c | Case sensitive matching |
| Match Option | i | Case insensitive matching |
| Match Option | m | Treat source string as multi-line activating Anchor chars |
| Match Option | n | Allow the period (.) to match any newline character |
| x | ignore white space characters |

### REGEXP_LIKE

REGEXP_LIKE performs complex regular expression pattern matching and supports much greater range of string patterns than LIKE. this function is introduced in 10g.
last name begin with T and the 2nd character is either 'o' or 'u'
last name begin with 'T' and end with 'r'
first name is either 'Steven' or 'Stephen'
last name contain double vowel characters (ie. 'aa', 'ee', 'ii', 'oo', 'uu') and the matching is non-case sensitive

### REGEXP_INSTR

REGEXP_INSTR performs complex regular expression pattern matching and supports much greater range of string patterns than INSTR. this function is introduced in 10g.
show the position of the 1st lowercase vowel characters

### REGEXP_SUBSTR

REGEXP_SUBSTR performs complex regular expression pattern matching and supports much greater range of string patterns than SUBSTR. this function is introduced in 10g.
extract the 1st character if the last name start with 'A' or 'C'
Start at 3rd position, extract 2 characters from the last name

### REGEXP_COUNT

REGEXP_COUNT performs count against a value and it is different from the aggregate COUNT function. This function is introduced in 11g.
find the occurrences of the vowel pattern in the last name

# Shared Servers
## Identifying when to use Oracle Shared Servers

A shared server can treat several users processes.

## References

* ↑ https://docs.oracle.com/cd/B28359_01/server.111/b28310/manproc001.htm

# Performance Monitoring

On the oracle database Web administration tool, there is a usage monitor shown on the right-hand side of the screen. This displays both the Storage used by Oracle and the current Memory in use. It also lists the total sessions and users.
For a more detailed view of server settings, Enter the Administration section, and open the Monitor.
* Sessions displays a list of users currently connected to the database.
* System Statistics displays the current resources used by the database. It can also be set to display changes within the statistics from a given reference point.
* Top SQL displays a list of previous SQL statements that are the most resource intensive.
* Long Operations displays the status of operations that are taking more than 6 seconds.

## Reacting to performance issues
# Proactive Maintenance
## Describing server-generated alerts
# Undo Management
## Sizing the undo tablespace
# Monitoring and Resolving Lock Conflicts
## Explaining lock modes
# Backup and Recovery Concepts
## Recovering from loss of a control file

Using RMAN (the Recovery Manager provided since Oracle 8):
If flash recovery area is configured and control file auto backup is on then:
This will restore the control file to the location specified by the initialization parameter CONTROL_FILES mentioned in initialization parameter.
If flash recovery area is configured and control file auto backup is off then:
This will restore the control file to the location specified by the initialization parameter CONTROL_FILES mentioned in initialization parameter.
If restoration is being done using recovery catalog then:
This will restore the control file to the location specified by the initialization parameter CONTROL_FILES mentioned in initialization parameter.
If no flash recovery area is configured, no recovery catalog is available and RMAN backup piece is available at default location then:

## Recovering from loss of a non–system-critical datafile
# SQL Cheatsheet

This "cheat sheet" covers most of the basic functionality that an Oracle DBA needs to run basic queries and perform basic tasks. It also contains information that a PL/SQL programmer frequently uses to write stored procedures. The resource is useful as a primer for individuals who are new to Oracle, or as a reference for those who are experienced at using Oracle.
A great deal of information about Oracle exists throughout the net. We developed this resource to make it easier for programmers and DBAs to find most of the basics in one place. Topics beyond the scope of a "cheatsheet" generally provide a link to further research.
Other Oracle References
* Oracle XML Reference—the XML reference is still in its infancy, but is coming along nicely.

### SELECT

The SELECT statement is used to retrieve rows selected from one or more tables, object tables, views, object views, or materialized views.

### SELECT INTO

Select into takes the values name, address and phone number out of the table employee, and places them into the variables v_employee_name, v_employee_address, and v_employee_phone_number.
This only works if the query matches a single item. If the query returns no rows it raises the NO_DATA_FOUND built-in exception. If your query returns more than one row, Oracle raises the exception TOO_MANY_ROWS.

### INSERT

The INSERT statement adds one or more new rows of data to a database table.
insert using the VALUES keyword
insert using a SELECT statement

### DELETE

The DELETE statement is used to delete rows in a table.
deletes rows that match the criteria

### UPDATE

The UPDATE statement is used to update rows in a table.
updates the entire column of that table
updates the specific record of the table eg:
updates the column invoice as paid when paid column has more than zero.

### SEQUENCES

Sequences are database objects that multiple users can use to generate unique integers. The sequence generator generates sequential numbers, which can help automatically generate unique primary keys, and coordinate keys across multiple rows or tables.

#### CREATE SEQUENCE

The syntax for a sequence is:
For example:

#### ALTER SEQUENCE

Increment a sequence by a certain amount:
Change the maximum value of a sequence:
Set the sequence to cycle or not cycle:
Configure the sequence to cache a value:
Set whether or not to return the values in order

### Generate query from a string

It is sometimes necessary to create a query from a string. That is, if the programmer wants to create a query at run time (generate an Oracle query on the fly), based on a particular set of circumstances, etc.
Care should be taken not to insert user-supplied data directly into a dynamic query string, without first vetting the data very strictly for SQL escape characters; otherwise you run a significant risk of enabling data-injection hacks on your code.
Here is a very simple example of how a dynamic query is done. There are, of course, many different ways to do this; this is just an example of the functionality.

### String operations
#### Length

Length returns an integer representing the length of a given string. It can be referred to as: length b, length c, length 2, and length 4.

#### Instr

Instr (in string) returns an integer that specifies the location of a sub-string within a string. The programmer can specify which appearance of the string they want to detect, as well as a starting position. An unsuccessful search returns 0.

#### Replace

Replace looks through a string, replacing one string with another. If no other string is specified, it removes the string specified in the replacement string parameter.

#### Substr

Substr (substring) returns a portion of the given string. The "start_position" is 1-based, not 0-based. If "start_position" is negative, substr counts from the end of the string. If "length" is not given, substr defaults to the remaining length of the string.
substr( string, start_position [, length])
    returns "pl/sql" since the "p" in "pl/sql" is in the 8th position in the string (counting from 1 at the "o" in "oracle")
    returns "cheatsheet" since "c" is in the 15th position in the string and "t" is the last character in the string.
    returns "cheat" since "c" is the 10th character in the string, counting from the end of the string with "t" as position 1.

#### Trim

These functions can be used to filter unwanted characters from strings. By default they remove spaces, but a character set can be specified for removal as well.

## DDL SQL
### Tables
#### Create table

The syntax to create a table is:
For example:

#### Add column

The syntax to add a column is:
For example:

#### Modify column

The syntax to modify a column is:
ALTER table syntax and examples:
For example:

#### Drop column

The syntax to drop a column is:
For example:

#### Constraints
##### Constraint types and codes

| Type Code | Type Description | Acts On Level |
| C | Check on a table | Column |
| O | Read Only on a view | Object |
| P | Primary Key | Object |
| R | Referential AKA Foreign Key | Column |
| U | Unique Key | Column |
| V | Check Option on a view | Object |

##### Displaying constraints

The following statement shows all constraints in the system:

##### Selecting referential constraints

The following statement shows all referential constraints (foreign keys) with both source and destination table/column couples:

##### Setting constraints on a table

The syntax for creating a check constraint using a CREATE TABLE statement is:
For example:

##### Unique Index on a table

The syntax for creating a unique constraint using a CREATE TABLE statement is:
For example:

##### Adding unique constraints

The syntax for a unique constraint is:
For example:

##### Adding foreign constraints

The syntax for a foregin constraint is:
For example:

##### Deleting constraints

The syntax for dropping (removing) a constraint is:
For example:

### INDEXES

An index is a method that retrieves records with greater efficiency. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

#### Create an index

The syntax for creating an index is:
UNIQUE indicates that the combination of values in the indexed columns must be unique.
COMPUTE STATISTICS tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose an optimal execution plan when the statements are executed.
For example:
In this example, an index has been created on the customer table called customer_idx. It consists of only of the customer_name field.
The following creates an index with more than one field:
The following collects statistics upon creation of the index:

#### Create a function-based index

In Oracle, you are not restricted to creating indexes on only columns. You can create function-based indexes.
The syntax that creates a function-based index is:
For example:
An index, based on the uppercase evaluation of the customer_name field, has been created.
To assure that the Oracle optimizer uses this index when executing your SQL statements, be sure that UPPER(customer_name) does not evaluate to a NULL value. To ensure this, add UPPER(customer_name) IS NOT NULL to your WHERE clause as follows:

#### Rename an Index

The syntax for renaming an index is:
For example:
In this example, customer_id is renamed to new_customer_id.

#### Collect statistics on an index

If you need to collect statistics on the index after it is first created or you want to update the statistics, you can always use the ALTER INDEX command to collect statistics. You collect statistics so that oracle can use the indexes in an effective manner. This recalcultes the table size, number of rows, blocks, segments and update the dictionary tables so that oracle can use the data effectively while choosing the execution plan.
The syntax for collecting statistics on an index is:
For example:
In this example, statistics are collected for the index called customer_idx.

#### Drop an index

The syntax for dropping an index is:
For example:
In this example, the customer_idx is dropped.

## DBA Related
### User Management
#### Creating a user

The syntax for creating a user is:
For example:

#### Granting privileges

The syntax for granting privileges is:
For example:

#### Change password

The syntax for changing user password is:
For example:

### Importing and exporting

There are two methods of backing up and restoring database tables and data. The 'exp' and 'imp' tools are simpler tools geared towards smaller databases. If database structures become more complex or are very large ( > 50 GB for example) then using the RMAN tool is more appropriate.

#### Import a dump file using IMP

This command is used to import Oracle tables and table data from a *.dmp file created by the 'exp' tool. Remember that this a command that is executed from the command line through $ORACLE_HOME/bin and not within SQL*Plus.
The syntax for importing a dump file is:
There are number of parameters you can use for keywords.
To view all the keywords:
An example:

## PL/SQL
### Operators
#### Arithmetic operators

* Addition: +
* Subtraction: -
* Multiplication: *
* Division: /
* Power (PL/SQL only): **

##### Examples

gives all employees from customer id 5 a 5% raise
determines the after tax wage for all employees

#### Comparison operators

* Greater Than: >
* Greater Than or Equal To: >=
* Less Than: <
* Less Than or Equal to: <=
* Equivalence: =
* Inequality: != ^= <> ¬= (depends on platform)

##### Examples
#### String operators

* Concatenate: ||
create or replace procedure addtest( a in varchar2(100), b in varchar2(100), c out varchar2(200) ) IS begin C:=concat(a,'-',b);

#### Date operators

* Addition: +
* Subtraction: -

### Types
#### Basic PL/SQL Types

Scalar type (defined in package STANDARD): NUMBER, CHAR, VARCHAR2, BOOLEAN, BINARY_INTEGER, LONG\LONG RAW, DATE, TIMESTAMP and its family including intervals)
Composite types (user-defined types): TABLE, RECORD, NESTED TABLE and VARRAY
LOB datatypes : used to store an unstructured large amount of data

#### %TYPE – anchored type variable declaration

The syntax for anchored type declarations is
For example
Note:
* Anchored variables allow for the automatic synchronization of the type of anchored variable with the type of <obj> when there is a change to the <obj> type.
* Anchored types are evaluated at compile time, so recompile the program to reflect the change of <obj> type in the anchored variable.

#### Collections

A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.
There are many good reasons to use collections.
* Dramatically faster execution speed, thanks to transparent performance boosts including a new optimizing compiler, better integrated native compilation, and new datatypes that help out with number-crunching applications.
* The FORALL statement, made even more flexible and useful. For example, FORALL now supports nonconsecutive indexes.
* Regular expressions are available in PL/SQL in the form of three new functions (REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR) and the REGEXP_LIKE operator for comparisons.
* Collections, improved to include such things as collection comparison for equality and support for set operations on nested tables.

## References

* ↑ http://www.psoug.org/reference/constraints.html
* ↑ "First Expressions" by Jonathan Gennick for more information in this issue
* Taking Up Collections
* Oracle Programming with PL/SQL Collections

### Stored logic
#### Functions

A function must return a value to the caller.
The syntax for a function is
For example:

#### Procedures

A procedure differs from a function in that it must not return a value to the caller.
The syntax for a procedure is:
When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:
* IN – The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
* OUT – The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
* IN OUT – The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.
Also you can declare a DEFAULT value;
The following is a simple example of a procedure:

#### Passing parameters to stored logic

There are three basic syntaxes for passing parameters to a stored procedure: positional notation, named notation and mixed notation.
The following examples call this procedure for each of the basic syntaxes for parameter passing:

##### Positional notation

Specify the same parameters in the same order as they are declared in the procedure. This notation is compact, but if you specify the parameters (especially literals) in the wrong order, the bug can be hard to detect. You must change your code if the procedure's parameter list changes.

##### Named notation

Specify the name of each parameter along with its value. An arrow (=>) serves as the association operator. The order of the parameters is not significant. This notation is more verbose, but makes your code easier to read and maintain. You can sometimes avoid changing code if the procedure's parameter list changes, for example if the parameters are reordered or a new optional parameter is added. Named notation is a good practice to use for any code that calls someone else's API, or defines an API for someone else to use.

##### Mixed notation

Specify the first parameters with positional notation, then switch to named notation for the last parameters. You can use this notation to call procedures that have some required parameters, followed by some optional parameters.

#### Table functions
### Flow control
##### Conditional Operators

* and: AND
* or: OR
* not: NOT

##### Example

IF salary > 40000 AND salary <= 70000 THEN ELSE IF salary>70000 AND salary<=100000 THEN ELSE

#### If/then/else
### Arrays
##### Associative arrays

* Strongly typed arrays, useful as in-memory tables

##### Example

* Very simple example, the index is the key to accessing the array so there is no need to loop through the whole table unless you intend to use data from every line of the array.
* The index can also be a numeric value.
* More complex example, using a record

## APEX

Oracle Application Express aka APEX, is a web-based software development environment that runs on an Oracle database.

### String substitution

* In SQL: :VARIABLE
* In PL/SQL: V('VARIABLE') or NV('VARIABLE')
* In text: &VARIABLE.

## References

* http://www.psoug.org/reference/